Hi to everybody!.
We have a problem when we try to import a file into our database.
Our table is:
Our problem is related to ASCII Extended chars.
The content of the file we are trying to import is (FILE.DAT):
The format file we are using for the import is (FORMAT_FILE.FMT):
And the sentence we are using is:
This is only a test case.
The code page in our OS is 1252, and we are trying to convert the chars for the file to its correct representation into the database.
If we change the format file, and we specify in its header versión: 7.0, then the import is correct. But we dont know if our problem is a bug in the BULK INSERT process. In adition, we are considering the migration to SQL Server 2008, and dont know if we change now our file formats, if it will work in 2008.
Thanks for your attention!
asked Oct 21, 2009 at 07:19 AM in Default
Codepage 1252 matches any Latin language: http://msdn.microsoft.com/en-us/library/ms186356.aspx
And a list of possible Latin1 Collations can be found using:
But if you have problems copying data between diferent collations, try: http://msdn.microsoft.com/en-us/library/ms190657.aspx
But i could not find an explanation why your format files works with version 7 and not with others, but on SQL Server 2008 you would be able to use header version 9.0: http://msdn.microsoft.com/en-us/library/ms178129.aspx
I reproduced your error, and i was only able to correctly import with versions 8.0 and 9.0 when removing the collation specification or when specifying the default SQL_Latin1_General_CP1_CI_AS, like:
1 SQLCHAR 0 30 "" 1 TEXTO ""
But using header version 7.0 on SQL Server 2008 the import worked as expected, so migrating to sql server 2008 shouldn't affect your import process.
Let us know if this helped.
answered Oct 21, 2009 at 10:36 AM