Given the following SQL Server Agent job USE [msdb] GO BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Do Nothing', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'No description available.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'Me', @job_id = @jobId OUTPUT IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Wait', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'WAITFOR DELAY ''00:00:10''', @database_name=N'master', @flags=0 IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO And the following calling code EXEC msdb.dbo.sp_start_job @job_name = 'Do nothing' -- waits for 10 seconds WAITFOR DELAY '00:00:05' EXEC msdb.dbo.sp_start_job @job_name = 'Do nothing' -- fails, job still running WAITFOR DELAY '00:00:10' EXEC msdb.dbo.sp_start_job @job_name = 'Do nothing' -- should succeed I get the following error: Job 'Do nothing' started successfully. Msg 22022, Level 16, State 1, Line 25 SQLServerAgent Error: Request to run job Do Nothing (from User Me) refused because the job is already running from a request by User Me. Job 'Do nothing' started successfully. So we can see the second sp_start_job call failed because the job was still running, then SQL Server continued execution the failure is a Level 16 and when I do the following PRINT 'hello' RAISERROR ('error',16,1) PRINT 'hello' I get the same "fail and continue" behaviour hello Msg 50000, Level 16, State 1, Line 50 error hello however, if I do the following, which also raises a Level 16 error, the second print is not run PRINT 'hello' ;THROW 51000, 'error', 1; PRINT 'hello' hello Msg 51000, Level 16, State 1, Line 50 error The docs for THROW state > Any error that occurs in a THROW statement causes the statement batch to be terminated. The docs for RAISERROR state > The error is returned to the caller if RAISERROR is run: >Outside the scope of any TRY block. >With a severity of 10 or lower in a TRY block. >With a severity of 20 or higher that terminates the database connection. My questions is, is sp_start_job using RAISERROR rather than throw - I have looked into the definition and can't see if anywhere

RAISERROR is as good as deprecated. [The documentation](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-ver16) says > New applications should use THROW instead of RAISERROR. Yet, it is commonly used to [flush the PRINT buffer](https://stackoverflow.com/questions/306945/how-do-i-flush-the-print-buffer-in-tsql) . Suppose that I'm writing a long stored procedure and I want regular reports of what step it is on. If RAISERROR is out of the question, how should I PRINT from it? Assume a relatively recent version of SQL Server. Let's say 2019.

I need to conditionally raise an error, but I can only use a simple statement and no stored procedure. I'd like to do something like this: select case when foo = "bar" then 1 else SIGNAL SQLSTATE 'ERROR' end; Unfortunately SIGNAL is only usable in triggers and procedures and I have to use this within an existing application that only allows me to enter statements, but not procedures. (I only have one long line and no way to set a DELIMITER etc.) Is there any other way to conditionally cause a runtime error ?

Please help me how to solve it. why is it throwing this error as my script is inserting data into various tables extracted from an environment. #376039 is the catch block where rollback and other raising error is happening

I need to raise one or more warning messages inside a child stored procedure and then collect all of them in the parent one. I tried with RAISERROR('Message 1',0,0) WITH NOWAIT but it doesn't work at all (ERROR_MESSAGE() is NULL in the code below). I wish something like code below, which is not working. It doesn't have to be RAISERROR, any solution is accepted. create procedure test_child as begin RAISERROR('Message 1',0,0) WITH NOWAIT; RAISERROR('Message 2',0,0) WITH NOWAIT; RAISERROR('Message 3',0,0) WITH NOWAIT; end ; create procedure test_parent as begin declare @err nvarchar(max); exec test_child; set @err = error_message(); select @err end ; exec test_parent;

I am adding a step in my SQL job where it does a WHILE EXISTS check for me before executing the last step. What it does is it will wait for 15minutes to see if the table I am checking has been populated with datetime data, in order to have the correct datetime data it should be greater than midnight. If it is greater than midnight I will go ahead and move to the last step. But I was stuck to add an error handling mechanism to close out the step with error after trying for a couple of time for 15minutes... Here is the query below:

WHILE EXISTS ( SELECT * FROM ETLTimeCheck WHERE EXEC_END_TIME is NULL OR EXEC_END_TIME > DATEADD(D,0,DATEDIFF(D,0,GETDATE())))
	BEGIN
			WAITFOR DELAY '00:15:00'
	END

So I started with this but got stuck figuring out the error handling/ how to raise an error,

WHILE EXISTS ( SELECT * FROM ETLTimeCheck WHERE EXEC_END_TIME is NULL OR EXEC_END_TIME > DATEADD(D,0,DATEDIFF(D,0,GETDATE())))
	BEGIN
		BEGIN TRY
			WAITFOR DELAY '00:15:00'
		END TRY
	END

BEGIN CATCH 
-- The Error Message
END CATCH

It seems to me that SQL THROW is missing a crucial feature that RAISERROR has, and that is the abilty to use WITH LOG so that an Alert can be fired when the error number is logged in the Application log. Is there a simple way to do this that is escaping me or do I still have to rely on the more cumbersome RAISERROR and sp_addmessage SP? I would like to be able to notify an Operator through an Alert using THROW in a CATCH block.

In my stored procedures, when a business rule is broken I raise an error that bubbles up to the C# client app and gets displayed to the user. e.g.

RAISERROR('Hey, you cannot do that because blah blah blah', 16, 1);

I want to distinguish errors that I raise from other SQL Server errors, because **I only want my errors to get displayed**. I think these are the only ways to send a message to the client app that this is a user message to display: Severity level, State, Return Code. But I think I should leave the severity level. - How to I tell my client app? - And what code or number should I use that is not already being used? - Or is there another way I have not considered? Or should I do this: THROW 50000, 'Hey, you cannot do that!', 1; **EDIT (13-April-2022)** I asked Erland Sommarskog whose response helped me realise that if you use this

RAISERROR('Hey, you cannot do that because blah blah blah', 16, 1);

...then the client app will always get 50000 as the error number. And with RAISERROR you can use parameterized messages. You just need to be aware that RAISERROR will not exit the batch. So, for me, RAISERROR wins.

I have an error level 16 alert set up as follows: USE [msdb] GO EXEC msdb.dbo.sp_add_alert @name=N'Error - Severity 16', @message_id=0, @severity=16, @enabled=1, @delay_between_responses=0, @include_event_description_in=1, @category_name=N'[Uncategorized]', @job_id=N'00000000-0000-0000-0000-000000000000' I was curious why SELECT 1/0; >Msg 8134, Level 16, State 1, Line 17 Divide by zero error encountered. was not raising an error but BACKUP DATABASE MyDatabase TO DISK = 'C:\FolderThatDoesntexist' >Msg 3201, Level 16, State 1, Line 8 Cannot open backup device 'C:\FolderThatDoesntexist'. Operating system error 5(Access is denied.). >Msg 3013, Level 16, State 1, Line 8 BACKUP DATABASE is terminating abnormally. was This post and The blog post it references suggest that SQL Server will only raise events for errors that are logged and this can be found in sys.messages So I queried sys.messages for these error codes: SELECT severity, message_id, is_event_logged FROM sys.messages WHERE language_id = 1033 AND message_id IN (8134,3201,3013) but found that all three are set to 0: Why therefore do 3201 and 3013 trigger my alert but 8134 does not?

I've been struggling with a task which requires me to raise a number of errors for testing purposes. Of course the time consuming errors are between 1 and 13000. I've managed to do them, but I need to do it several times on multiple test instances and I wanted to ask if there's a way to raise SQL Engine errors which normally only could be raised by the engine itself. By 'a way', I mean any possible routes to generate those error messages. The ultimate goal is that the messages appear in the current error log. Any help would be appreciated!

It is well known, that SSMS (or maybe the SQL Server, but I'd guess more the SSMS) delays PRINT messages until several lines (40) are gathered in the buffer, before they are really printed to the message window in SSMS (exception: something else is printed out, e.g. the number of rows affected by an UPDATE or the batch is done). So I usually use RAISEERROR(, 0, 0) WITH NOWAIT to print it out immeadiately. This works well for the first 500 rows, after this SSMS seems to start to buffer them again (50 lines, starting at 1000 lines the buffer seems to increase to 1000). Does anybody knows, how I can prevent this "buffer feature" (e.g. if I manually run the statistic updates by using Ola Hallengreens maintenance solution, I'd prefer to know, what it really does at the moment without having to use sp_whoisactive etc.). PS: You can simulate this behavior by using the following "script"

`
DECLARE @i INT = 0

WHILE @i < 10000
BEGIN
    SET @i += 1
    RAISERROR('Step %i', 0, 0, @i) WITH NOWAIT
    --PRINT @i
    WAITFOR DELAY '00:00:01.0' -- wait 1 second, feel free to decrease
END

`

I have a function that checks is the file is present on disk in an specific location . Using that function I am checking for the latest backup, in the code below. If we don't have a backup anymore, for a specific date, I want to raise an error. Currently I am using raiserror but because of Deprecated raiserror detection I would like to use throw instead . How can I do it? this is part of my stored procedure: IF @ServerNameToRestoreOn IS NULL THROW 50001, '@ServerNameToRestoreOn cannot be NULL - it must be the name of server\instance you want to restore the database to', 1 IF @Database IS NULL THROW 50001, '@Database cannot be NULL - it must be the name of the database you want to restore', 1 IF NOT EXISTS ( SELECT MAX(backup_set_id) FROM msdb.dbo.backupset a INNER JOIN msdb.dbo.backupmediafamily b ON a.media_set_id = b.media_set_id WHERE a.database_name = @Database AND a.type='D' AND a.is_copy_only = 0 AND a.backup_finish_date < @StopAt AND master.dbo.fn_FileExists(b.physical_device_name) = 1 GROUP BY database_name ) BEGIN SELECT @sql = 'The database called "%s" does not have a backup before' + CAST (@StopAt AS VARCHAR(20)) + @vCrlf + @vCrlf RAISERROR(@sql ,16,1,@Database) END How can I replace the raiserror by throw? I want to show the @Database and the @stopat in the error message or, in better wording: how do you pass THROW() a message where you can still use printf-style substitution like you can with RAISERROR?

I want to upgrade SQL server from 2008 to 2014 versiyon. I checked all of the deprecated features including raiserror syntax. But I have many stored procedures to check. Some of them use new syntax, some of them use old syntax and I need to alter the old syntax from them. How can I check all of the procedures to detect old raiserror syntax?

I can easily raise user defined errors where severity is not higher than 18. When is higher, I get this error > Error severity levels greater than 18 can only be specified by members of the > sysadmin role, using the WITH LOG option. I have access to sysadmin role, so can someone specify the syntax for raising errors WITH LOG option. Thanks.

What will happen if a trigger on a table hits the part of the trigger that includes the RAISERROR code below? RAISERROR('Attempt to modify supposedly immutable number.',16,1) How will we know this code was executed?

I'd like to quickly dump some context into an error message including some boolean flags using the handy-dandy substitution parameters of RAISERROR such as CREATE PROCEDURE MyProc ( @param1 INT = 1, @param2 VARCHAR(255) = 'hello world' ) AS BEGIN ... RAISERROR('Inconsistent state occurred processing @param1=%d @param2=%s', 16, 1, @param1, @param2) ... END According to the documentation there is no type specification for bit type (boolean), but notes that > These type specifications are based on the ones originally defined for the printf function in the C standard library. And as this post notes, > ... since any integral type shorter than int is promoted to int when passed down to printf()s variadic arguments, you can use %d But it does not work in SQL, as I get the following error (2748): > Cannot specify bit data type (parameter ###) as a substitution parameter. Am I forced to use a helper variable? Thanks. P.S. Using compatibility mode for 2008R2.

From what I see, having the RAISE_APPLICATION_ERROR cancels the other operations that I would have in the trigger. I tried writing first the INSERT and then the RAISE_APPLICATION_ERROR hoping that they get executed in the order in which they are read but it doesn't work like this...The error is thrown and the INSERT is canceled. How could I make both of the operations get executed (from the same trigger) ? I also tried creating two triggers .. one of them doing the RAISE_APPLICATION_ERROR part and the other doing the INSERT and establishing the order using FOLLOWS and PRECEDES but it didn't work. The trigger should block the DROP DDL (that's what for I used the RAISE_APPLICATION_ERROR) and it also should do an insert in a LOG type table (that's what for I used the INSERT).

How I should escape the % character in params so my RAISERROR retun my message declare @msg varchar(max) = (SELECT ' Message with % ' AS MSG) if @msg is not null begin RAISERROR (@msg ,16,1); end This will rise error with message > Msg 2787, Level 16, State 1, Line 4 > Invalid format specification: '% '. For end users this message is unreadable. My messages is generated from database and setting this message. Way I do avoid getting wrong message is replacing set @msg = REPLACE(@msg,'%','P') But I still do not know how to add % sign

I have a remote SQL Server that I can connect to using a local instance of SSMS. When raising errors, we use RAISERROR( 'This works', 16, -1 ) Remotely, this works fine if it's all uppercase, or even if any one letter is uppercase. raiserRor( 'Still works', 16, -1 ) If I try to run it using all lowercase though, it does not work raiserror( 'Error', 16, -1 ) It returns the error message >Msg 121, Level 20, State 0, Line 3 >A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.) Even if I comment out the lowercase raiserror statement it still fails with the same error message. If I log on to the server and execute these statements, they all work. It only seems to be when I connect remotely that this is an issue. Why can't RAISERROR be completely lowercase? Even if commented? --- The server is an install of SQL Server 2012 Developer edition on Windows Server 2012. >Microsoft SQL Server 2012 - 11.0.5058.0 (X64) > May 14 2014 18:34:29 > Copyright (c) Microsoft Corporation > Developer Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor) I've used local installs of SQL Server Management Studio 2012 and 2014 and tested against databases with 2005 and 2012 compatibility levels and the same behavior occurs. I tested on other machines and the same results occurred. I don't currently have a machine to test outside of our companies network though.

I want operators to be notified when I throw a custom error message. My understanding is that I need to add a message to sys.messages, then I can either RAISERROR or THROW that error ID. Creating an alert on the message ID and setting it to send to operators should create those notifications. I've created a SQL Agent alert on the message ID of 50005, and am using the following T-SQL to create the message: EXEC sp_addmessage @msgnum = 50005, @severity = 16, @msgtext = N'%s'; GO Then, if I execute this: RAISERROR (50005, -- Message id. 16, -- Severity, 1, -- State, N'My custom message'); THROW 50005, 'My custom message', 1; GO I get the following output as expected from both RAISERROR and THROW: > Msg 50005, Level 16, State 1, Line 68 My custom message When I view the history of the alert though, it shows that it has not been triggered, and the operator does not receive an email update. My agent alert looks like this: Scripting the alert generates the following: USE [msdb] GO EXEC msdb.dbo.sp_update_alert @name=N'Alert DBA on custom errors', @message_id=50005, @severity=0, @enabled=1, @delay_between_responses=0, @include_event_description_in=1, @database_name=N'', @notification_message=N'', @event_description_keyword=N'', @performance_condition=N'', @wmi_namespace=N'', @wmi_query=N'', @job_id=N'00000000-0000-0000-0000-000000000000' GO EXEC msdb.dbo.sp_update_notification @alert_name=N'Alert DBA on custom errors', @operator_name=N'My Operator', @notification_method = 1 GO For your convenience, if you try to recreate my issue this will help you drop from sys.messages: sp_dropmessage @msgnum = 50005; GO