Calling Executables from SQL Server and getting return value.

Hi All,

I have a requirement of calling a Executable from SQL Server.
I know we can use -

EXEC @.result = Master..xp_cmdshell @.<command_string
However I want to get the return value (int) from the executable.
xp_cmdshell always returns 0 or 1. How can the executable return code
say 99 be obtained in SQL Server?

Thanks & Regards,
Chandra MohanThis is a bit of a kludge, but one method is to execute your application
via a CMD file and ECHO the %ERRORLEVEL% so that it is returned in the
xp_cmdshell resultset for subsequent interrogation. For example

MyApp.cmd

@.MyApplication.EXE
@.ECHO APPLICATION RETURN CODE=%ERRORLEVEL%

SQL script:

SET NOCOUNT ON
DECLARE @.ReturnCode int
DECLARE @.ApplicationReturnCode int
CREATE TABLE #Messages(Message nvarchar(255))
INSERT INTO #Messages
EXEC @.ReturnCode = master..xp_cmdshell 'c:\MyApp\MyApp.cmd'
SELECT @.ApplicationReturnCode =
CAST(SUBSTRING(Message, 25, 10) AS int)
FROM #Messages
WHERE LEFT(Message, 24) = 'APPLICATION RETURN CODE='
SELECT
@.ReturnCode,
@.ApplicationReturnCode
DROP TABLE #Messages
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Chandra Mohan" <bschandramohan@.yahoo.com> wrote in message
news:bb0ef6.0308250314.77157f14@.posting.google.com ...
> Hi All,
> I have a requirement of calling a Executable from SQL Server.
> I know we can use -
> EXEC @.result = Master..xp_cmdshell @.<command_string>
> However I want to get the return value (int) from the executable.
> xp_cmdshell always returns 0 or 1. How can the executable return code
> say 99 be obtained in SQL Server?
> Thanks & Regards,
> Chandra Mohan