May 28, 2015 at 7:37 am
1. Run SQL Profiler with T-SQL and Errors and Warnings (Exception).
2. Open a new query window in SSMS and run the following script (note the IF/DROP is not required to reproduce, just makes it easier to run the statement again):
IF OBJECT_ID('tempdb..#tab1', 'U') IS NOT NULL
DROP TABLE #tab1;
Create table #tab1 (id int)
insert into #tab1 values (1)
Expected Results:
-SSMS shows query executed successfully and Messages: "(1 row(s) affected)".
-SQL Profiler shows statements completes.
Actual Results:
-SSMS shows query executed successfully and Messages: "(1 row(s) affected)".
-SQL Profiler shows "Invalid object name '#tab1'" Exception prior to SQL:BatchStarting ONLY on the first time you run the statement from the query window (connection) . Run it again in the same query window and you don't get the Exception in SQL Profiler.
Why is SQL throwing the exception on the first execution of the CREATE + INSERT batch, and only in SQL Profiler? I ask because this appears to be throwing an exception in an application (using LINQ). We have multiple separate instances of the application using the same SQL and the error is not thrown in all of them and when its thrown it appears at random times. This is very similar to this user's issue.
May 28, 2015 at 8:30 am
Because when you first run it, the table doesn't exist, so the if statement returns 'invalid object' in the background.
The next time you run the whole statement, the table does exist because the first run created. As such no error message.
May 28, 2015 at 8:36 am
cunningham (5/28/2015)
Because when you first run it, the table doesn't exist, so the if statement returns 'invalid object' in the background.The next time you run the whole statement, the table does exist because the first run created. As such no error message.
That was a theory however doesn't look to be the case as I just tried and got no such exception.
May 28, 2015 at 8:48 am
cunningham (5/28/2015)
cunningham (5/28/2015)
Because when you first run it, the table doesn't exist, so the if statement returns 'invalid object' in the background.The next time you run the whole statement, the table does exist because the first run created. As such no error message.
That was a theory however doesn't look to be the case as I just tried and got no such exception.
Actually i do get exception 'Error: 208, Severity: 16, State: 0' when I run it for the first time, but not the second. so my first thoughts might be correct after all
May 28, 2015 at 9:04 am
It seems to be due to deferred name resolution, and something only picked up by profiler. See Gail Shaw's answer on this thread: http://stackoverflow.com/questions/812522/how-to-find-what-caused-errors-reported-in-a-sql-server-profiler-trace.
You can confirm by including SQL:StmtRecompile in the trace.
You'll see a deferred compile for the INSERT statement, and if you just run the code up to and including the CREATE statement, and then separately run the insert, the exception does not occur.
On subsequent runs in the same session, the deferred compile does not occur, so you don't see the exception.
I haven't been able to track down the Connect item referenced in Gail's response on that thread yet, though.
Cheers!
May 28, 2015 at 9:08 am
EDIT: Posted right after Jacob's response
I'll try that and see if it explains how it could possibly surface in .Net
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply