Solved

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

Posted on 2004-09-22
13
330 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

914 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

15 Experts available now in Live!

Get 1:1 Help Now