using DOS START command inside of xp_cmdshell

  • 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.


    Live to Throw
    Throw to Live
    Will Summers

  • 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

  • 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?


    Live to Throw
    Throw to Live
    Will Summers

  • 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!).

  • You can create a job scheduled for the next second which will delete itself after it's completed.

    _____________
    Code for TallyGenerator

  • 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.


    Live to Throw
    Throw to Live
    Will Summers

  • 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

  • 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.


    Live to Throw
    Throw to Live
    Will Summers

  • 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

  • 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.

  • KTD - Wednesday, November 1, 2017 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 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • the needed parameter for START is /WAIT

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • Rudyx - the Doctor - Thursday, November 2, 2017 3:32 PM

    the 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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