WAITFOR and display-immediate - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

WAITFOR and display-immediate

Author

Topic

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 05:19:38

I want to run a manual loop like this in Query Analyser:


WHILE 1 = 1
BEGIN
	SELECT 'Backup', GetDate()
	exec MyBackupSproc
	SELECT 'Waitfor', GetDate()
	WAITFOR DELAY '00:05:00'
END

but I would like the SELECTs to display immediately - so that I can manually abort it in the middle of a WAITFOR, and not in the middle of the SProc

Suggestions pls?

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-01 : 05:28:38

you can't with selects but you can with raiserror:


WHILE 1 = 1
BEGIN
	RAISERROR (N'Backup', -- Message text.
           10, -- Severity,
           1) -- State); -- Second argument.
	WITH NOWAIT

	select top 1 * from orders 
	RAISERROR (N'Waitfor', -- Message text.
           10, -- Severity,
           1) -- State); -- Second argument.
	WITH NOWAIT
	WAITFOR DELAY '00:05:00'
END

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 05:29:15

"RAISERROR"

Would never have thought of that, thanks.

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-01 : 05:35:38

that's why we're here

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 06:10:44

It even "pushes" the results from any earlier SELECTs, which suits me Just Fine.


WHILE 1=1
BEGIN
	SELECT 'Backup', GetDate()
	RAISERROR (N'Backup Start', 10, 1) WITH NOWAIT
	exec MyBackupSproc
	RAISERROR (N'Backup End', 10, 1) WITH NOWAIT
	SELECT 'Waitfor', GetDate()
	WAITFOR DELAY '00:05:00'
END

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-01 : 06:18:57

cool isn't it

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources