How to identify the parameter causing an error? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to identify the parameter causing an error?

Author

Topic

alanmac
Starting Member

26 Posts

Posted - 2012-05-18 : 05:25:54

Hi,

I have an SP which is working fine when I call it from my local machine but when I call it from a production web server it is throwing up this error:

Arithmetic overflow error converting int to data type numeric

Is there any way to identify which parameter is causing the problem? I have quite a few being passed to the SP, and have no idea which one it could be.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-05-18 : 05:54:25

You should look into SQL Server Profiler:

http://thefirstsql.com/2011/01/07/sql-server-profiler-a-tutorial/

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

alanmac
Starting Member

26 Posts

Posted - 2012-05-18 : 06:00:32

I tried Profiler but it froze and crashed when I ran it. I think I need a complete reinstall.

That aside, it would be nice if I could place something in my SPs which would send details of a rogue parameter. At the moment I have this:


declare @success bit
declare @spname as nvarchar(50)

set @success = 0
set @spname = object_name(@@procid)

    -- Update code would be here
	
		
if (@@error <> 0)
begin
	raiserror ('Error %i has occurred in the stored procedure %s while updating records.', 16, 1, @@error, @spname)
	goto ErrorHandler
end

set @success = 1


ErrorHandler:

return @success

I'd love to be able to modify this so that it gives me the name of the parameter.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-18 : 11:42:48

It's hard to say without knowing what you are doing. Presumably, the call to the stored procedure succeeded (meaning the parameters were of the correct data types). However, the sproc is generating an error. This could mean several things:

1. You are manipulating the parameter(s) in such a way that is causing an implicit conversation (and thus the error).
2. You are comparing or assigning a parameter to a column in a table that are not of the same data type (and thus the error)

There are other ways this error could be generated, but those are the two most likely (IMHO). So, I'd suggest you compare the data type of the parameters to those in the tables they are "mapped" with. If that is all good, then look at how you are manipulating the parameters or variables within the sproc itself.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources