Avatar of Jim Metcalf
Jim Metcalf
Flag for United States of America 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
SQL

Avatar of undefined
Last Comment
Jim Metcalf

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Martyn Spencer

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.

sqlcolumntypes.png
SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Jim Metcalf

ASKER
Thanks Gentlemen -
both techniques were valuable