Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2006
    Posts
    4

    Unanswered: Running a SP in background (Asynchro)

    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:
    Code:
    $DTS_result = $dbj->Execute(mssql_query("EXECUTE Run_DTS_Packages"));
    My Stored procedure (without username & password...):
    Code:
    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

  2. #2
    Join Date
    May 2006
    Posts
    16
    use a timer to refresh your page and check your procedure.

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    that will only work if you launch the sproc call on a separate thread from the main thread. I know nothing about php so I don't know if it's possible or not.

  4. #4
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    I'm really curious to see the answer to this one! Having a Stored Procedure initiate an Async procedure would have some wonderful ramifications.

    The SQL Server Agent obviously initiates jobs, and I notice that scheduled Database Maintenance creates xp_sqlmaint jobs, implying that they are Extended Stored Procedures.

    A read thru the Extended Stored Procedures doc may be of help since it allows you to expose SQL to the outside world, and visa-versa. However; a quick link by someone would be ideal!

    The PHP portion of the question isn't really applicable to this forum. Surely there's a PHP forum that can answer that one.

  5. #5
    Join Date
    Oct 2006
    Posts
    4
    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

  6. #6
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by CFGilles
    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
    What part of all this creates an asyncronous thread? I'll take a leap and say that the SQL Server Agent runs all jobs asyncronously, but that's just my guess.

    Surely SQL Server has a built in xp_ to initiate a job. Going out to the shellcmd seems clunky. Hmmm, got a link?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •