asked on

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

ASKER CERTIFIED SOLUTION

Martyn Spencer

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

slightwv (䄆 Netminder)

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

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)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Dustin Saunders

@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

ASKER

Thanks Gentlemen -
both techniques were valuable