This is always a fun problem to track down. Part of a DTS package I worked on had this problem where it converted ‘New York’ to ‘NE’ instead of ‘NY’ using a table full of state abbreviations. Once I found out where this problem was, I’m even more mystified at what the problem really is.
Within the SQL Designer for creating Lookup queries in DTS, I created the following query:
SELECT RTRIM(UPPER(Abbreviation)) AS State FROM States WHERE (Name = ?) OR (Abbreviation = ?)
This yielded two results: NE amd NY. So there’s where the error occurred. Now for the shitty part.
I take the same query and run it in SQL Analyzer like so:
SELECT RTRIM(UPPER(Abbreviation)) AS State FROM States WHERE (Name = 'New York') OR (Abbreviation = 'New York')
and of course, it returns one result: NY.
I don’t know what in the hell paramaterized queries do in the background within DTS, but I don’t like it.
In the end, I just ended up cheating:
SELECT RTRIM(UPPER(Abbreviation)) AS State FROM States WHERE (Name = ?) OR (CAST(Abbreviation AS VARCHAR(50)) = ?)