patternsqlMinor
Display local variable in while loop
Viewed 0 times
localwhileloopdisplayvariable
Problem
I have a while loop that is running for days (bydesign) see related Is there a limit to how many backups can be appended to a single file?
I have a local variable that displays the loop count, when I run it in a query window I can display the count with
I am now running the while loop in a job, and I want to display the
Create the database and prep it
Try running in a query window to test
Stop after a couple of loops and check the SQL logs and the query message
The error is not in the SQL logs, but is displayed in the query message.
I don't think you can use
How can I display the local variable in a while loop, real time?
I have a local variable that displays the loop count, when I run it in a query window I can display the count with
print @counterI am now running the while loop in a job, and I want to display the
@countervalue. After some research I tried RAISERROR (Transact-SQL) which should display in the SQL error log, but it is not working. I read Using RAISERROR I think I have the code correct, but not sure it can be used in While loop, with a local variable.Create the database and prep it
USE [master]
GO
Create database T_test
USE [master]
GO
ALTER DATABASE [T_test] SET RECOVERY FULL WITH NO_WAIT
GO
BACKUP DATABASE T_test to disk= N'K:\Test\T_test.bak' with noinit --Create first full
GOTry running in a query window to test
DECLARE @counter int
SET @counter = 0
WHILE 1=1
BEGIN
BACKUP DATABASE [T_test] TO
DISK = N'K:\Test\T_Test.diff' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'T_Test-Diff Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION
SET @counter = @counter + 1;
RAISERROR(N'Count equals :%d', 16, 1, @counter );
print @counter
ENDStop after a couple of loops and check the SQL logs and the query message
The error is not in the SQL logs, but is displayed in the query message.
I don't think you can use
GO 5000000 with a local variable, at least in my attempts it resets every time it is re-declared. How can I display the local variable in a while loop, real time?
Solution
There are two extensions of RAISERROR that may help:
To write it to the error log:
To display it in the messages tab in "real time":
To write it to the error log:
DECLARE @msg NVARCHAR(1000) = N''
RAISERROR(@msg, 16, 1) WITH LOG;To display it in the messages tab in "real time":
DECLARE @msg NVARCHAR(1000) = N''
RAISERROR(@msg, 16, 1) WITH NOWAIT;Code Snippets
DECLARE @msg NVARCHAR(1000) = N''
RAISERROR(@msg, 16, 1) WITH LOG;DECLARE @msg NVARCHAR(1000) = N''
RAISERROR(@msg, 16, 1) WITH NOWAIT;Context
StackExchange Database Administrators Q#246474, answer score: 8
Revisions (0)
No revisions yet.