Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2007
    Posts
    54

    Exclamation Unanswered: xp_cmdshell - asynch?

    I'll start of with a little overview of what i'm trying to do. then i'll address the why i have to do it this way. (pootle flump, if you're reading this, this first part will be a bit of review a for you! it's a different type of question... same fundamental issue)

    i need a table trigger that will write the pk value to a temp table whenever a record has been modified to have a status of 1.

    this temp value will then be read by a stored procedure which will simply return the pk value it reads. it will also delete the value from the temp table, ensuring there's only ever one value at a time in this table. this stored proc will be called by an external application...

    as for why i'm doing it this way. we are using an iway / biztalk type application for the industrial world and as per their support department, this is the only way to get data out from a sql database into their app without "knowing" which record to request.

    So in a nut shell we have:

    1. sql database table changes.
    2. trigger runs and save changes to another temp table within the same database. trigger then shells out via xp_cmdshell and runs a dos utility supplied by this third party company. this tool essentially runs their program, which in turn calls the second stored proc within the original sql database.
    3. this second stored proc is what queries the temp table, grabs the value and returns it as an output parm. it then deletes the value.

    When i trigger the trigger by making a change on my database table, i get the following error after a few seconds:

    no row was updated. the data in row X was not committed.
    error source: .net SQLClient Data Provider
    Error message; timeout expired. The timeout period elapsed prior to completion or the server is not responding.

    my guess is that it's waiting for the xp_cmdshell to come back but it's not for some reason.
    as i'm posting here, i'm also trying to troubleshoot the second sp to ensure that the select and delete is working properly. but i thought i should also ask if i can make xp_cmdshell run asynchronously?
    although the change is not ultimately saved, the trigger does shell out and call the external application...

    if anyone has any comments (other than this is a really bad way of exchanging data... i know. but this is the solution that was given to me by their support group!)

    thanks.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    As the whole thing happens in the "context" of the update statement, the changes have not been committed when the second stored procedure tries to read the data. Which means the rows are still locked for read (writers block readers unless you are using SQL Server 2005 and have changed the default installation).

    The only way I can see around this "deadlock" is to start the external program (you are not really running a DOS program, are you?) by using a batch file, that uses the "start" command to kick off the external program. Thus the xp_cmdshell comes back immediately, the initial update statement is finished and thus the locks are released and the second procedure should be able to read the data.

  3. #3
    Join Date
    Jul 2007
    Posts
    54
    well they sent me an exe file which loads their application and runs the transaction that i've passed as an arg.

  4. #4
    Join Date
    Jul 2007
    Posts
    54
    could i just create a windows scripting file of some sort? what would the xp_cmdshell command look like in this case?
    ps. thank you.

  5. #5
    Join Date
    Jul 2007
    Posts
    54
    Quote Originally Posted by shammat View Post
    As the whole thing happens in the "context" of the update statement, the changes have not been committed when the second stored procedure tries to read the data. Which means the rows are still locked for read (writers block readers unless you are using SQL Server 2005 and have changed the default installation).

    The only way I can see around this "deadlock" is to start the external program (you are not really running a DOS program, are you?) by using a batch file, that uses the "start" command to kick off the external program. Thus the xp_cmdshell comes back immediately, the initial update statement is finished and thus the locks are released and the second procedure should be able to read the data.
    one other question i have then is, will i still have these issues if i write out to an excel file or a plain text file?

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by chubbypama View Post
    could i just create a windows scripting file of some sort? what would the xp_cmdshell command look like in this case?
    Just create a batch file that starts your .exe
    But you need to use the "start" command in order to start the .exe from within the batch file, otherwise the .exe will not be started in the background.

    Then simply call that batch file from xp_cmdshell.

    I haven't used xp_cmdshell, but I'd guess you simply pass the name of the batchfile instead of the .exe

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Calling xp_cmdshell from a trigger is even worse than having a trigger to begin with. I see why you have to have it, but there are better ways to call an exe from a trigger. Look into creating a scheduled task and then just call msdb.dbo.sp_start_job @job_name=N'whatever'. This way your trigger does not wait on anything to complete, and finishes right away.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Jul 2007
    Posts
    54

    tried using a file instead

    hi guys. thanks for the responses. i like the idea of the scheduled job... and i'm also curious about the "start" command. thanks for both of those suggestions. i'll try them when i'm back in the office monday.
    just fyi though.
    the last thing i tried while in the office was to write to a file instead of a table. this is what their support folks originally suggested but it seemed a little silly to have a database write out to a file when i should be able to use a table. in any case, i tried it and it seems to work, except for one little problem.

    when i create my file, it seems to have appended a space, and what looks like a carriage return line feed sequence of some sort.
    so when i try to read it back it and convert it to an int, it's failing.
    i'm not too proficient with tsql as you've probably guessed by now,... but i'm wondering if it has any string manipulation functions like instr() or mid() etc.
    i guess somehow i have to figure out what those characters really are at the end of the file first.

    i can't remember the exact syntax i used but i did something like:
    rtrim(convert(int, myvariable))

    but since i'm just using a dos "type" command to get the entire file contents into my select statement, my gut is telling me the string also contains the crlf stuff at the end of the line.

    do you have any suggestions for me?

  9. #9
    Join Date
    Jul 2007
    Posts
    54
    i just found this on another website:
    set @myvariable = REPLACE(REPLACE(@myvariable, CHAR(13), ' '), CHAR(10), ' ')

Posting Permissions

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