asked on 9/12/2018

SQL Error - expecting integer but finding nvarchar

Problem with Query - help with diagnosing error in sql.
Sql management studio provides me with an error message when i run a query. If some one knows what the problem is... rogue data in a column that shouldnt be there... GREAT!
But i would also want to know... a method for figuring this out... hopefully a method that resides in management studio.

error and query is attached

sql profiler etc...
error.JPG

SQL

Last Comment

Jim Metcalf

9/18/2018

ASKER CERTIFIED SOLUTION

Martyn Spencer

9/12/2018

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

slightwv (䄆 Netminder)

9/12/2018

I assume SQL Server?

To locate the "bad data, look at the ISNUMERIC function:
https://docs.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql?view=sql-server-2017

You didn't provide the nvarchar column or which table it is in so something like:
select some_column from some_table where ISNUMERIC(some_column) <> 1;

Dustin Saunders

9/12/2018

Easiest way IMO is just open the tables in SSMS and look at the column data types and see what doesn't match. Or pop open designer and drop in the tables to get an overview.

SOLUTION

slightwv (䄆 Netminder)

9/12/2018

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Dustin Saunders

9/12/2018

@slightwv I think you're right and I mistook the question.

Is there a reason why constraints can't be introduced to your column? If you go clean it up, how did the bad data get there and what happens when bad data gets inserted again?

Jim Metcalf

9/18/2018

ASKER

Thanks Gentlemen -
both techniques were valuable