FIX: A call to a RAISERROR statement in a Transact-SQL batch can cause a SQLAgent job to fail and to lose output in SQL Server (309802)


The information in this article applies to:

  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Enterprise Evaluation Edition
  • Microsoft SQL Server 2000 Workgroup Edition
  • Microsoft SQL Server 2000 Desktop Engine (MSDE)
  • Microsoft SQL Server 2000 Developer Edition 64 bit
  • Microsoft SQL Server 2000 Enterprise Edition 64-bit
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

SYMPTOMS

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.

RESOLUTION

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

Hotfix information

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.
   Date         Time   Version         Size       File name
   -------------------------------------------------------------------
   31-May-2003  05:15  2000.80.818.0      78,400  Console.exe
   27-Oct-2003  14:51  2000.80.873.0     315,968  Custtask.dll
   30-Jan-2004  02:59  2000.80.911.0      33,340  Dbmslpcn.dll
   24-Apr-2003  12:42                    786,432  Distmdl.ldf
   24-Apr-2003  12:42                  2,359,296  Distmdl.mdf
   29-Jan-2003  12:25                        180  Drop_repl_hotfix.sql
   11-Sep-2003  13:56  2000.80.859.0   1,905,216  Dtspkg.dll
   26-Aug-2003  06:46  2000.80.854.0     528,960  Dtspump.dll
   23-Jun-2003  09:10  2000.80.837.0   1,557,052  Dtsui.dll
   23-Jun-2003  09:10  2000.80.837.0     639,552  Dtswiz.dll
   23-Apr-2003  13:21                    747,927  Instdist.sql
   02-May-2003  12:26                      1,581  Inst_repl_hotfix.sql
   30-Jan-2004  02:59  2000.80.911.0      90,692  Msgprox.dll
   31-Mar-2003  12:37                      1,873  Odsole.sql
   30-Jan-2004  02:59  2000.80.911.0      62,024  Odsole70.dll
   30-Jan-2004  02:59  2000.80.911.0      25,144  Opends60.dll
   30-Jan-2004  02:59  2000.80.911.0      57,904  Osql.exe
   02-Apr-2003  09:45  2000.80.797.0     279,104  Pfutil80.dll
   04-Aug-2003  04:47                    550,780  Procsyst.sql
   11-Sep-2003  11:07                     12,305  Qfe469315.sql
   22-May-2003  09:27                     19,195  Qfe469571.sql
   29-Jan-2004  11:47                  1,090,380  Replmerg.sql
   30-Jan-2004  02:59  2000.80.911.0     221,768  Replprov.dll
   30-Jan-2004  02:59  2000.80.911.0     307,784  Replrec.dll
   29-Jan-2004  09:54  2000.80.911.0     159,813  Replres.rll
   05-Sep-2003  10:30                  1,087,150  Replsys.sql
   13-Aug-2003  02:58                    986,603  Repltran.sql
   30-Jan-2004  02:59  2000.80.911.0     287,304  Rinitcom.dll
   30-Jan-2004  02:59  2000.80.911.0      57,916  Semnt.dll
   29-Jul-2003  06:43  2000.80.819.0     492,096  Semobj.dll
   31-May-2003  04:57  2000.80.818.0     172,032  Semobj.rll
   02-Jan-2004  06:12  2000.80.904.0      53,832  Snapshot.exe
   09-Dec-2003  06:37                    117,834  Sp3_serv_uni.sql
   04-Feb-2004  11:16  2000.80.913.0      28,672  Sqlagent.dll
   04-Feb-2004  11:17  2000.80.913.0     311,872  Sqlagent.exe
   07-Jan-2004  09:08  2000.80.905.0     126,976  Sqlakw32.dll
   30-Jan-2004  02:59  2000.80.911.0   4,215,360  Sqldmo.dll
   07-Apr-2003  04:14                     25,172  Sqldumper.exe
   29-Jan-2004  09:47  2000.80.911.0      28,672  Sqlevn70.rll
   30-Jan-2004  02:59  2000.80.911.0     180,792  Sqlmap70.dll
   02-Sep-2003  13:26  2000.80.857.0     188,992  Sqlmmc.dll
   02-Sep-2003  09:33  2000.80.857.0     479,232  Sqlmmc.rll
   21-Oct-2003  10:38  2000.80.871.0     401,984  Sqlqry.dll
   30-Jan-2004  02:59  2000.80.911.0      57,920  Sqlrepss.dll
   04-Feb-2004  11:18  2000.80.913.0   7,610,449  Sqlservr.exe
   30-Jan-2004  02:59  2000.80.911.0     590,396  Sqlsort.dll
   30-Jan-2004  02:59  2000.80.911.0      45,644  Sqlvdi.dll
   30-Jan-2004  02:59  2000.80.911.0     106,588  Sqsrvres.dll
   30-Jan-2004  02:59  2000.80.911.0      33,340  Ssmslpcn.dll
   30-Jan-2004  02:59  2000.80.911.0      82,492  Ssnetlib.dll
   30-Jan-2004  02:59  2000.80.911.0      25,148  Ssnmpn70.dll
   27-Oct-2003  14:51  2000.80.873.0     123,456  Stardds.dll
   30-Jan-2004  02:59  2000.80.911.0     158,240  Svrnetcn.dll
   30-Jan-2004  02:59  2000.80.911.0      76,416  Svrnetcn.exe
   30-Apr-2003  10:22  2000.80.816.0      45,132  Ums.dll     
   30-Jan-2004  02:59  2000.80.911.0      98,872  Xpweb70.dll
Note Because of file dependencies, the most recent hotfix or feature that contains these files may also contain additional files.

After you apply this hotfix, SQL Server Agent behavior is defined by a new registry DWORD value that is called TruncateJobResultOnError in one of the following registry subkeys:
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\SQLServerAgent (default instance)
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\SQLServerAgent (named instance)
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.

WORKAROUND

To work around the problem, use either one of the following methods, based on your requirements.

Method 1

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.

Method 2

Use a script file in the job stepIf 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:
  1. Save the script in a file.
  2. 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.

STATUS

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.

MORE INFORMATION

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

  1. Create a new SQL Server Agent job with a Transact-SQL Script (TSQL) job step.
  2. 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
  3. On the Advanced tab, under the Transact-SQL Script (TSQL) command options section, type C:\Raiserror.log in the Output file box.
  4. Click to select the Append option.
  5. Click Apply, and then click OK.
  6. Click Apply, and then click OK.
  7. In SQL Server Enterprise Manager, right-click the SQL Server Agent job that was created, and then click Start Job.
  8. Start SQL Profiler Trace.
  9. 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
  10. 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:59

SQL 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           sysobjects
2           sysindexes
3           syscolumns

(3 rows(s) affected)

Job 'raiserror' : Step 1, 'test raiserror' : Began Executing 2002-07-23 15:49:17

SQL 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.
Modification Type:MinorLast Reviewed:9/26/2005
Keywords:kbHotfixServer kbQFE kbQFE kbSQLServ2000preSP4fix kbfix kbbug kbpending KB309802 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.