November 7, 2006 at 9:16 am
What I want to do is to issue a START command inside the xp_cmdshell. This should start a new cmd process with whatever command I put after the START and then continue and not wait for the cmd to finish.
What happens is the xp_cmdshell waits for any other processes to finish before returning a finished status.
When I manually run the same command in a cmd window, it finishes and returns control to the cmd window. I can then close the cmd window and the subprocess continues. Which is basically what I want to do, but inside xp_cmdshell. The command that comes after the START command is generated at runtime.
I have tried to put the START command inside a batch file and then run the batch file from xp_cmdshell, but I get the same results.
I have tried to pipe the output of the START command to another file, but same results.
Any help in this matter is greatly appreciated.
November 7, 2006 at 2:06 pm
In the past I created a nightly process that kicked off jobs from a table schedule.
I wanted the sproc scheduler to kick off individual threads. I solved the problem by kicking off jobs, which go off and execute on their own. I only had so many types of threads and created that many typed/generic jobs that I passed appropriate values.
Not sure if that is a solution type you were searching for.
daryl
November 7, 2006 at 2:27 pm
Ok, how can I get a start a stored procedure in a script and not have the script wait for the stored procedure to finish before continuing?
November 7, 2006 at 2:34 pm
Create a job that fires each proc that needs to be ran. Then fire all the jobs sequentially, DOS will not wait for their execution to continue (he I understand what he said!).
November 7, 2006 at 2:39 pm
You can create a job scheduled for the next second which will delete itself after it's completed.
_____________
Code for TallyGenerator
November 8, 2006 at 5:48 am
I think I have the seeds to my solution. Thanks.
I am still curious as to why the xp_cmdshell won't work with the START command in DOS.
November 8, 2006 at 3:00 pm
That's probably because of what you are trying to do within that START.
There must be some OSQL or BCP call, right?
_____________
Code for TallyGenerator
November 9, 2006 at 7:39 am
I'm not trying to do anything special inside the START command.
My command inside my procedure is:
declare @CMD CHAR(100)
SET @CMD='START DIR C:\*.* /S'
exec master..xp_cmdshell @CMD
START DIR C:\*.* /S takes about 1 seconds if I run it out of a command window and then another 10-20 sec for the other window to finish.
If I run the above SQL in a query window, then it takes 10-20 seconds to run. In my opinion, it should take 1 second to run in the query window and then the CMD window should take 10-20 seconds to end.
December 2, 2006 at 1:44 am
Hi there,
I had xp_cmdchell 'start bla bla bla' running well till last night. I spent hours figuring out what could be wrong. Exactly same situation as you told.
Doesn't really matter either mentioned command is execudet by sqlagent or directly from query analyzer. I mean - different users in my case.
But anyway, after server restart it runs again .... so , any ideas on it ?!
Best,
Aivars
November 1, 2017 at 1:32 pm
Can I answer a question 11 years later?
Its a very picky thing but I have found this will make it work. It can be used to do a form of multi threading since the start executes without waiting.
declare @rc as int
declare @DynamicSQL Varchar(500)
Set @DynamicSQL = 'cmd /c start /d c:\windows\ /B SQLCMD -U "username" -P "password" -S "server" -q " select GETDATE()" -d database -o "T:\Alerts\report.txt" '
print @DynamicSQL
execute @rc = XP_CMDSHELL @DynamicSQL
I don't always test my SQL scripts, but when I do, I test in Production.
November 1, 2017 at 2:52 pm
KTD - Wednesday, November 1, 2017 1:32 PMCan I answer a question 11 years later?Its a very picky thing but I have found this will make it work. It can be used to do a form of multi threading since the start executes without waiting.
declare @rc as int
declare @DynamicSQL Varchar(500)
Set @DynamicSQL = 'cmd /c start /d c:\windows\ /B SQLCMD -U "username" -P "password" -S "server" -q " select GETDATE()" -d database -o "T:\Alerts\report.txt" '
print @DynamicSQL
execute @rc = XP_CMDSHELL @DynamicSQL
I haven't tried it but that looks right to me. And I love it when someone answers a question even if it is 11 years old. Seriously. Well done.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 2, 2017 at 3:32 pm
the needed parameter for START is /WAIT
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
November 2, 2017 at 6:31 pm
Rudyx - the Doctor - Thursday, November 2, 2017 3:32 PMthe needed parameter for START is /WAIT
Hey there, Rudy. Long time no see. Glad to see you're still around.
I could be wrong but I'm pretty sure that /WAIT is the exact opposite of what the OP is trying to do which is to kick something off and exit from the xp_CmdShell without waiting.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 24, 2021 at 5:32 pm
Hi Jeff,
To start with Merry Christmas and Happy New Year
I came across a situation where I have to invoke parallel sessions and that has to be done through query window with various different values for a proc in batch , after doing research, I came across this article and have started testing and based on my tests, I don't see parallel sessions open and different sets of commands do run under each session but it runs serially.
To test it I have tested the following piece of code.
declare @minid tinyint =1, @maxid tinyint =10
declare @sqlstring varchar(4000)
declare @rc int
while @minid <= @maxid
begin
Set @sqlstring = ' cmd /c start /d c:\windows\ /B SQLCMD -E -S "'+ convert(varchar(256),@@servername)+'" -i "e:\canbedeleted\test.sql" -o "e:\canbedeleted\logs\test_'+convert(varchar(3),@minid) +'.log" -d master'
print @sqlstring
exec @rc =master.dbo.xp_cmdshell @sqlstring
set @minid = @minid +1
end
Test.sql file contains the following code.
declare @miniteration tinyint =1 ,@maxiteration tinyint =3
startprocess:
while @miniteration <= @maxiteration
begin
select getdate() CollectDateTime,substring(s2.status,1,30) Status,s2.session_id,s2.start_time,s2.blocking_session_id,convert(varchar(6),s2.cpu_time) CPU,
s2.logical_reads,s2.reads,s2.writes, s2.command,
convert(varchar(30),DB_NAME(s2.database_id)) DBName,
s2.percent_complete,substring(s2.wait_type,1,45) WaitType,substring(s2.last_wait_type,1,20) LastWaitResource,
s2.wait_time,
s2.wait_resource,
SUBSTRING (s1.text, (s2.statement_start_offset/2)+1,
((CASE s2.statement_end_offset
WHEN -1 THEN DATALENGTH(s1.text)
ELSE s2.statement_end_offset
END - s2.statement_start_offset)/2) + 1) AS statement_text
,s3.login_name,
s3.program_name,s3.host_name -- into dbadmin.dbo.temp_capture
from master.sys.dm_exec_requests s2
cross apply master.sys.dm_exec_sql_text (s2.sql_handle)s1
inner join master.sys.dm_exec_sessions s3
on s2.session_id = s3.session_id
waitfor delay '00:00:10'
set @miniteration = @miniteration +1
end
I see the code is working fine, but intent of invoking parallel sessions is not happening, just want to follow up, am I missing any thing
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy