Solved

How to find in what Trigger/StoredProc/Function the error occurs

Posted on 2004-09-22
13
329 Views
Last Modified: 2009-12-16
I'm executing a stored procedure. The code in the procedure invokes a lot of triggers that each of them start other stored procedures, use functions, ....
When looking at the messages i'm getting the typical lines of xxx records Processed, but suddenly also an error

(14 row(s) affected)
(37 row(s) affected)
Server: Msg 298, Level 16, State 1, Line 311
The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error.

I know whet the error means but i don't know in what trigger/.... it occurs so i have no clue on wich table i'm getting the problem.

Any suggestions
0
Comment
Question by:HLEBOEUF
  • 5
  • 3
  • 3
  • +2
13 Comments
 
LVL 9

Expert Comment

by:solution46
ID: 12121338
I would suggest adding a return value to each sproc you call, and checking that value each time you call it. Then you can use customised errror messages (using RAISERROR - see online books for details) to detail exactly where your sproc failed.

As a short term solution, you could try putting 'print' statements at key points in the sproc to get an idea where it is falling over.

Hope this helps,

s46.
0
 
LVL 2

Expert Comment

by:KhunJean
ID: 12122005
If this happens you should also question the design of the database and procedures.
If is not transparant anymore it needs an overhaul.
A lot of the trigger code can be placed in the stored procedures that modify the data.

This error is probably an implicit conversion that makes it even harder to track.
Solution46 option is the best way to find it. Tedious but effective.

I presume you also know the problems that can arise when using triggers.

In my experience with databases is that if it can be solved somewhere else than in a trigger use that option.
The only need i had was when i had to use then deleted and inserted tables inside a trigger while an update. All others could be done by stored procedures.

Triggers are usefull when users have acces to the tables themselves. Then you almost have no choice but to use them.
In another environment it would be ideal to allow only data access and modification through stored procedures. Like an API for the database. In that way you have total control and a lot more transparency.

just my 2 cents. :)




0
 
LVL 9

Expert Comment

by:solution46
ID: 12122577
KJ... using the terms 'well designed database' and 'allowing users access to the tables' in the same paragraph???? tut tut... :)

Tirggers, Cursors, Temp Tables: spawn of Satan. Occasionally necessary evils but there is usually a better way of doing things...

s46.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 12122997
You should be able to get some idea from carefully stepping thru the code yourself, assuming there aren't three or more triggers of the same type on the same table.  

For example, since your output is:

(14 row(s) affected)
(37 row(s) affected)
Server: Msg 298, Level 16, State 1, Line 311
The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error.


Beginning at the starting proc, and knowing the param values passed in, you should be able to determine which statement affects 14 rows, which succeeding statement affects 37 rows, and then which statement has both a datetime and a smalldatetime column(s) in it.
0
 
LVL 21

Expert Comment

by:mastoo
ID: 12123118
I'll admit I haven't used it for this, but I think Sql Profiler can indicate errors along with all of the sql that is running so it would quickly pinpoint the problem.
0
 
LVL 2

Expert Comment

by:KhunJean
ID: 12123420
@solution46

KJ... using the terms 'well designed database' and 'allowing users access to the tables' in the same paragraph???? tut tut... :)

Brrr. You are right. :))
It definately should not have read users. But DBA is not that much better. :)
Actually it happens more than should.
I have seen a lot of end user applications with direct access to the tables on the server. It makes your shiver.
And even worse. Application without the source code, so you HAVE to use triggers. Aarrgg.

Better start in the right way. For me that is no triggers, no #Temp tables.
BUT sometimes i need raw speed, then everything is allowed. :)
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 12123741
Triggers are often vital, *especially* if you allow direct end-user access to the tables -- which, as noted by others, of course you should not.
0
 
LVL 9

Accepted Solution

by:
solution46 earned 125 total points
ID: 12125535
mastoo: Profiler isn't much use here, I' afraid. It's very good for debugging the connection between your aplication and SQL Server, so that you can see exactly what is being passed to SQL Server, but it doesn't tell you what SQL Server does with it afterwards.

HLEBOEUF: ScottPletcher's idea is a good one; something else you could try is looking through your tables for coumns with type date, and seraching for values with years < 1753 or > 9999. Your sproc is trying to convert from a dte to a smalldate and failing; the only thing that will cause this error is a date outside the acceptable range. Of course, if your database is huge, this wont help but it may give an idea.

s46.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 12126085
Actually you could check for year < 1900 or > 2079, since smalldatetime will only handle values between 1/1/1900 and 6/something/2079.
0
 
LVL 9

Expert Comment

by:solution46
ID: 12126163
aw hell... yeah so it does. I was looking at the wrong bit of the online books :).
0
 
LVL 2

Expert Comment

by:KhunJean
ID: 12127538
an easy way to look through all your tables, stored procedures is to script everything.
Then load it into an editor and you can do a search.
Much easier than opening the individual procedures/triggers and table definitions.
Just right click on a database choose all tasks -> Generate SQL scripts.
This will show a dialog where you can select what you want to script.
Check all the tabs and checkboxes for what you want to script.
0
 
LVL 9

Expert Comment

by:solution46
ID: 12143458
cheers bud,

s46
0
 

Author Comment

by:HLEBOEUF
ID: 12143491
Guys,

Thx for the effort, i knew that there was a error in the data (a datetime that came not-filled from a delphi application), but i just wanted to knwo if there was a way to find (without the print instruction) in what procedure this error occured so that i didn't have to overlook all the data implicated.

BTW, i believe that a database with a lot of code in it (SP / Funcs) activated by triggers is the only environment that works when you have quite some developpers working, from different departments on a database. I wanted all my 'logic', and much data-preparation for the data-warehouse to be in the database, and not to depend on a programmer that has to start some procedures after an update.
As we run quite some olap on the database working by the old nightly -batches system whas not a solution for us.

This error was only due to a lack of data-definition that should have verified the dates before entering the database, ond yes, by a constraint !!!!

Thank you guys.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now