xp_cmdshell in stored procedure not the same result as cmd prompt

Question

xp_cmdshell in stored procedure not the same result as cmd prompt

dirk.dromgoole

SSC-Addicted

Points: 442
More actions
May 20, 2014 at 10:40 am

#293477

I have a stored procedure that uses xp_cmdshell to execute an executable. I made code changes to the executable and compiled a new build. The stored procedure that uses xp_cmdshell to execute is not recognizing the code changes but when I run the same command from cmd prompt it works fine. Any ideas?

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

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

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 1

dirk.dromgoole (5/20/2014)
I have a stored procedure that uses xp_cmdshell to execute an executable. I made code changes to the executable and compiled a new build. The stored procedure that uses xp_cmdshell to execute is not recognizing the code changes but when I run the same command from cmd prompt it works fine. Any ideas?

First thought would be more than one instance of the executable on the system.

😎

Ed Wagner SSC Guru Points: 287024 More actions · Answer 2

I'm thinking the same thing. When you shell out, are you giving it the full path and filename to the executable? If not, then you're probably executing the wrong copy of it, so you're not getting the results from the new one.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

Sounds like a reason to move away from xp_cmdshell. What is the executable doing and can it be hosted on a machine other than a machine hosting SQL Server and be executed via a remote shell?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 4

Hi

When you make changes to the executable you can recompile the SP.

You could also create the SP with recompile option, so it removes and creates new plans for next executions.

Igor Micev,My blog: www.igormicev.com

dirk.dromgoole SSC-Addicted Points: 442 More actions · Answer 5

The issue was that a copy of the program that was being executed was somehow locked into cache on the server. After the program was unlocked it picked up the revised program.

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 6

dirk.dromgoole (6/17/2014)
The issue was that a copy of the program that was being executed was somehow locked into cache on the server. After the program was unlocked it picked up the revised program.

Very curious here, could you share some details on this?

😎