Monthly Archives: March 2015

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.

Advertisements

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.

Windows Phone Listbox: ensure ItemSource is bound before SelectedItem

Just spent a couple of hours trying to figure out why setting the SelectedItem of a ListBox kept crashing the application. It turns out that setting the ItemSource binding before the SelectedItem is a no go as per this StackOverflow posting.

So beware when designing your Windows Phone pages. For example, look at the snippet below.

 <toolkit:ListPicker            
            Header="Select a stop"
            Margin="{StaticResource PhoneMargin}"
            Grid.Row="2" 
            Grid.Column="0"
            SelectionMode="Single"
             ItemsSource="{Binding Stops}"
            SelectedItem="{Binding SelectedStop, Mode=TwoWay}">

If you place SelectedItem before ItemSource application will crash when you attempt to programmatically set the SelectedItem.

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     &quot;,&quot; 		   2     RouteId                Latin1_General_CI_AS
2       SQLCHAR             0       0     &quot;,&quot; 		   4     Name                   Latin1_General_CI_AS
3       SQLCHAR             0       0     &quot;,&quot;		   5     LongName               Latin1_General_CI_AS
4       SQLCHAR             0       0     &quot;,&quot;		   6     Description            Latin1_General_CI_AS
5       SQLCHAR             0       0     &quot;,&quot;		   7     RouteType              &quot;&quot;
6       SQLCHAR             0       0     &quot;\r\n&quot;	   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.

Visual Studio Community Edition – thank you Microsoft

Thank you Microsoft for providing us with a community edition, yet full fledged version of Visual Studio. This initiative levels the playing field in terms of cost of entry for development in the .NET ecosystems. With the best IDE on the planet in Visual Studio now free, coupled with a well though out language such as C# and a robust code execution environment backed by the CLR, it remains up to us to imagine the kinds of industrial problems to solve.

Thanks again Microsoft, Satya Nadella and all those who came together to make this happen.