what is the xp_cmdshell why it is showing value as 4

Question

DECLARE @RetVal int

Exec @RetVal = xp_cmdshell 'dtexec /f package_path', no_output

select @RetVal It is showing as 4.

What is the meaning for 4. Advance thanks . Please help me.

Sweet2010

Answer 1

xp_cmdshell simply opens a Windows command shell and passes a string for execution and returns the result of what was executed. 

In this example the string being passed is dtexec /f package_path .  dtexec is a utility that configures and executes Integration Services packages. The value 4 indicates that the package could not be found. The dtexec utility (including return codes and their meaning) is described here:  http://msdn.microsoft.com/en-us/library/ms162810.aspx.  The xp_cmdshell utility is described here:  http://msdn.microsoft.com/en-us/library/ms175046(SQL.90).aspx.

 

Regards,

Gail

 

 

---

Gail Erickson [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights

Answer 2

Gail,

The page describing xp_cmdshell tells that the return values are either 1 or 0. This is not true according to what the user got. I think this section needs to be explained in greater detail and perhaps a link to dtexec return codes as an example should be also added.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

Hi Naomi,

The xp_cmdshell topic does indeed list a Return Code value of 0 or 1 to indicate success or failure of executing the xp_cmdshell procedure itself. Most system stored procedures that that, although some return no value at all as a Return Code.   

However, the Return Code is different than the Result Set returned by the procedure, which can return any values that are the result of executing the string that was passed.

I'm sure that the Result Set section of the xp_cmdshell topics could be improved, however, given the example used in that section (EXEC xp_cmdshell 'dir *.exe';)  I don't think the reader would expect that statement to return just 1 or 0.

Best,

Gail

 

 

 

---

Gail Erickson [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights

Answer 4

Take a look at this thread http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/d6c40bf5-e968-4d6b-a7bc-f50aa030ecc4/#d6c40bf5-e968-4d6b-a7bc-f50aa030ecc4 and the quoted KB article.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog