Can SP run a command .exe file

Question

Can SP run a command .exe file

hhp

Right there with Babe

Points: 756
More actions
September 27, 2002 at 5:22 pm

#79671

Is it possible to create a stored procedure that can run a command .exe file
that is on a different server and then
take the output .csv file and import it into a table somewhere?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Prakash Heda SSCertifiable Points: 5539 More actions · Answer 1

You can use master..xp_cmdshell to fire a command or a batch file now with the help of cmd mapping commands you can reach that file.

The output of any command can be stored this way:

dir c: > result.csv

so the command will be

master..xp_cmdshell 'dir c: > result.csv'

now you can read this file with the help of open data source method to read a csv file and can store the output into a table with the select into clause

Hope this helps......

Prakash

Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA

csraocvs SSC Journeyman Points: 94 More actions · Answer 2

Hi Prakash,

I have a different problem. I have a AFTER insert trigger on a table. In the trigger, I would

like to read a .txt file ( c:\directory\xxx.txt ) which has

only one value in it. I need to copy the value into a table

in the database table. How can I do this with any method ?

I tried BULK INSERT and BCP in the trigger but they are allowed

in a trigger. I appreciate if you can reply ASAP.

Thanks

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 3

Do you want to run the exe on another server or run it from the other server. If the user account SQL Server is running with has access to that exe then you can execute it like

xp_cmdshell '\\server\share\my.exe'

If the output is to stdout then you can insert the results into a table useing

INSERT INTO MyTable(column) exec xp_cmdshell '\\server\share\my.exe'

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

csraocvs SSC Journeyman Points: 94 More actions · Answer 4

Dear Simon,

I have an After insert trigger on a table and I execute a JAVA class file as below.

-------------

select @my_cmd='D:\j2re\bin\java.exe -classpath "'+'D:\MSSQL7\JOBS\sub_key." gensubkey ' > D:\MSSQL\JOBS\sub_key\qry1.txt'

EXEC @key_result=master..xp_cmdshell @my_cmd

-------------

When I insert a record into the table, it inserts a record and executes this trigger. I notice that the java class executes and creates a text file with the returned value.

Now the trouble is I need this returned value into another table. So that I can continue rest of the processing in the trigger.

I appreciate if you can answer to my queries as below.

1) EXEC @key_result=master..xp_cmdshell @my_cmd

This @key_result is 0 or 1 ( success or failure ). How can I catch the actual returned value from the java program.

2) I tried BULK INSERT and BCP commands in the trigger but I get errors that these are not allowed inside a trigger OR an S.P which gets invoked within the trigger.

Is there any other method for getting the result into a table.

Thanking you very much and wait for your reply.

--Chandra

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 5

The return value of xp_cmdshell is the dos errorlevel value. If you want the results from your file in D:\MSSQL\JOBS\sub_key\qry1.txt you can return this output into a table.

i.e

CREATE TABLE results (output text)

select @my_cmd='D:\j2re\bin\java.exe -classpath "'+'D:\MSSQL7\JOBS\sub_key." gensubkey '

INSERT INTO results (output) EXEC @key_result=master..xp_cmdshell @my_cmd

You can then do anything with the data in the results table

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

csraocvs SSC Journeyman Points: 94 More actions · Answer 6

Thanks a lot for your help, Simon. It works in a Stored procedure but not in a trigger. So I created a new S.P with the INSERT and xp_cmdshell commands. Then called it from the trigger.