Foreach variable cannot be applied

I have created a foreach container to capture values from a query and run a script for each line. For some reason though I can't get past the variable mapping.

The SQL Task that is linked to the Foreach container is somthing like this:

SELECT str1, str2, str2, nValue1, nValue2

FROM MyTable

In the SQL Task I've set the following

ResultSet = Full Result Set

Connection Type = OLE DB

Result Name = 0

I've created the following Variables with a Package scope

s1 as String

s2 as String

s3 as String

n1 as Double

n2 as Double

When I run the package I get the following errors

Foreach variable mapping number 4 to variable "User : : n1" cannot be applied

Foreach variable mapping number 5 to variable "User : : n2" cannot be applied

The data type of nValue1 and nValue2 in MyTable are both numeric(19,4)

In setting the variable types in the package it seems that the closest to numeric(19,4) would be double. Is this why it is failing? Is there any way around it? I just need to pass the variables which are numeric and have decimal places.

Any insight would be greatly appreciated.

Regards,

Bill

See if this blog post helps you:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

|||

Thanks for the pointer, I found that if I cast the numeric fields in my query to float that the mapping seemed to pass the variables ok.

Regards,

Bill

Foreach Loop, Data Flow task buffer failed

I have a package that runs fine by itself.But when I run it inside a Foreach Loop container on a parent package, I got a buffer error after a few loops.Here are a couple of the error lines:

A buffer failed while allocating 49085616 bytes.

The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E.

I already played around with the Data Flow task’s DefaultBufferMaxRows and DefaultBufferSize properties, and I am still getting the error. Just wondering if there is a memory leak or something with the Foreach Loop task.I haven’t install SP1.Maybe SP1 fixes this issue?

Could be that not the Foreach loop itself is leaking, rather one or multiple components inside that dataflow were the culprit.

I highly recommend you install SP1 to see whether that helps, since I know there were some memory issues addressed in SP1.

thanks

wenyang

|||I have SP1 installed and I have a similar issue. I do not get an error but the DataFlow hangs at 33 in OnProgress/Pre-execute event (Datacode=33 in sysdtslog90). My package executes another package from within the 'ForEach' loop. The child package contains the DataFlow task. When I run the child package standalone (i.e. not from the parent package containing the 'ForEach' loop) with the same variables as in the parent, the DataFlow works fine.

Foreach Loop, Data Flow task buffer failed

I have a package that runs fine by itself.But when I run it inside a Foreach Loop container on a parent package, I got a buffer error after a few loops.Here are a couple of the error lines:

A buffer failed while allocating 49085616 bytes.

The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E.

I already played around with the Data Flow task’s DefaultBufferMaxRows and DefaultBufferSize properties, and I am still getting the error. Just wondering if there is a memory leak or something with the Foreach Loop task.I haven’t install SP1.Maybe SP1 fixes this issue?

Could be that not the Foreach loop itself is leaking, rather one or multiple components inside that dataflow were the culprit.

I highly recommend you install SP1 to see whether that helps, since I know there were some memory issues addressed in SP1.

thanks

wenyang

|||I have SP1 installed and I have a similar issue. I do not get an error but the DataFlow hangs at 33 in OnProgress/Pre-execute event (Datacode=33 in sysdtslog90). My package executes another package from within the 'ForEach' loop. The child package contains the DataFlow task. When I run the child package standalone (i.e. not from the parent package containing the 'ForEach' loop) with the same variables as in the parent, the DataFlow works fine.

Foreach loop with parallel execution

Is is possible to get the iterations in a foreach loop to run in parallel? What I need to do is to spawn an arbitrary number of parallel execution paths that all look exactly the same. The number is equal to the number of input files, which varies from time to time. Any help is appreciated!

Regards,
Lars R?nnb?ck

Nope. This feature was available in some beta releases, so you may notice it references in newsgroups and forums. But it was cut due to complexity and quality issues.

|||Thanks for the answer, albeit not what I was hoping for. Could it be "simluated" by having a loop containing only an Execute Package Task and the ExecuteOutOfProcess flag set to true?

Regards,
Lars|||ExecuteOutOfProcess does not change synchronous behavior of Execute Package Task - the task still waits for the package to finish.

If you want to start child packages really asynchronously - i.e. start child and continue execution of parent package, use Execute Process Task to start dtexec, specify
application="cmd.exe" and
parameters="/c start dtexec.exe /f package file ..."

You'll also need to configure child package using DTEXEC's command line (where I've left '...').|||Note that
1) parent can't reliably get execution result from the children, since it may exit before all children finish,
2) in some cases you may get even worse performance compared to sequential execution - since all these packages will clash for processor and memory.|||Thank you very much for your help Michael. I will try the proposed solution and compare performance with running everything sequentially, which we might end up doing then. When support for parallelism was included I suppose that was done in way to minimize clashes for processors and memory, so my final question is if it will reappear in a later version or service pack?

Thanks,
Lars|||3) You don't have any control over the degree of parallelism. If you have 100 files, but only want to process three at a time for example.
|||

lasa wrote:

Thank you very much for your help Michael. I will try the proposed solution and compare performance with running everything sequentially, which we might end up doing then. When support for parallelism was included I suppose that was done in way to minimize clashes for processors and memory, so my final question is if it will reappear in a later version or service pack?

Thanks,
Lars

The smart money says this will appear in a later version. Alot of people are asking for it.

-Jamie|||

Wouldn't it be possible to achieve control over the degree of parallelism using a Dummy package and the /MaxConcurrent flag of dtexec? Say I start four "real" packages in parallel using cmd.exe and use /MaxConcurrent 4 as an option to dtexec, then I start one dummy package using dtexec directly with the option /MaxConcurrent 1. The way I have understood it, the dummy package will now be queued for execution and will start only when the number of parallell processes goes below 1, i e when all four "real" packages are finished?
I am going to try this out and will report back.
Regards,
Lars

|||Since the experiment above didn't work out the way I thought (the dummy package started regardless of the fact that four other packages were running) I am guessing that I have misunderstood the /MaxConcurrent option. Taken from BOL:

Specifies the number of executable files that the package can run concurrently. The value specified must be a non-negative integer, or -1. A value of -1 means that SSIS will allow a maximum number of concurrently running executables that is equal to the total number of processors on the computer executing the package, plus two.

What kind of executable files is the text referring to? Those that are called using the "Execute Process Task" within a package? It made more sense that the SSIS engine would only allow a certain number of concurrently running packages.

Regards,
Lars|||Executables are tasks in a single package. The SSIS runtime nor DTExec do not do any interprocess communication to limit the number of packages or tasks running across process boundaries.

Matt

Foreach loop with parallel execution

Is is possible to get the iterations in a foreach loop to run in parallel? What I need to do is to spawn an arbitrary number of parallel execution paths that all look exactly the same. The number is equal to the number of input files, which varies from time to time. Any help is appreciated!

Regards,
Lars R?nnb?ck

Nope. This feature was available in some beta releases, so you may notice it references in newsgroups and forums. But it was cut due to complexity and quality issues.

|||Thanks for the answer, albeit not what I was hoping for. Could it be "simluated" by having a loop containing only an Execute Package Task and the ExecuteOutOfProcess flag set to true?

Regards,
Lars|||ExecuteOutOfProcess does not change synchronous behavior of Execute Package Task - the task still waits for the package to finish.

If you want to start child packages really asynchronously - i.e. start child and continue execution of parent package, use Execute Process Task to start dtexec, specify
application="cmd.exe" and
parameters="/c start dtexec.exe /f package file ..."

You'll also need to configure child package using DTEXEC's command line (where I've left '...').|||Note that
1) parent can't reliably get execution result from the children, since it may exit before all children finish,
2) in some cases you may get even worse performance compared to sequential execution - since all these packages will clash for processor and memory.|||Thank you very much for your help Michael. I will try the proposed solution and compare performance with running everything sequentially, which we might end up doing then. When support for parallelism was included I suppose that was done in way to minimize clashes for processors and memory, so my final question is if it will reappear in a later version or service pack?

Thanks,
Lars|||3) You don't have any control over the degree of parallelism. If you have 100 files, but only want to process three at a time for example.|||

lasa wrote:

Thank you very much for your help Michael. I will try the proposed solution and compare performance with running everything sequentially, which we might end up doing then. When support for parallelism was included I suppose that was done in way to minimize clashes for processors and memory, so my final question is if it will reappear in a later version or service pack?

Thanks,
Lars

The smart money says this will appear in a later version. Alot of people are asking for it.

-Jamie|||

Wouldn't it be possible to achieve control over the degree of parallelism using a Dummy package and the /MaxConcurrent flag of dtexec? Say I start four "real" packages in parallel using cmd.exe and use /MaxConcurrent 4 as an option to dtexec, then I start one dummy package using dtexec directly with the option /MaxConcurrent 1. The way I have understood it, the dummy package will now be queued for execution and will start only when the number of parallell processes goes below 1, i e when all four "real" packages are finished?
I am going to try this out and will report back.
Regards,
Lars

|||Since the experiment above didn't work out the way I thought (the dummy package started regardless of the fact that four other packages were running) I am guessing that I have misunderstood the /MaxConcurrent option. Taken from BOL:

Specifies the number of executable files that the package can run concurrently. The value specified must be a non-negative integer, or -1. A value of -1 means that SSIS will allow a maximum number of concurrently running executables that is equal to the total number of processors on the computer executing the package, plus two.

What kind of executable files is the text referring to? Those that are called using the "Execute Process Task" within a package? It made more sense that the SSIS engine would only allow a certain number of concurrently running packages.

Regards,
Lars|||Executables are tasks in a single package. The SSIS runtime nor DTExec do not do any interprocess communication to limit the number of packages or tasks running across process boundaries.

Matt

Foreach Loop based on Variable String separated by Semi-Colon Items?

Hi,

How can i integrate a foreach loop based on a variable that have for example "ONE;TWO;THREE" and i want to run for ONE, TWO and for THREE items...

Is this possible using the foreach task? Or do i have to code it in a script (not a good ideia)?
Best Regards,
Luis Sim?esNot sure if you can do this using what is provided but if not then you can write your own enumerator.

-Jamie|||How can i write my own enumerator? And how is it integrated in production servers?

Best Regards,
Luis Sim?es|||

Hello LUIS,

Could you pse give more details. a For Loop initially sounds more like what

you may like.

The variable that holds "ONE;TWO;THREE". You would have to convert the text

Three into 3 or if you know everything starts at ONE then you could count

the elements in the array using a Script task and assign that to a variable.

Why are you using the ForEach enum?

Is it that you want to do something like enumerate over a directory and only

pick up say 3 files?

Thanks

Allan

> Hi,

>

> How can i integrate a foreach loop based on a variable that have for

> example "ONE;TWO;THREE" and i want to run for ONE, TWO and for THREE

> items...

>

> Is this possible using the foreach task? Or do i have to code it in a

> script (not a good ideia)?

>

> Best Regards,

> Luis Sim?es

|||I have solved the problem by creating a specific foreach enumerator.

But in this case this is what i needed:

Imagine i pass parameters over a variable and those parameters are sent separated by a semi-colon.

For example company's names:

variable1 = "company1;company2;company3"

and i want to execute some task's for "company1", "company2" and so on...
Best Regards,
Luis Sim?es|||The easiest way to do this would be to have a script task inside a for loop that strips off the first item in the string and stores it in a variable. You can then use that variable in whatever way you want to.

When the string is empty - set a boolean variable to false. The for loop should only execute if that boolean variable is true.

-Jamie

ForEach Loop - Testing for when Enumerator is Empty

I have a SSIS package this set to run at a specific time each day. If there are no files for the ForEach tool to work upon...while it doesn't 'fail'...I would like to test for the condition that the enumerator was empty...so that I could send an email message reminding someone to followup and investigate.

What would be the best way to test for that condition?

Cordell,

You could add a counter variable to the package, use a Script Task within the ForEach loop to increment the variable, and then use an expression on a precendence constraint following the loop to decide to mail based on the count variable still being zero.

Here are some helpful links to get you started:
About variables - http://msdn2.microsoft.com/en-us/library/ms141085.aspx
Using variables in Script Tasks - http://msdn2.microsoft.com/en-us/library/ms135941.aspx
Precendence Constraint - http://msdn2.microsoft.com/en-us/library/ms141261.aspx

Cheers,
Patrik

|||

thank you Patrik for researching a solution for my need. I thought this is what I would have to end up doing, but wanted to make sure I was not missing something obvious.

It would be nice in the next major update of SSIS that this condition would be provided as an attribute/event to test for in the ForEach Loop tool.

...cordell...

p.s. Is there a place at MSDN to enter feature requests such as this?

|||

Cordell,

Feedback can be submitted to http://connect.microsoft.com.

Glad I could help,
Patrik

Forcing SQL Express to synchronize

I have a situation where I have SQL Server 2005 as my Publisher/Distributor
and I have it setup to run Push Merge Replication. The subscriber is an
instance of SQL Server Express. Obviously SQL Server Express doesn't have
the means to force synchronization. I do have it setup at the moment to run
Continiously but this will definitely be resource intensive running Merge
Replication on a 1000 or so databases...so I would like to be able to set it
up as Run on Demand and force the synchronization from the application. I
have seen BOL but that RMO code is all in .Net, I am programming in
VB6...and furthermore it looks like I have to use a Pull Subscription in
order to code for RMO...is that correct?
How should I go about setting this up for optimum performance?
Thanks!
RSH
I would advise you to use a pull subscription and use the activeX controls
(you can use the SQL 2005 activeX control with VB6 - use set
test=CreateObject("SQLDistribution.90.SQLDistribut ion")
), or use WSM for that.
You want to use a pull as it has a lower impact on the publisher and you can
run it from the subscriber which is especially important if your publisher
is offline from time to time.
For WSM go to Start, All Programs, Accessories and select Synchronize. Click
on MS SQL Server and select Properties to select your publication and set
subscription properties.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:O5%230v1Q9FHA.3044@.TK2MSFTNGP10.phx.gbl...
>I have a situation where I have SQL Server 2005 as my Publisher/Distributor
>and I have it setup to run Push Merge Replication. The subscriber is an
>instance of SQL Server Express. Obviously SQL Server Express doesn't have
>the means to force synchronization. I do have it setup at the moment to
>run Continiously but this will definitely be resource intensive running
>Merge Replication on a 1000 or so databases...so I would like to be able to
>set it up as Run on Demand and force the synchronization from the
>application. I have seen BOL but that RMO code is all in .Net, I am
>programming in VB6...and furthermore it looks like I have to use a Pull
>Subscription in order to code for RMO...is that correct?
> How should I go about setting this up for optimum performance?
> Thanks!
> RSH
>
|||Hilary,
Thanks for your reply.
Question 1:
I set up a Pull subscription scenerio and on the SQLServer Express instance
when I run WSM I get the following error:
"The Schema script 'Accrual_2.sch' could not be propogated to the
Subscriber."
"The process could not read the file 'C:\Program Files...' due to OS Error
3"
That directory does not exist from the "unc\" part of the path so I am
assuming that a snapshot hasn't been created. If I am correct how do I
generate a snapshot in SQL Express? Or is it created on the SQL Server
instance and copied over?
Question 2:
The VB Code below is my quick attempt at using the Active X control. The
code generates the following error: "The subscription to publication
'TestSub1' has expired or does not exist" which is not right...it does in
fact exist as illustrated in my screenshot...what am I missing here?
Option Explicit
' Add Reference Microsoft SQL Distribution Control 8.0
Private mobjDistr As SQLDISTXLib.SQLDistribution
Private Sub main()
Set mobjDistr = New SQLDISTXLib.SQLDistribution
On Error GoTo Errhandler
With mobjDistr
'Set up the Publisher
.Publisher = "VIRTUAL1"
.PublisherDatabase = "00010101"
.Publication = "TPTestSub1"
.PublisherSecurityMode = NT_AUTHENTICATION
'Set up the Subscriber.
.Subscriber = "PARENTVM\TPSVCCTR01"
.SubscriberDatabase = "00010101"
.SubscriberDatasourceType = SQL_SERVER
.SubscriberSecurityMode = DB_AUTHENTICATION
.SubscriberLogin = "V1"
.SubscriberPassword = "pw"
'Set up the Subscription.
.SubscriptionType = PULL
.SynchronizationType = AUTOMATIC
'Synchronize the data.
.Initialize
.Run
.Terminate
MsgBox "Complete"
End With
Exit Sub
Errhandler:
MsgBox Error
End Sub
Thanks a ton for your help!
RSH
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ecjk%23rR9FHA.1184@.TK2MSFTNGP12.phx.gbl...
>I would advise you to use a pull subscription and use the activeX controls
>(you can use the SQL 2005 activeX control with VB6 - use set
>test=CreateObject("SQLDistribution.90.SQLDistribu tion")
> ), or use WSM for that.
> You want to use a pull as it has a lower impact on the publisher and you
> can run it from the subscriber which is especially important if your
> publisher is offline from time to time.
> For WSM go to Start, All Programs, Accessories and select Synchronize.
> Click on MS SQL Server and select Properties to select your publication
> and set subscription properties.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:O5%230v1Q9FHA.3044@.TK2MSFTNGP10.phx.gbl...
>
|||This almost always means a permissions problem. From what it looks like you
are connecting from your subscriber to c:\Program files\... on your
publisher, or at least it should be your publisher. Make sure when you
configured your distributor that your snapshot location was defined as a
share. If not you can go back and fix it by right clicking on your
replication folder and selecting distributor properties.Select the
Publishers node, and for your publisher select the browse button to the
right of your publisher and in the default snapshot folder make it a share.
For your second question, the reason you most frequently get this message is
because the snapshot has not being generated for this publication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:Olcv3AT9FHA.636@.TK2MSFTNGP10.phx.gbl...
> Hilary,
> Thanks for your reply.
> Question 1:
> I set up a Pull subscription scenerio and on the SQLServer Express
> instance when I run WSM I get the following error:
> "The Schema script 'Accrual_2.sch' could not be propogated to the
> Subscriber."
> "The process could not read the file 'C:\Program Files...' due to OS Error
> 3"
> That directory does not exist from the "unc\" part of the path so I am
> assuming that a snapshot hasn't been created. If I am correct how do I
> generate a snapshot in SQL Express? Or is it created on the SQL Server
> instance and copied over?
>
> Question 2:
>
> The VB Code below is my quick attempt at using the Active X control. The
> code generates the following error: "The subscription to publication
> 'TestSub1' has expired or does not exist" which is not right...it does in
> fact exist as illustrated in my screenshot...what am I missing here?
> Option Explicit
> ' Add Reference Microsoft SQL Distribution Control 8.0
> Private mobjDistr As SQLDISTXLib.SQLDistribution
> Private Sub main()
> Set mobjDistr = New SQLDISTXLib.SQLDistribution
> On Error GoTo Errhandler
> With mobjDistr
> 'Set up the Publisher
> .Publisher = "VIRTUAL1"
> .PublisherDatabase = "00010101"
> .Publication = "TPTestSub1"
> .PublisherSecurityMode = NT_AUTHENTICATION
> 'Set up the Subscriber.
> .Subscriber = "PARENTVM\TPSVCCTR01"
> .SubscriberDatabase = "00010101"
> .SubscriberDatasourceType = SQL_SERVER
> .SubscriberSecurityMode = DB_AUTHENTICATION
> .SubscriberLogin = "V1"
> .SubscriberPassword = "pw"
> 'Set up the Subscription.
> .SubscriptionType = PULL
> .SynchronizationType = AUTOMATIC
> 'Synchronize the data.
> .Initialize
> .Run
> .Terminate
> MsgBox "Complete"
> End With
> Exit Sub
> Errhandler:
> MsgBox Error
> End Sub
> Thanks a ton for your help!
> RSH
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ecjk%23rR9FHA.1184@.TK2MSFTNGP12.phx.gbl...
>
|||Thanks.
How do I generate the snapshot?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:evENuiV9FHA.1188@.TK2MSFTNGP12.phx.gbl...
> This almost always means a permissions problem. From what it looks like
> you are connecting from your subscriber to c:\Program files\... on your
> publisher, or at least it should be your publisher. Make sure when you
> configured your distributor that your snapshot location was defined as a
> share. If not you can go back and fix it by right clicking on your
> replication folder and selecting distributor properties.Select the
> Publishers node, and for your publisher select the browse button to the
> right of your publisher and in the default snapshot folder make it a
> share.
> For your second question, the reason you most frequently get this message
> is because the snapshot has not being generated for this publication.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:Olcv3AT9FHA.636@.TK2MSFTNGP10.phx.gbl...
>
|||Hillary,
I set the Snapshot folder up as a Share as you described yesterday. I am no
longer getting the error about:
"The Schema script 'Accrual_2.sch' could not be propogated to the
Subscriber." and I see the files being setup in the share.
I am still getting the pesky error: "The subscription to publication
'TestSub1' has expired or does not exist"
I assume that as part of the setup that somewhere the subscriber is aware of
where to look for the snapshot...I did not see any properties for this when
setting up the subscriber. Also something that might be a bit odd is that I
am generating the snapshot from the Publisher side...is this correct? Or
should I somehow generate it from the subscriber (if so how?) and if that is
the case how do I make the Publisher aware of the fact that it was created?
Thanks alot for your time!
Ron
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:eZBocEb9FHA.500@.TK2MSFTNGP15.phx.gbl...
> Thanks.
> How do I generate the snapshot?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:evENuiV9FHA.1188@.TK2MSFTNGP12.phx.gbl...
>

forcing data format mask without modifting code

I have statement which is comparing a smalldatetime column to literal string as follows:

sales_date ='21-9-2004 0:0:0.000'

when I run the statement in query analyzer it bombs out with:

Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

If I alter the format of the date literal to '2004-09-21 00:00:00' the statement works.

Is there anyway of forcing the statement to treat '21-9-2004 0:0:0.000' as '2004-09-21 00:00:00' without modifying the statement itself ?There might be a global setting for how datetime fields are treated by default, I've never been tempted to go look for it. I'd rather do a CONVERT instead. There's also a 'SET DATEFORMAT' that might work for you.

What's wrong with changing the statement?|||Where is the data coming from? Is it always in that format? Can you use SUBSTRING?|||Unfortunately the data is in a liternal string 'DD-MM-YYYY' when in fact I require it be in 'MM-DD-YYYY' format.|||Couldn't you do something like...

cast(day(sales_date()) as varchar(2)) + '-' +
cast(month(sales_date()) as varchar(2)) + '-' +
cast(year(sales_date()) as char(4)) + ' 0:0:0.000'

??

Forcing a package to run as 32 bit on a x64 machine using SQL Server Agent?

I have a need to force a package to run using the 32-bit runtime from the SQL Server Agent. The machine is a x64 unit. I'm having to use an ODBC driver to extract data from our ERP package that will only run in 32 bit. Any help would be appreciated.

Hi.

Here is another suggestion: run distributed queries through SqlExpress/32. Here is a sample for MS Access:

http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-200564.html

Hope this helps

|||

In Agent, use an Operating System (CmdExec) step and use the copy of dtexec.exe in C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn

hth

Donald Farmer

|||Thanks, Donald. That worked like a charm.sql

Forcing 32 bit SSIS

I have an Itanium 64bit server to run SSIS packages on. I have one package with three parralell streams. When I run the package in 64 bit mode using dtexec, it runs through validation and exits with no reported errors, when I run it from a job, the job fails and says to see job log, which has no errors.

When I run it in 32 bit mode using the GUI, it runs all the way through.

Does anyone know how to launch SSIS in 32 bit mode from a job on an Itanium?

Thanks
Larry C

I haven't used Itanium, but I suspect the following method will hold true-

On a 64-bit version of SQL Server 2005, if you wish to schedule a package to execute a package under 32-bit mode, you will have to use the Operation System (CmdExec) job step type. The SSIS Package Execution step type will always use the 64-bit runtime, but by using an Operating system step you can explicitly specify that the 32-bit version of DTEXC should be used. As above the 32-bit version can be found in C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe.

x64
(http://wiki.sqlis.com/default.aspx/SQLISWiki/x64.html)

|||Thanks so much, that will get us by great until we can resolve the issue we're having with the 64 bit version.

Forcing 32 bit SSIS

I have an Itanium 64bit server to run SSIS packages on. I have one package with three parralell streams. When I run the package in 64 bit mode using dtexec, it runs through validation and exits with no reported errors, when I run it from a job, the job fails and says to see job log, which has no errors.

When I run it in 32 bit mode using the GUI, it runs all the way through.

Does anyone know how to launch SSIS in 32 bit mode from a job on an Itanium?

Thanks
Larry C

I haven't used Itanium, but I suspect the following method will hold true-

On a 64-bit version of SQL Server 2005, if you wish to schedule a package to execute a package under 32-bit mode, you will have to use the Operation System (CmdExec) job step type. The SSIS Package Execution step type will always use the 64-bit runtime, but by using an Operating system step you can explicitly specify that the 32-bit version of DTEXC should be used. As above the 32-bit version can be found in C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe.

x64
(http://wiki.sqlis.com/default.aspx/SQLISWiki/x64.html)

|||Thanks so much, that will get us by great until we can resolve the issue we're having with the 64 bit version.|||

Larry does it worked for you in Itanium 2,please share your experience.

Thanks

|||It worked for us in 32bit mode ok. We had lots of little things that didn't work or worked differently on the Itanium and eventually decided to move all jobs to a 32bit box. We haven't had any issues since.

forceseek performance and cost...

Hi,

I have just run the 2 queries from the BOL to test the forceseek option:

USE AdventureWorks;

GO

SELECT*

FROM Sales.SalesOrderHeader AS h

INNERJOIN Sales.SalesOrderDetail AS d

ON h.SalesOrderID = d.SalesOrderID

WHERE h.TotalDue > 100

AND(d.OrderQty > 5 OR d.LineTotal < 1000.00);

GO

SELECT*

FROM Sales.SalesOrderHeader AS h

INNERJOIN Sales.SalesOrderDetail AS d WITH(FORCESEEK)

ON h.SalesOrderID = d.SalesOrderID

WHERE h.TotalDue > 100

AND(d.OrderQty > 5 OR d.LineTotal < 1000.00);

GO

When I take a look at the execution plan, the first query has a cost of 20% and the forceseek take 80% of the total cost.

Also the number of logical read jump from 1238 to 66194!!!

So the forceseek option has a bad impact on the query.

The BOL says if there is a lot of IO, using the forceseek can provide better performance... but in this case its not so clear...

can you explain more in detail when its better to use the forceseek?

Thanks.

The cost increases,but if you look, the index change of clustered index scan to clustered index seek and if you compare the estimated I/O cust and estimated CPU Cost you can see the differences.The number reduce....

|||

Actually this is expected behavior. We have approx 30k rows of Order Header with approx 120k with order lines. What you tell SQL Server in the latter query, is to ignore the fact that we are retrieving all the rows, and still use a lookup instead of a scan. So what SQL Server does (on your demand) is to take each and every order one by one and lookup the corresponding order lines. If you looked carefully at the execution plan, you'ld see that the merge join was replaced with a inner loop join. The latter is more efficient if you lookup only a few values, but when you lookup all the values a merge join is way much faster.

As I understand the FORCESEEK its a way of forcing seeking in the index the few times that SQL Server believes it has to read more or less the whole table, while it actually is going to retrieve just a few rows.

Anybody is free to correct me if I'm wrong on this one.

Edit: That said, when I run a trace it actually seems that the last query is the faster, I would guess that is since all the data already are in memory.

|||

Hints are there to be used when the optimizer produces a less than optimal plan and, despite over two decades of effort, query optimizers still can't get the 100% best plan 100% of the time.

So what is FORCESEEK intended to deal with? The optimizer is always working with imperfect data about the values in the table. And when you start doing a join using multiple predicates that include operators such as "<" and ">" its ability to accurately estimate the number of rows that will be touched declines. Particularly true if you don't have up to date statistics on some of the columns, or if you encounter one of the rare instances where the histograms don't capture enough information to do accurate estimates. Historically (dating back to the System R research project) if you don't have statistics that indicate otherwise then an operator such as ">" is considered to be true for 1/3 of the data. Thus ORing two such conditions would lead to an assumption that 2/3 of the table was going to be retrieved which clearly would lead to a scan over a seek. This is a grand oversimplification of how things work in this day and age, but the basics help you understand why the optimizer might choose the wrong plan. It might think that 2/3 of the rows must be touched while you know that based on the actual data it is more like .01%. The bottom line being, there are cases where the optimizer will think that a scan offers the best performance but where human knowledge of the data makes it clear that a seek is the better alternative.

Using the optimizer's own statistics to see if FORCESEEK is better or not is a mistake. The reason the optimizer didn't choose the seek strategy on its own is that it estimated it would take a lot more I/O and rejected that strategy. So when you use FORCESEEK and look at the estimates what you see are the (incorrect) estimates that the optimizer saw when it considered this strategy on its own. You just can't use that data, you need to look at the actual performance of the query.

Hal

sql

forceseek performance and cost...

Hi,

I have just run the 2 queries from the BOL to test the forceseek option:

USE AdventureWorks;

GO

SELECT*

FROM Sales.SalesOrderHeader AS h

INNERJOIN Sales.SalesOrderDetail AS d

ON h.SalesOrderID = d.SalesOrderID

WHERE h.TotalDue > 100

AND(d.OrderQty > 5 OR d.LineTotal < 1000.00);

GO

SELECT*

FROM Sales.SalesOrderHeader AS h

INNERJOIN Sales.SalesOrderDetail AS d WITH(FORCESEEK)

ON h.SalesOrderID = d.SalesOrderID

WHERE h.TotalDue > 100

AND(d.OrderQty > 5 OR d.LineTotal < 1000.00);

GO

When I take a look at the execution plan, the first query has a cost of 20% and the forceseek take 80% of the total cost.

Also the number of logical read jump from 1238 to 66194!!!

So the forceseek option has a bad impact on the query.

The BOL says if there is a lot of IO, using the forceseek can provide better performance... but in this case its not so clear...

can you explain more in detail when its better to use the forceseek?

Thanks.

The cost increases,but if you look, the index change of clustered index scan to clustered index seek and if you compare the estimated I/O cust and estimated CPU Cost you can see the differences.The number reduce....

|||

Actually this is expected behavior. We have approx 30k rows of Order Header with approx 120k with order lines. What you tell SQL Server in the latter query, is to ignore the fact that we are retrieving all the rows, and still use a lookup instead of a scan. So what SQL Server does (on your demand) is to take each and every order one by one and lookup the corresponding order lines. If you looked carefully at the execution plan, you'ld see that the merge join was replaced with a inner loop join. The latter is more efficient if you lookup only a few values, but when you lookup all the values a merge join is way much faster.

As I understand the FORCESEEK its a way of forcing seeking in the index the few times that SQL Server believes it has to read more or less the whole table, while it actually is going to retrieve just a few rows.

Anybody is free to correct me if I'm wrong on this one.

Edit: That said, when I run a trace it actually seems that the last query is the faster, I would guess that is since all the data already are in memory.

|||

Hints are there to be used when the optimizer produces a less than optimal plan and, despite over two decades of effort, query optimizers still can't get the 100% best plan 100% of the time.

So what is FORCESEEK intended to deal with? The optimizer is always working with imperfect data about the values in the table. And when you start doing a join using multiple predicates that include operators such as "<" and ">" its ability to accurately estimate the number of rows that will be touched declines. Particularly true if you don't have up to date statistics on some of the columns, or if you encounter one of the rare instances where the histograms don't capture enough information to do accurate estimates. Historically (dating back to the System R research project) if you don't have statistics that indicate otherwise then an operator such as ">" is considered to be true for 1/3 of the data. Thus ORing two such conditions would lead to an assumption that 2/3 of the table was going to be retrieved which clearly would lead to a scan over a seek. This is a grand oversimplification of how things work in this day and age, but the basics help you understand why the optimizer might choose the wrong plan. It might think that 2/3 of the rows must be touched while you know that based on the actual data it is more like .01%. The bottom line being, there are cases where the optimizer will think that a scan offers the best performance but where human knowledge of the data makes it clear that a seek is the better alternative.

Using the optimizer's own statistics to see if FORCESEEK is better or not is a mistake. The reason the optimizer didn't choose the seek strategy on its own is that it estimated it would take a lot more I/O and rejected that strategy. So when you use FORCESEEK and look at the estimates what you see are the (incorrect) estimates that the optimizer saw when it considered this strategy on its own. You just can't use that data, you need to look at the actual performance of the query.

Hal

FORCEPLAN causes different results in SQL SERVER 2000

Hello,
Has anyone run into a situation where using SET FORCEPLAN ON changes
the results set of a stored procedure? I've got a large, complicated
procedure that spits out a large hierarchical table of aggregated
values. I noticed that some of the data was coming out incorrectly,
even though the components seem to be correct. In the process of
troubleshooting I tried setting FORCEPLAN to ON, and found that the
procedure started spitting out the correct data.
Unfortunately due to performance issues we can't just leave FORCEPLAN
on, so I need to get to thr root of this problem. I'm also curious as
to why this would happen in the first place. Does anyone have any
ideas? My understanding of FORCEPLAN is that it changes the order of
joins in a query, changing the performance. But it shouldn't change
the data itself, correct?
On a hunch I tried reindexing, thinking that maybe some bad info was
cached, but no luck.
Does anyone know of any instances where FORCEPLAN would change the
results set of a query?
Thanks,
Chris RutledgeWhat is the actual query?
"Chris Rutledge" <csrutledge@.gmail.com> wrote in message
news:1138826752.664351.310480@.g43g2000cwa.googlegroups.com...
> Hello,
> Has anyone run into a situation where using SET FORCEPLAN ON changes
> the results set of a stored procedure? I've got a large, complicated
> procedure that spits out a large hierarchical table of aggregated
> values. I noticed that some of the data was coming out incorrectly,
> even though the components seem to be correct. In the process of
> troubleshooting I tried setting FORCEPLAN to ON, and found that the
> procedure started spitting out the correct data.
> Unfortunately due to performance issues we can't just leave FORCEPLAN
> on, so I need to get to thr root of this problem. I'm also curious as
> to why this would happen in the first place. Does anyone have any
> ideas? My understanding of FORCEPLAN is that it changes the order of
> joins in a query, changing the performance. But it shouldn't change
> the data itself, correct?
> On a hunch I tried reindexing, thinking that maybe some bad info was
> cached, but no luck.
> Does anyone know of any instances where FORCEPLAN would change the
> results set of a query?
> Thanks,
> Chris Rutledge
>|||It's pretty enormous, but I think the pertinent bit is in here:
SELECT SUM(Event0.Qty * EventCmpt.AggSign ) AS SQ ,
NodeSku.ProductStream, NodeSku.ProductDescription, NodeSku.PrdName,
Event0.TimePeriod - @.MaxRel AS TimePeriod
-- Node Table Joins
FROM ( SELECT EventId, TimePeriod, NodeId, EventTypeId, Qty,
TP.PlanId, GroupId, TransId FROM Event
INNER JOIN @.TableEventTimePeriod AS TP
ON TP.EventTimePeriod=TimePeriod
AND TP.PlanId=Event.PlanId
AND IsValidFlag=1 ) AS Event0
INNER JOIN NODESKU
ON Event0.NodeId=NODESKU.NodeId
JOIN ( SELECT EventCmpt, AggSign FROM EventCmpt
WHERE EventId=@.ElementId UNION SELECT @.ElementId, 1 AS
AggSign )
EventCmpt ON Event0.EventTypeId=EventCmpt
GROUP BY Event0.TimePeriod, NodeSku.ProductStream,
NodeSku.ProductDescription, NodeSku.PrdName
WITH ROLLUP
-- multi-component grouping.
HAVING GROUPING (NodeSku.ProductDescription)=GROUPING(NodeSku.PrdName)
AND
GROUPING(Event0.TimePeriod)=0
AND (NodeSku.ProductStream IS NOT NULL OR
GROUPING(NodeSku.ProductStream ) = 1 )
AND (NodeSku.ProductDescription IS NOT NULL OR
GROUPING(NodeSku.ProductDescription ) = 1 )
AND (NodeSku.PrdName IS NOT NULL OR GROUPING(NodeSku.PrdName ) = 1
)
) B
INNER JOIN PlanPeriod ON B.TimePeriod <= PlanPeriod.PeriodId
AND PlanPeriod.CalendarId = ( SELECT CalendarId FROM Plans WHERE
PlanId = @.PlanId )
AND PlanPeriod.PeriodId BETWEEN @.SubRangeMin AND @.SubRangeMax
GROUP BY B.ProductStream, B.ProductDescription, B.PrdName,
PlanPeriod.PeriodId
ORDER BY B.ProductStream, B.ProductDescription, B.PrdName
END
ELSE ...|||I can not say why is this happening without looking at the sample code and
sample data to reproduce the problem in my environment. However, it appears
to me that you may be using ANSI 89 standard in your SQL. Try changing that
to ANSI 92 and see what output are your seeing.
"Chris Rutledge" wrote:

> Hello,
> Has anyone run into a situation where using SET FORCEPLAN ON changes
> the results set of a stored procedure? I've got a large, complicated
> procedure that spits out a large hierarchical table of aggregated
> values. I noticed that some of the data was coming out incorrectly,
> even though the components seem to be correct. In the process of
> troubleshooting I tried setting FORCEPLAN to ON, and found that the
> procedure started spitting out the correct data.
> Unfortunately due to performance issues we can't just leave FORCEPLAN
> on, so I need to get to thr root of this problem. I'm also curious as
> to why this would happen in the first place. Does anyone have any
> ideas? My understanding of FORCEPLAN is that it changes the order of
> joins in a query, changing the performance. But it shouldn't change
> the data itself, correct?
> On a hunch I tried reindexing, thinking that maybe some bad info was
> cached, but no luck.
> Does anyone know of any instances where FORCEPLAN would change the
> results set of a query?
> Thanks,
> Chris Rutledge
>|||Thanks for the reply, Nitin. Which elements appear to be from ANSI 89?
I'm not very familiar with the differences.|||This might be the case if you have the old style outer join in your
query (for example WHERE MyCol *= OtherCol). For an old style inner join
it shouldn't matter. (for example SELECT ... FROM A, B WHERE A.id=B.id)
However, the snippet you posted does not contain such syntax.
Now, the bottom line is that it is a bug. Because with SQL you specify
the result, and this result should be the same (i.e. correct) regardless
of the order in which the steps are executed.
Unfortunately, to analyse the problem, the entire query and execution
plan is necessary, and probably also the DDL and maybe even (some?)
data.
Gert-Jan
Chris Rutledge wrote:
> Thanks for the reply, Nitin. Which elements appear to be from ANSI 89?
> I'm not very familiar with the differences.|||I've had problems where certain indexes have been corrupt, and because
SET FORCEPLAN can cause different indexes to be used, this may explain
why you're getting different resuts.
Are you able to identify the different indexes being used in the two
scenarios and try recreating them?|||Hi folks,
I've done some more experimentation with your guidance and I think I'm
narrowing in on the problem. I tried adding some where statements to
the sp to limit the data i'm getting back. With the new, smaller
dataset it's returning the correct values.
I did a side-by-side comparison of the two versions of the stored
procedure using Beyond Compare. You can take a look yourself here:
http://www.wamsystemsweb.com/SQL/DiffReport.html
(note, the filtered version of the sp is waaaay off to the right)
The differences between the two are highlighted in red.
The filter itself is from lines 357 to 364.
I believe the relevant bits of the execution plan are from 383 to 410
or 504 to 520.
Based on the feedback I've been getting I located the indexes that were
being used by the unfiltered version that weren't being used by the
filtered version. I dropped and recreated those indexes, but I had no
luck.
Now I'm looking at the details of the execution plans for the two. The
filtered version (the one that works) uses more nested loops, while the
unfiltered version is using a lot of hash matches and merge joins.
Thanks much for all your feedback, folks. Any additional hints are
greatly appreciated.
Chris|||Even more to the point, except for the SET FORCEPLAN ON statement early
on, these two stored procedures are identical. Their execution plans
appear to be identical. The sp on the left generates the correct data,
the one on the right does not:
http://www.wamsystemsweb.com/SQL/DiffReport2.html
Chris|||Hmm, never mind. What I actually illustrated in the two above examples
is SHOWPLAN_TEXT doesn't work the way I think it does.

Forced auto refresh to avoid page caching

Good Afternoon!
I've run into an issue whilst developing a report using VS.NET, RS and SQL
Enterprise Server 2000.
The problem I have is that I'm using RS to develop a web based app that can
be used to edit base table data. I have the app working fine, but it keeps
taking it's data from the cached reports. Obviously, this means that every
time a change is made to the base data, the user needs to refresh the page -
not an easy thing to remember when processing several hundred entries.
I have the "Do not cache temporary copies of this report" radio button
selected (by default). Also, the "Autorefresh" text box is not an option I
can take as it is unusable when there are groups set up on the table - they
keep expanding and collapsing.
I've seen ways of embedding some (rather shaky) HTML to force the refresh.
See below:
<META HTTP-EQUIV="Pragma" CONTENT="no-cache">
<META HTTP-EQUIV="Pragma-directive" CONTENT="no-cache">
<META HTTP-EQUIV="cache-directive" CONTENT="no-cache">
Is anyone aware of how I can write this into the .rdl, or report? Or, is
there a way to force the page to refresh?
Thanks in advance
JonTry adding the rs:ClearSession=true to the report url.
--
Adrian M.
MCP
"Jonathan Martin" <jonathan.martin@.pcservicecall.co.uk> wrote in message
news:%23aqKz5aHFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Good Afternoon!
> I've run into an issue whilst developing a report using VS.NET, RS and SQL
> Enterprise Server 2000.
> The problem I have is that I'm using RS to develop a web based app that
> can be used to edit base table data. I have the app working fine, but it
> keeps taking it's data from the cached reports. Obviously, this means
> that every time a change is made to the base data, the user needs to
> refresh the page - not an easy thing to remember when processing several
> hundred entries.
> I have the "Do not cache temporary copies of this report" radio button
> selected (by default). Also, the "Autorefresh" text box is not an option
> I can take as it is unusable when there are groups set up on the table -
> they keep expanding and collapsing.
> I've seen ways of embedding some (rather shaky) HTML to force the refresh.
> See below:
> <META HTTP-EQUIV="Pragma" CONTENT="no-cache">
> <META HTTP-EQUIV="Pragma-directive" CONTENT="no-cache">
> <META HTTP-EQUIV="cache-directive" CONTENT="no-cache">
> Is anyone aware of how I can write this into the .rdl, or report? Or, is
> there a way to force the page to refresh?
> Thanks in advance
>
> Jon
>
>|||I tried it manually, and it didn't appear to change anything.
Is there a way I can embed that statement in the rdl?
Thanks
Jon
"Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
news:euodxncHFHA.4048@.TK2MSFTNGP15.phx.gbl...
> Try adding the rs:ClearSession=true to the report url.
> --
> Adrian M.
> MCP
> "Jonathan Martin" <jonathan.martin@.pcservicecall.co.uk> wrote in message
> news:%23aqKz5aHFHA.1172@.TK2MSFTNGP12.phx.gbl...
>> Good Afternoon!
>> I've run into an issue whilst developing a report using VS.NET, RS and
>> SQL Enterprise Server 2000.
>> The problem I have is that I'm using RS to develop a web based app that
>> can be used to edit base table data. I have the app working fine, but it
>> keeps taking it's data from the cached reports. Obviously, this means
>> that every time a change is made to the base data, the user needs to
>> refresh the page - not an easy thing to remember when processing several
>> hundred entries.
>> I have the "Do not cache temporary copies of this report" radio button
>> selected (by default). Also, the "Autorefresh" text box is not an option
>> I can take as it is unusable when there are groups set up on the table -
>> they keep expanding and collapsing.
>> I've seen ways of embedding some (rather shaky) HTML to force the
>> refresh. See below:
>> <META HTTP-EQUIV="Pragma" CONTENT="no-cache">
>> <META HTTP-EQUIV="Pragma-directive" CONTENT="no-cache">
>> <META HTTP-EQUIV="cache-directive" CONTENT="no-cache">
>> Is anyone aware of how I can write this into the .rdl, or report? Or, is
>> there a way to force the page to refresh?
>> Thanks in advance
>>
>> Jon
>>
>

force SQL-Server 2000 to run on a specific CPU

Do you know how to force SQL-Server 2000 to run on a specific CPU on an SMP
system?In EM on the Processor page of the Server Properties.
Bob Castleman
DBA Poseur
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:D4BA00D2-8181-4827-899B-C3E3DEB6BF8C@.microsoft.com...
> Do you know how to force SQL-Server 2000 to run on a specific CPU on an
> SMP
> system?
>

force SQL-Server 2000 to run on a specific CPU

Do you know how to force SQL-Server 2000 to run on a specific CPU on an SMP
system?
In EM on the Processor page of the Server Properties.
Bob Castleman
DBA Poseur
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:D4BA00D2-8181-4827-899B-C3E3DEB6BF8C@.microsoft.com...
> Do you know how to force SQL-Server 2000 to run on a specific CPU on an
> SMP
> system?
>

force SQL-Server 2000 to run on a specific CPU

Do you know how to force SQL-Server 2000 to run on a specific CPU on an SMP
system?In EM on the Processor page of the Server Properties.
Bob Castleman
DBA Poseur
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:D4BA00D2-8181-4827-899B-C3E3DEB6BF8C@.microsoft.com...
> Do you know how to force SQL-Server 2000 to run on a specific CPU on an
> SMP
> system?
>

Force SQL Server to recompile stored procedures every time they run (SQL Server 7/2000)

This is a solution for a very specific problem, and it's one that you'll hardly ever use, but it's important to know about that one scenario where it can save your neck. Ordinarily, stored procedures are only recompiled if they're no longer in the procedure cache. But if a stored procedure's execution plan is still in the cache, then SQL Server reuses the compiled stored
procedure and its existing execution plan. This is almost always the best course of action. Almost always, but not always.
Sometimes, however, reusing an existing plan doesn't offer the most efficient performance. Imagine, for example, that your stored procedure accepts a parameter that determines the nature
of a JOIN operation. The results can vary in a big way, so you wouldn't want your procedure to be locked into an execution plan that might be completely inappropriate for that JOIN. In a highly
specialized case like this, you might want to force SQL Server to recompile the procedure every time the procedure runs. Doing so comes at a performance cost, but this might be offset by the
savings you gain in not executing the procedure with an awful compiled execution plan. Consider carefully whether to use this approach (or whether to re-engineer the over-design of your
application to avoid this situation in the first place). Should you need to instruct SQL Server to recompile each time, add the WITH RECOMPILE directive to the procedure, like this:
CREATE PROCEDURE ProcName
@.Param int /* ... other parameters */
WITH RECOMPILE
AS /* ... procedure code follows */

If we omit "WITH RECOMPILE", what will be the consequence? Thanks

WITH RECOMPILE can kill an Asp.net application because HTTP is stateless. The better solution is to force SQL Server to put all your stored procs in the procedure cache on start up. There is a stored proc in the Master called SP(system stored proc) Procoption you can use it to auto start all your stored procs. Recompile is modified in SQL Server 2005 you can recompile only the line you need then your solution will be ok for now there are alternatives. See code below the only value for option is Startup and value is true for ON and false for OFF. Hope this helps.

sp_procoption[@.ProcName =]'procedure'
,[@.OptionName =]'option'
,[@.OptionValue =]'value'

|||

Please kindly elaborate more on: "WITH RECOMPILE can kill an Asp.net application because HTTP is stateless." What do "kill" and "stateless" mean here? Thanks again.

|||

You get the best performance if all your stored procs are in the procedure cache all the time WITH RECOMPILE will not allow that, so everytime your stored proc is accessed your user will wait for SQL Server to recompile the stored proc before executing it. In Asp.net some processes will time out before your stored proc will execute. Kill means your code will timeout and stateless means a protocol without state HTTP is one of them. Your users will wait for SQL Server which is session to finish before objects on your pages can be accessed. The first thing to know about stored procs is avoid Recompile even in Windows appilcation. Hope this helps.