resource limit was reached

Error:
The OLE DB provider "SQLNCLI" for linked server reported an error. Execution
terminated by the provider because a resource limit was reached.
Situation:
Large query is pulled from one server to another (data warehouse).
Suspect:
DTC on the Windows server 2003 where the source server is located (SQL 2k)
does not have DTC enabled:
Looking for:
A work-around as we are in a situation where the server cannot yet be
rebooted.
Question:
Is there a way to configure named pipes to effectively handle the resource
limitation on linked servers?
--
Regards,
JamieAs a workaround, can you try using TCP\IP provider for linking?
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:A4DB5E7F-56F3-46AE-9858-0F67546B82ED@.microsoft.com...
> Error:
> The OLE DB provider "SQLNCLI" for linked server reported an error.
> Execution
> terminated by the provider because a resource limit was reached.
> Situation:
> Large query is pulled from one server to another (data warehouse).
> Suspect:
> DTC on the Windows server 2003 where the source server is located (SQL 2k)
> does not have DTC enabled:
> Looking for:
> A work-around as we are in a situation where the server cannot yet be
> rebooted.
> Question:
> Is there a way to configure named pipes to effectively handle the resource
> limitation on linked servers?
> --
> Regards,
> Jamie|||I think I may be misunderstanding. I'm unaware of TCP/IP configurations
other than what I already use. Which it may be that you misunderstood my
question, thinking I am using named pipes (not so - have named pipes turned
off).
Thought is - Will named pipes offer a temporary solution to overcome the
resource limitation imposed by the WinSvr2003 R2 without the DTC turned on?
Dunno enough to guess whether there is something in it that will work.
BACKGROUND:
I currently use TCP/IP and aliasing and I suspect the linked servers use
that already to communicate.
When we access a view on a SQL 2000 server using SQL 2005 on another server
(both are WinSvr 2003 R2 64 bit).
There is a problem with the DTC (I did not mention this one - I am waiting
for a server reboot to fix it - http://support.microsoft.com/kb/329332 or
http://support.microsoft.com/?kbid=873160 The operation could not be
performed because the OLE DB provider 'SQLOLEDB' was unable to begin a
distributed transaction. OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a]. [OLE/DB provider
returned message: New transaction cannot enlist in the specified transaction
coordinator. ]).
The one housing SQL 2000 does not yet have DTC setup for network access.
The situation does not allow us to reboot (short of an emergency of which
this is not - instead each view must be re-written to get around the resource
issue - usually just create a sub-query in place of the view).
Herein lies the problem: After rewriting a bunch of failing views we
discovered that if a view is altered and consumes more resources, the
borderline ones we use for data-warehousing archive information at night can
fail for no other reason than that - view times out - resource limit
reached... blah blah). I hate it when a job fails.
Regards,
Jamie
"TheSQLGuru" wrote:
> As a workaround, can you try using TCP\IP provider for linking?
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:A4DB5E7F-56F3-46AE-9858-0F67546B82ED@.microsoft.com...
> > Error:
> > The OLE DB provider "SQLNCLI" for linked server reported an error.
> > Execution
> > terminated by the provider because a resource limit was reached.
> > Situation:
> > Large query is pulled from one server to another (data warehouse).
> > Suspect:
> > DTC on the Windows server 2003 where the source server is located (SQL 2k)
> > does not have DTC enabled:
> > Looking for:
> > A work-around as we are in a situation where the server cannot yet be
> > rebooted.
> >
> > Question:
> > Is there a way to configure named pipes to effectively handle the resource
> > limitation on linked servers?
> > --
> > Regards,
> > Jamie
>
>|||Misread first post. My bad. I don't know that there is a way to cut DTC
out of the loop, regardless of what connection mechanism you are using. I
don't have any other suggestions for possible workarounds for your issue
either. :(
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:AF702DC6-3A07-4D85-9713-8C5406099EED@.microsoft.com...
>I think I may be misunderstanding. I'm unaware of TCP/IP configurations
> other than what I already use. Which it may be that you misunderstood my
> question, thinking I am using named pipes (not so - have named pipes
> turned
> off).
> Thought is - Will named pipes offer a temporary solution to overcome the
> resource limitation imposed by the WinSvr2003 R2 without the DTC turned
> on?
> Dunno enough to guess whether there is something in it that will work.
> BACKGROUND:
> I currently use TCP/IP and aliasing and I suspect the linked servers use
> that already to communicate.
> When we access a view on a SQL 2000 server using SQL 2005 on another
> server
> (both are WinSvr 2003 R2 64 bit).
> There is a problem with the DTC (I did not mention this one - I am waiting
> for a server reboot to fix it - http://support.microsoft.com/kb/329332 or
> http://support.microsoft.com/?kbid=873160 The operation could not be
> performed because the OLE DB provider 'SQLOLEDB' was unable to begin a
> distributed transaction. OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a]. [OLE/DB provider
> returned message: New transaction cannot enlist in the specified
> transaction
> coordinator. ]).
> The one housing SQL 2000 does not yet have DTC setup for network access.
> The situation does not allow us to reboot (short of an emergency of which
> this is not - instead each view must be re-written to get around the
> resource
> issue - usually just create a sub-query in place of the view).
> Herein lies the problem: After rewriting a bunch of failing views we
> discovered that if a view is altered and consumes more resources, the
> borderline ones we use for data-warehousing archive information at night
> can
> fail for no other reason than that - view times out - resource limit
> reached... blah blah). I hate it when a job fails.
>
> --
> Regards,
> Jamie
>
> "TheSQLGuru" wrote:
>> As a workaround, can you try using TCP\IP provider for linking?
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
>> news:A4DB5E7F-56F3-46AE-9858-0F67546B82ED@.microsoft.com...
>> > Error:
>> > The OLE DB provider "SQLNCLI" for linked server reported an error.
>> > Execution
>> > terminated by the provider because a resource limit was reached.
>> > Situation:
>> > Large query is pulled from one server to another (data warehouse).
>> > Suspect:
>> > DTC on the Windows server 2003 where the source server is located (SQL
>> > 2k)
>> > does not have DTC enabled:
>> > Looking for:
>> > A work-around as we are in a situation where the server cannot yet be
>> > rebooted.
>> >
>> > Question:
>> > Is there a way to configure named pipes to effectively handle the
>> > resource
>> > limitation on linked servers?
>> > --
>> > Regards,
>> > Jamie
>>

Repost:External Assemblies in Reports

Hi ALL,
Is it true that any custom assembly that I create ,by defualt has
"Execution" permission and if it dosn't attempt to have access to protected
resources like files ,that secuity policy will do and I don't need to change
any security policy?
I've cretaed a simple Assemly which only returns an string ,and I'he given
it full permission in report server security policy ,but I still get a
security error...
Any Sugessions?
I appreciate your help.
ALIThis sounds like your custom assembly does more than "just return a string".
Also, just changing the security policy configuration is not sufficient if
your custom assembly needs more than ExecutionOnly permissions. You also
need to explicitly assert permissions in the code of your custom assembly.
Some articles about using custom code / custom assemblies:
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ERSCstCode.asp
*
http://msdn.microsoft.com/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
Also, if your custom assembly is signed you'll need to put APTCA on it.
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_rdl_2l7m.asp
Debugging custom assembly issues:
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_rdl_8wyq.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"ALI-R" <newbie@.microsoft.com> wrote in message
news:e40e4O45EHA.2592@.TK2MSFTNGP09.phx.gbl...
> Hi ALL,
> Is it true that any custom assembly that I create ,by defualt has
> "Execution" permission and if it dosn't attempt to have access to
protected
> resources like files ,that secuity policy will do and I don't need to
change
> any security policy?
> I've cretaed a simple Assemly which only returns an string ,and I'he given
> it full permission in report server security policy ,but I still get a
> security error...
> Any Sugessions?
> I appreciate your help.
> ALI
>
>|||I think I solved the problem,,,
I added "AllowPartiallyTrustedCallers" attribute to my assembly's manifiest
and now it is working.I think because "Host Expression Assemby" ,which is
responsible to call my custom assembly,has only "Execution" permission,it is
somehow classified as partially Trusted Caller and it can't call my assemly
without my assemly being marked by that attribute.
No My assemly dos nothing except returning a sime string which has been
constructed in its construcotr.
Anyways,thanks for your help .
Ali
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:OX1d$p%235EHA.4004@.tk2msftngp13.phx.gbl...
> This sounds like your custom assembly does more than "just return a
string".
> Also, just changing the security policy configuration is not sufficient if
> your custom assembly needs more than ExecutionOnly permissions. You also
> need to explicitly assert permissions in the code of your custom assembly.
> Some articles about using custom code / custom assemblies:
> *
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ERSCstCode.asp
> *
>
http://msdn.microsoft.com/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
> Also, if your custom assembly is signed you'll need to put APTCA on it.
> http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_rdl_2l7m.asp
> Debugging custom assembly issues:
> http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_rdl_8wyq.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "ALI-R" <newbie@.microsoft.com> wrote in message
> news:e40e4O45EHA.2592@.TK2MSFTNGP09.phx.gbl...
> > Hi ALL,
> >
> > Is it true that any custom assembly that I create ,by defualt has
> > "Execution" permission and if it dosn't attempt to have access to
> protected
> > resources like files ,that secuity policy will do and I don't need to
> change
> > any security policy?
> >
> > I've cretaed a simple Assemly which only returns an string ,and I'he
given
> > it full permission in report server security policy ,but I still get a
> > security error...
> >
> > Any Sugessions?
> >
> > I appreciate your help.
> > ALI
> >
> >
> >
>|||Yes, if the custom assembly is signed, you need to have the
AllowPartiallyTrustedCallers attribute set (this is what I meant with APTCA
in the previous posting).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"ALI-R" <newbie@.microsoft.com> wrote in message
news:%23k9HrDF6EHA.3708@.TK2MSFTNGP14.phx.gbl...
> I think I solved the problem,,,
> I added "AllowPartiallyTrustedCallers" attribute to my assembly's
manifiest
> and now it is working.I think because "Host Expression Assemby" ,which is
> responsible to call my custom assembly,has only "Execution" permission,it
is
> somehow classified as partially Trusted Caller and it can't call my
assemly
> without my assemly being marked by that attribute.
> No My assemly dos nothing except returning a sime string which has been
> constructed in its construcotr.
> Anyways,thanks for your help .
> Ali
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:OX1d$p%235EHA.4004@.tk2msftngp13.phx.gbl...
> > This sounds like your custom assembly does more than "just return a
> string".
> > Also, just changing the security policy configuration is not sufficient
if
> > your custom assembly needs more than ExecutionOnly permissions. You also
> > need to explicitly assert permissions in the code of your custom
assembly.
> >
> > Some articles about using custom code / custom assemblies:
> > *
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ERSCstCode.asp
> > *
> >
>
http://msdn.microsoft.com/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
> >
> > Also, if your custom assembly is signed you'll need to put APTCA on it.
> > http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_rdl_2l7m.asp
> > Debugging custom assembly issues:
> > http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_rdl_8wyq.asp
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "ALI-R" <newbie@.microsoft.com> wrote in message
> > news:e40e4O45EHA.2592@.TK2MSFTNGP09.phx.gbl...
> > > Hi ALL,
> > >
> > > Is it true that any custom assembly that I create ,by defualt has
> > > "Execution" permission and if it dosn't attempt to have access to
> > protected
> > > resources like files ,that secuity policy will do and I don't need to
> > change
> > > any security policy?
> > >
> > > I've cretaed a simple Assemly which only returns an string ,and I'he
> given
> > > it full permission in report server security policy ,but I still get a
> > > security error...
> > >
> > > Any Sugessions?
> > >
> > > I appreciate your help.
> > > ALI
> > >
> > >
> > >
> >
> >
>|||Thanks for your excellent help.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23$g0cwG6EHA.3076@.TK2MSFTNGP15.phx.gbl...
> Yes, if the custom assembly is signed, you need to have the
> AllowPartiallyTrustedCallers attribute set (this is what I meant with
APTCA
> in the previous posting).
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "ALI-R" <newbie@.microsoft.com> wrote in message
> news:%23k9HrDF6EHA.3708@.TK2MSFTNGP14.phx.gbl...
> > I think I solved the problem,,,
> > I added "AllowPartiallyTrustedCallers" attribute to my assembly's
> manifiest
> > and now it is working.I think because "Host Expression Assemby" ,which
is
> > responsible to call my custom assembly,has only "Execution"
permission,it
> is
> > somehow classified as partially Trusted Caller and it can't call my
> assemly
> > without my assemly being marked by that attribute.
> >
> > No My assemly dos nothing except returning a sime string which has been
> > constructed in its construcotr.
> >
> > Anyways,thanks for your help .
> >
> > Ali
> >
> > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> > news:OX1d$p%235EHA.4004@.tk2msftngp13.phx.gbl...
> > > This sounds like your custom assembly does more than "just return a
> > string".
> > > Also, just changing the security policy configuration is not
sufficient
> if
> > > your custom assembly needs more than ExecutionOnly permissions. You
also
> > > need to explicitly assert permissions in the code of your custom
> assembly.
> > >
> > > Some articles about using custom code / custom assemblies:
> > > *
> > >
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ERSCstCode.asp
> > > *
> > >
> >
>
http://msdn.microsoft.com/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
> > >
> > > Also, if your custom assembly is signed you'll need to put APTCA on
it.
> > >
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_rdl_2l7m.asp
> > > Debugging custom assembly issues:
> > >
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_rdl_8wyq.asp
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > >
> > > "ALI-R" <newbie@.microsoft.com> wrote in message
> > > news:e40e4O45EHA.2592@.TK2MSFTNGP09.phx.gbl...
> > > > Hi ALL,
> > > >
> > > > Is it true that any custom assembly that I create ,by defualt has
> > > > "Execution" permission and if it dosn't attempt to have access to
> > > protected
> > > > resources like files ,that secuity policy will do and I don't need
to
> > > change
> > > > any security policy?
> > > >
> > > > I've cretaed a simple Assemly which only returns an string ,and I'he
> > given
> > > > it full permission in report server security policy ,but I still get
a
> > > > security error...
> > > >
> > > > Any Sugessions?
> > > >
> > > > I appreciate your help.
> > > > ALI
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Re-Post Execution Plan

Hi,
Say if you had a store procedure called p_SelectData
defined as follows with the parameter @.WhereClause can by
set to either
"@.Where = ' Where Surname = ''Jones''...
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''.
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''
and HasDetails = 1
You get the idea, a dynamic where clause.
The SP is as follows
CREATE procedure dbo.p_SelectData
@.WhereClause varchar(100) as
DECLARE @.SQLString varchar(255)
SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
EXECUTE ( @.SQLString)
My question is can a proper execution plan be formed
internally by SQL Server, or does the exec with the where
clause stop it from forming ?
N.B. No I didn't implement this it was there when I got
there.
Thanks.Hi,
Firstly, you will always get an execution plan, but I'm guessing you are ask
ing if it will cache it and re-use it.
I would say that in your simple example, you will get an execution plan cach
ed for the stored procedure. In complex stored procedures, it is possible th
at the execution plan will be re-compiled during execution, but you can read
more about that in BOL.
As for the EXEC statment, I don't believe that the plan will be cached. The
reason being that the table name is not qualified with the owner/schema. It
is possible to have two tables with the same name but different owners, and
data and/or indexing is dif
ferent enough to result in a different execution plan. If the table name was
fully qualified, that I feel that it would use auto-parameterization.
"Jimbo" wrote:

> Hi,
> Say if you had a store procedure called p_SelectData
> defined as follows with the parameter @.WhereClause can by
> set to either
> "@.Where = ' Where Surname = ''Jones''...
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''.
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''
> and HasDetails = 1
> You get the idea, a dynamic where clause.
> The SP is as follows
> CREATE procedure dbo.p_SelectData
> @.WhereClause varchar(100) as
> DECLARE @.SQLString varchar(255)
> SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
> EXECUTE ( @.SQLString)
> My question is can a proper execution plan be formed
> internally by SQL Server, or does the exec with the where
> clause stop it from forming ?
> N.B. No I didn't implement this it was there when I got
> there.
> Thanks.
>
>|||Thanks Al, that was my thought on it as well, but I
thought I had better check my facts before continuing with
it.

>--Original Message--
>Hi,
>Firstly, you will always get an execution plan, but I'm
guessing you are asking if it will cache it and re-use it.
>I would say that in your simple example, you will get an
execution plan cached for the stored procedure. In complex
stored procedures, it is possible that the execution plan
will be re-compiled during execution, but you can read
more about that in BOL.
>As for the EXEC statment, I don't believe that the plan
will be cached. The reason being that the table name is
not qualified with the owner/schema. It is possible to
have two tables with the same name but different owners,
and data and/or indexing is different enough to result in
a different execution plan. If the table name was fully
qualified, that I feel that it would use auto-
parameterization.
>"Jimbo" wrote:
>
by[vbcol=seagreen]
Forename ''Paul''.[vbcol=seagreen]
Forename ''Paul''[vbcol=seagreen]
@.WhereClause[vbcol=seagreen]
where[vbcol=seagreen]
>.
>|||If you want to do a bit of experimenting, have a look at the Stored Procedur
e Events of the Profiler. These can show individual statements within the St
ored Procedure being executed and if there has been a Cache Hit or Cache Mis
s. That way it will be poss
ible to see if the EXEC call goes to the Cache or not
"Jimbo" wrote:

> Thanks Al, that was my thought on it as well, but I
> thought I had better check my facts before continuing with
> it.
>
>
> guessing you are asking if it will cache it and re-use it.
> execution plan cached for the stored procedure. In complex
> stored procedures, it is possible that the execution plan
> will be re-compiled during execution, but you can read
> more about that in BOL.
> will be cached. The reason being that the table name is
> not qualified with the owner/schema. It is possible to
> have two tables with the same name but different owners,
> and data and/or indexing is different enough to result in
> a different execution plan. If the table name was fully
> qualified, that I feel that it would use auto-
> parameterization.
> by
> Forename ''Paul''.
> Forename ''Paul''
> @.WhereClause
> where
>

Re-Post Execution Plan

Hi,
Say if you had a store procedure called p_SelectData
defined as follows with the parameter @.WhereClause can by
set to either
"@.Where = ' Where Surname = ''Jones''...
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''.
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''
and HasDetails = 1
You get the idea, a dynamic where clause.
The SP is as follows
CREATE procedure dbo.p_SelectData
@.WhereClause varchar(100) as
DECLARE @.SQLString varchar(255)
SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
EXECUTE ( @.SQLString)
My question is can a proper execution plan be formed
internally by SQL Server, or does the exec with the where
clause stop it from forming ?
N.B. No I didn't implement this it was there when I got
there.
Thanks.
Hi,
Firstly, you will always get an execution plan, but I'm guessing you are asking if it will cache it and re-use it.
I would say that in your simple example, you will get an execution plan cached for the stored procedure. In complex stored procedures, it is possible that the execution plan will be re-compiled during execution, but you can read more about that in BOL.
As for the EXEC statment, I don't believe that the plan will be cached. The reason being that the table name is not qualified with the owner/schema. It is possible to have two tables with the same name but different owners, and data and/or indexing is dif
ferent enough to result in a different execution plan. If the table name was fully qualified, that I feel that it would use auto-parameterization.
"Jimbo" wrote:

> Hi,
> Say if you had a store procedure called p_SelectData
> defined as follows with the parameter @.WhereClause can by
> set to either
> "@.Where = ' Where Surname = ''Jones''...
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''.
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''
> and HasDetails = 1
> You get the idea, a dynamic where clause.
> The SP is as follows
> CREATE procedure dbo.p_SelectData
> @.WhereClause varchar(100) as
> DECLARE @.SQLString varchar(255)
> SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
> EXECUTE ( @.SQLString)
> My question is can a proper execution plan be formed
> internally by SQL Server, or does the exec with the where
> clause stop it from forming ?
> N.B. No I didn't implement this it was there when I got
> there.
> Thanks.
>
>
|||Thanks Al, that was my thought on it as well, but I
thought I had better check my facts before continuing with
it.

>--Original Message--
>Hi,
>Firstly, you will always get an execution plan, but I'm
guessing you are asking if it will cache it and re-use it.
>I would say that in your simple example, you will get an
execution plan cached for the stored procedure. In complex
stored procedures, it is possible that the execution plan
will be re-compiled during execution, but you can read
more about that in BOL.
>As for the EXEC statment, I don't believe that the plan
will be cached. The reason being that the table name is
not qualified with the owner/schema. It is possible to
have two tables with the same name but different owners,
and data and/or indexing is different enough to result in
a different execution plan. If the table name was fully
qualified, that I feel that it would use auto-
parameterization.[vbcol=seagreen]
>"Jimbo" wrote:
by[vbcol=seagreen]
Forename ''Paul''.[vbcol=seagreen]
Forename ''Paul''[vbcol=seagreen]
@.WhereClause[vbcol=seagreen]
where
>.
>
|||If you want to do a bit of experimenting, have a look at the Stored Procedure Events of the Profiler. These can show individual statements within the Stored Procedure being executed and if there has been a Cache Hit or Cache Miss. That way it will be poss
ible to see if the EXEC call goes to the Cache or not
"Jimbo" wrote:

> Thanks Al, that was my thought on it as well, but I
> thought I had better check my facts before continuing with
> it.
>
> guessing you are asking if it will cache it and re-use it.
> execution plan cached for the stored procedure. In complex
> stored procedures, it is possible that the execution plan
> will be re-compiled during execution, but you can read
> more about that in BOL.
> will be cached. The reason being that the table name is
> not qualified with the owner/schema. It is possible to
> have two tables with the same name but different owners,
> and data and/or indexing is different enough to result in
> a different execution plan. If the table name was fully
> qualified, that I feel that it would use auto-
> parameterization.
> by
> Forename ''Paul''.
> Forename ''Paul''
> @.WhereClause
> where
>

Re-Post Execution Plan

Hi,
Say if you had a store procedure called p_SelectData
defined as follows with the parameter @.WhereClause can by
set to either
"@.Where = ' Where Surname = ''Jones''...
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''.
or
@.Where = Where Surname = ''Jones'' and Forename ''Paul''
and HasDetails = 1
You get the idea, a dynamic where clause.
The SP is as follows
CREATE procedure dbo.p_SelectData
@.WhereClause varchar(100) as
DECLARE @.SQLString varchar(255)
SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
EXECUTE ( @.SQLString)
My question is can a proper execution plan be formed
internally by SQL Server, or does the exec with the where
clause stop it from forming ?
N.B. No I didn't implement this it was there when I got
there.
Thanks.Hi,
Firstly, you will always get an execution plan, but I'm guessing you are asking if it will cache it and re-use it.
I would say that in your simple example, you will get an execution plan cached for the stored procedure. In complex stored procedures, it is possible that the execution plan will be re-compiled during execution, but you can read more about that in BOL.
As for the EXEC statment, I don't believe that the plan will be cached. The reason being that the table name is not qualified with the owner/schema. It is possible to have two tables with the same name but different owners, and data and/or indexing is different enough to result in a different execution plan. If the table name was fully qualified, that I feel that it would use auto-parameterization.
"Jimbo" wrote:
> Hi,
> Say if you had a store procedure called p_SelectData
> defined as follows with the parameter @.WhereClause can by
> set to either
> "@.Where = ' Where Surname = ''Jones''...
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''.
> or
> @.Where = Where Surname = ''Jones'' and Forename ''Paul''
> and HasDetails = 1
> You get the idea, a dynamic where clause.
> The SP is as follows
> CREATE procedure dbo.p_SelectData
> @.WhereClause varchar(100) as
> DECLARE @.SQLString varchar(255)
> SET @.SQLString = 'Select * From MockTable ' + @.WhereClause
> EXECUTE ( @.SQLString)
> My question is can a proper execution plan be formed
> internally by SQL Server, or does the exec with the where
> clause stop it from forming ?
> N.B. No I didn't implement this it was there when I got
> there.
> Thanks.
>
>|||Thanks Al, that was my thought on it as well, but I
thought I had better check my facts before continuing with
it.
>--Original Message--
>Hi,
>Firstly, you will always get an execution plan, but I'm
guessing you are asking if it will cache it and re-use it.
>I would say that in your simple example, you will get an
execution plan cached for the stored procedure. In complex
stored procedures, it is possible that the execution plan
will be re-compiled during execution, but you can read
more about that in BOL.
>As for the EXEC statment, I don't believe that the plan
will be cached. The reason being that the table name is
not qualified with the owner/schema. It is possible to
have two tables with the same name but different owners,
and data and/or indexing is different enough to result in
a different execution plan. If the table name was fully
qualified, that I feel that it would use auto-
parameterization.
>"Jimbo" wrote:
>> Hi,
>> Say if you had a store procedure called p_SelectData
>> defined as follows with the parameter @.WhereClause can
by
>> set to either
>> "@.Where = ' Where Surname = ''Jones''...
>> or
>> @.Where = Where Surname = ''Jones'' and
Forename ''Paul''.
>> or
>> @.Where = Where Surname = ''Jones'' and
Forename ''Paul''
>> and HasDetails = 1
>> You get the idea, a dynamic where clause.
>> The SP is as follows
>> CREATE procedure dbo.p_SelectData
>> @.WhereClause varchar(100) as
>> DECLARE @.SQLString varchar(255)
>> SET @.SQLString = 'Select * From MockTable ' +
@.WhereClause
>> EXECUTE ( @.SQLString)
>> My question is can a proper execution plan be formed
>> internally by SQL Server, or does the exec with the
where
>> clause stop it from forming ?
>> N.B. No I didn't implement this it was there when I got
>> there.
>> Thanks.
>>
>.
>|||If you want to do a bit of experimenting, have a look at the Stored Procedure Events of the Profiler. These can show individual statements within the Stored Procedure being executed and if there has been a Cache Hit or Cache Miss. That way it will be possible to see if the EXEC call goes to the Cache or not
"Jimbo" wrote:
> Thanks Al, that was my thought on it as well, but I
> thought I had better check my facts before continuing with
> it.
>
> >--Original Message--
> >Hi,
> >
> >Firstly, you will always get an execution plan, but I'm
> guessing you are asking if it will cache it and re-use it.
> >
> >I would say that in your simple example, you will get an
> execution plan cached for the stored procedure. In complex
> stored procedures, it is possible that the execution plan
> will be re-compiled during execution, but you can read
> more about that in BOL.
> >
> >As for the EXEC statment, I don't believe that the plan
> will be cached. The reason being that the table name is
> not qualified with the owner/schema. It is possible to
> have two tables with the same name but different owners,
> and data and/or indexing is different enough to result in
> a different execution plan. If the table name was fully
> qualified, that I feel that it would use auto-
> parameterization.
> >
> >"Jimbo" wrote:
> >
> >> Hi,
> >>
> >> Say if you had a store procedure called p_SelectData
> >> defined as follows with the parameter @.WhereClause can
> by
> >> set to either
> >>
> >> "@.Where = ' Where Surname = ''Jones''...
> >> or
> >> @.Where = Where Surname = ''Jones'' and
> Forename ''Paul''.
> >> or
> >> @.Where = Where Surname = ''Jones'' and
> Forename ''Paul''
> >> and HasDetails = 1
> >>
> >> You get the idea, a dynamic where clause.
> >>
> >> The SP is as follows
> >>
> >> CREATE procedure dbo.p_SelectData
> >> @.WhereClause varchar(100) as
> >> DECLARE @.SQLString varchar(255)
> >> SET @.SQLString = 'Select * From MockTable ' +
> @.WhereClause
> >> EXECUTE ( @.SQLString)
> >>
> >> My question is can a proper execution plan be formed
> >> internally by SQL Server, or does the exec with the
> where
> >> clause stop it from forming ?
> >>
> >> N.B. No I didn't implement this it was there when I got
> >> there.
> >>
> >> Thanks.
> >>
> >>
> >>
> >.
> >
>