Running DOS Command Prompt(cmd) command thought SSMS

  • Hi All,

    Is it possible to run DOS command prompt commands through T-Sql Statement or by some procedure?

    I have got a scenario, wherein i need to run a command(command from third party) which takes column values as a parameters.

  • Take a look at xp_cmdshell. That allows you to run command prompt batches/commands from T-SQL.

    Be warned that it does create security risks if you turn that on. (It's turned off by default.)

    Another (possibly better) option might be to write a CLR proc. VB.Net's System.IO should be able to do what command prompt commands do, in most cases, and it'll be easy to make it more secure that way.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Hi,

    I am using a xp_cmdshell method of running command and i used the following tsql statement

    exec xp_cmdshell 'bop_cmd'

    but it gives following error

    'bop_cmd' is not recognized as an internal or external command,

    but, when i ran the same command in command prompt, its gives me the approperiate result

    Kindly provide your view on this

    Thanks & Regards,

    Naveen

  • naveenreddy.84 (9/30/2009)


    Hi,

    I am using a xp_cmdshell method of running command and i used the following tsql statement

    exec xp_cmdshell 'bop_cmd'

    but it gives following error

    'bop_cmd' is not recognized as an internal or external command,

    but, when i ran the same command in command prompt, its gives me the approperiate result

    Kindly provide your view on this

    Thanks & Regards,

    Naveen

    change your command to

    exec xp_cmdshell 'location of file\bop_cmd'

    It is good practise to put the file extension for the file on as well.

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • I tried giving the complete path

    exec master..xp_cmdshell 'call E:\PROGRA~1\CA\SERVIC~\bin\bop_cmd.exe'

    and i got following error: "The system cannot find the specified path"; but the file do exists

    I need to understand if what am doing is correct. I am trying to run an exe from the Tsql statement

  • naveenreddy.84 (9/30/2009)


    I tried giving the complete path

    exec master..xp_cmdshell 'call E:\PROGRA~1\CA\SERVIC~\bin\bop_cmd.exe'

    and i got following error: "The system cannot find the specified path"; but the file do exists

    I need to understand if what am doing is correct. I am trying to run an exe from the Tsql statement

    You cannot use a drive letter if the path is on any machine other than the server. Instead, you will need to use a UNC to a "share". Also, the server must be able to "see" the path which means the server must be logged in as a user that can see the path.

    --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)

  • I am running this on my local system and the file do exists in the path specified.Also, i can run this command through command prompt.

  • naveenreddy.84 (10/1/2009)


    I am running this on my local system and the file do exists in the path specified.Also, i can run this command through command prompt.

    Like I said... the SQL Server has to be able to "see" your local system.

    --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)

  • If the files exist on different machines, you might want to consider using a CLR and use impersonation to be able to access the different machines. If you do it on the SQL level, and you're trying to access files on another machine, you'll probably run into problems with the local user not having proper access rights / etc...

  • Is drive E a local drive, or a mapped drive on the network?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Its a local drive!!!

  • Have you tried running:

    exec master..xp_cmdshell 'E:\PROGRA~1\CA\SERVIC~\bin\bop_cmd.exe'

    Also, have you tried that with the full path enclosed in quotes, instead of the 8.3-style path?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • yes, i even tried doing that!!!

  • In that case, I think the exe you're calling may be returning something to SQL that it recognizes as an error code. Try this:

    declare @Err int;

    exec @Err = master..xp_cmdshell 'E:\PROGRA~1\CA\SERVIC~\bin\bop_cmd.exe';

    select @Err;

    What do you get from that?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Is the path on the server the same as the path on your workstation? The exec xp_cmdshell is running on the server, not your workstation.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply