what is the value of xp_cmdshell

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

Check comments at the end of the xp_cmdshell topic in BOL

Looks like you need to post in the Documentation forum, as the help only indicates 0 and 1 as possible return values.

---

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


My blog

Answer 2

My guess is the "no_output".

If you say no_output, then why do you need to know what is the return value.

From http://msdn.microsoft.com/en-us/library/ms175046.aspx

no_output

Is an optional parameter, specifying that no output should be returned to the client.

Change it to:

DECLARE @RetVal int

Exec @RetVal = xp_cmdshell 'dtexec /f  package_path'

select @RetVal

---

Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.

Answer 3

See http://support.microsoft.com/kb/184039

 

Behavior in Version 4.21 and 6.0

The return code in xp_cmdshell in versions 4.21 and 6.0 is limited in that it returns a value of either 0 or 1 depending on whether the command was executed successfully. The return code does not provide the actual exit code of the command being called. 

Behavior in Version 6.5 and Later

In version 6.5 and later, xp_cmdshell is improved. It returns the actual exit code from the command being executed. This provides a much more useful mechanism in using the return code inside a batch or stored procedure.

 

---

N 56°04'39.26"
E 12°55'05.63"

Answer 4

It doesn't explain what 4 will mean in the context of the command executed, though, so that's left for the user to find out.

Also, I'll post a cross reference in the Documentation forum, so this error can be fixed rather than KB. 

UPDATE. I see that OP already posted there.

---

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


My blog

Answer 5

Since the command shell is executing DTEXEC, it IS easy to find out.

According to http://msdn.microsoft.com/en-us/library/ms162810.aspx OP didn't put the correct path to the package (relative the database server).

 

Value	Description
0	The package executed successfully.
1	The package failed.
3	The package was canceled by the user.
4	The utility was unable to locate the requested package. The package could not be found.
5	The utility was unable to load the requested package. The package could not be loaded.
6	The utility encountered an internal error of syntactic or semantic errors in the command line.

---

N 56°04'39.26"
E 12°55'05.63"

Answer 6

Perhaps OP has space in the package path? It so, the package path shoud be enclosed with double quotes.

DECLARE @returncode int
EXEC @returncode = xp_cmdshell 'dtexec /f "C:\My packages\UpsertData.dtsx"'

---

N 56°04'39.26"
E 12°55'05.63"