executing multiple procs at one go

Hi,

I would like to execute multiple stored procs by calling a single script say A. I want to A to execute on a nightly basis. The multiple stored procs are bulk inserts into separate tables. I want to be notified of the error if one among the many stored procs fail when I call A. But if one fails, I don't want A to fail. The remaining stored procs should execute. Does anyone have a script wriiten for A? I guess A is just not about -

EXEC SP1
EXEC SP2
EXEC SP3
EXEC SP4
EXEC SP5

Thankscreate proc dbo.A as
declare @.rc int, @.error int, @.cmd varchar(255)
set @.error = 0
EXEC @.rc = SP1
if @.rc != 0 set @.error = 1
EXEC @.rc = SP2
if @.rc != 0 set @.error = 2
EXEC @.rc = SP3
if @.rc != 0 set @.error = 3
EXEC @.rc = SP4
if @.rc != 0 set @.error = 4
EXEC @.rc = SP5
if @.rc != 0 set @.error = 5

if @.error = 1 begin
set @.cmd = 'Failed to execute SP' + cast(@.error as varchar(10))raiserror (@.cmd, 15, 1)
return (1)
end
return (0)|||Thanks. I shall test it soon and let you know if I have any problems.|||If more than one fails this logic will report the last one. In order to capture all failed you'll have to create a temp table (either # or @.) and insert a record with corresponding info after each SP fired. At the end of execution check for presence of rows in that table and if found, - construct the error message based on contents.|||Yeah, but if you have to rollback, your logging would rollback as well, and you'll have no idea...

echo out to a text file

Also some errors just raise and you can't trap them...I wonder what @.rc would be set to in those cases...NULL?

@.@.ERROR might be non zero in that case...

I'll have to test it...

Vivek: Let us know how it goes...

Executing a package multiple times in parallel - can't see it in the GUI

Hi,
I am calling a package 4 times, in parallel, from a parent package. It
would be really nice if I could see 4 instances of this package in the GUI so I can see what each is doing.

Is that possible?

-JamieJamie, thanks for the feedback. The designer experience here is a little rough. I don't think we'll be able to do anything about it for now.
Please open a bug and we'll take a look for V.Next.
One thing you might try is to log progress and open the log whenever you want to see progress.
Thanks,
K|||Done. Track ID 642775426

Incidentally Kirk, did you see my post "Rowcounts don't appear" which is along a similar theme.

|||Yep, saw that Jamie. Thanks a heap. Have already put it into the V.Next "Think hard about this one" bucket.
K|||Cool. Cos I have to be honest, that's a major irritation for me at the moment cos of the huge (and I do mean HUGE) datasets I've been working with.

-Jamiesql

ExecuteOutOfProcess calling a transactional child package causes Access is Denied.

I have a master package that contains an Execute Package Task whose ExecuteOutOfProcess flag is True, and that calls a child package whose TransactionOption = Required. The job is running in Sql Agent, and the step that calls the master package is configured to run under a certain domain account that is not in the local Administrators group. With this, I get the following:

messageText: Error 0x80070005 while loading package file "C:\program files\microsoft sql server\90\dts\Packages\ETL\Fact_Various_TransactionalChannels.dtsx". Access is denied.

When I add the domain account to the local Administrators group, this error does not occur. From a blog entry, I read that when a child package is executed out of process, the resultant OS process is called dtshost.exe (http://blogs.conchango.com/jamiethomson/comments/1414.aspx). Do I simply need to give my domain account permission to spawn this process? If so, what permission is it? Is there a group that contains this permission?

Is it possible that the domain account does not have access to "C:\program files\microsoft sql server\90\dts\Packages\ETL\"

-Jamie?

|||Unfortunately, no. The account has full control over that path.

Execute xp_cmdshell from store procedure (called from aspx)

Hi, I have been searching for an answer to allow me to execute xp_cmdshell from withing store prcedure by calling the store procedure from an aspx via click on a button. This is what I found from my reserch but was not able to know where to set the rights and what each of them means:
"To run xp_cmdshell for a non-system administrator user, you must grant the following rights.
MSSQLServer and SQLServerAgent Services
Act as part of the Operating System.
Increase Quotas.
Replace a process level token.
Log on as a batch job."

The above quote was from the following link:
http://support.microsoft.com/default.aspx?scid=kb;en-us;264155

The bottom line is to be able to call a store procedure from an aspx page to execute the code which contain xp_cmdshell command, an example of such command is like:
------------------
EXEC master..xp_cmdshell 'dir d:\BT_import\Data\sales_option_price_report.csv'

Thanks for your help
ehx5The "rights" refer to the user account that is running the extended stored proc, namely the user account for teh MSSQLServer service and the SQLServerAgent service. Once you know who they are you can go to the security policy editor and grant them the said rights. However, this is *VERY* dangerous and I really would recommend that you do NOT take this path. If someone gets onto to DB the xp_xmdShell 'Format c'. You get the idea. Much, much better to let some Business logic component sort this out, or even your own extended proc.

Execute Stored Procedure Y asynchronously from Stored Proc X using SQL Server 2000

I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.

When you say that you want to return from the SQL server execution.Really all of the Execution has to be completed before that Can Happen.When I say all of the Execution I mean both the X and Y[ that is Y and X internally called by Y].Am not sure that why you want the code to be executed this way but try out following list of possible case that can handle the requirement you have.

1. Stored Procedure X

Statement 1

Statement 2

Call to Y

Statement 4

Statement 5

Split the above code to three ... Statement 1 and 2 be in a single procedure . CAll it , once after execution return ...

from the code , again from the call the SP Y async.

sql API now support Async Calls ..hope your are using that ... BeginXXX and EndXXX pairs

|||

My requirement is little bit different man. I want to do lot of work in backgroud.

when i call a stored proc X then it should execute few lines of code and call Stored Proc Y in background. Once stored proc X call stored proc Y then stored proc X should return me result set (i mean execution of X is done) without waiting to get it complete stored Proc Y because it is running in background.

how can we do it? pls suggest me some solution.

You can suggest me solution either in ASP.NET 2.0 OR Sql Server 2000.

|||

Not very sure, but some ppl said it can be down using OLE automation . Could you please check this article?

http://www.databasejournal.com/features/mssql/article.php/10894_3427581_2

Changing Stored Procedure to Submit Code Asynchronously

Now that you understand the initial performance problem with SP "usp_enter_order," let me discuss how I could re-write this SP to submit the slow code asynchronously. First, I will need to create a "new" SP that contains the slow code. The second thing will be to replace the slow code in "usp_enter_order" with some OLE Automation that submits the "new" SP asynchronously. Below is the code for the new SP, I called it "usp_run_slow_code":

Hope my suggestion can help

Execute SQL Task not running?

Hi All,

I have a strange problem that I can't find any information about and was hoping someone could help me out.

Some background:

* I am calling an SSIS package from ASP.Net using the in-process, .net methods.
* I am importing a flat file into a staging SQL Server table using the OLE DB Destination.

* After I import the data, I call some Execute SQL Tasks that does some cleaning of the data.

* I then move the data from the staging table to a final table.

Here's the problem:

When the SQL Task like this:
WHILE @.@.rowcount > 0
UPDATE Staging_Table
SET [Test1] = REPLACE([Test1], SUBSTRING([Test1], PATINDEX('%[^a-zA-Z0-9 ]%', [Test1]), 1), ' ')
WHERE PATINDEX('%[^a-zA-Z0-9 ]%', [Test1]) <> 0

is supposed to be run, sometimes it will, sometimes it won't. The character replacement always works when I test the package through BIDS but when I call the package from code, sometimes the task doesn't seem to get called. Or, at least, it isn't updating the table correctly. The staging table is poplated and copied to the final table but the character replacement doesn't occur in-between.

Does anyone have any advice on the possible causes or where to investigate further?

Thanks,
s.

Turn on logging so you can find out what's going on.

-Jamie

|||Hi Jamie,

The log doesn't show anything different between a correct and incorrect run. I think I may have figured out an answer though. I finally figured out that the problem only occurs when I run multiple packages on multiple threads at the same time. Although none of the packages update the same table, perhaps it is something with the @.rowcount that is causing trouble? I was able to fix the problem (as far as I have seen so far) by changing the package executions to use dtexec.

s.

Execute SQL Task hangs...

Hi,

I'm having a problem with one 'Execute SQL Task' calling a stored proc...

The problem is that once the stored proc has finished executing after 10 mins (I know it has thanks to an audit log table entry ), the SSIS task carries on hanging for another 20mins before it gets the task execution result and moves to the following task.

I tried:

- adding/removing a return statement at the end of the stored proc. It doesn't solve the problem

- running the package without debugging and outside visual studio, and I'm still getting the same problem. so it's nothing to do with the debugger...

- tried various settings for the transaction and isolation settings... not getting any better

- changed the stored proc so that it processes less data. Execution time goes down to a couple of minutes and the SSIS tasks only hangs for 2 mins... better but not really helping...

Any clues? I'm new to SSIS so I must have certainly missed something!

Thanks.

Have you used SQL Server profiler to check if the SP has actually finished? I never have seen that problem before and I cannot think it is a problem in SSIS.|||Check for blocking, plenty of info in Books Online. I'm old school so try sp_who2 as start.|||

sp_who2 was another thing I tried... there was no runnable process, no lock etc..

anyway, I finally managed to sort this out... I changed the connection type from OLE DB to ADO.NET and it did the trick...

I have to say it's rather puzzling... I did check the stored proc in case there was something unusual... nocount is set to on, there is not print statement, no select statement, just one return at the end of the stored proc...

when running in visual studio, the debugger process runs at full cpu but doesn't do anything apart from eating more and more memory...

anyway, it's sorted for the time being...

Thanks for your suggestions.

Execute SQL Stored Procedure in Different Threads of SQL

In One Stored procedure i m calling two - three different stored
procedure . Can i run sub Stored Procedure (Calling Procedure ) in
different SQL threads . If Yes than how provide sample SQL code
See i m executing one stored procedure in Query analyser but in that
procedure i m calling 5 stored procedue , can i run all this stored
procedure on SQL Different threads for faster processing
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!Do the 5 SPs need to communicate with each other. If they dont then you can
create 5 SQLAgent Jobs (one for each of the
5 SPs) and from the main SP you can start the 5 Jobs which would actually
execute the underlying 5 SPs.
Gopinath Rajee
"Dharmesh Doshi" <dharmesh@.sdgsoftware.com> wrote in message
news:uZmN2pnHFHA.2700@.TK2MSFTNGP09.phx.gbl...
> In One Stored procedure i m calling two - three different stored
> procedure . Can i run sub Stored Procedure (Calling Procedure ) in
> different SQL threads . If Yes than how provide sample SQL code
> See i m executing one stored procedure in Query analyser but in that
> procedure i m calling 5 stored procedue , can i run all this stored
> procedure on SQL Different threads for faster processing
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Execute SQL Stored Procedure in Different Threads of SQL

In One Stored procedure i m calling two - three different stored
procedure . Can i run sub Stored Procedure (Calling Procedure ) in
different SQL threads . If Yes than how provide sample SQL code
See i m executing one stored procedure in Query analyser but in that
procedure i m calling 5 stored procedue , can i run all this stored
procedure on SQL Different threads for faster processing
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Do the 5 SPs need to communicate with each other. If they dont then you can
create 5 SQLAgent Jobs (one for each of the
5 SPs) and from the main SP you can start the 5 Jobs which would actually
execute the underlying 5 SPs.
Gopinath Rajee
"Dharmesh Doshi" <dharmesh@.sdgsoftware.com> wrote in message
news:uZmN2pnHFHA.2700@.TK2MSFTNGP09.phx.gbl...
> In One Stored procedure i m calling two - three different stored
> procedure . Can i run sub Stored Procedure (Calling Procedure ) in
> different SQL threads . If Yes than how provide sample SQL code
> See i m executing one stored procedure in Query analyser but in that
> procedure i m calling 5 stored procedue , can i run all this stored
> procedure on SQL Different threads for faster processing
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Execute SQL Stored Procedure in Different Threads of SQL

In One Stored procedure i m calling two - three different stored
procedure . Can i run sub Stored Procedure (Calling Procedure ) in
different SQL threads . If Yes than how provide sample SQL code
See i m executing one stored procedure in Query analyser but in that
procedure i m calling 5 stored procedue , can i run all this stored
procedure on SQL Different threads for faster processing
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Do the 5 SPs need to communicate with each other. If they dont then you can
create 5 SQLAgent Jobs (one for each of the
5 SPs) and from the main SP you can start the 5 Jobs which would actually
execute the underlying 5 SPs.
Gopinath Rajee
"Dharmesh Doshi" <dharmesh@.sdgsoftware.com> wrote in message
news:uZmN2pnHFHA.2700@.TK2MSFTNGP09.phx.gbl...
> In One Stored procedure i m calling two - three different stored
> procedure . Can i run sub Stored Procedure (Calling Procedure ) in
> different SQL threads . If Yes than how provide sample SQL code
> See i m executing one stored procedure in Query analyser but in that
> procedure i m calling 5 stored procedue , can i run all this stored
> procedure on SQL Different threads for faster processing
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Execute Process Task using StandardInputVariable

Greetings!

I am using a Excute Process Task calling a selfwritten EXE and I'm trying to pass a command line argument to it. The code for the EXE is quit simple:

Public Sub Main()
Try
Dim info As String = My.Application.CommandLineArgs.Item(0)
MessageBox.Show("Info:" & info)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub

According to the help files there are two ways to pass an argument, using the property Arguments, which I guess is for hard-coded stuff, and the StandardInputVariable for passing dynamic info.

When I use the Arguments property everything works fine. I get the info messagebox with correct data. But when I leave that blank and instead use the StandardInputVariable property setting it to my package variable User::Info then I crash into an exception meaning that no arguments existed (array is out of bonds).

The variable User::Info was at first filled from a previous SSIS task and using the OnPreExecute breakpoint I verified that it contained a stringvalue. I then hardcoded a string value into the variable, but nothing helps. The task refuses to start my EXE with the data in the StandardInputVariable as an argument.

Why is this not working?

When you use the StandardInputVariable property setting, are you declaring the StandardInputVariable property in the code for the EXE?|||

I am not sure what you mean by this. The StandardInputVariable is just a property in the SSIS task Execute Process. It's supposed to just pass the content of the selected variable as a start-up argument to the selected EXE. Is there some kind of a global variable inside my VS2005 VB project that automatically gets some info that is not passed as an argument? Or can I declare something like that?

All my EXE file should need to do is look at the arguments (the first only in this case) passed to it like this:

Dim arg as string = My.Application.CommandLineArgs.Item(0)

If i use the Argument propert it works fine. But that is for hard coded start-up values like standard /H for help or /Q for quiet. I need to pass a dynamic value and that's what StandardInputVariable is for. I have verified that my SSIS variable contains the correct data. Yet no argument is delivered to the EXE file.

|||

Use an expression on the Arguments property for passing both static and dynamic command line arguments to an execute process task.

By using an expression, you can incorporate any combination. For example, the expression on Arguments could be: "/Q" + @.User::FilePath

The StandardInputVariable is a task property pointing to a variable who's contents will be streamed in on the process' standard input file handle. If you log the Execute Process specific event entitled "ExecuteProcessVariableRouting", and make use of the StandardInputVariable task property, note that that the log message says, "Routing stdin from variable "User:<your variable here>".

|||

It turns out that you need to include a call to Console.Readline, as follows:

Dim info As String = Console.Readline

And, of course, make sure that the StandardInputVariable property is set to the package variable containing the value you want to pass. I also left the Auguments property setting blank when I tested this procedure.

Carla

|||

Thank you very much. This solved the problem.

I never thought of using Console since my application is a Windows Forms executable, but it worked like a charm.

|||

Please!

Is it possible to link two o more system variables (StartTime + UserName) in a user variable?

I tried Name=User::MyVar and Value=System::StartTime + "Test" but when I try to read by using your

Dim Info As String = Console.ReadLine

it give me the string "System::StartTime + "Test""

Thanks in advance.

Alex.|||

Not sure if I completely understand your question, but it sounds like you need to use an expression for your variable. You can do this by setting the EvaluateAsExpression property of the variable to TRUE, and then enter your expression (i.e., User::MyVar + User::MyVar2) into the Expression property.

You may need to cast the variables to concatenate them. The expression builder will help you validate that the expression is correct.

Execute Process Task using StandardInputVariable

Greetings!

I am using a Excute Process Task calling a selfwritten EXE and I'm trying to pass a command line argument to it. The code for the EXE is quit simple:

Public Sub Main()
Try
Dim info As String = My.Application.CommandLineArgs.Item(0)
MessageBox.Show("Info:" & info)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub

According to the help files there are two ways to pass an argument, using the property Arguments, which I guess is for hard-coded stuff, and the StandardInputVariable for passing dynamic info.

When I use the Arguments property everything works fine. I get the info messagebox with correct data. But when I leave that blank and instead use the StandardInputVariable property setting it to my package variable User::Info then I crash into an exception meaning that no arguments existed (array is out of bonds).

The variable User::Info was at first filled from a previous SSIS task and using the OnPreExecute breakpoint I verified that it contained a stringvalue. I then hardcoded a string value into the variable, but nothing helps. The task refuses to start my EXE with the data in the StandardInputVariable as an argument.

Why is this not working?

When you use the StandardInputVariable property setting, are you declaring the StandardInputVariable property in the code for the EXE?|||

I am not sure what you mean by this. The StandardInputVariable is just a property in the SSIS task Execute Process. It's supposed to just pass the content of the selected variable as a start-up argument to the selected EXE. Is there some kind of a global variable inside my VS2005 VB project that automatically gets some info that is not passed as an argument? Or can I declare something like that?

All my EXE file should need to do is look at the arguments (the first only in this case) passed to it like this:

Dim arg as string = My.Application.CommandLineArgs.Item(0)

If i use the Argument propert it works fine. But that is for hard coded start-up values like standard /H for help or /Q for quiet. I need to pass a dynamic value and that's what StandardInputVariable is for. I have verified that my SSIS variable contains the correct data. Yet no argument is delivered to the EXE file.

|||

Use an expression on the Arguments property for passing both static and dynamic command line arguments to an execute process task.

By using an expression, you can incorporate any combination. For example, the expression on Arguments could be: "/Q" + @.User::FilePath

The StandardInputVariable is a task property pointing to a variable who's contents will be streamed in on the process' standard input file handle. If you log the Execute Process specific event entitled "ExecuteProcessVariableRouting", and make use of the StandardInputVariable task property, note that that the log message says, "Routing stdin from variable "User:<your variable here>".

|||

It turns out that you need to include a call to Console.Readline, as follows:

Dim info As String = Console.Readline

And, of course, make sure that the StandardInputVariable property is set to the package variable containing the value you want to pass. I also left the Auguments property setting blank when I tested this procedure.

Carla

|||

Thank you very much. This solved the problem.

I never thought of using Console since my application is a Windows Forms executable, but it worked like a charm.

|||

Please!

Is it possible to link two o more system variables (StartTime + UserName) in a user variable?

I tried Name=User::MyVar and Value=System::StartTime + "Test" but when I try to read by using your

Dim Info As String = Console.ReadLine

it give me the string "System::StartTime + "Test""

Thanks in advance.

Alex.|||

Not sure if I completely understand your question, but it sounds like you need to use an expression for your variable. You can do this by setting the EvaluateAsExpression property of the variable to TRUE, and then enter your expression (i.e., User::MyVar + User::MyVar2) into the Expression property.

You may need to cast the variables to concatenate them. The expression builder will help you validate that the expression is correct.