Professional Web Applications Themes

simultaneous stored procedures - Microsoft SQL / MS SQL Server

Hi there! I have a challenging task. Situation: A systems transfers data files to a folder and updates the status in a SQL table. My duty was to pass the files to another application and also update the status table. I did it creating an updet and insert trigger, that imports the files in the next application using the stored proceder xp_cmdshell. This works fine. The problem here is that cmdshell works seriell. This means that if an import lasts for 1 min, the Sourcesystem has to wait until it gets the returncode from xp_cmdshell. But the sourcesystem has to ...

  1. #1

    Default simultaneous stored procedures

    Hi there!
    I have a challenging task.
    Situation: A systems transfers data files to a folder and
    updates the status in a SQL table. My duty was to pass
    the files to another application and also update the
    status table. I did it creating an updet and insert
    trigger, that imports the files in the next application
    using the stored proceder xp_cmdshell. This works fine.
    The problem here is that cmdshell works seriell. This
    means that if an import lasts for 1 min, the Sourcesystem
    has to wait until it gets the returncode from
    xp_cmdshell. But the sourcesystem has to work on
    immediately (max 10 ms delay). Is there a way to execute
    several xp_cmdshell's at the same time and to give the
    sourcesystem a returncode right away.
    Thank you very much for your help
    regards
    christoph

    christoph Guest

  2. #2

    Default Re: simultaneous stored procedures

    You move the code into a sql job and in your stored procedure you would kick
    the job off with "sp_start_job". See sql book online for detail on how to
    call this sproc.

    --
    -oj
    Rac v2.2 & QALite
    http://www.rac4sql.net


    "christoph" <at> wrote in message
    news:02b701c35ed7$ed2b8de0$gbl... 


    oj Guest

  3. #3

    Default simultaneous stored procedures

    You can create a scheduled task for each import and start
    it using sp_start_job.

    Rather than using a status table the system will be a lot
    simpler if you poll the directory for the file arriving
    then move it to an archive directory when finished - this
    will mean that everything is self recovering and there is
    no interaction between the systems so easy to test.
    You will get a bit of delay due to the poll interval which
    sounds like it would cause a problem for you if really
    mean what you say.
    1 min for import then has to process withing 10 ms doesn't
    sound reasonable - if it is necessary then you might want
    to think about the architecture.
     
    Nigel Guest

  4. #4

    Default Re: simultaneous stored procedures

    Hi

    If you don't want to use a job, then you could use VBS as this can start
    threads asyncronously... but knowing when the finish may be the difficult
    bit!

    John

    "christoph" <at> wrote in message
    news:02b701c35ed7$ed2b8de0$gbl... 


    John Guest

Similar Threads

  1. Stored Procedures
    By Anthony M. Davis in forum Coldfusion - Advanced Techniques
    Replies: 28
    Last Post: February 9th, 03:17 PM
  2. dt_ Stored Procedures
    By chopper in forum ASP Database
    Replies: 2
    Last Post: July 20th, 04:06 PM
  3. Stored Procedures and 4GL
    By Ahmer Sajjad in forum Informix
    Replies: 1
    Last Post: September 9th, 01:23 PM
  4. Stored Procedures
    By Vladi in forum Microsoft SQL / MS SQL Server
    Replies: 9
    Last Post: August 13th, 01:52 PM
  5. SQL-DMO and stored procedures
    By in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 24th, 02:46 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139