Category Archives: TSQL

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,
 CONSTRAINT [PK_BusStops] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
))

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:

NULL
NULL
xxxx
NULL
NULL
NULL

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.

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.

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,
CONSTRAINT [PK_Addresses] PRIMARY KEY CLUSTERED (Id),
CONSTRAINT [FK_Addresses_Zones] FOREIGN KEY([ZoneId]) REFERENCES [dbo].[Zones] ([Id]) ON DELETE CASCADE
) ON [PRIMARY]

and


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,
 CONSTRAINT [PK_Zones] PRIMARY KEY CLUSTERED (Id),
) ON [PRIMARY]

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;
go

create procedure dbo.sp_recreate_table_using_script
 @create_script_name varchar(200),
 @tablename_to_create varchar(100)
as
 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
 f.name 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
 from
 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 = '';

 -- http://technet.microsoft.com/en-us/library/ms180169.aspx
 open foreign_key_cursor
 fetch next from foreign_key_cursor into
 @foreign_key,
 @child_table_name,
 @child_column_name,
 @parent_table_name,
 @parent_column_name

 while @@FETCH_STATUS = 0
 begin
 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
 @foreign_key,
 @child_table_name,
 @child_column_name,
 @parent_table_name,
 @parent_column_name
 end

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

print 'Executing ' + @drop_keys_query
execute(@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
execute(@recreate_keys_query);

go

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.