BULK INSERT w/odd delimiter OK in 2000, not 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

BULK INSERT w/odd delimiter OK in 2000, not 2005

Author

Topic

davee
Starting Member

6 Posts

Posted - 2008-06-12 : 18:54:10

We use BULK INSERT to load client data into our program. One of our clients uses the character '²' (0xB2) as a field delimiter in their input files. This worked fine in SS2000 but is failing in SS2005. After some testing, it appears that any high-ASCII value has the same problem; if I set the delimiter to anything below 0x80, it works and with any value of 0x80 or higher it fails.

I've verified that the format file we're using is correct for all of the tested delimiter values. (|, \t, ², ~, and €). The database collation sequence is SQL_Latin1_General_CP1_CI_AS if that matters.

Is there a way I can force acceptance of high-ASCII values as delimiters in SS2005? Do I need to play with the system code pages or the collation sequence?

Any assistance gratefully received.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-13 : 08:13:16

Please post code for BULK INSERT, and possible the file too.

E 12°55'05.25"
N 56°04'39.16"

davee
Starting Member

6 Posts

Posted - 2008-06-13 : 11:44:39

The format file:

9.0
3
1 SQLCHAR 0 0 "" 0 action_code ""
2 SQLCHAR 0 30 "²" 2 col_212 ""
3 SQLCHAR 0 60 "\r\n" 3 col_213 ""

The BULK INSERT command (this is generated on the fly and run via EXECUTE IMMEDIATE):

BULK INSERT dbo.x_import_48218873 FROM '\\dengh\imports\received\product.txt' WITH ( FORMATFILE='\\dengh\imports\received\x_import48218873.fmt', ERRORFILE='\\dengh\imports\received\x_import48218873.err', TABLOCK,MAXERRORS=99999,FIRSTROW=1,DATAFILETYPE='char',CODEPAGE=1252)

Obviously, the file paths are specific to my situation. The numbers in the file names are just there for uniqueness and have no meaning.

The target table is defined as:

CREATE TABLE [dbo].[x_import_48218873](
[action_code] [varchar](15) NULL DEFAULT ('ADD'),
[col_212] [varchar](30) NULL,
[col_213] [varchar](60) NULL,
[row_id] [int] IDENTITY(1,1) NOT NULL
)

The first 10 lines from the input file:

42-18234983²Teenage Girl Walking a Dog/42-18234983
42-17453734²Woman in Winter Landscape/42-17453734
NW008100²Ceramics Market/NW008100
42-15278375²German Chancellor Helmut Kohl/42-15278375
U1593933²Priests Burn Draft Records /U1593933
42-19143747²Basketball - NCAA - Pac-10 Media Day/42-19143747
42-18804367²Wicked/42-18804367
42-16806272²Sexy Surfer Carrying Surfboard/42-16806272
42-18464642²Cowboy Up!/42-18464642
EMBB001077²Sad Kitty/EMBB001077

davee
Starting Member

6 Posts

Posted - 2008-06-19 : 14:24:54

Microsoft Support is able to repro the problem, Case #SRX080618602109.

I'll post the resolution when we get one.

Thanks to all who looked at this.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-19 : 14:29:12

Do you get the same error with BCP?

E 12°55'05.25"
N 56°04'39.16"

davee
Starting Member

6 Posts

Posted - 2008-06-19 : 15:33:08

No, bcp works properly.

Unfortunately, the creation & execution of the command line is compiled into our program and the process runs on unattended machines as a service, so I can't just swap this out or ask for user intervention.

We went to BULK INSERT from bcp when clients had problems with upgraded SQL server installations and the first-available version of bcp in their system paths was unreliable. (we'd build a v8 format file & the system would find bcp v7 b/c it was still in the PATH variable. Bad Things™ happened.)

davee
Starting Member

6 Posts

Posted - 2008-07-15 : 20:34:33

A workaround, provided by the MS support folks, is to use a hex encoding of the delimiter if it's in the problem rangs (d128-255, 0x80-0xFF).

Our problem child is 0xB2, so in the format file gets encoded as '\xB2'. Works like a charm with minimal changes to the app.

The functional format file:

9.0
3
1 SQLCHAR 0 0 "" 0 action_code ""
2 SQLCHAR 0 30 "\xB2" 2 col_212 ""
3 SQLCHAR 0 60 "\r\n" 3 col_213 ""

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-16 : 00:50:30

Thank you for the feedback!

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources