Improve company productivity with a Business Account.Sign Up


xp_cmdshell from within a cursor

Posted on 2002-03-07
Medium Priority
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 800 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

595 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