Running a stored procedure in Query Analyzer vs ADO - Execution Di

I am running SQL Server 2000 SP3. I have a stored procedure that I call from
a VB 6.0 Component using ADO/OLEDB. I turn on SQL profiler and see that the
stored proc executes in 22 seconds. I take the stored procedure call straight
out of profiler and paste it into SQL Query Analyzer and run the stored
procedure. It executes in Query Analyzer in 4 seconds. When I look at the
execution plan, Query Analyzer uses a different plan (more efficient) than
the plan used when the stored procedure is executed through ADO. How is this
possible? What am I doing wrong with ADO? How come the stored procedure will
not execute in 4 seconds when I call it through ADO? I can consistently
duplicate this.
Todd.Danner@.wachovia.com wrote:
> I am running SQL Server 2000 SP3. I have a stored procedure that I
> call from a VB 6.0 Component using ADO/OLEDB. I turn on SQL profiler
> and see that the stored proc executes in 22 seconds. I take the
> stored procedure call straight out of profiler and paste it into SQL
> Query Analyzer and run the stored procedure. It executes in Query
> Analyzer in 4 seconds. When I look at the execution plan, Query
> Analyzer uses a different plan (more efficient) than the plan used
> when the stored procedure is executed through ADO. How is this
> possible? What am I doing wrong with ADO? How come the stored
> procedure will not execute in 4 seconds when I call it through ADO? I
> can consistently duplicate this.
How are you executing the procedure from ADO? Post the code you are
testing on ADO (all relevant code) and the SQL you use in QA..
David G.

Running a stored procedure in Query Analyzer vs ADO - Execution Di

I am running SQL Server 2000 SP3. I have a stored procedure that I call from
a VB 6.0 Component using ADO/OLEDB. I turn on SQL profiler and see that the
stored proc executes in 22 seconds. I take the stored procedure call straigh
t
out of profiler and paste it into SQL Query Analyzer and run the stored
procedure. It executes in Query Analyzer in 4 seconds. When I look at the
execution plan, Query Analyzer uses a different plan (more efficient) than
the plan used when the stored procedure is executed through ADO. How is this
possible? What am I doing wrong with ADO? How come the stored procedure will
not execute in 4 seconds when I call it through ADO? I can consistently
duplicate this.Todd.Danner@.wachovia.com wrote:
> I am running SQL Server 2000 SP3. I have a stored procedure that I
> call from a VB 6.0 Component using ADO/OLEDB. I turn on SQL profiler
> and see that the stored proc executes in 22 seconds. I take the
> stored procedure call straight out of profiler and paste it into SQL
> Query Analyzer and run the stored procedure. It executes in Query
> Analyzer in 4 seconds. When I look at the execution plan, Query
> Analyzer uses a different plan (more efficient) than the plan used
> when the stored procedure is executed through ADO. How is this
> possible? What am I doing wrong with ADO? How come the stored
> procedure will not execute in 4 seconds when I call it through ADO? I
> can consistently duplicate this.
How are you executing the procedure from ADO? Post the code you are
testing on ADO (all relevant code) and the SQL you use in QA..
David G.

Run DTS Olap Package

Dear Friends,

I needed to know how to solve this situation.

I have a DTS with OLAP objects and when I schedule the execution is always to give mistake in the Customer's servant...

In our facilities it is always to work well...

Did anybody already try problems of this type?

Thank you
Paulo A. J. PiresUsually DTS includes names of involved servers, accounts, etc. Is it all the same on customer server?|||You are right the names are not the same ones...

But it was to trust the programmer that is to install DTS in the Customer (as always they did)...

Where do I can me to see the servers where it is linked?

Thank you,
Paulo A. J. Pires

Run a task multiple times

I have a situation where I run the same taks multiple times during the execution. I would like to have one task which runs every time, instead of duplicating the task over and over 14 times in my script.

Basically, it is an audit log, which I set variables and then insert into a SQL table the variables.

I would like to do this:

Task1 Success--> Set Vars --Success--> Log
|
Task2 Success--> Set Vars --Success-| (do the Log task again)
|
Task3 -Success--> Set Vars --Success-| (do the Log task again)
|
etc

This works, however, I have to duplicate Log over and over and over. No OR does not work, because it still only executes the Log task once.

Another option I thought of, but cannot find a way to implement is: Make the Log task "disabled" with no dependencies, then in the Set Vars script, enable and execute Log and disable again.

Any ideas?I take it these are variables in SetVars are a custom variable set, since the default SSIS logging can handle many logging situations.

This looks like a case for event handlers.

Create three package scoped event handlers

OnPreExecute:
Set a package scoped variable IsFailed to false

OnTaskFailed:

Set a package scoped variable IsFailed to true provided the task is

one of the (Task1,Task2,Task3 or SetVars tasks)

OnPostExecute:
Check the package scope IsFailed variable
and log as needed (only one log implementation and instance needed)

There are scenarios were this would not work, involving parallelism of tasks, but the depiction you have would appear to be task1 followed by task2...|||It seems that you may want to look into SSIS logging capabilities and custom logging component http://www.thejoyofcode.com/SSIS_Custom_Logging_Task_for_Event_Handlers.aspx. There is also Microsoft example for it I reckon (html and smtp log provider samples)- http://msdn2.microsoft.com/en-us/library/ms365179.aspx

In case you have requirement doing it in task - I would recommend creating a custom task - http://msdn2.microsoft.com/en-us/library/ms345156.aspx. In this case of custom "Log" task - the code would not be duplicated - although you would still need to drag and drop it 14 times to designer and link it|||Ok. LOG was a bad wording. I was trying to get is how to run the same task multiple times.

Lets just say for argument the "LOG" task above is a "Send Email Task", it doesn't matter what the task does. I don't want 14 send email tasks which are exactly the same. I want ONE task and have it execute 14 times.

The best thing, would be if there was a "shortcut" mechanisim, which doesn't exist, to allow this. So I could "reference" the orginal task, even if I had to have 13 shortcuts to the original task that would be ok with me.|||

Tom Phillips wrote:

Ok. LOG was a bad wording. I was trying to get is how to run the same task multiple times.

Lets just say for argument the "LOG" task above is a "Send Email Task", it doesn't matter what the task does. I don't want 14 send email tasks which are exactly the same. I want ONE task and have it execute 14 times.

The best thing, would be if there was a "shortcut" mechanisim, which doesn't exist, to allow this. So I could "reference" the orginal task, even if I had to have 13 shortcuts to the original task that would be ok with me.

The smallest unit of reusability in SSIS (currently) is a package. hence if you want to reuse some functionality anywhere you want you should split it out into a seperate package and call it. This works quite well.

its possible that eventhandlers (as mentioned previously) could solve the problem also although without knowing more about your particular situation I can't make a call on that.

-Jamie

|||

Jamie Thomson wrote:

The smallest unit of reusability in SSIS (currently) is a package. hence if you want to reuse some functionality anywhere you want you should split it out into a seperate package and call it. This works quite well.

its possible that eventhandlers (as mentioned previously) could solve the problem also although without knowing more about your particular situation I can't make a call on that.

-Jamie

Jamie,

"The smallest unit of reusability in SSIS (currently) is a package" is the key.

Under any circimstance, I have to duplicate something, either the execute package or send email or in the above event handler example 3 TIMES 14 tasks, over and over and over.

I would like to suggest to MS they add a "shortcut task" which "references" another task. So only one ocurance of the actual task exists. Even though I would have to duplicate the "shortcut" over and over, if I make a change to the original task it would propagate to all the "shortcuts". There are some techincal details to work out on this, for example, what about expressions in the original, do they run or not, can you override them in the "shortcut"? But I think it would be doable.

In my case I have 14 tasks which are identical, and I had to change one thing in every single one of them. What happens when there are 30, or 100 of them? The shortcut would have eliminated that problem.

Thank you|||

Tom Phillips wrote:

Jamie Thomson wrote:

The smallest unit of reusability in SSIS (currently) is a package. hence if you want to reuse some functionality anywhere you want you should split it out into a seperate package and call it. This works quite well.

its possible that eventhandlers (as mentioned previously) could solve the problem also although without knowing more about your particular situation I can't make a call on that.

-Jamie

Jamie,

"The smallest unit of reusability in SSIS (currently) is a package" is the key.

Under any circimstance, I have to duplicate something, either the execute package or send email or in the above event handler example 3 TIMES 14 tasks, over and over and over.

I would like to suggest to MS they add a "shortcut task" which "references" another task. So only one ocurance of the actual task exists. Even though I would have to duplicate the "shortcut" over and over, if I make a change to the original task it would propagate to all the "shortcuts". There are some techincal details to work out on this, for example, what about expressions in the original, do they run or not, can you override them in the "shortcut"? But I think it would be doable.

In my case I have 14 tasks which are identical, and I had to change one thing in every single one of them. What happens when there are 30, or 100 of them? The shortcut would have eliminated that problem.

Thank you

Tom,

What you're talking about is reusability of tasks. This is my number one request (in a list of literally hundreds) for the next version of SSIS. Unfortunately I don't think we'll see it in SQL Server 2007/8 which is intended to be a small release after the masses in SQL Server 2005, but hopefully in the one after that.

Until then, lump all your reusable stuff into a dedicated package.

-Jamie

|||I would hope to see something before 2022.

I "think" the shortcut method would be realtively easy to do. I wonder if I could create a custom task which allowed referencing another task, basically copy the functionallity at runtime? I will have to look into it.

Thanks