Thursday, March 29, 2012
ExecContextHit through SQL Profiler
Hopefully someone can answer this question. I am running a procedure which u
ses Dynamic SQL ie: Scroll Cursor, User Functions, and sp_executesql to up
date a table. When I run this sp in a query window it runs much faster then
when it is called from a Jo
b. I ran Profiler traces with both methods and noticed that there were SP:Ex
ecContectHit entries in the Traces. Does this mean that at the point that I
see these statements that there is an sp_recompile occurring within the sp?
There is definitely a sligh
t delay in the job sp after each of the SP:ContectHit statements. If it is r
ecompiling is there anyway to prohibit that?
Thanks !!ExecContextHit means that an execution context version (has
session specific info) was found in cache.
Monitor SP:Recompile for recompiles. If you are experiencing
recompiles, you can reference the following:
INF: Troubleshooting Stored Procedure Recompilation
http://support.microsoft.com/?id=243586
You should also make sure the job has set nocount on in the
beginning of the stored procedure and T-SQL batches.
Depending on your version of SQL Server, you can also hit
issues with delays in Agent jobs if you aren't on the latest
service pack. I think it was SQL 7, SP4 that addressed
issues related to this.
-Sue
On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:
>Hello,
>Hopefully someone can answer this question. I am running a procedure which uses Dyn
amic SQL ie: Scroll Cursor, User Functions, and sp_executesql to update a table. W
hen I run this sp in a query window it runs much faster then when it is called from
a J
ob. I ran Profiler traces with both methods and noticed that there were SP:E
xecContectHit entries in the Traces. Does this mean that at the point that I
see these statements that there is an sp_recompile occurring within the sp?
There is definitely a slig
ht delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is t
here anyway to prohibit that?
>Thanks !!|||The set nocount on worked. Thank You !
"Sue Hoegemeier" wrote:
> ExecContextHit means that an execution context version (has
> session specific info) was found in cache.
> Monitor SP:Recompile for recompiles. If you are experiencing
> recompiles, you can reference the following:
> INF: Troubleshooting Stored Procedure Recompilation
> http://support.microsoft.com/?id=243586
> You should also make sure the job has set nocount on in the
> beginning of the stored procedure and T-SQL batches.
> Depending on your version of SQL Server, you can also hit
> issues with delays in Agent jobs if you aren't on the latest
> service pack. I think it was SQL 7, SP4 that addressed
> issues related to this.
> -Sue
> On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
> <nupee@.discussions.microsoft.com> wrote:
>
Job. I ran Profiler traces with both methods and noticed that there were SP:
ExecContectHit entries in the Traces. Does this mean that at the point that
I see these statements that there is an sp_recompile occurring within the sp
? There is definitely a sl
ight delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is
there anyway to prohibit that?[vbcol=seagreen]
>|||Your welcome - thanks for posting back that it fixed the
issue.
-Sue
On Tue, 29 Jun 2004 12:48:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>The set nocount on worked. Thank You !
>"Sue Hoegemeier" wrote:
>
a Job. I ran Profiler traces with both methods and noticed that there were S
P:ExecContectHit entries in the Traces. Does this mean that at the point tha
t I see these statements that there is an sp_recompile occurring within the
sp? There is definitely a s
light delay in the job sp after each of the SP:ContectHit statements. If it is recompiling i
s there anyway to prohibit that?[vbcol=seagreen]
ExecContextHit through SQL Profiler
Hopefully someone can answer this question. I am running a procedure which uses Dynamic SQL ie: Scroll Cursor, User Functions, and sp_executesql to update a table. When I run this sp in a query window it runs much faster then when it is called from a Jo
b. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a sligh
t delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
Thanks !!
ExecContextHit means that an execution context version (has
session specific info) was found in cache.
Monitor SP:Recompile for recompiles. If you are experiencing
recompiles, you can reference the following:
INF: Troubleshooting Stored Procedure Recompilation
http://support.microsoft.com/?id=243586
You should also make sure the job has set nocount on in the
beginning of the stored procedure and T-SQL batches.
Depending on your version of SQL Server, you can also hit
issues with delays in Agent jobs if you aren't on the latest
service pack. I think it was SQL 7, SP4 that addressed
issues related to this.
-Sue
On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:
>Hello,
>Hopefully someone can answer this question. I am running a procedure which uses Dynamic SQL ie: Scroll Cursor, User Functions, and sp_executesql to update a table. When I run this sp in a query window it runs much faster then when it is called from a J
ob. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a slig
ht delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
>Thanks !!
|||ExecContextHit means that an execution context version (has
session specific info) was found in cache.
Monitor SP:Recompile for recompiles. If you are experiencing
recompiles, you can reference the following:
INF: Troubleshooting Stored Procedure Recompilation
http://support.microsoft.com/?id=243586
You should also make sure the job has set nocount on in the
beginning of the stored procedure and T-SQL batches.
Depending on your version of SQL Server, you can also hit
issues with delays in Agent jobs if you aren't on the latest
service pack. I think it was SQL 7, SP4 that addressed
issues related to this.
-Sue
On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:
>Hello,
>Hopefully someone can answer this question. I am running a procedure which uses Dynamic SQL ie: Scroll Cursor, User Functions, and sp_executesql to update a table. When I run this sp in a query window it runs much faster then when it is called from a J
ob. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a slig
ht delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
>Thanks !!
|||The set nocount on worked. Thank You !
"Sue Hoegemeier" wrote:
[vbcol=seagreen]
> ExecContextHit means that an execution context version (has
> session specific info) was found in cache.
> Monitor SP:Recompile for recompiles. If you are experiencing
> recompiles, you can reference the following:
> INF: Troubleshooting Stored Procedure Recompilation
> http://support.microsoft.com/?id=243586
> You should also make sure the job has set nocount on in the
> beginning of the stored procedure and T-SQL batches.
> Depending on your version of SQL Server, you can also hit
> issues with delays in Agent jobs if you aren't on the latest
> service pack. I think it was SQL 7, SP4 that addressed
> issues related to this.
> -Sue
> On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
> <nupee@.discussions.microsoft.com> wrote:
Job. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a sl
ight delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
>
|||Your welcome - thanks for posting back that it fixed the
issue.
-Sue
On Tue, 29 Jun 2004 12:48:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>The set nocount on worked. Thank You !
>"Sue Hoegemeier" wrote:
a Job. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a s
light delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?[vbcol=seagreen]
|||The set nocount on worked. Thank You !
"Sue Hoegemeier" wrote:
[vbcol=seagreen]
> ExecContextHit means that an execution context version (has
> session specific info) was found in cache.
> Monitor SP:Recompile for recompiles. If you are experiencing
> recompiles, you can reference the following:
> INF: Troubleshooting Stored Procedure Recompilation
> http://support.microsoft.com/?id=243586
> You should also make sure the job has set nocount on in the
> beginning of the stored procedure and T-SQL batches.
> Depending on your version of SQL Server, you can also hit
> issues with delays in Agent jobs if you aren't on the latest
> service pack. I think it was SQL 7, SP4 that addressed
> issues related to this.
> -Sue
> On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
> <nupee@.discussions.microsoft.com> wrote:
Job. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a sl
ight delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
>
|||Your welcome - thanks for posting back that it fixed the
issue.
-Sue
On Tue, 29 Jun 2004 12:48:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>The set nocount on worked. Thank You !
>"Sue Hoegemeier" wrote:
a Job. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a s
light delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?[vbcol=seagreen]
Tuesday, March 27, 2012
exec sp_executesql vs. sp_executesql and performance
then not.
Using the profiler, I identified an application-issued statement that
performed poorly. It took this form:
exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @.Parm1)',
N'@.Parm1 int', @.Parm1 = 8609
t2 is a foreign key column, and is indexed.
I took the statement into query analyzer and executed it there. The
query plan showed that it was doing a scan of the primary key index,
which is clustered. That's a bad choice.
I then fiddled with it to see what would result in a good plan.
1) I changed it to hard code the query value (but with the parm
definition still in place. )
It performed well, using the correct index.
Here's how it looked.
exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS
[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],
cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],
cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],
cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],
cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROM
cbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@.Parm1 int',
@.Parm1 = 8609
After doing this, re-executing the original form still gave bad
results.
2) I restored the use of the parm, but removed the 'exec' from the
start.
It performed well.
After that (surprise!) it also performed well in the original form.
What's going on here?elRoyFlynn (lit@.twcny.rr.com) writes:
> t2 is a foreign key column, and is indexed.
> I took the statement into query analyzer and executed it there. The
> query plan showed that it was doing a scan of the primary key index,
> which is clustered. That's a bad choice.
Sometimes it is, sometimes it's not. This is a delicate choice that
the optimizer have to make. Non-clustered index + bookmark lookup, or
clustered index scan? The first strategy fantastic if there are only
a few hits, but disastrous if you hit, say, 30% of the rows. Many page
will be accessed more than once, and it will be a lot slower than a CI
scan.
> 2) I restored the use of the parm, but removed the 'exec' from the
> start.
> It performed well.
> After that (surprise!) it also performed well in the original form.
Probably parameter sniffing. SQL Server caches the query plan for the
query, and the cached plan is built from the parameter value that
query first was run for. That value may have been handled best with
a CI scan.
But it might also be that the statistics were poor initially, and caused
SQL Server to make an incorrect estimate. But SQL Server has auto-
statistics, so it could be that statistics were updated, and the plan
was flushed, and a new plan built.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In this case, the "bad plan" clearly is: a 6-second response time vs.
a sub-second response when the "best" plan is used.
Problem is, the application generated this query in the "exec sp..."
form hundreds of times, getting the bad result each time. It was while
the app was still running that I worked in query analyzer. I executed
the problem sql multiple times, duplicating the bad result, before
trying it the other way. The first time I did it the other way, it
worked well, which also immediately fixed the application. Coincidence
, unrelated to the different execution form, that just at that moment
mss figured out that the other plan was better? I'm skeptical. I
think that something about 'exec sp_.." vs. plain "sp_..." had an
unintended effect.
But thanks for the response, I'll think about it.|||elRoyFlynn (lit@.twcny.rr.com) writes:
> In this case, the "bad plan" clearly is: a 6-second response time vs.
> a sub-second response when the "best" plan is used.
It should be admitted that this is quite common. The optimizer seems to
be overly conservative with regards to non-clustered indexes.
> Coincidence , unrelated to the different execution form, that just at
> that moment mss figured out that the other plan was better? I'm
> skeptical. I think that something about 'exec sp_.." vs. plain "sp_..."
> had an unintended effect.
It could be that the misisng "exec" triggered a recompile of the query,
but from what I know about how the cache works, I can't really see that
it would matter.
What could matter, though, is whether you changed somehting inside
the query. With regards to single queries, the cache is both case-
and space-sensitive. (But the part "EXEC sp_executesql" is not in
the cache, only the first argument to sp_executesql is.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql
Wednesday, March 21, 2012
Exclude system IDs in 2005?
exclude system IDs as was possible in 2000 on the Filters tab. Is this
possible?
TIA, ChrisRHi Chris
System objects are managed completely differently in SQL Server 2005, which
is why I imagine they removed that option.
What data column are you trying to filter, for what types of objects?
You can just use the not like filter to exclude names you're not interested
in.
HTH
Kalen Delaney, SQL Server MVP
"ChrisR" <NotAChance@.ms.com> wrote in message
news:eRn%23sb0sGHA.1288@.TK2MSFTNGP02.phx.gbl...
> Im trying out Profiler in 2005 for my first time, but don't see a way to
> exclude system IDs as was possible in 2000 on the Filters tab. Is this
> possible?
> TIA, ChrisR
>|||Thanks Kalen.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OuvE090sGHA.372@.TK2MSFTNGP06.phx.gbl...
> Hi Chris
> System objects are managed completely differently in SQL Server 2005,
which
> is why I imagine they removed that option.
> What data column are you trying to filter, for what types of objects?
> You can just use the not like filter to exclude names you're not
interested
> in.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "ChrisR" <NotAChance@.ms.com> wrote in message
> news:eRn%23sb0sGHA.1288@.TK2MSFTNGP02.phx.gbl...
>
Exclude system IDs in 2005?
exclude system IDs as was possible in 2000 on the Filters tab. Is this
possible?
TIA, ChrisRHi Chris
System objects are managed completely differently in SQL Server 2005, which
is why I imagine they removed that option.
What data column are you trying to filter, for what types of objects?
You can just use the not like filter to exclude names you're not interested
in.
--
HTH
Kalen Delaney, SQL Server MVP
"ChrisR" <NotAChance@.ms.com> wrote in message
news:eRn%23sb0sGHA.1288@.TK2MSFTNGP02.phx.gbl...
> Im trying out Profiler in 2005 for my first time, but don't see a way to
> exclude system IDs as was possible in 2000 on the Filters tab. Is this
> possible?
> TIA, ChrisR
>|||Thanks Kalen.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OuvE090sGHA.372@.TK2MSFTNGP06.phx.gbl...
> Hi Chris
> System objects are managed completely differently in SQL Server 2005,
which
> is why I imagine they removed that option.
> What data column are you trying to filter, for what types of objects?
> You can just use the not like filter to exclude names you're not
interested
> in.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "ChrisR" <NotAChance@.ms.com> wrote in message
> news:eRn%23sb0sGHA.1288@.TK2MSFTNGP02.phx.gbl...
> > Im trying out Profiler in 2005 for my first time, but don't see a way to
> > exclude system IDs as was possible in 2000 on the Filters tab. Is this
> > possible?
> >
> > TIA, ChrisR
> >
> >
>sql
Wednesday, March 7, 2012
Exception report in the SQL Profiler
I keep getting an Event Class 'Exception' in the SQL Profiler.
The text data is 'Error: 208, Severity: 16, State: 0'
However, neither ObjectID or the ObjectName colums are populated.
Error 208 states that object which does not exist is referenced.
However, I can't seem to pinpoint what it's actually complaining about.
The application that calls the sproc where this occurs reports no errors
and the sprocs do execute fine.
How can I troubleshoot this problem?
Thanks
Frank Rizzo wrote:
> Hello,
> I keep getting an Event Class 'Exception' in the SQL Profiler.
> The text data is 'Error: 208, Severity: 16, State: 0'
> However, neither ObjectID or the ObjectName colums are populated.
> Error 208 states that object which does not exist is referenced.
> However, I can't seem to pinpoint what it's actually complaining
> about.
> The application that calls the sproc where this occurs reports no
> errors and the sprocs do execute fine.
> How can I troubleshoot this problem?
> Thanks
Many 208 errors are generated because of temp table use and do not
indicate there is actually a problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||David Gugick wrote:
> Frank Rizzo wrote:
>
> Many 208 errors are generated because of temp table use and do not
> indicate there is actually a problem.
Ok, but how can I be sure? Also, exactly what about the temp table
actually generates this exception.
|||Frank Rizzo wrote:
> Ok, but how can I be sure? Also, exactly what about the temp table
> actually generates this exception.
When a stored procedure is called and there is no plan in cache, SQL
Server generates a new plan. At compile time, the temp table (assuming
it is created in the proc) does not exist and SQL Server kicks out the
208 error (sometimes multiple ones). That is, an object is accessed, but
does not exist, even though it's really fine.
Once the plan is in cache, subsequent executions don't cause the error
unless you are really dealing with an object that is missing.
If this occurrs from a stored procedure, you can examine the
SP:StmtStarting event just prior to the 208 Exception. That's the
statement that triggered the problem. If you also see a 208 Exception
before the SP:CacheInsert and SP:Starting events, that's because when
the procedure was compiled, the object was missing (that's where you'd
see the errors for temp tables as well on the initial execution).
From outside a stored procedure, have a look at the SQL:StmtStarting or
RPC:Starting event for the trigger text.
There's no easy way to determine the bad from the acceptable 208 errors
other than examining the trace in more detail and seeing what statements
triggered the problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Exception report in the SQL Profiler
I keep getting an Event Class 'Exception' in the SQL Profiler.
The text data is 'Error: 208, Severity: 16, State: 0'
However, neither ObjectID or the ObjectName colums are populated.
Error 208 states that object which does not exist is referenced.
However, I can't seem to pinpoint what it's actually complaining about.
The application that calls the sproc where this occurs reports no errors
and the sprocs do execute fine.
How can I troubleshoot this problem?
ThanksFrank Rizzo wrote:
> Hello,
> I keep getting an Event Class 'Exception' in the SQL Profiler.
> The text data is 'Error: 208, Severity: 16, State: 0'
> However, neither ObjectID or the ObjectName colums are populated.
> Error 208 states that object which does not exist is referenced.
> However, I can't seem to pinpoint what it's actually complaining
> about.
> The application that calls the sproc where this occurs reports no
> errors and the sprocs do execute fine.
> How can I troubleshoot this problem?
> Thanks
Many 208 errors are generated because of temp table use and do not
indicate there is actually a problem.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David Gugick wrote:
> Frank Rizzo wrote:
>> Hello,
>> I keep getting an Event Class 'Exception' in the SQL Profiler.
>> The text data is 'Error: 208, Severity: 16, State: 0'
>> However, neither ObjectID or the ObjectName colums are populated.
>> Error 208 states that object which does not exist is referenced.
>> However, I can't seem to pinpoint what it's actually complaining
>> about.
>> The application that calls the sproc where this occurs reports no
>> errors and the sprocs do execute fine.
>> How can I troubleshoot this problem?
>> Thanks
>
> Many 208 errors are generated because of temp table use and do not
> indicate there is actually a problem.
Ok, but how can I be sure? Also, exactly what about the temp table
actually generates this exception.|||Frank Rizzo wrote:
> Ok, but how can I be sure? Also, exactly what about the temp table
> actually generates this exception.
When a stored procedure is called and there is no plan in cache, SQL
Server generates a new plan. At compile time, the temp table (assuming
it is created in the proc) does not exist and SQL Server kicks out the
208 error (sometimes multiple ones). That is, an object is accessed, but
does not exist, even though it's really fine.
Once the plan is in cache, subsequent executions don't cause the error
unless you are really dealing with an object that is missing.
If this occurrs from a stored procedure, you can examine the
SP:StmtStarting event just prior to the 208 Exception. That's the
statement that triggered the problem. If you also see a 208 Exception
before the SP:CacheInsert and SP:Starting events, that's because when
the procedure was compiled, the object was missing (that's where you'd
see the errors for temp tables as well on the initial execution).
From outside a stored procedure, have a look at the SQL:StmtStarting or
RPC:Starting event for the trigger text.
There's no easy way to determine the bad from the acceptable 208 errors
other than examining the trace in more detail and seeing what statements
triggered the problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Exception report in the SQL Profiler
I keep getting an Event Class 'Exception' in the SQL Profiler.
The text data is 'Error: 208, Severity: 16, State: 0'
However, neither ObjectID or the ObjectName colums are populated.
Error 208 states that object which does not exist is referenced.
However, I can't seem to pinpoint what it's actually complaining about.
The application that calls the sproc where this occurs reports no errors
and the sprocs do execute fine.
How can I troubleshoot this problem?
ThanksFrank Rizzo wrote:
> Hello,
> I keep getting an Event Class 'Exception' in the SQL Profiler.
> The text data is 'Error: 208, Severity: 16, State: 0'
> However, neither ObjectID or the ObjectName colums are populated.
> Error 208 states that object which does not exist is referenced.
> However, I can't seem to pinpoint what it's actually complaining
> about.
> The application that calls the sproc where this occurs reports no
> errors and the sprocs do execute fine.
> How can I troubleshoot this problem?
> Thanks
Many 208 errors are generated because of temp table use and do not
indicate there is actually a problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David Gugick wrote:
> Frank Rizzo wrote:
>
>
> Many 208 errors are generated because of temp table use and do not
> indicate there is actually a problem.
Ok, but how can I be sure? Also, exactly what about the temp table
actually generates this exception.|||Frank Rizzo wrote:
> Ok, but how can I be sure? Also, exactly what about the temp table
> actually generates this exception.
When a stored procedure is called and there is no plan in cache, SQL
Server generates a new plan. At compile time, the temp table (assuming
it is created in the proc) does not exist and SQL Server kicks out the
208 error (sometimes multiple ones). That is, an object is accessed, but
does not exist, even though it's really fine.
Once the plan is in cache, subsequent executions don't cause the error
unless you are really dealing with an object that is missing.
If this occurrs from a stored procedure, you can examine the
SP:StmtStarting event just prior to the 208 Exception. That's the
statement that triggered the problem. If you also see a 208 Exception
before the SP:CacheInsert and SP:Starting events, that's because when
the procedure was compiled, the object was missing (that's where you'd
see the errors for temp tables as well on the initial execution).
From outside a stored procedure, have a look at the SQL:StmtStarting or
RPC:Starting event for the trigger text.
There's no easy way to determine the bad from the acceptable 208 errors
other than examining the trace in more detail and seeing what statements
triggered the problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com
Exception repeately occurring
I have a strange problem with my SQL server 2k instllation - every 10
mintutes when I have the Profiler trace with the entire "Errors" Event
category selected, the following 5 exceptions show up in the trace and
that too for the same SPID.
Error: 16955, Severity: 16, State: 2
Error: 16945, Severity: 16, State: 1
Error: 16955, Severity: 16, State: 2
Error: 16945, Severity: 16, State: 1
Error: 16955, Severity: 16, State: 2
Error: 16945, Severity: 16, State: 1
I have no clue why this is occurring - I tried running a trace with the
SP:StmtCompleted event on, but no other stored procedures show up with
the same spid close to the time where this exception is logged.
Does anyone have a clue as to why this error is occurring ?
RahulPondy (fd96121@.yahoo.com) writes:
> I have a strange problem with my SQL server 2k instllation - every 10
> mintutes when I have the Profiler trace with the entire "Errors" Event
> category selected, the following 5 exceptions show up in the trace and
> that too for the same SPID.
> Error: 16955, Severity: 16, State: 2
> Error: 16945, Severity: 16, State: 1
> Error: 16955, Severity: 16, State: 2
> Error: 16945, Severity: 16, State: 1
> Error: 16955, Severity: 16, State: 2
> Error: 16945, Severity: 16, State: 1
> I have no clue why this is occurring - I tried running a trace with the
> SP:StmtCompleted event on, but no other stored procedures show up with
> the same spid close to the time where this exception is logged.
So what you in a such situation like this is this:
select * from master..sysmessages where error in (16955, 16945)
You could also have looked up the errors in Books Online, by simply
searching for them. This could give you the bonus that there might be
entire topic to troubleshoot the problem. I would not expect that in
this case, though.
These are the messages:
16945 The cursor was not declared.
16955 Could not create an acceptable cursor.
I would guess that 16945 is a consequence of 16955.
Apparently there is some code out there where the cursor declaration
fails, and where there is no error handling, so that execution continues.
Note that this may not have to be a stored procedure. Hypothetically
it could be a server-side cursor initiated by some client API as well.
In any case, it's a problem specific to that process, and it is not that
your server is about to go belly-up.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp