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_cmds hell @<command_strin g>

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]

Re: Calling Executables from SQL Server and getting return value.

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=%ERRORLEVE L%

SQL script:

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

--
Hope this helps.

Dan Guzman
SQL Server MVP

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

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800

http://www.sqlserverfaq.com

http://www.mssqlserver.com/faq

-----------------------

"Chandra Mohan" <bschandramohan @yahoo.com> wrote in message
news:bb0ef6.030 8250314.77157f1 4@posting.googl e.com...[color=blue]
> Hi All,
>
> I have a requirement of calling a Executable from SQL Server.
> I know we can use -
>
> EXEC @result = Master..xp_cmds hell @<command_strin g>
>
> 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[/color]