SQL Server BCP Utility: INSERT failed because the following SET options have incor rect settings: ‘QUOTED_IDENTIFIER

If you use the SQL Server BCP utility to upload data to SQL Server, or SQL Server Azure, you may run into the following error:

Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]INSERT failed because the following SET options have incor
rect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on com
puted columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index opera
tions.

The script I was using is as follows:

echo off
echo ..... executing do_bulk_insert_azure
echo off

set server=%1   
set username=%2
set password=%3
set db=%4
set db_table=%5
set data_dir=%6
set format_file=%7
set first_row=%8
set schema=%9

if [%9] == [] (
	set schema=dbo
)

set fullTablePath=%db%.%schema%.%db_table%

@REM this first line is for SQLCMD and should direct to doSqlCmd
set sql_cmd="delete from %fullTablePath%;"
echo ..... deleting current records using %sql_cmd%
echo ..... on server %server% 
echo ..... using username %username%

sqlcmd -S %server% -U %username% -P %password% -d %db% -Q %sql_cmd% 

echo ..... bulk importing using bcp with options:
echo ..... parameters are: 
echo .....   server      = %server%
echo .....   database    = %db%
echo .....   table       = %db_table% 
echo .....   data_dir    =   %data_dir%
echo .....   format_file =   %format_file%
echo .....   schema      =   %schema%
echo .....   first_row   =   %first_row%
echo .....   -k to keep null 
echo .....   -F %first_row% for first row

if exist %data_dir% (
	echo ..... %data_dir% exists so we are good to go
) else (
	echo ..... ERROR: %data_dir% does not exist.  BCP will choke!
)

echo ..... command is:
echo bcp %fullTablePath% in %data_dir% -f %format_file% -S %server% -U %username% -P %password% -k -F %first_row%
echo ..... please wait.....

bcp %fullTablePath% in %data_dir% -f %format_file% -S %server% -U %username% -P %password% -k -F %first_row%

echo ..... successfully updated table.

When I execute the script as follows:

PS C:\Users\Klaus\downloads> update_transit_database_table_azure my_azure_db.database.windows.net azure_username azure_password db_name oc_transpo “c:\users\knji\downloads\oc_transpo” stops stops.txt stops.fmt

I am greeted with the above error. The fix is simple:

Supply the -q parameter to BCP as follows:

bcp %fullTablePath% in %data_dir% -f %format_file% -S %server% -U %username% -P %password% -k -F %first_row%

According to the documentation, this executes the SET QUOTED_IDENTIFIERS to ON. In my case, this needed to be ON because the stops table does contain a spatial index.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s