x

Find specific data element causing error

Hello, I am using SQL2005 and have a large view that is causing me problems. It is returning the error message - "Conversion failed when converting the nvarchar value '60067-6221' to data type int." and I have searched all of the fields I believe could be causing this, but found nothing. Is there a way to have SQL Server return the exact row number in the exact table that is causing the error? Since this view is joining 8 tables and has about 75 fields, it has been a nightmare trying to find the offending data element. BTW, I have used this view for about 3 years for a quarterly update and it has worked fine so it is newly imported data that is causing the issue.

Thanks,

Scott

more ▼

asked Jan 22, 2010 at 06:25 PM in Default

avatar image

Scott 1 1
32 3 3 6

(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

It sounds like the zip+4 is being used in a join or a conversion within your view that's causing the error to spring up. Finding the exact field would be easy if you look through the SQL code for where that column is being used.

more ▼

answered Jan 22, 2010 at 08:38 PM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

(comments are locked)
10|1200 characters needed characters left

If there is a conversion issue it sounds like this problem is in a join rather than in the selected columns, unless you have columns that you are converting or using in calculations or concatenations. Check each column on both sides of each join is the same data type, if not then you need to CAST or CONVERT one column to match the one on the other side of the join. I used to have a lot of problems like this until I started using a 3rd party tool to help TSQL writing - (check out Red Gate's SQL Prompt. This shows you the column data type simply by hovering your cursor over the column name, anywhere in your script).

If the joins are all clear, check the columns you are doing any 'work' with - conversions, calculations and concatenations - and again check the columns are of the right type or cast/convert them accordingly

If you are still having problems, try commenting out all your columns and see if you still get the error, work through un-commenting columns a few at a time until the error reappears and you have then located where the issue is.

more ▼

answered Jan 23, 2010 at 04:54 PM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

  • except you don't always need cast / convert - the conversion can be implicit...

Jan 23, 2010 at 07:30 PM Matt Whitfield ♦♦

Matt, I agree if the conversion is implicit then the data types should be OK but then wouldnt get any errors. If you are comparing dissimilar types that wont convert implicitly then the CAST or CONVERT can handle it, typically with a COALESCE if a column is NULLABLE etc.

Jan 24, 2010 at 07:29 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

I don't know whether or not this will help, but that looks like a US "ZIP+4" code. I looked it up, and it is a valid zip code for Palatine, IL.

more ▼

answered Jan 22, 2010 at 06:38 PM

avatar image

Tom Staab ♦
14.5k 7 15 21

(comments are locked)
10|1200 characters needed characters left

I wrote an article on finding a specific value over a number of columns or even the entire database and since you have a specific value to look for it may be useful. It is at http://www.sqlservercentral.com/articles/Search/64877/

One caveat that I mention in the article is that it can take a very long time to run when dealing with a large database.

more ▼

answered Jan 22, 2010 at 07:01 PM

avatar image

TimothyAWiseman
15.6k 22 57 38

Timothy, thanks for the response. The code you have does a great job of bringing back a list of all of the columns in the view but I am not sure how it will identify which columns contain the value I am looking for. So you use the 'Exec FindValue' statement to bring back the columns and then loop over them to search for the specific value?

As to other comments, yes it is probably a Zip+4 value and I have searched all of my Zip fields to no avail. BTW, this is a data warehouse so normalization is not an opton.

Thanks, Scott

Jan 22, 2010 at 10:24 PM Scott 1 1

The procedure in the article should only return the rows that have the value you specified in it, so once you are looking at only those rows it should be relatively easy to find both the column in question and the primary key of that row.

Jan 23, 2010 at 12:12 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

Also note, the offending value may not even be returned in the view for the error to surface. I've had similar errors on 2005 where a value that could not be converted to int was in the table but not part of the result set, almost as if SQL tries to convert every row in the table before the predicate is applied.

more ▼

answered Jan 23, 2010 at 12:15 AM

avatar image

Scot Hauder
6.5k 13 16 22

Multiple things could cause that, an index scan, the value is part of the join criteria but filtered out later in the query, the value was put into a table spool... all sorts of things.

Jan 23, 2010 at 08:39 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x162

asked: Jan 22, 2010 at 06:25 PM

Seen: 3032 times

Last Updated: Jan 22, 2010 at 06:25 PM

Copyright 2018 Redgate Software. Privacy Policy