July 17, 2010 at 11:28 am
Well that's just weird. I just posted on one of the 2-year-old threads referenced in this article earlier this morning, before I saw that this article had been republished. Perhaps now would be a good time to buy a Lotto ticket?
July 20, 2010 at 6:33 am
Nice article. I too had some of the same misconceptions about padding as you.
July 20, 2010 at 7:10 am
tony.sawyer (7/16/2010)
The ANSI_PADDING affects the CHAR types too - surprised the hell out of me when I finally discovered why my tables weren't linking!doing a simple
select '<' + prod_code + '>' from ...showed it to only contain 7 characters with no trailing white space.
select '<' + cast(prod_code as char(10)) + '>' from ...showed up the trailing 3 spaces (and then allowed EF4 to link the data to another table that had ANSI_PADDING set to ON)We're using SQL Server 2005 here - give it a shot and see if you get the same results
Tony,
You are correct. The difference is that unicode (nchar/nvarchar) always behave with ANSI_PADDING ON settings.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 28, 2011 at 3:25 pm
To brighten the path for others following this later (like me), I wanted to spell out more about what this means for a Lookup in SSIS (according to the package I just got working). SSIS includes any trailing spaces in a lookup, and when matching to the input source, 'a' != 'a ' (space).
My source was Excel, and I didn't find any data conversion that would add the trailing spaces so that 'a' coming in would be 'a ', so I changed my lookup from a table/view selection to a SQL statement that trimmed my db-side fields to that 'a ' was changed to 'a', thus matching the 'a' coming from Excel.
Adding the trim is easy enough, but I'm puzzled why SSIS won't allow a data conversion to a type that will behave like char(10) and automatically add trailing spaces (if I just missed it, please explain how to do that in SSIS).
October 30, 2011 at 7:20 pm
I believe, and I'm not an SSIS expert, that SSIS is doing the compare within SSIS, meaning that .NET comparison rules apply. You need to match case AND trim any trailing spaces. I also believe that an Excel source uses Unicode data types for any strings, so SQL Server would pad with trailing spaces.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 46 through 49 (of 49 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy