Post reply

Running DOS Command Prompt(cmd) command thought SSMS

  • September 28, 2009 at 11:54 am

    #134113

    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.

  • September 28, 2009 at 12:13 pm

    #1059135

    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

  • September 30, 2009 at 1:20 am

    #1059825

    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

  • September 30, 2009 at 1:26 am

    #1059830

    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]

  • September 30, 2009 at 2:17 am

    #1059842

    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

  • September 30, 2009 at 10:43 pm

    #1060273

    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)
    Intro to Tally Tables and Functions

  • October 1, 2009 at 12:01 am

    #1060284

    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.

  • October 1, 2009 at 12:25 am

    #1060288

    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)
    Intro to Tally Tables and Functions

  • October 1, 2009 at 11:21 am

    #1060583

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

  • October 1, 2009 at 11:50 am

    #1060601

    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

  • October 1, 2009 at 12:01 pm

    #1060609

    Its a local drive!!!

  • October 1, 2009 at 12:06 pm

    #1060612

    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

  • October 1, 2009 at 11:43 pm

    #1060786

    yes, i even tried doing that!!!

  • October 2, 2009 at 7:29 am

    #1060908

    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

  • October 2, 2009 at 8:44 am

    #1060979

    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