We help IT Professionals succeed at work.

SQL Error - expecting integer but finding nvarchar

jamesmetcalf74
on
75 Views
Last Modified: 2018-09-18
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
Comment
Watch Question

Software Developer / Linux System Administrator
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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 SaundersCo-Founder and Chief Architect
CERTIFIED EXPERT
Top Expert 2016

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Dustin SaundersCo-Founder and Chief Architect
CERTIFIED EXPERT
Top Expert 2016

Commented:
@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?

Author

Commented:
Thanks Gentlemen -
both techniques were valuable

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.