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