asked on

xp_cmdshell Return result output

Good Afternoon

Experts:

Earlier today chapmandew helped me out with getting my S/P to run a .sql script which is being executed from a VB.net application. After doing some testing here, I realized I forgot to account for something...success/error return. HI attached the S/P as a code snippet.

I execute this from my VB.net application using the following line of code:
Exec LBSI_RunScripts 'C:\Enhance\Resources\CrystalScripts\MaintainParam.sql','(local)','3DDemo'

If I send over an invalid path, I don't know how to let the VB.net application know that the script was not produced due to an error? Is there some way to pass back an error/result code? When I run the above line from Management Studio with the path spelled wrong, I get a message indicating bad filename in Management Studio. How can I communicate that back to the calling program?

Thanks for the help,
jimbo99999

ALTER Procedure [dbo].[LBSI_RunScripts]
      (
            @FilePathName nvarchar(100),
            @DBServerName nvarchar(100),
            @DBName nvarchar(50)
      )
AS
declare @sql nvarchar(max)
declare @error int
BEGIN
SET @sql = 'xp_cmdshell ''sqlcmd -S ' + @DBServerName + ' -d ' + @DBName + ' -i ' +  @FilePathName + ''''
EXEC sp_executesql @sql
end

Open in new window

Ashish Patel

when something gets executed by xp_cmdshell, then the output from it never gets stored or in other words we cant fetch the output for further use. Its just a message dispaly.

Jimbo99999

ASKER

asvforce:

I was not aware of that. Let's see then...how can I find out if the execution ended successfully or not.
Is there any kind of command or way to check if a S/P exists? Kind of like: If IsPresent(S/P name).

Thanks,
jimbo99999

ASKER CERTIFIED SOLUTION

Ashish Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jimbo99999

ASKER

asvforce:

Excellent...I am cooking with Crisco now.

Thanks for the help,
EJD