Post reply

problem to export data from sql2005 to access2000 with ansi_padding

  • September 27, 2006 at 6:02 am

    #114466

     

    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 28, 2006 at 2:01 pm

    #663140

    ANSI_Padding controls how data is stored, not how it is inserted. So setting ANSI_Padding off in SQL Server has no effect whatsoever on how Access stores it. You need to look at the Access settings.

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply