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\Crys
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]
declare @sql nvarchar(max)
declare @error int
SET @sql = 'xp_cmdshell ''sqlcmd -S ' + @DBServerName + ' -d ' + @DBName + ' -i ' + @FilePathName + ''''
EXEC sp_executesql @sql