October 11, 2011 at 3:19 am
1) How do you return an ERRORLEVEL code of say 0,1 or 2 to a bat file or the cmd o/s, depending the result of some select you do on a sql server 2005 database table using t-sql?
The resulting output from the t-sql statement should be viewable using
cmd>echo %ERRORLEVEL%
At the moment the code I am using is as below, but only returns an echo message. But I want it to to throw an ERRORLEVEL message to the operating system. (It is run using sqlcmd which is run from a .bat file)
--@test is set to 1 2 or 3 depending on an earlier t-sql query.
--0 is ok
--1 is fail
--2 nearly full
IF (@test = 0)
SELECT '0'
--ie instead of select '0' i want it to throw an cmd ERRORLEVEL to the o/s
ELSE
BEGIN
IF (@test = 1)
SELECT '1'
ELSE
SELECT '2'
END;
2) When this query is run I also want to stop any ERROR messages being echoes out by sql-server. Eg if the t-sql query failed because the database was down. How do you stop sql server error messages being echoed out.
Any help appreciated.
Thanks for your help in advance.
Mark
October 11, 2011 at 4:22 am
I don't think that a batch script is the right tool for this kind of things.
I would rather look into PowerShell or, at least, VBScript.
Batch scripting has a very limited support for error handling.
-- Gianluca Sartori
October 11, 2011 at 10:21 am
I have already got a batch script which when an ERRORLEVEL number is returned from a t-sql script (run using sqlcmd from the batch file, would handle the ERRORLEVEL as you can see below
I NEED TO KNOW HOW TO RETURN AN ERRORLEVEL FROM A T-SQL SCRIPT, to the calling .bat file or even cmd o/s????
I have tried RETURN <then the error no> in the t-sql however says not able to use RETURN here?
Any help would be much appreciated. Its getting a little urgent now.
The batch file is below is only for info and required to answer above.
The batch file should handle the errorlevel and echo appropriate info as required to screen.
All I need is the t-sql to pump out an ERRORLEVEL.
IF ERRORLEVEL 13 GOTO ERROR13
IF ERRORLEVEL 12 GOTO ERROR12
IF ERRORLEVEL 5 GOTO ERROR5
IF ERRORLEVEL 4 GOTO ERROR4
IF ERRORLEVEL 3 GOTO ERROR3
IF ERRORLEVEL 2 GOTO ERROR2
IF ERRORLEVEL 1 GOTO ERROR1
:: If we get this far, then there was no error code
GOTO NOERROR
:ERROR13
ECHO ERROR: Error opening temporary file in temp directory!
GOTO :EXIT
:ERROR12
ECHO ERROR: -server or -f options not specified or not found in registry
GOTO :EXIT
:ERROR5
ECHO ERROR: Error reading file/message text
GOTO :EXIT
:ERROR4
ECHO ERROR: Problem with the file/message text
GOTO :EXIT
:ERROR3
ECHO ERROR: Error reading file/message text or attached file
GOTO :EXIT
:ERROR2
ECHO ERROR: Error Level 2 returned
GOTO :EXIT
:ERROR1
REM !!!!ECHO ERROR: Error Level 1 returned
rem echo %ERRORLEVEL%
echo whatever error message I want to echo to screen
GOTO :EXIT
:NOERROR
:: Any additional batch processing goes here
rem !!!echo %ERRORLEVEL%
:EXIT
:: End of batch file here
October 12, 2011 at 2:06 am
As I said in my previous post, I don't see a batch file as a the right tool for this job.
However, you could redirect the output of SQLCMD to an output file and read the file to a variable in the batch script.
I did something similar here: http://spaghettidba.com/2011/07/06/backup-all-user-databases-with-tdpsq/
Hope this helps
Gianluca
-- Gianluca Sartori
October 12, 2011 at 6:43 am
Gianluca,
Thanks for your input. I'm sure I would be able to apply your coding somewhere in future.
I also agree with your comment but the monitoring tool we are using requires me to use a .bat batch file.
It then requires the .bat file to simply return an ERRORLEVEL code/number when called.
Either 1 2 or 0 (which will represent fatal, warning or ok). That's why I want to know HOW TO RETURN AN ERRORLEVEL CODE from SQL SERVER to the calling batch program. I thought it was simply by using RETRUN and then the error code.
We should be able to check the returned ERRORLEVEL in command line by typing echo %ERRORLEVEL
At present the .bat file is able to handle ERRORLEVEL code from sqlcmd.
But I need to be able to return the ERRORLEVEL codes I want ie 1 2 or 0 depending conditions, from sqlserver.
I would think it is something like this...
--0 is ok
--1 is fail
IF (@test = 0)
--SELECT '0'
RETURN 0
ELSE
BEGIN
IF (@test = 1)
--SELECT '1'
RETURN 1
ELSE
--SELECT '2'
RETURN 2
END;
How do we return a command line ERRORLEVEL code from SQL Server??? I thuoght it was by RETURN <no> as above. But it complains I cannot use RETURN here.
This is getting a little urgent, so any suggestions would be much appreciated.
October 12, 2011 at 7:22 am
HOW TO RETURN AN ERRORLEVEL CODE from SQL SERVER to the calling batch program. I thought it was simply by using RETRUN and then the error code.
Short answer: it can't be done.
How do we return a command line ERRORLEVEL code from SQL Server??? I thuoght it was by RETURN <no> as above. But it complains I cannot use RETURN here.
You cannot use RETURN, because it is something you can use in procedures and functions, not in T-SQL batches.
Long answer:
You can use the technique I used in my blog post to store the query results in a text file. With "query results" I mean that you should SELECT the value you want to assign to %ERRORLEVEL%.
@ECHO OFF
SQLCMD -E -Q "SET NOCOUNT ON; SELECT 8;" -h -1 -o output.txt
FOR /F %%A IN (output.txt) DO CALL :perform %%A
:perform
EXIT /B %1
The code highlighted in bold (SELECT 8) returns a fixed ERRORLEVEL 8. Change it to incorporate your logic.
Hope this answers your question.
-- Gianluca Sartori
October 12, 2011 at 10:26 am
Encapsulate the t-SQL code in a stored procedure. Then Execute that procedure in your cmd file.
Create procedure aTestProcedure as
Set nocount on;
<insert your code here>
Then the command you run in your cmd file is "exec aTestProcedure".
disclaimer: haven't tried this myself yet, My iphone is great with other things, but not with T-Sql reviewing. Test in a dev environment and evaluate before trying in production.
Best regards
Jonas
October 13, 2011 at 11:39 am
Appologies for the delay in respond. Been on leave...
I can hopefully use both strategies in future.
I'm trying the return by encapsulating the sql in a proc
The proc is meant to basically check if a database is up or down. if it is down return a 1 (ie a failure)
The input from the sqlcmd in the bat file is the database name....(-d %arg1%)
The output would be the return code.
please help. It don't seem to be creating the procedure.
.................................................................................
The sql server procedure is below...
Create procedure SQLS_DBState_Procedure as
Set nocount on;
use [master]
go
DECLARE @d_user_access as float(9)
DECLARE @d_state as nvarchar(12)
DECLARE @myVar as varchar(255)
DECLARE @ERRLEVEL varchar(255)
--SET @myVar = 'SBTestDatabase'
SET @myVar = '$(myVar)'
SET @ERRLEVEL=0
SET @d_user_access=(select user_access from sys.databases a where a.name = @myvar)
SET @d_state=(select state_desc from sys.databases a where a.name = @myvar)
--0 is ok
--1 is fail
IF (@d_state = 'ONLINE' and @d_user_access = 0)
BEGIN
--Comment only...SELECT @myvar+' database is up FROM SQL SCRIPT' --works
--SET @dummyvar = 'dummy' --
SET @ERRLEVEL=0
RETURN @ERRLEVEL
END
ELSE
SET @ERRLEVEL=1
BEGIN
--comment only...SELECT '4'
--RETURN 1
RETURN @ERRLEVEL
END
;
.....................................................................................
The .bat file is...
@ECHO OFF
rem this is dbname being pased from cmd sqlcmd command
set arg1=%1
sqlcmd -h -1 -S sqltest01gdfuk\test -d %arg1% -i C:\SQLS_DBState.prc -v myVar="%arg1%"
IF ERRORLEVEL 3 GOTO ERROR3
IF ERRORLEVEL 2 GOTO ERROR2
IF ERRORLEVEL 1 GOTO ERROR1
:: If we get this far, then there was no error code
GOTO NOERROR
:ERROR3
ECHO ERROR: Error reading file/message text or attached file
GOTO :EXIT
rem echo %arg1% database is down
:ERROR2
ECHO ERROR: Error Level 2 returned
GOTO :EXIT
:ERROR1
REM !!!!ECHO ERROR: Error Level 1 returned
rem echo %ERRORLEVEL%
exit /b 1
GOTO :EXIT
:NOERROR
:: Any additional batch processing goes here
rem !!!echo %ERRORLEVEL% will be
echo %arg1% database is up
:EXIT
rem PAUSE
rem !!!! :EXITNOPAUSE
:: End of batch file here
...........................................................................................
I'm lost. please be kind and guide me.
Do i exec from the sqlcmd command in the bat file.
or is there two step process. ie run
exec C:\SQLS_DBState.prc
then use that in the sqlcmd?
At present the running of the bat file is erroring with same error
????
thanks for your patience. But I am a newbie.
Ta
October 13, 2011 at 11:41 am
FYI. As you can see I've shortend the .bat and the sql scripts for ease of use.
October 14, 2011 at 2:27 am
First of all, I would not use a stored procedure at all.
The procedure wuold have to be created in master, but it's not a good practice. Leave the system databasese alone, you'll thank me later.
If you insist using a procedure, your code has to be slightly revised:
USE [master] -- REALLY???? I wouldn't create it in master.
GO
CREATE PROCEDURE SQLS_DBState_Procedure AS
BEGIN
SET NOCOUNT ON;
DECLARE @d_user_access as float(9)
DECLARE @d_state as nvarchar(12)
DECLARE @myVar as varchar(255)
DECLARE @ERRLEVEL varchar(255)
--SET @myVar = 'SBTestDatabase'
SET @myVar = '$(myVar)'
SET @ERRLEVEL=0
SELECT
@d_user_access = user_access,
@d_state = state_desc
FROM sys.databases a
WHERE a.name = @myvar
--0 is ok
--1 is fail
IF (@d_state = 'ONLINE' and @d_user_access = 0)
BEGIN
--Comment only...SELECT @myvar+' database is up FROM SQL SCRIPT' --works
--SET @dummyvar = 'dummy' --
SET @ERRLEVEL=0
RETURN @ERRLEVEL
END
ELSE
BEGIN
SET @ERRLEVEL=1
--comment only...SELECT '4'
--RETURN 1
RETURN @ERRLEVEL
END
;
END
GO is the default batch separator in SSMS and SQLCMD. If you put it inside the procedure body, you end the batch and the procedure gets truncated. I suggest to always wrap the procedure body inside BEGIN/END blocks, so that if a GO command slips inside the procedure body, you get a syntax error.
When you use a RETURN command inside the procedure, you set the integer return value for the procedure and you have to retrieve it from the calling statement.
Something like this:
DECLARE @return_value int;
EXEC @return_value = SQLS_DBState_Procedure;
The code I provided for the batch file relies on output values for a SELECT statement, so you would have to SELECT out the procedure return value:
SELECT @return_value
I think everything would work better if you:
1) Avoid using a stored procedure and use a T-SQL batch instead
2) Put the batch text in a separate .SQL file
3) Invoke the batch from SQLCMD
SUGGESTED SOLUTION:
Create the file check_db_state.sql
SET NOCOUNT ON;
WITH dbs_check (N) AS (
SELECT
CASE
WHEN user_access = 0 AND state_desc = 'ONLINE' THEN 0
ELSE 1
END
FROM sys.databases dbs
WHERE dbs.name = '$(database_name)'
)
SELECT ISNULL(dbc.N, fixed.N) AS N
FROM ( SELECT 2 ) AS fixed (N)
LEFT JOIN dbs_check AS dbc
ON 1 = 1;
Create the file check_db_state.cmd
@ECHO OFF
rem this is dbname being pased from cmd sqlcmd command
set arg1=%1
sqlcmd -h -1 -E -d master -i check_db_state.sql -v database_name=%arg1% -o check_db_state.out
FOR /F %%A IN (check_db_state.out) DO CALL :perform %%A
:perform
EXIT /B %1
Open cmd.exe, change directory to where you saved the files and type:
check_db_state.cmd tempdb
echo %ERRORLEVEL%
You should see 0 as output.
check_db_state.cmd dummy
echo %ERRORLEVEL%
You should see 2 as output.
Hope this helps
Gianluca
-- Gianluca Sartori
October 14, 2011 at 2:46 am
Apparently the sql procedure was only complaining because it didn't like
"use master
go"
in the procedure.
I've created the procedure via new query in sql server mgnt studio. So now I have a SQLS_DBState_Procedure in master database.
I need to be able to pass in the database name and depending on db state be able to collect the RETURN value which can be checked in cmd by
echo $ERRORLEVEL.
I'm updating this, so you don't waste time explaining how to create a procedure.
I just need to be able to run this now using sqlcmd I assume?
thanks so far for your help gianluca and jolas.
you may please keep assisting me with this if you see if I'm going wrong. I'm sure being a newbie I'll run into a problem in the running stage.
thanks
October 14, 2011 at 3:24 am
jjunkjjunk (10/14/2011)
Apparently the sql procedure was only complaining because it didn't like"use master
go"
in the procedure.
That was the reason, as I explained in my previous reply.
I've created the procedure via new query in sql server mgnt studio. So now I have a SQLS_DBState_Procedure in master database.
I don't think this is a good idea. I advise against creating objects in the master database.
I need to be able to pass in the database name and depending on db state be able to collect the RETURN value which can be checked in cmd by
echo $ERRORLEVEL.
I'm updating this, so you don't waste time explaining how to create a procedure.
I just need to be able to run this now using sqlcmd I assume?
You can use the code I posted erlier.
thanks so far for your help gianluca and jolas.
you may please keep assisting me with this if you see if I'm going wrong. I'm sure being a newbie I'll run into a problem in the running stage.
thanks
You're welcome. If you have further issues, ask and I'll try to help.
-- Gianluca Sartori
October 14, 2011 at 6:00 am
Hi I managed to create the procedure in master database
in dbo." " schema
..............................
USE [master]
GO
CREATE PROCEDURE dbo.SQLDBState_Proc AS
BEGIN
SET NOCOUNT ON;
DECLARE @d_user_access as float(9)
DECLARE @d_state as nvarchar(12)
DECLARE @myVar as varchar(255)
DECLARE @ERRLEVEL INTEGER
--@ERRLEVEL INT OUTPUT
-- DECLARE @ERRLEVEL INTEGER OUTPUT
--SET @myVar = 'SBTestDatabase'
SET @myVar = '$(myVar)'
SET @ERRLEVEL=0
SELECT
@d_user_access = user_access,
@d_state = state_desc
FROM sys.databases a
WHERE a.name = @myvar
--0 is ok
--1 is fail
--if (select name from sys.databases
--where name = @myvar) = ''--NULL
-- set @ERRLEVEL = 2
IF (@d_state = 'ONLINE' and @d_user_access = 0)
BEGIN
--Comment only...SELECT @myvar+' database is up FROM SQL SCRIPT' --works
--SET @dummyvar = 'dummy' --
SET @ERRLEVEL=0
RETURN @ERRLEVEL
END
ELSE
BEGIN
SET @ERRLEVEL=1
--comment only...SELECT '4'
--RETURN 1
RETURN @ERRLEVEL
END
;
END
...........................................
How do I run the procedure from the bat file
I've just changed the sqlcmd to run the SQLDBState.prc that is now in the dbo schema of the master database...
sqlcmd -h -1 -S sqltest01gdfuk\test -d master -E -Q "SET NOCOUNT ON; exec dbo.SQLDBState_Proc.prc" -v myVar="%arg1%"
From cmd line I run as
C:\>
C:\>
C:\>C:\SQLDBState\dbchecktest2 SBTestDatabase
However getting the error...
Msg 911, Level 16, State 1, Server SQLTEST01GDFUK\TEST, Line 1
Could not locate entry in sysdatabases for database 'dbo'. No entry found with t
hat name. Make sure that the name is entered correctly.
C:\>
I'm probably not understanding?
I'd need enter the db name when executing the bat file.
What do i need to add...I do need to run as bat file inputing db.
The file file should i guess run the proc and return errorlevel that at moment it should be able to handle.
Be gentle I'm getting lost again...
thanks so far. has been helpful so far....hopefully nearly there.
October 14, 2011 at 7:39 am
jjunkjjunk (10/14/2011)
Hi I managed to create the procedure in master databasein dbo." " schema
I advise against that. You don't need to and, IMHO, you don't have to. Use a T-SQL batch instead.
SET @myVar = '$(myVar)'
It doesn't work this way. Variables can be expanded inside the T-SQL batch, not inside the stored procedure. If you want to propagate the varable from the command line to the stored procedure, you will have to add a parameter to the stored procedure and pass it from the T-SQL batch in SQLCMD.
From cmd line I run as
C:\>C:\SQLDBState\dbchecktest2 SBTestDatabase
However getting the error...
Msg 911, Level 16, State 1, Server SQLTEST01GDFUK\TEST, Line 1
Could not locate entry in sysdatabases for database 'dbo'. No entry found with t
hat name. Make sure that the name is entered correctly.
Why don't you use the working example I already have provided in my previous posts?
You can't ask for my help and then insist doing things your way. You're free to code it the way you like best but, if you want my advice, take it or leave it.
-- Gianluca Sartori
October 14, 2011 at 7:42 am
sqlcmd -h -1 -S sqltest01gdfuk\test -d master -E -Q "SET NOCOUNT ON; exec dbo.SQLDBState_Proc.prc" -v myVar="%arg1%"
Drop ".prc", it's not part of the procedure name:
sqlcmd -h -1 -S sqltest01gdfuk\test -d master -E -Q "SET NOCOUNT ON; exec dbo.SQLDBState_Proc" -v myVar="%arg1%"
However, I stand my point: don't use a stored procedure.
-- Gianluca Sartori
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply