Running a SP in background (Asynchro)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Running a SP in background (Asynchro)

Author

Topic

CFGilles
Starting Member

3 Posts

Posted - 2006-10-11 : 15:12:24

Hi all,

I'm calling a PHP page that run a stored procedure. The SP contains a xp_cmdshell command that runs a DTS package. This DTS package is HUGE and will take many hours to import tables from an Oracle database to SQL Server. But I don't want my PHP page to "hang" in the process...

How can I execute my PHP page and refresh it every 30 seconds to see if the importation is done ? In my actual code, I create a "start_file.txt" when I begin the importation and a "end_file.txt" when it's done. I want to refresh every 30 seconds to see if the "end_file.txt" is created and display a "Importation Done!" message.

To be able to do that, I need the DTS Package to run in the background of the web server, or asynchronously from my PHP page.

Simple (stupid) question: is it possible to create a SP to call my main SP so it will run independently ??

Let's review some of my code here.

Part of my PHP page:

$DTS_result = $dbj->Execute(mssql_query("EXECUTE Run_DTS_Packages"));

My Stored procedure (without username & password...):

CREATE PROCEDURE Run_DTS_Packages AS
exec master.dbo.xp_cmdshell 'C:\Progra~1\Micros~3\80\Tools\Binn\ISQL.EXE -S RHEA -U user -P pwd -Q "ISQL_Batch ''D:\DDFIImporte\IMPICAFI.bat''" -n -d DDFI'
GO

Any help will be greatly appreciate !!

CFGilles

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-10-12 : 05:51:18

Try...

exec master.dbo.xp_cmdshell 'START C:\Progra~1\Micros~3\80\Tools\Binn\ISQL.EXE -S RHEA -U user -P pwd -Q "ISQL_Batch ''D:\DDFIImporte\IMPICAFI.bat''" -n -d DDFI'
GO

The START should launch ISQL as a seperate task.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-10-12 : 06:01:16

Or you could create a job that runs the package and start the job. That would run on the server.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

CFGilles
Starting Member

3 Posts

Posted - 2006-10-12 : 09:36:21

Hi Andrew,

Your solution is working but not as a seperate task in PHP. I need to launch that task outside of PHP so I can monitor is the job is done or not by refreshing the page every 30 seconds. If I wait for the page to execute, what's the point ?

I will try NR's solution, create and execute a job in SQL Server.

Thanks all, I will shoot you my results !

CFGilles

CFGilles

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-10-12 : 10:16:40

Or you could schedule a task in SQL server to execute once a minute all day long to look for a "kickstart point" which would allow your long runnign job to execute.
Your PHP page would just create the "kickstart point"

CFGilles
Starting Member

3 Posts

Posted - 2006-10-12 : 10:19:33

Hi all, I finally resolved the problem with help from around the net.. !

The solution:
1) SQL Server -> Management -> SQL Server Agent -> Jobs
You have to add a new job with, in the Steps Tab, paste the xp_cmdshell action you want. My step looks like this (without the username/password):
exec master.dbo.xp_cmdshell 'C:\Progra~1\Micros~3\80\Tools\Binn\ISQL.EXE -S MyServer -U MyUserName -P MyPassword -Q "ISQL_Batch ''My Package and variables''" -n -d MyDataSource'
I named my Job: Run_DTS_Packages

2) You have to create a Stored Procedure to call that new job:
CREATE PROCEDURE Call_DTS_Packages AS
SET NOCOUNT ON
EXEC msdb.dbo.sp_start_job @job_name = 'Run_DTS_Packages', @server_name = 'MyServer'
GO

3) For the PHP fan club, here's the call:
$DTS_result = $dbj->Execute(mssql_query("EXECUTE Call_DTS_Packages"));

I hope that little knowledge tranfer will help some of you !

Regards

CFGilles

nr
SQLTeam MVY

12543 Posts

Posted - 2006-10-12 : 10:37:11

You should use osql rather than isql.
Anyway you can use dtsexec to run a dts package in the jobstep (unless you have a batch file which does other things too).
The start job looks ok though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources