Solved

xp_cmdshell from within a cursor

Posted on 2002-03-07
2
845 Views
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 @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!
0
Comment
Question by:wkj7ny
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
Comment Utility
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
  WHERE Flag IS NULL

  WHILE NOT (@Key IS NULL)
  BEGIN
    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  
  END

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)

CHeers



 

0
 

Author Comment

by:wkj7ny
Comment Utility
angellll,

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.

wkj7ny
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now