Bulk Insert errors

Question

Post reply

Bulk Insert errors

David Kranes

SSCrazy

Points: 2523
More actions
April 27, 2009 at 9:09 am

#130292

Hello, I am trying to use bulk insert to import data into a table which has a computed column. I am getting the following error:
Msg 1934, Level 16, State 1, Server CORPSYS02-8485, Line 1
INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
The database quoted_identifier option is set to FALSE. I noticed in the table properties that quoted_identifier is set to TRUE. Is there a way to change this at the table level? What needs to be done to make this work?
Best Regards,
~David

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

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

David Kranes SSCrazy Points: 2523 More actions · Answer 1

Here is some more info:

Table Definition:

CREATE TABLE [outlet](

[retailer_id] [int] NOT NULL,

[outlet_id] [nvarchar](20) NOT NULL,

[group_id] AS (left([outlet_id],(3))) PERSISTED NOT NULL,

[outlet_desc] [nvarchar](40) NULL,

[outlet1_address] [nvarchar](100) NULL,

[outlet2_address] [nvarchar](100) NULL,

[city_name] [nvarchar](60) NULL,

[state_code] [nchar](2) NULL,

[postal_code] [nvarchar](15) NULL,

[country] [nchar](2) NULL,

[hours_desc] [nvarchar](100) NULL,

[contact_text] [nvarchar](100) NULL,

[search_allowed_cd] [nchar](1) NULL,

[ship_rout_priority] [int] NULL,

[latitude] [float] NULL,

[longitude] [float] NULL,

[order_volume] [int] NOT NULL,

[rec_update_date] [datetime] NOT NULL,

[rec_create_date] [datetime] NOT NULL,

[rec_update_id] [int] NOT NULL,

CONSTRAINT [pk_outlet] PRIMARY KEY CLUSTERED

(

[outlet_id] ASC,

[retailer_id] ASC)

)

Bulk Insert Command:

bulk insert outlet from 'C:\CRS\EnterpriseSelling2.6_2005\datafeed\data\USoutlet.delsert' with (formatfile='C:\CRS\EnterpriseSelling2.6_2005\datafeed\USoutlet57389.fmt',datafiletype='widechar',tablock,firstrow=2,maxerrors=50000)

Format file:

9.0

19

1SQLNCHAR010"|"1retailer_idSQL_Latin1_General_Cp437_BIN

2SQLNCHAR040"|"2outlet_idSQL_Latin1_General_Cp437_BIN

3SQLNCHAR080"|"4outlet_descSQL_Latin1_General_Cp437_BIN

4SQLNCHAR0200"|"5outlet1_addressSQL_Latin1_General_Cp437_BIN

5SQLNCHAR0200"|"6outlet2_addressSQL_Latin1_General_Cp437_BIN

6SQLNCHAR0120"|"7city_nameSQL_Latin1_General_Cp437_BIN

7SQLNCHAR02"|"8state_codeSQL_Latin1_General_Cp437_BIN

8SQLNCHAR030"|"9postal_codeSQL_Latin1_General_Cp437_BIN

9SQLNCHAR02"|"10countrySQL_Latin1_General_Cp437_BIN

10SQLNCHAR0200"|"11hours_descSQL_Latin1_General_Cp437_BIN

11SQLNCHAR0200"|"12contact_textSQL_Latin1_General_Cp437_BIN

12SQLNCHAR01"|"13search_allowed_cdSQL_Latin1_General_Cp437_BIN

13SQLNCHAR010"|"14ship_rout_prioritySQL_Latin1_General_Cp437_BIN

14SQLNCHAR015"|"15latitudeSQL_Latin1_General_Cp437_BIN

15SQLNCHAR015"|"16longitudeSQL_Latin1_General_Cp437_BIN

16SQLNCHAR010"|"17order_volumeSQL_Latin1_General_Cp437_BIN

17SQLNCHAR023"|"18rec_update_dateSQL_Latin1_General_Cp437_BIN

18SQLNCHAR023"|"19rec_create_dateSQL_Latin1_General_Cp437_BIN

19SQLNCHAR010"\r"20rec_update_idSQL_Latin1_General_Cp437_BIN

Sample Data:

1|1610101|PLACE D'|1234 YOUR STREET||OTTAWA|ON|K1C 1K6|CA|||F|0|||0|2009-04-17 15:23:09|2009-04-17 15:23:09|1

1|1210102|PLACE FL|4567 BOUL. WILFRED HAMEL||QUÉBEC|QC|G1M 2S6|CA|||F|0|||0|2009-04-17 15:23:09|2009-04-17 15:23:09|1

1|1810103|YORKDALE|2886 JOHNSON STR||TORONTO|ON|M6A 2T9|CA|||F|0|||0|2009-04-17 15:23:09|2009-04-17 15:23:09|1

1|1010104|HALIFAX|1600 CALFORD ROAD||HALIFAX|NS|B3L 2H8|CA|||F|0|||0|2009-04-17 15:23:09|2009-04-17 15:23:09|1

1|2610105|MIDTOWN|122 3RD STREET||SASKATOON|SK|S7K 1V9|CA|||F|0|||0|2009-04-17 15:23:09|2009-04-17 15:23:09|1

How can I load this without getting this error.

Best Regards,

~David

David Kranes SSCrazy Points: 2523 More actions · Answer 2

It turns out that the default for sqlcmd is qouted_identifier off. I needed to use the -I swith and now it works.

Best Regards,

~David