Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

34 Posts

Posted - 10/26/2012 :  11:30:57  Show Profile  Reply with Quote

I am using xp_cmdshell to create a trigger file in a perticular location in my stored procedure.
I am calling this procedure from windows batch file. I am using %errorlevel% in batch file to check whether the file created successfully or not(to check whether the stored proc executed succesfully or not)..

If that location doesn't exist its not catcuring that error into
%errorlevel% , even though the file not created it saying the stored proc exceuted successfully...

How to capture that error in batch script?

Flowing Fount of Yak Knowledge

6065 Posts

Posted - 10/26/2012 :  13:53:03  Show Profile  Reply with Quote
without trying understand what your overall objective is - how about check for the existence of the folder before you attempt create a file there? Or check the that the file exists after its attempted creation?

Be One with the Optimizer
Go to Top of Page

Starting Member

34 Posts

Posted - 10/26/2012 :  14:10:07  Show Profile  Reply with Quote
Yes, We can check for the existense of the folder in the stored procedure, If it exists then it should create the trigger file, if not it prints the error message..I am calling the procedure from windows batch script as follws

@@set osq200=osql /a 4096 /b /E /e /d %dbn% /m-1 /r 0 /S %dbs% /Q "exec SP_Create_TriggerFile %2,%1 "
@%osq200% >>%3rec.txt 2>%3rec_err.txt
@set dberr=%errorlevel%
@if %dberr% GTR 0 goto createDATriggerFileErr >>%3rec.txt

If the direcory doesn't exits it just prints the error message, but it wont go to createDATriggerFileErr in the windows script.

My scenario is if the directory doesn't exits, the stored procedure should throw some error so that %errorlevel%
should be 1, so that it goes to createDATriggerFileErr .
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000