Showing posts with label executed. Show all posts
Showing posts with label executed. Show all posts

Sunday, February 26, 2012

Exit DTS Package

Is there a way to exit an executed DTS package using Microsoft TSQL without causing an error or does this need to be in VB?The TSQL executes within the package, and thus I don't think there is a way to directly stop the package using TSQL. I suppose you could call system stored procedures to command shell out and stop the service, or even identify the executing process and kill it, but I would certainly never recommend anything like this.

Your best be may be to have the TSQL return an error code or set an environment variable that your DTS package can respond to.|||Okay thanks. I did raiserror but that makes the package a failure which is what I don't want.|||the handling of return codes inside DTS is a different animal from RAISEERROR|||I ended up writing it in VB but thanks guys.

Sunday, February 19, 2012

Execution plans differ

I have a stored procedure that is taking too long to execute. In trying to
diagnose the problem and come up with a solution, I executed the query the
stored procedure runs in query analyzer and it ran very fast. I compared th
e
two execution plans and they are vastly different.
to clarify I took the text of the stored procedure and copied it into query
analyzer. The stored procedure takes 2 paramaters. I declared the 2
parameters as variables and set them to the values that were being passed
into the stored procedure.
When executed in the stored procedure the query takes 3 minutes. When
executed in query analyzer it takes 7 seconds. Why would the execution plan
s
be so different?
The reason the stored procedure plan takes so long is it does an index scan
and then a bookmark lookup of more than 6 million rows. The ultimate result
set is only a few rows so this is definately not needed. When run in query
analyzer only the rows needed are retrieved.
Thanks,
DerrickParameter sniffing... your stored procedure is likely cached with a bad plan
due to atypical or non-optimal parameter values.
http://tinyurl.com/rkgbm
"Derrick Pope" <DerrickPope@.discussions.microsoft.com> wrote in message
news:8692616D-CF3E-411E-B17C-C95732A59BEE@.microsoft.com...
>I have a stored procedure that is taking too long to execute. In trying to
> diagnose the problem and come up with a solution, I executed the query the
> stored procedure runs in query analyzer and it ran very fast. I compared
> the
> two execution plans and they are vastly different.
> to clarify I took the text of the stored procedure and copied it into
> query
> analyzer. The stored procedure takes 2 paramaters. I declared the 2
> parameters as variables and set them to the values that were being passed
> into the stored procedure.
> When executed in the stored procedure the query takes 3 minutes. When
> executed in query analyzer it takes 7 seconds. Why would the execution
> plans
> be so different?
> The reason the stored procedure plan takes so long is it does an index
> scan
> and then a bookmark lookup of more than 6 million rows. The ultimate
> result
> set is only a few rows so this is definately not needed. When run in
> query
> analyzer only the rows needed are retrieved.
> --
> Thanks,
> Derrick