Category Archives: Cloud

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.

The value of quality tech support from web hosting partner

Technical service is paramount in our business, especially if you, like me, do not like farting around with stuff other people do better, such as hosting web applications.  I prefer to think of my speciality as crafting and building top notch software solutions and prefer to let others who are best at providing for me the infrastructure to do just this.

This is what http://www.discountasp.net has provided for me over the years. I have nothing but praise about their commitment to quality infrastructure, support and service. Regardless of the time and day you are almost guaranteed to get a response from these guys in very reasonable time, in most cases under three hours.

See, I have an ASP.NET MVC transit application being prototyped. ¬†Application is being migrated from Windows Azure because I could not find a way to host it on a relative path. And since discountasp.net is already hosting our family charity’s website, I naturally turned to them for help.

I signed up for a new account on Jan 18, 2015, and in 24 hours after updating my DNS settings with my domain registrar, my domain name was up and running. Next, I deployed my ASP.NET MVC application to the discountasp.net servers and this is when things started to get interesting.

Since the application’s database still resides in Azure, I had to add an IP white list to entry to Azure’s management portal . The folks at discountasp.net promptly provided the IP address list and were also very quick to correct all the misunderstandings ( and I had many) that kept coming up. We went back and forth on this several times and at one point, they were prepared to do a remote session via webex. Now, this is technical support.

After they confirmed to me that they were able to connect to my Azure database, it was just a simple matter for me to correct some minor glitches in the application and re-deploy it for it to start working correctly. If I was doing all of this by myself, there is no way the work could have been done in such a short period of time, further distracting me from the actual task of creating great software.

Nothing but good words for these guys and this is what, in my humble opinion, is a distinguishing factor amongst web hosting partners. Having these kinds of reliable partners is well worth the money, allowing one to focus on what they do best.

And no, I am not affiliated with them in any way.