Microsoft KB Archive/309802

= 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 =

Article ID: 309802

Article Last Modified on 11/2/2007

-

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 Edition
 * Microsoft SQL Server 2000 Workgroup Edition
 * Microsoft SQL Server 2000 Desktop Engine (Windows)
 * Microsoft SQL Server 2000 Developer Edition
 * 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 -  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 &quot;SQL Profiler&quot; 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:

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.

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 &quot;Error Message Severity Levels&quot; 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.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; 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 &quot;Symptoms&quot; 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.</li>  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 </li> On the Advanced tab, under the Transact-SQL Script (TSQL) command options section, type C:\Raiserror.log in the Output file box.</li> Click to select the Append option.</li> Click Apply, and then click OK.</li> Click Apply, and then click OK.</li> In SQL Server Enterprise Manager, right-click the SQL Server Agent job that was created, and then click Start Job.</li> Start SQL Profiler Trace.</li>  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 </li> Run the job again.</li></ol>

Here is the result that is generated in the Raiserror.log file. <pre class="fixed_text">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.

Keywords: kbbug kbfix kbqfe kbsqlserv2000presp4fix kbpending kbhotfixserver KB309802

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.