Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
acnsql
Starting Member
2 Posts |
 Posted - 2015-05-04 : 07:35:22
|
Hi,I am using stored procedure in batch which is asosql /UABC /P ABC /S SERVER1 /d database1 /q "exit(test_sp)".when I am running this procedure through batch, its getting executed successfully but still I am getting an errorlevel of -100 in batch output.Request your help on this.Thanks in advance |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2015-05-04 : 10:15:45
|
osql /UABC /P ABC /S SERVER1 /d database1 /q "exec test_sp".
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2015-05-04 : 14:39:00
|
We generate a temporary script file (we do that, dynamically, from the BATCH file but a static/fixed file will do) which contains:DECLARE @intErrNo int EXEC @intErrNo=dbo.MySProc @Param1=xxx, @Param2=yyy:EXIT(SELECT @intErrNo) The BATCH file contains:SQLCMD -d MyDatabase -i MyTempScript.SQL -o X:\MyPath\MyProcessName.OUT -b -l 300 -S . -E >>X:\MyPath\MyProcessName.ERRIF ERRORLEVEL 1 GOTO SQL1_Error I presume OSQL is similar |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
acnsql
Starting Member
2 Posts |
Posted - 2015-05-05 : 07:21:17
|
| I am using OSQL for calling the stored procedure |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-05-05 : 10:15:21
|
| Use whichever you like (OSQL is obsolete though), my point was to set an ERRORLEVEL (using EXIT) that the command line can make available within a BATCH file or similar. I didn't find a way to directly set the ERRORLEVEL with the return result of an SProc UNLESS I used an intermediate / temporary script, as described above. |
|
|
|
|
|
|
|
|
SQL stored procedure in Batch