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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

650 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