December 20, 2021 at 7:23 pm
There is a error in the application logs with table name not allowing nulls into column. However, the sql server error log does not show which proc is causing the error. Looked at monitoring tool did not find anything associated. Is there any other place to look into it. Do yo think the better option is going forward have them use try catch to capture the details?
December 20, 2021 at 7:47 pm
My HOPE is that your application is throwing an error. Since the application is known, you either have the source to it OR can contact the vendor to get them to fix the source. If you have the source, read through it or trace through it to reproduce the error. If it is a 3rd party tool, let them know what you did and get them to help you fix it.
I would personally not tackle this from the database side trying to find what stored procedure writes to the table. BUT if you do want to go that route, I highly recommend SQL Search (a free tool from RedGate) which allows you to search through your database to find all of the stored procedures (or any objects) that contain specific keywords like your table name.
Now with the above said, it MIGHT not be a stored procedure that is updating the table either. It could VERY easily be that the application is writing directly to the table. It is a practice I am not a fan of, but it is done at times, and is more common when an application supports multiple database engines.
My opinion - going forward, have the application do sanity checks to ensure that all required fields have values populated before passing it over to SQL.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 21, 2021 at 2:44 pm
You can use Extended Events to capture errors. This can include, if needed, the T-SQL statement or Object_ID of the query involved.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 22, 2021 at 1:00 am
There is a error in the application logs with table name not allowing nulls into column. However, the sql server error log does not show which proc is causing the error. Looked at monitoring tool did not find anything associated. Is there any other place to look into it. Do yo think the better option is going forward have them use try catch to capture the details?
I'd be on the developers of that application like white on rice about what they haven't included in the application logs.
I'll also tell you that there's a really good chance that it's not a proc to begin with. Might be some "ad hoc" code from the app and the app logs should show that or at least the part of the app it was failing in.
Barring all of that, Grant is spot on about being able to use Extended Events to trap for the error and display the code and other gazintas to troubleshoot the problem.
Even when you find it, good luck with getting people to fix it. I'd bet Credits to Navy Beans that the proposed fix will be to remove the NOT NULL constraint from the column.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply