September 26, 2006 at 9:00 am
hi,
I export data from sql2005 to access2000 but when i run the command text
set ansi_padding off
INSERT INTO OPENROWSET (connection string for a access2000 databse
,select * from desttable) select * from table
the value of the character fields are full of space for the entire lenght of the field.
In the table in sql2005 the ansi_padding is off and the values of character fields are correct.
the table in sql2005 is created with the option ansi_padding off
the type of field is char(120) and , for example contain the value "miss ross" and there are no space
when i export the data in access the field contain the value ('miss ross') but the field is full of space for the entire lenght of the field.
thank you
pl
September 27, 2006 at 7:01 am
When ever you store a value in a Char() field the database automatically adds the spaces to pad the field to the proper length. If you are storing values that have differign lengths like 'miss ross' and 'mr smith' I'd suggest a varchar(120) datatype. This should eliminate your issues with trailing spaces.
Also, if you can't change the datatype in your database, when you are exporting your data, instead of using select *, specify each column name and rtrim the column when you do it. It's typically a bad idea to use Select * because you have no control over column order and numerous other things.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply