xp_cmdshell problem

Question

xp_cmdshell problem

Ian Lampert-355116

SSC Journeyman

Points: 77
More actions
January 9, 2008 at 7:14 am

#61487

Calling batch file using xp_cmdshell within a procedure but job never ends.
The batch file works perfectly when called from a prompt and the procedure works fine with some other command in xp_cmdshell.
Will be happy to provide more information...

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

looks like we'll need more info...can you post the actual contents of the bat file?

I've used xp_commandshell lots of times, and usually, if it doesn't come back it's due to either a permissions issue..ie YOU can run the batch file, but the account under which SQL Server can't because of a mapped dirve, or network permission, or something like that.

also, if the command is calling a program, adn the program raises an error which would be a messagebox waitng for someone to click "ok", that would stop the command from returning.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ian Lampert-355116 SSC Journeyman Points: 77 More actions · Answer 2

I have pasted the code from the batch file below. As I say, this works perfectly from the command prompt. I do appreciate your comments but surely the above means that it is perhaps not one of the reasons that you suggest?

The batch file simply changes the working directory and runs an application. The only reason that I used the batch file was so that I could change the working directory.

C:

CD Program Files\IC\HRG4 Reference Cost Grouper

hrggrouper -i D:\PCT_Warehouse\LoadFiles\GrouperFiles\AEGrouperTemp.csv -o D:\PCT_Warehouse\LoadFiles\GrouperFiles\AEGrouperOutput.csv -d D:\PCT_Warehouse\LoadFiles\GrouperFiles\AE.rdf -l em_v40

Lowell SSC Guru Points: 323518 More actions · Answer 3

ok, The command line you pasted does make it a little clearer...and I'm going to guess that the issue is still permissions;

hrggrouper.exe

-i D:\PCT_Warehouse\LoadFiles\GrouperFiles\AEGrouperTemp.csv <--input file i assume

-o D:\PCT_Warehouse\LoadFiles\GrouperFiles\AEGrouperOutput.csv <--<--output file i assume

-d D:\PCT_Warehouse\LoadFiles\GrouperFiles\AE.rdf <--<--reference file?

-l em_v40 <--<--Which server?

This is what i'd double check:

It's pretty safe to assume that you are running the command as "sa" i guess, so remember "sa" never logs in on the machine/network.

Is the d:\ drive a hard disk on the SQL server, or is it a mapped drive to a network location/other machine? that could be the issue.

the program hrggrouper.exe...does it only manipulate the text files, or does it connect to a database or network object somewhere? Here's I'm checking that if i run the program as myself or Administrator, it has no problem connecting, but maybe not as the account SQL Server is running.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!