xp_cmdshell Return result output

Posted on 2008-02-01
Medium Priority
Last Modified: 2012-05-05
Good Afternoon


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,

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

Open in new window

Question by:Jimbo99999
  • 2
  • 2
LVL 23

Expert Comment

by:Ashish Patel
ID: 20799477
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.

Author Comment

ID: 20799841

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).

LVL 23

Accepted Solution

Ashish Patel earned 2000 total points
ID: 20799937
select * from sysobjects where object_name(id)= 'SPNAME'

Author Comment

ID: 20799997

Excellent...I am cooking with Crisco now.  

Thanks for the help,

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question