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

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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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,

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. :)

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...

IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Scott PletcherSenior DBACommented:
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.
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.

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. :)
Scott PletcherSenior DBACommented:
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.
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Actually you could check for year < 1900 or > 2079, since smalldatetime will only handle values between 1/1/1900 and 6/something/2079.
aw hell... yeah so it does. I was looking at the wrong bit of the online books :).
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.
cheers bud,

HLEBOEUFAuthor Commented:

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.