Category Archives: Azure

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.

BCP vs BULK INSERT on SQL Server Azure

I just realized that BULK INSERT is not supported on SQL Server Azure, so had to change some import scripts to use BCP instead. The response you get from SQL Server Azure does not explicitly tell you this. MSDN’s article on BULK INSERT does not say so either. Thanks to some kind folks at Stack Overflow for point me to BCP.

Since BCP works on a local instance of SQL Server as well as Azure, I will stick to it for now, unless proven otherwise.

SQL Server BCP copies 0 rows without error

The exercise is simple.

You have a text file with the following content:

route_id,route_short_name,route_long_name,route_desc,route_type,route_url
1-184,1,,,3,
2-184,2,,,3,
4-184,4,,,3,

and need to import this file into a table with the following schema:

CREATE TABLE [dbo].[BusRoutes](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[RouteId] [varchar] (10) NOT NULL CONSTRAINT [NN_RouteId_BusRoutes] UNIQUE,
	[RouteInternalId] [int] NOT NULL DEFAULT -1,
	[Name] [varchar](10) NOT NULL,	
	[LongName] [varchar] (200) NULL,
	[Description] [varchar](200) NULL,
	[RouteType] [int],
	[RouteUrl] [varchar](300),
	[IsActive] [bit] NOT NULL CONSTRAINT [NN_IsActive_BusRoutes] DEFAULT 1,
 CONSTRAINT [PK_BusRoutes] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The format file, you have defined as follows:

11.0
6
1       SQLCHAR             0       0     "," 		   2     RouteId                Latin1_General_CI_AS
2       SQLCHAR             0       0     "," 		   4     Name                   Latin1_General_CI_AS
3       SQLCHAR             0       0     ","		   5     LongName               Latin1_General_CI_AS
4       SQLCHAR             0       0     ","		   6     Description            Latin1_General_CI_AS
5       SQLCHAR             0       0     ","		   7     RouteType              ""
6       SQLCHAR             0       0     "\r\n"	   8     RouteUrl               Latin1_General_CI_AS

This format file looks suspect, but it worked last year!

You issue the following command to your Azure database from PowerShell or your favourite command line tool.

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

replacing those variables accordingly and cross your fingers.

SQL Server replies back without error but saying it imported zero rows. You spend a couple of days on this only to find out that there must be a blank line at the end of the data file, amongst other annoyances, for things to work and that even if the file looks “well formed”, BCP may not think so. You have even run this file through the GTFS FeedValidator which found problems with other files but not this one.

A guaranteed way to determine if your file is well formed is to open it in Notepad. If it looks well formed there, BCP will be able to read it. A file is well-formed in this context, if it is presented as specified in your format file. For example, if lines are supposed to be terminated with Windows EOL \r\n, you should not see this:

route_id,route_short_name,route_long_name,route_desc,route_type,route_url 1-184,1,,,3,2-184,2,,,3,4-184,4,,,3,
5-184,5,,,3,6-184,6,,,3,7-184,7,,,3,8-184,8,,,3,

Instead you should see the format presented earlier in this article.

Another way is to open your file in Notepad++ and using the Regular Expression search mode to look for \n, which is the UNIX end of line character set.  Assuming you are doing all this work on Windows you should do a search \n and replace with \r\n.  This should fix your problem.

Happy importing.

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.