Calling Executables from SQL Server and getting return value.

[Chandra Mohan]

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

[Dan Guzman]

This 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....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Chandra Mohan" <bs************@yahoo.com> wrote in message
news:bb************************@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