xp_cmdshell from within a cursor

Posted on 2002-03-07
Last Modified: 2006-11-17
I have a dilemma with xp_cmdshell within a cursor.

I am working on a Windows NT box running MS SQL Server 7.0.

I am attempting to write an insert trigger to fire off an ActivX exe I have written.

I am attempting to exec the xp_cmdshell from within a cursor.  The xp_cmdshell works fine outside the cursor, but will not function correctly within the cursor.

It is possible that I should not be attempting to use a cursor and there is some much better solution and if so, I'd welcome that answer instead of an answer to my cursor/xp_cmdshell issue.

The big picture is that I have created a table into which a path will be inserted when a user wishes to have the file cached (retrieved) in the background off of an OTG system (The OTG part is irrelevant).

When an insert occurs, I need to attempt to detect which path was inserted, grab that path, and launch my ActiveX Exe... passing it the path.

The first problem I encountered was attempting to identify which path was inserted if 2 or more inserts were made at nearly the same time.  According to the documentation, I need to select from my table where the record exists in the 'inserted' table.

I haven't reached that part in the development yet, but I am assuming that if 3 inserts occur nearly simultaneously, three triggers will fire, all three will find at least 3 records inserted in the 'insert' table, and none of them will have the foggiest idea which one should be processed by which instance of the trigger.

I therefore gave my table a flag field of CacheStatus so I can keep track of whether or not it has been previously processed.  I can live will all three triggers each processing all three requests.  I just don't want all three triggers processing the same one request and the other 2 being ignored.  I also don't want each run reprocessing all of the requests ever inserted since the beginning of time.

At this point I am still attempting to work out the syntax from within the Query Analyzer before moving on to the actual trigger.  Hence, I don't actually have an insert trigger that is firing nor do I expect to find the request in the 'inserted' trigger so I am only selecting off the CacheStatus field.

Below is an example of the test code I am using:

-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ >>>
declare @DocPath  varchar(250)
declare @mycntr int
select @mycntr = 0
declare @cmd sysname
declare otg_req_cursor cursor local dynamic type_warning for
  select DocPath
    from OtgCacheRequest
      where CacheStatus = 0
  for update of CacheStatus

open otg_req_cursor
  fetch next from otg_req_cursor
    into @DocPath

  while (@@fetch_status = 0)
    select @mycntr = @mycntr + 1
    if @mycntr > 5 break

    set @cmd = "'c:\\obgc\\OtgBkGrndCache.exe " + @DocPath + "'"

    print 'exec master..xp_cmdshell ' + @cmd + ', no_output'

    exec master..xp_cmdshell @cmd, no_output

    SELECT @TranName = 'MyTransaction'
      Update OtgCacheRequest
        set CacheStatus = 1
          where DocPath = @DocPath
    fetch next from otg_req_cursor
      into @DocPath
close otg_req_cursor
deallocate otg_req_cursor
PRINT 'Done...'
-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ <<<

With the "print 'exec master..xp_cmdshell..." line, I am merely attempting to print out an exact representation of the xp_cmdshell line that is in question.  The code successfully updates the CacheStatus to 1 and prints 'Done...' so I have to assume that the script terminates normally.  And there are no error messages returned.  However, OtgBkGrndCache.exe does NOT execute.

If I copy the output from the "print 'exec master..xp_cmdshell..." line, paste it into the Query Analyzer, and run it outside the cursor, OtgBkGrndCache.exe executes successfully.

My questions are:

  1. Should I be taking an entirely different approach? (No cursor? Something other than xp_cmdshell?)
  2. If I should be attempting to exec xp_cmdshell from within the cursor, what do I need to do to get it to execute?
Unfortunately, you'll have to be pretty darned clear in any answers or you'll lose me very, very quickly :)

Question by:wkj7ny
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 200 total points
ID: 6849824
The approach i use is:

* We have the table OtgCacheRequest in which rows are inserted, and on which we have the trigger.
* We have a second table (OtgWork), with only 4 fields:
   - ID (identity and PK),
   - Key (Key value to the OtgCacheRequest table
   - TimeStamp (date+time when this row was created, with default value of getdate() )
   - Flag (a flag indicating if this row was processed or not)

* The Trigger should only insert the "Key" to OtgWork table, the other fields take the automatic, default or NULL values

* To handle those rows, use a stored procedure with code like this:
  DECLARE @Key <datatype>
  DECLARE @Data varchar(250)

  SELECT TOP 1 @Key = Key FROM OtgWork

    UPDATE OtgWork
    SET Flag = 'W'
    WHERE Key = @Key
    SELECT @Data = DocPath
    FROM OtgCacheRequest
    WHERE keyfield = @Key
    <your code to launch xp_cmdshell ...>

    -- The request has been done, you can either
    -- delete this row, or update it with
    -- different flag value
    DELETE OtgWork
    WHERE Key = @Key
    --set the @key value to NULL
    SET @Key = NULL
    SELECT TOP 1 @Key = Key FROM OtgWork
    WHERE Flag IS NULL  

Now, you can launch this code using a scheduled job, or/and inside the trigger. The rows with a non-null flag should be cleared after some time, that's why we have this timestamp value... Either we reset the flag to NULL after x hours, or remove the lines (depends on the requests)




Author Comment

ID: 6899949

Thank you for you answer.  It didn't turn out to be exactly what I needed. I finally found that the problem was actually a syntax thing.  The person who mentored me on the use of xp_cmdshell said I had to add the inner string quotes in xp_cmdshell and it turned out that I should NOT have added those quotes.

However, I did use most of the rest of your example and ended up with a much cleaner solution, so I am giving you the "A".

Thanks again.


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need time in SQL 8 30
WSUS Console loosed connectivity to the database. 13 35
Access #Deleted data 20 36
Need age at date of document 3 8
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question