June 24, 2009 at 11:59 am
I'm seriously confused. I think this might be due to a server setting, but I'm not sure which one. Any advice would be appreciated.
On my QC server, MyCol is a varchar(20) that is being populated from another DB with a 16 character string. I have to parse this into a text file and pad it out to 20 spaces for a mainframe app. When I just do the SELECT MyCol, I only get 16 spaces, so I concatenated it with a SPACE(4), giving me "SELECT MyCol + SPACE(4)" for my SQL code.
In Production, though, it pads it out to 24 spaces (varchar(20) + 4 instead of varchar(16) + 4).
I've double checked the column datatype. It's the same on both servers. I've done a LEN on the field in question on both servers, it's 16 characters long for all values. The SQL version is the same (2005 SP1+ hotfix). So it's got to be something else. Something I'm missing.
Thoughts?
June 24, 2009 at 12:58 pm
try REPLICATE
create table t1 (MyCol1 varchar(20) null)
insert into t1 values('ABCDEFGHIJKLMNOP')
select dataLength(mycol1) from t1
SELECT MyCol1 + REPLICATE(' ', 20 - DATALENGTH(MyCol1)) from t1
June 24, 2009 at 3:45 pm
Double check the ANSI_PADDING settings on both servers? My guess is they are not the same...;-)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 25, 2009 at 4:16 am
Ansi Padding is unchecked on both servers.
Thing that really bugs me about this is that I restored the database on QC from a Production backup before I ran this, so if it was a database setting, it should have copied over and ran the same on both.
I'll test the replicate thing, but because I'm using this code in an SSIS package, I was trying to avoid Replicate subtraction so the package wouldn't complain about the possibility of the result being too big for the destination column.
June 25, 2009 at 4:23 am
Ahhh. You know what? Someone patched my server without telling me.
QC is running version 9.00.2153 and Prod is running version 9.00.3233 (not in Steve's Build List at [/url]). We're not supposed to have our servers out of sync like that.
I bet that's the problem. Version 3233, for some reason, has something in it that reads a varchar() as char() in SSIS regardless of how long the values in that column actually are.
Does anyone have that problem in any builds beyond 3233?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply