This article was previously published under Q309802
Important This article contains information about how to modify the registry. Make sure to back up the registry before you modify it. Make sure that you know how to restore the registry if a problem occurs. For more information about how to back up, restore, and modify the registry, click the following article number to view the article in the Microsoft Knowledge Base:
256986 Description of the Microsoft Windows registry
When a SQLAgent job step type is Transact-SQL script, and when the statements in the Command field of the New Job Step - <SQL Server Instance Name> dialog box call a Transact-SQL RAISERROR statement with a severity level of 11 or higher without the WITH LOG option, the job will end with a failed result. However, although all the Transact-SQL statements run after the RAISERROR statement call runs, all the output is suppressed. Therefore, no output is generated for the statements after the RAISERROR statement runs. This can really cause confusion about what happened to the statements in a job. You must look at a SQL Profiler trace if you want to see what actually ran. For more information about a SQL Profiler trace, see the "SQL Profiler" topic in SQL Server Books Online.
Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall your operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.
Service pack information
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
290211 How to obtain the latest SQL Server 2000 service pack
The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.
To enable the new behavior, set this TruncateJobResultOnError value to 0. After you restart SQL Server Agent, job results will no longer be truncated by raiserror statements. To restore the legacy behavior, you can set the TruncateJobResultOnError value to 1. This is the default value if the value is not set.
Warning The value change to 0 may not be an effective approach when there is a script that has an infinite loop with error being thrown. Agent logs the error continuously and may use all of the disk space.
To work around the problem, use either one of the following methods, based on your requirements.
Determine what severity level you want to use
If you want to call a RAISERROR statement for informational purposes, you must use a severity level of 10 for the error message. A severity level of 10 represents an informational message that indicates that a problem occurred because the user entered incorrect information.
If you want to abort the batch when a RAISERROR statement is called, you must use a severity level of 20 or higher for the error message, and you must include the WITH LOG option with the RAISERROR statement. When you do so, all the Transact-SQL statements that were supposed to run after the call to the RAISERROR statement are aborted, and all the open transactions are automatically rolled back.
For more information about the severity levels, see the "Error Message Severity Levels" topic in SQL Server Books Online.
Use a script file in the job step
If you want the whole script to execute regardless of how frequently the RAISERROR statement is called, and the severity level is less than 20, you can use a script file in the job step. To do so, follow these steps:
Save the script in a file.
Create a SQL Server Agent job with a CmdExec job step, and then run the script file in the step.
Here is an example:
osql -E -i c:\script.sql
When you use a script file, you will receive the same result if the batch is run by using the osql utility that you receive if the batch is run by using SQL Query Analyzer.
Calling a RAISERROR statement with a severity level of 20 or higher with the WITH LOG option causes the connection to close, and all the statements that were supposed to run after the RAISERROR call are skipped.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.
If the severity level of the error message in the SQL Server Agent job step of the RAISERROR statement is 2 through 9, the job and the job step end with a failed result. However, the output is not suppressed for the statements that are run after the RAISERROR statement in the batch.
If the severity level of the error message in the RAISERROR statement is 10, 1, or 0, the job and the job step runs successfully. Therefore, the problem that is mentioned in the "Symptoms" section of this article does not occur.
Although the SQL Server Agent job fails if you run the same SQL script by using SQL Query Analyzer, the isql utility, or the osql utility, you will receive the result that you expect.
Steps to reproduce the behavior
Create a new SQL Server Agent job with a Transact-SQL Script (TSQL) job step.
Type or paste the following Transact-SQL statement in the Command box.
select convert(varchar(50), @@version) as ' SQL Server Version Number' raiserror('Test raiserror with severity 10', 10, 1) select id, convert(varchar(12), name) as 'Table Name' from sysobjects where id < 4
On the Advanced tab, under the Transact-SQL Script (TSQL) command options section, type C:\Raiserror.log in the Output file box.
Click to select the Append option.
Click Apply, and then click OK.
Click Apply, and then click OK.
In SQL Server Enterprise Manager, right-click the SQL Server Agent job that was created, and then click Start Job.
Start SQL Profiler Trace.
For the same SQL Server Agent job step, change the severity level to 11 by replacing the command in the job step as follows.
select convert(varchar(50), @@version) as ' SQL Server Version Number' raiserror('Test raiserror with severity 11', 11, 1) select id, convert(varchar(12), name) as 'Table Name' from sysobjects where id < 4
Run the job again.
Here is the result that is generated in the Raiserror.log file.
Job 'raiserror' : Step 1, 'test raiserror' : Began Executing 2002-07-23 15:48:59SQL Server Version Number --------------------------------------------------Microsoft SQL Server 2000 - 8.00.534 (Intel x86) (1 rows(s) affected)Test raiserror with severity 10 [SQLSTATE 01000]id Table Name ----------- ------------1 sysobjects2 sysindexes3 syscolumns(3 rows(s) affected)Job 'raiserror' : Step 1, 'test raiserror' : Began Executing 2002-07-23 15:49:17SQL Server Version Number --------------------------------------------------Microsoft SQL Server 2000 - 8.00.534 (Intel x86) (1 rows(s) affected)Msg 50000, Sev 11: Test raiserror with severity 11 [SQLSTATE 42000]
In this Raiserror.log file, you can see that with a severity level of 10, all the output from the job was printed in the output file, and the job had a successful result.
In this Raiserror.log file, you can see that with a severity level of 11, only the output from the first two statements was printed to the output file, and the job had a failed result. Also, the RAISERROR message is in an error message format instead of in an informational format of a severity level 10. The trace file shows that all the statements ran successfully.
If you change the severity level to a number from 2 through 9, the output is not suppressed and the job result will fail for the step and for the job. The message format will be the same as an error message format for severity level 11 or higher.