x

Problems with BULK INSERT and COLLATIONS

Hi to everybody!.

We have a problem when we try to import a file into our database.

Our table is:

CREATE TABLE [dbo].[ACENTOS] (
[TEXTO] [char] (30) COLLATE
SQL_Latin1_General_CP850_BIN NOT NULL
) ON [PETICIONES_FG_Datos]

Our problem is related to ASCII Extended chars.

The content of the file we are trying to import is (FILE.DAT):

AEIOU aeiou ÁÉÍÓÚ áéíóú ñÑ

The format file we are using for the import is (FORMAT_FILE.FMT):

8.0
1
1 SQLCHAR 0 30 "" 1 TEXTO SQL_Latin1_General_CP850_BIN

And the sentence we are using is:

BULK INSERT PETICIONES.dbo.ACENTOS FROM "FILE.DAT" WITH (
CODEPAGE = 1252, FORMATFILE = 'FORMAT_FILE.FMT' )

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!

more ▼

asked Oct 21, 2009 at 07:19 AM in Default

Jesus Núñez gravatar image

Jesus Núñez
21 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort oldest

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:

SELECT * FROM fn_helpcollations() where name like '%latin1%'

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:

9.0

1

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.

more ▼

answered Oct 21, 2009 at 10:36 AM

Gustavo gravatar image

Gustavo
592 4 4 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x473
x66
x10

asked: Oct 21, 2009 at 07:19 AM

Seen: 4344 times

Last Updated: Oct 21, 2009 at 07:19 AM