Using xp_cmdshell within a cursor

[andyrobins]

Hello.

I have recently started using transact SQL and have come across a small problem. I'm trying to launch a list of Perfmon files from within a cursor using xp_cmdshell. The problem is that it appears to wait until the launched application has terminated before it will cursor onto the next list within the table. Below is the code that I am using.

DECLARE @cursor VARCHAR(100)
DECLARE @tblname VARCHAR(50)
DECLARE @dayname VARCHAR(50)
DECLARE @server VARCHAR(100)
DECLARE @launchpml VARCHAR(100)

SET @dayname=(SELECT DATENAME(WEEKDAY, GETDATE()))
SET @tblname='servers_'+@dayname
SET @cursor='DECLARE svr_cursor CURSOR FOR SELECT servers FROM '+@tblname

EXEC(@cursor)

OPEN svr_cursor

FETCH NEXT FROM svr_cursor INTO @server

WHILE(@@fetch_status=0)

BEGIN
SET @launchpml='start perfmon4 d:\perfmon_templates\'+@dayname +'\' +@server
EXEC master..xp_cmdshell @launchpml
FETCH NEXT FROM svr_cursor INTO @server
END


CLOSE svr_cursor

DEALLOCATE svr_cursor

I am a bit new to this so I don't doubt that it is the wrong way of going about it so feel free to correct anything!!!!

 

[NoCoolHandle]

Bad news is that this is the expected behaviour....

You might want to look into calling a COM object instead of using an executable.

Once built and installed you need to use sp_oacreate and the other 2 procs it refers to in BOL. Be carefull of return datatypes, they can cause problmes if they are wrong.


Rob

PS it is a bit more of a pain to use, but should save your problem.

 

[dky1e]

If you want to achieve parallel execution then you might want to look into creating sql server jobs dynamically and then starting them. You can set them to delete upon success. Look into sp_add_job, sp_add_jobstep, sp_add_jobserver, and sp_start_job.

That way you can cursor through all records and the only wait time will be on creation of the job, since the job running will run on a different connection.

Hope this helps.

 

[andyrobins]

Thanks for the help all. In the end I just used a script to auto-populate a batch file that was scheduled to run every morning as a temporary solution but I think that Dky1e's idea would be more suitable as it tidies itself up afterwards and is without repitition should the job fail.

Thanks again