Running DOS Command Prompt(cmd) command thought SSMS

Question

Post reply

Running DOS Command Prompt(cmd) command thought SSMS

naveenreddy.84

Ten Centuries

Points: 1252
More actions
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.

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

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

naveenreddy.84 Ten Centuries Points: 1252 More actions · Answer 2

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

Silverfox SSC-Insane Points: 23150 More actions · Answer 3

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]

naveenreddy.84 Ten Centuries Points: 1252 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

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)

naveenreddy.84 Ten Centuries Points: 1252 More actions · Answer 6

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.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

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)

kramaswamy SSCoach Points: 18181 More actions · Answer 8

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

GSquared SSC Guru Points: 260824 More actions · Answer 9

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

naveenreddy.84 Ten Centuries Points: 1252 More actions · Answer 10

naveenreddy.84

Ten Centuries

Points: 1252

October 1, 2009 at 12:01 pm

#1060609

Its a local drive!!!

GSquared SSC Guru Points: 260824 More actions · Answer 11

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

naveenreddy.84 Ten Centuries Points: 1252 More actions · Answer 12

naveenreddy.84

Ten Centuries

Points: 1252

October 1, 2009 at 11:43 pm

#1060786

yes, i even tried doing that!!!

GSquared SSC Guru Points: 260824 More actions · Answer 13

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

Bill Scott SSCrazy Points: 2338 More actions · Answer 14

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.