• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 923
  • Last Modified:

xp_cmdshell from within a cursor

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 :)

1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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)



wkj7nyAuthor Commented:

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now