ExecContextHit through SQL Profiler

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]

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]

EXEC permission on a SCHEMA COLLECTION?

I have a permissions problem with a table/procedure that I hope someone can help me with.

To set the scene .......

All my procs/tables/functions etc are owned by dbo.

I have a windows security group that is granted permissions to EXEC all procs. No one has permissions to tables.

I have a table that has an XML column and the column has a schema collection bound to it.

The table has a computed column that relies on a function to extract a datetime element from the XML in the XML column and I have an index on this computed column.

I have a proc that selects from this table and uses the computed date column for filtering. However, the select statement is build dynamically and uses sp_execute to perform the SELECT. This of course breaks the ownership chain.

To fix the above I have a user that was created from a certificate and the above proc is signed with the certificate. The user is granted select privileges on the table. This fixes the problem. (In fact, all procs/functions/triggers are signed in this way).

Now (finally) the problem ....

When I run the above proc as admin, it works fine.

When I run it as a member of the security group (mentioned earlier) I receive ...

EXECUTE permission is denied on object 'my_schema_collection', database 'mydb', schema 'dbo'

The 'my_schema_collection' mentioned above is the schema collection to which my xml column is bound.

What? How can I grant EXEC permission to a schema collection?

Anyone have any ideas?

Thanks,

~swg

http://msdn2.microsoft.com/en-us/library/ms179867.aspx fyi.

|||

Hi,

Many thanks for this. Of course it solved my problem. (I hate it when people just respond with a link .... it just highlights my foolishness )

Seriously though, as a follow-up question and for my own information, from a security standpoint what is the purpose of granting exec to a schema collection? I'm trying to figure out what the advantage is of having to apply an additional exec permission on a schema collection given that permissions are already granted to tables/procs etc. that use it.

I guess I should start by asking what actually IS exec permission i.e. what does is permit in this context?

Thanks,

~swg

|||

You should check on the SQL Server XML forum for details on xml schema collections and their security.

Thanks
Laurentiu

EXEC in SQL Functions

Hi,

I need to pass a table name and id to a function and return a row count
I need to use EXEC or SP_EXECUTESQL to run dynamic SQL
It wont work in functions. Following is my function

alter FUNCTION [dbo].[GetRowCount] (@.TblName NVARCHAR(25) , @.Itemid INT)
RETURNS INT
AS BEGIN
DECLARE @.RowCnt INT
set @.RowCnt = 0
DECLARE @.Sqlstring nvarchar(2000)

set @.Sqlstring = 'SELECT @.RowCnt = COUNT(*) FROM ['+ @.TblName +'] WHERE Itemid = '+ convert(varchar(10),@.Itemid)
EXEC @.Sqlstring

RETURN @.RowCnt
END

while executing this I get the following error ....
"Only functions and extended stored procedures can be executed from within a function." and "Incorrect syntax near the keyword 'EXEC' "

does anyone have any ideas of this ?
Thanks.
vidhya

Moving to the T-SQL forum.|||

You can't use sp_executesql inside functions.

Why would you want to do this? Perhaps you can change the calling mechanism?

|||You cannot execute a command with exec or sp_executesql nor can execute a stored procedure in a function.
HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

instead of using sp_execute, write another function and pass that variable value into that function.

u can call function into another function.

|||

You cannot use an exec statement with in a user defined function. What i can see in your code is you are returning single integer value from your function which you can very well do in a stored procedure using return statement there as well.

I think you should do it in a stored procedure

sql

EXEC in SQL Functions

Hi,

I need to pass a table name and id to a function and return a row count
I need to use EXEC or SP_EXECUTESQL to run dynamic SQL
It wont work in functions. Following is my function

alter FUNCTION [dbo].[GetRowCount] (@.TblName NVARCHAR(25) , @.Itemid INT)
RETURNS INT
AS BEGIN
DECLARE @.RowCnt INT
set @.RowCnt = 0
DECLARE @.Sqlstring nvarchar(2000)

set @.Sqlstring = 'SELECT @.RowCnt = COUNT(*) FROM ['+ @.TblName +'] WHERE Itemid = '+ convert(varchar(10),@.Itemid)
EXEC @.Sqlstring

RETURN @.RowCnt
END

while executing this I get the following error ....
"Only functions and extended stored procedures can be executed from within a function." and "Incorrect syntax near the keyword 'EXEC' "

does anyone have any ideas of this ?
Thanks.
vidhya

Moving to the T-SQL forum.|||

You can't use sp_executesql inside functions.

Why would you want to do this? Perhaps you can change the calling mechanism?

|||You cannot execute a command with exec or sp_executesql nor can execute a stored procedure in a function.
HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

instead of using sp_execute, write another function and pass that variable value into that function.

u can call function into another function.

|||

You cannot use an exec statement with in a user defined function. What i can see in your code is you are returning single integer value from your function which you can very well do in a stored procedure using return statement there as well.

I think you should do it in a stored procedure

Exception comes from database,

Hi All,

Currently i am working on some web application, and facing some exceptions. Actually I am throwing exception from DB functions in some of cases, and then displaying error.aspx (custom error page). It is working fine.

But when i change language of browser from english to any other language, first time my custom error page comes, but afterwards, following error page comes:

Server error in '/sampletest' application
Exception of Project.module.myException is thrown.

Please help if any idea for same.

Please also let me know whether i have to kept lots of aspx files for different types of exception or messages.

Thanks In Advance
Arnold

If you give a sample of code then its easier to find where issue is...

ExcelMDX assembly source property is blank

Hi, We're using SQL Server 2005 SP5. Excel 2005 was recently installed on the server so we could use Excel functions in MDX. However, we still get the function does not exist. For example: Query (1, 20) The '[Excel].[Ceiling]' function does not exist. VBA functions work fine.

When looking at the properties of the ExcelMDX Assembly (via SQL Server Mgmt Studio) the Source property is blank.

It just appears that Excel is not registered with Analysis Services. I've searched everywhere for an answer and found a few others describing the same problem but no suggested solutions. Any help would be appreciated.

Thanks,

Ed

I don't have any documentation on this, but I believe that for COM based assemblies that the source property is meant to be blank. The way COM activation works, it does not make sense to load COM assembly source into SSAS. Where as .Net source can be re-assembled into a byte stream and executed directly.

The source property is blank on my laptop and the Excel functions work fine.

Does Excel run OK on the server? It sounds like it might be an issue with the COM registration.

Another option might be that, if you have someone with .Net skills, you might find that implementing your own .Net stored proc might be a faster solution. Generally speaking there is less marshalling overhead with COM assemblies than .Net ones, but the Excel calls have to go cross process which can have a significant performance impact.