July 17, 2008 at 7:55 am
Hello,
Is there a way for me to determine when a certain field gets a null value? This field is giving me problems when there is a null value so I'm trying to determine what statement and also when this particular field obtains the null value. There are tons of statements dealing with that field and it's only happening in production so it's been a headache to try to find the culprit. I'm not that savvy at SQL so, is there something I could do in Profiler to maybe alert me when the field gets a null value? Or maybe adding a trigger to that table, whenever the null value is put in the field... do something? Not sure what that something could be so that it could help me find the statement.
Any clues?
BTW, I can't change the schema and make that a non-nullable field.
July 17, 2008 at 8:09 am
You could use profiler to create a trace filtering on that tables object_id. Save the trace results to a table and then query looking for when that column is null.
Are the inserts/updates being done with ad-hoc SQL or stored procedures? You could also fix the code that accesses the column to correctly handle nulls, this is fairly easily done with Coalesce(column, [desired value]) in your SQL.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 17, 2008 at 8:25 am
Thanks for your reply. I've never done this so I'm doing some research now on how to filter by object_id and put the trace results in a table - so far I've found that by doing this I'd only see certain events in the trace so I'm not sure if this would help me.
http://support.microsoft.com/kb/q303445/
Anyways, there is a combination of both ad-hoc statements / stored proc calls dealing with this table/field, and changing the code is not an option at this point. I'm trying to understand the issue first if you know what I mean.
Thanks again.
July 17, 2008 at 8:37 am
You would want events from the stored procedures and T-SQL event classes. If you use SP:StmtCompleted and SQL:StmtCompleted you can filter on ObjectID and should get what you want.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 17, 2008 at 9:22 am
Got it. Now, if I understand you correctly, this will give me all the statements that involve this table. Is it possible to determine which statement is inserting the null value in the field?
I don't mean to repeat myself but I'm trying to understand this thoroughly.
Thanks a bunch!
July 17, 2008 at 9:51 am
The TextData column in the trace/profiler will have the exact sql statement(s) that have run. If you export the results to a table and then you can check the textdata column for the text "null" so something like:
Select
*
From
tracetable
Where
textdata like '%null%'
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 17, 2008 at 2:52 pm
why not alter the column and add the NOT NULL clause.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 17, 2008 at 3:43 pm
If I do that, the application will blow in front of the user when the first NULL value comes up.. the code is not ready to handle that for now. That's why I mentioned that I can't change the schema right now.
I need to find the root of the problem before I can act on it.
July 17, 2008 at 3:48 pm
Thanks Jack for your help / patience. Unfortunately (and I hate sounding too negative) the following doesn't seem to work for me:
Select
*
From
tracetable
Where
textdata like '%null%'
Most of the times, the word null is not going to be spelled out in the statement, as the field is being updated by a variable or by some function that returns "null"... back to square 1.
July 17, 2008 at 4:04 pm
If a SQL function is setting the value to null you should see that happening. Really the best thing is to identify the offending code and use isnull or coalesce to set it to a value. Then when you have that fix existing rows with an update.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply