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 - <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 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 "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
- 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: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