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 @TranName VARCHAR(20)
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)
  begin
    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'
    BEGIN TRANSACTION @TranName
      Update OtgCacheRequest
        set CacheStatus = 1
          where DocPath = @DocPath
    COMMIT TRANSACTION @TranName
   
    fetch next from otg_req_cursor
      into @DocPath
  end
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 :)

Thanks!