Category Archives: SQL Server

SQL Server Conversion failed when converting the varchar value ‘xxxx’ to data type int – A Root Cause Analysis

There are many reasons but this is what I ran into:

The query was simple as follows:

select top 1 stopid from busstops where code = cast('836' as int)

Relevant portions of the table schema is as follows:

CREATE TABLE [dbo].[BusStops](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[StopID] [varchar](20) NULL,
	[Code] [varchar](20) NULL,
	[Name] [varchar](100) NULL,
	[Summary] [varchar](200) NULL,
	[Lat] [float] NULL,
	[Lon] [float] NULL,	
	[the_geom] [geography] NULL,

When the specified query was executed, SQL Server returned the following response:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'xxxx' to data type int.

However, if an equivalent query was executed

select top 1 stopid from busstops where code = cast('8789' as int)

this returned the correct result. What is going on here? Are my eyes planning tricks on me?

Apparently, there is a bad value in the code column of the busstops table that is not evaluating to an integer. But is the value entry literally ‘xxxx’ or is this some SQL Server error code? To answer this question, I ran the following query:

select code from busstops where isnumeric(code) = 0 

and sure enough, there were a bunch of NULLs and several entries with literal values of ‘xxxx’ as shown below:


There you go, bad data causing the problem. Presented with a string of ‘xxxx’, SQL Server was unable to convert it to an int, hence the error. Just out of curiosity, I also wanted to know why the first query failed with code of 836 while second query with code of 8789 work?

When you select a top N, SQL Server will stop evaluating the query as soon as it has accumulated N records that meet this criteria. There is a possibility, therefore, that the entire table will not be scanned or seeked, if we are dealing with indexed rows. The theory I formulated was the row with code = 8789 definitely appears before any rows with code = xxxx. To validate this hypothesis, I ran this query:

select id, code from busstops where code in ('8789', '836', 'xxxx')

and the result was as follows;

select id, code from busstops where code in (‘8789’, ‘836’, ‘xxxx’)

92030, 8789
93097, xxxx
96824, xxxx
96930, 836

thus validating the hypothesis. To get to the row containing code = 836, SQL Server has to evaluate the rows containing code = xxxx and it trips here.

I then did a search in the original text file for ‘xxxx’ and sure enough, there were a couple of lines containing this string literal such as :

WH367,xxxx,”RICHARDSON / ALL SAINTS H.S.”,,latitide,longitude,,,0

which is the root cause. To fix this, I correct the data file, re-imported by data into SQL Server and was back to business. However, to make this query more robust and to be able to withstand further discrepancies in the data, further considering this field is of type varchar, I decided to change the query to

select top 1 stopid from busstops where code = '836'

Happy coding.

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

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 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:


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,

The format file, you have defined as follows:

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,

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.

TSQL script to drop foreign keys, re-create table and restore keys

I have run into several situations during database modeling where I had to drop and recreate an existing table.  But the process involved dropping foreign key relationships to this table, creating the table and then re-creating the foreign key relations.  The process felt very manual with no added value, so I decided to do something about it.

First some context.

Assume two tables: “Addresses” and “Zones” created with the following script:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Addresses]') AND type in (N'U'))
DROP TABLE[dbo].[Addresses]

CREATE TABLE [dbo].[Addresses](
[Id] int IDENTITY(1,1) NOT NULL,
[Name] varchar(50),
[UnitNumber] varchar(10),
[Street] varchar(100),
[City] varchar(100),
[Province] varchar(100),
[PostalCode] varchar(10),
[Country] varchar(50),
[ZoneId] int,
[AddedOn] datetime,
[LastModifiedOn] datetime,
[IsActive] [bit] NOT NULL DEFAULT 1,


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Zones]') AND type in (N'U'))
DROP TABLE[dbo].[Zones]

CREATE TABLE [dbo].[Zones](
 [Id] int IDENTITY(1,1) NOT NULL,
 [Name] varchar(50),
 [CountryCode] varchar(5), -- The ISO 3166 CountryCode
 [Geometry] geometry,
 [OffPeakStartHour] int,
 [OffPeakEndHour] int,
 [OnPeakStartHour] int,
 [OnPeakEndHour] int,
 [MidPeakStartHour] int,
 [MidPeakEndHour] int,
 [MonthStart] int,
 [MonthEnd] int,
 [TimeZone] int,
 [AddedOn] datetime,
 [LastModifiedOn] datetime,
 [IsActive] [bit] NOT NULL DEFAULT 1,

Once created tables, the parent Zones table cannot simply be dropped unless all associated foreign key associations have been dropped.  If not, you get an error similar to this:

Msg 3726, Level 16, State 1, Line 7
Could not drop object 'dbo.Zones' because it is referenced by a FOREIGN KEY constraint.
Msg 2714, Level 16, State 6, Line 9
There is already an object named 'Zones' in the database.

from Microsoft SQL Server Studio Management (SSMS).

The error does not tell us specifically which tables currently holds these FOREIGN KEY constraints.  Now, if there was just one of such constraints, as in this example, it is simple enough to find the child Addresses table, drop its foreign key relationship to the parent Zones table, re-create the Zones table, then re-create the keys, using regular means.  Of course this assumes you know that the Addresses table is the only with a child relationship to Zones.

If there are several of such relationships to the Zones table, however, you have to drop each one, create table, then recreate the relationships.  And if your model changes regularly (which it probably will during original system design), this will be a fairly regular process.

Personally, I shy away from manual labor, especially, if it involves repetitive and manual process which teaches me nothing new.   So for this specific problem, I created this simple TSQL sript that will find and drop all foreign key relationships to a table, create the table based on another TSQL script then recreate the keys again.   It goes like this:

if OBJECT_ID ( 'dbo.sp_recreate_table_using_script', 'P' ) is not null
 drop procedure dbo.sp_recreate_table_using_script;

create procedure dbo.sp_recreate_table_using_script
 @create_script_name varchar(200),
 @tablename_to_create varchar(100)
 declare @foreign_key varchar(100),
 @child_table_name nvarchar(100),
 @child_column_name varchar(100),
 @parent_table_name varchar(100),
 @parent_column_name varchar(100),
 @drop_keys_query varchar(max),
 @recreate_keys_query varchar(max)

declare foreign_key_cursor cursor for

-- Gets all foreign keys to referenced table
with [CTE_FoeignKeys]
 as (
 select as ForeignKey
 , OBJECT_NAME(f.parent_object_id) AS ChildTableName
 , COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ChildColumnName
 , OBJECT_NAME (f.referenced_object_id) AS ParentTableName
 , COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ParentColumnName
 sys.foreign_keys as f
 INNER JOIN sys.foreign_key_columns as fc on f.OBJECT_ID = fc.constraint_object_id

 where OBJECT_NAME(f.referenced_object_id) = @tablename_to_create

 select * from [CTE_FoeignKeys]

 set @drop_keys_query = '';
 set @recreate_keys_query = '';

 open foreign_key_cursor
 fetch next from foreign_key_cursor into

 while @@FETCH_STATUS = 0
 print '... Processing foreign key: ' + @foreign_key

 set @drop_keys_query = @drop_keys_query + ' alter table [dbo].[' + @child_table_name + ']' + ' drop constraint ' + @foreign_key + ';';
 set @recreate_keys_query = @recreate_keys_query +
 ' alter table [dbo].[' + @child_table_name + '] with check add constraint [' + @foreign_key + '] foreign key ([' + @child_column_name + '])' +
 ' references [dbo].[' + @parent_table_name + '] ([' + @parent_column_name +']) on delete cascade; ' +
 ' alter table [dbo].[' + @child_table_name + '] check constraint [' + @foreign_key +'];'; 

 fetch next from foreign_key_cursor into

 print '... releasing cursor resources... '
 close foreign_key_cursor;
 deallocate foreign_key_cursor;

print 'Executing ' + @drop_keys_query

print 'Executing ' + @create_script_name
set @create_script_name = 'SQLCMD -S [SQLServerInstance]-d [DatabaseName]-E -i ' + @create_script_name
exec xp_cmdshell @create_script_name 

print 'Executing ' + @recreate_keys_query


It works as follows:

  1. Look for all foreign key constraints targeting the parent table. Credit goes to this StackOverflow posting for the snippet.
  2. Temporarily drop all the foreign key relationships.
  3. Run the script at provided location to re-create our target table.
  4. Recreate all foreign keys that were dropped in the second step.

This script uses system Stored Procedure Xp_CmdShell which needs to be enabled.

After enabling “Xp_CmdShell” and adding the above Stored Procedure to the database, it can invoke it like so:

declare @create_script_location varchar(100), @table_name varchar(100)
set @create_script_location = 'C:\dev\data\scripts\create-zones-table.sql' ;
set @table_name = 'Zones';

exec sp_recreate_table_using_script @create_script_location, @table_name

Happy Coding.