August 28, 2011 at 8:56 am
Hi -
I have a VB Script where I write Longitude and Latitude to VARIABLES and then pass those Variables back to SQL to be inserted into a table. In my VB Script and my SSIS package I have the Variables set to String, since SSIS does not allow the variables to be set to decimals. The Longitude and Latitude are reading in fine in SSIS. I can see this through a MessageBox. However, when it gets into my SQL table. I am getting made up values at the end of my decimal. ie -83.911
becomes -83.9110873256980
If any one has any suggestions, please let me know.
CREATE PROCEDURE [Lti].[MergeAddresses] --@AllGeoCoded BIT OUTPUT,
@EtlLoadId INT = 0 ,
@Data_AddressId INT ,
@Data_Address VARCHAR(55) ,
@Data_City VARCHAR(30) ,
@Data_State VARCHAR(2) ,
@Data_Zip VARCHAR(5) ,
@Data_Lat DECIMAL(20,15),
@Data_Lon DECIMAL(20,15)
WITH RECOMPILE
AS
BEGIN
SET nocount ON
DECLARE @ImportLoadId INT
DECLARE @AddressId INT
DECLARE @Address VARCHAR(55)
DECLARE @City VARCHAR(30)
DECLARE @State VARCHAR(2)
DECLARE @Zip VARCHAR(5)
DECLARE @Latitude DECIMAL(20,15)
DECLARE @Longitude DECIMAL(20,15)
SET @ImportLoadId = @EtlLoadId
SET @AddressId = @Data_AddressId
SET @Address = @Data_Address
SET @City = @Data_City
SET @State = @Data_State
SET @Zip = @Data_Zip
SET @Latitude = @Data_Lat
SET @Longitude = @Data_Lon
---------------------------------------------------------------------
InsertNewAddresses:
SELECT @Step = 'InsertNewAddresses'
EXEC [Elig].[Logging].[InsertProcessStep] @ProcessLogId = @ProcessLogId,
@Step = @Step, @ProcessStepId = @ProcessStepId OUTPUT
INSERT INTO [Elig].[Lti].[Addresses]
( [AddressId] ,
[Address] ,
[City] ,
[State] ,
[Zip] ,
[Latitude] ,
[Longitude]
)
SELECT @AddressId AS [AddressId] ,
@Address AS [Address] ,
@City AS [City] ,
@State AS [State] ,
@Zip AS [Zip] ,
@Latitude AS [Latitude] ,
@Longitude AS [Longitude]
Thanks,
Amy
August 28, 2011 at 12:39 pm
It sounds like you are getting hit with conversion of string in VB to Decimal in SQL as a problem.
Executing:
declare @val varchar(20) = '-83.911', @bval DECIMAL(20,15)
set @bval=@val
select @bval, @val
produces the decimal result with all zeros at the end. Try changing the input variables from DECIMAL to char(8) if you know that is the total accuracy you want in the result. Even if extra characters are being transmitted in the character (string) field, the field should truncate them before making them decimal.
You also say VB, that language isn't known for keeping types straight in IO. Verify what you are getting in the SQL "string" if you use varchar(20).
August 28, 2011 at 2:58 pm
Tried that too. The problem that I had using this method is that it ended up cutting off the decimals. So, -83.11 instead of -83.113. I checked the data in SSIS using the MSGBOX and it was fine. By the time that it was read into the VARCHAR(255), it was chopped. The problem is that sometimes I have numbers trailing the decimal and sometimes four.
August 28, 2011 at 3:00 pm
Thanks for your reply 🙂
August 28, 2011 at 11:58 pm
Did you also try Ken Lee's last suggestion?
Ken Lee-263418 (8/28/2011)
... Verify what you are getting in the SQL "string" if you use varchar(20).
And if so, what was the outcome?
August 29, 2011 at 7:07 am
Yes. I did try setting the data to a VARCHAR(255). It was truncating the data.
However, the decimal mystery has been solved.
I had a co-worker take a look first thing this morning. When I was calling the Stored Procedure from SSIS, I still had the Variables set to Float. Ugh. Something so simple can waste so much time!! At least it got fixed!! I left the stored procedure alone.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply