June 18, 2013 at 10:44 am
SELECT * FROM [dbo].[REPORT_NOTES] AS Rptn results in one record.
A column called INV_NUM with a value of "998877" and a column called TEXT with a value of "Note information..."
The SSIS package is not picking up the text qualifiers of double quotes on the INV_NUM, but it is removing the double quotes on the TEXT column. They have tasked me with the ability to fix this in the Stored Proc. I have tried all of the below with no luck!
UPDATE
[dbo].[REPORT_NOTES]
SET
[INV_NUM] = REPLACE([INV_NUM] , '"' , '');
UPDATE
[dbo].[REPORT_NOTES]
SET
[INV_NUM] = LTRIM(RTRIM([dbo].[REPORT_NOTES].[INV_NUM]));
What happens is the LTRIM and RTRIM successfully complete, but the 6 digit INV_NUM is still showing a length of 8 characters. This is a problem since this column is used to join on tables. Since the INV_NUM is 8 characters, SQL can't find the corresponding 6 character INV_NUM in the DB and the process fails.
What can I do to actually get the LTRIM and RTRIM to really work and make that value a real 6 digit actual INV_NUM?
Thank you
June 18, 2013 at 10:47 am
LTRIM and RTRIM remove spaces. If they're not removing your non-printable characters, then they're not spaced. Identify what they are (use the ASCII command) and then use REPLACE or LEFT and RIGHT or SUBSTRING
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2013 at 10:57 am
Check the column or parameter definitions for char instead of varchar. Conversions between those are very tricky.
June 18, 2013 at 11:30 am
Thank you Gail.
I realized that the data was working fine before a specific date. After that date, the report loading started to fail. I sent the ticket back to the developer and told him to fix it. I'm not going to BandAid a problem caused by a change without ticket/approval becuase I may break something else downstream.
Thank you for your help.
June 18, 2013 at 11:35 am
😀
Typically the cause for something like this is non-space whitespace characters. There are a few ascii characters that don't show, but aren't spaces. CR and LF are two that immediately come to mind. Since they aren't spaces, LTRIM and RTRIM won't get rid of them, but they are valid characters and SSIS especially is incredibly picky about strings being absolutely, completely equal. Far more so than SQL Server which is perfectly happy to ignore trailing spaces when comparing stings because it needs to do transparent comparisons (without explicit or implicit conversions) between char and varchar.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply