Hello,
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]
Showing posts with label cursor. Show all posts
Showing posts with label cursor. Show all posts
Thursday, March 29, 2012
ExecContextHit through SQL Profiler
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 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]
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]
exec stored proc and use udf or select in it
Hi,
I have a stored procedure which has a cursor that populates a table
datatype variabel with few fields on eof which is of type [nText].
Now for each row in the above table type variabel I have to call
another stored proc to which I have to pass the [nText] field value.
Can anyone suggest how to do it?
Thanks,
Vikas.Could you call the stored procedure passing the ntext value before inserting
it into the table variable? You could call it after inserting as well, but
before fetching the next value from the cursor.
"vikassah@.gmail.com" wrote:
> Hi,
> I have a stored procedure which has a cursor that populates a table
> datatype variabel with few fields on eof which is of type [nText].
> Now for each row in the above table type variabel I have to call
> another stored proc to which I have to pass the [nText] field value.
> Can anyone suggest how to do it?
> Thanks,
> Vikas.
>|||(vikassah@.gmail.com) writes:
> I have a stored procedure which has a cursor that populates a table
> datatype variabel with few fields on eof which is of type [nText].
> Now for each row in the above table type variabel I have to call
> another stored proc to which I have to pass the [nText] field value.
> Can anyone suggest how to do it?
I don't really understand what you are trying to do. Maybe you could
be more specific?
Note, though that you cannot assign to variables of the type ntext,
so you may have to rework your strategy.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
I have a stored procedure which has a cursor that populates a table
datatype variabel with few fields on eof which is of type [nText].
Now for each row in the above table type variabel I have to call
another stored proc to which I have to pass the [nText] field value.
Can anyone suggest how to do it?
Thanks,
Vikas.Could you call the stored procedure passing the ntext value before inserting
it into the table variable? You could call it after inserting as well, but
before fetching the next value from the cursor.
"vikassah@.gmail.com" wrote:
> Hi,
> I have a stored procedure which has a cursor that populates a table
> datatype variabel with few fields on eof which is of type [nText].
> Now for each row in the above table type variabel I have to call
> another stored proc to which I have to pass the [nText] field value.
> Can anyone suggest how to do it?
> Thanks,
> Vikas.
>|||(vikassah@.gmail.com) writes:
> I have a stored procedure which has a cursor that populates a table
> datatype variabel with few fields on eof which is of type [nText].
> Now for each row in the above table type variabel I have to call
> another stored proc to which I have to pass the [nText] field value.
> Can anyone suggest how to do it?
I don't really understand what you are trying to do. Maybe you could
be more specific?
Note, though that you cannot assign to variables of the type ntext,
so you may have to rework your strategy.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Subscribe to:
Posts (Atom)