SQL Server Q&A

Symptoms
This article describes the following about this hotfix release:The issues that are fixed by the hotfix packageThe prerequisites for installing the hotfix packageWhether you must restart the computer after you install the hotfix packageWhether the hotfix package is replaced by any other hotfix packageWhether you must make any registry changesThe files that are contained in the hotfix package
Resolution
When you trace exception events in Microsoft SQL Server 2000 on an IA-64 computer,the performance of the statement that raises an error differs from the performance of the statement that does not raise an error.
Note For more information about how to trace exception events in SQL Server 2000, see the “More information” section.
For example, you have a statement that inserts an already existing value into a primary key column of a table. When you run the insert statement, you receive an error message that resembles the following:

Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint ‘PK_Table1′. Cannot insert duplicate key in object ‘Table1′.
The statement has been terminated.Additionally, you can see the error in the SQL Profiler trace output. If you repeatedly run this insert statement for several hours, some executions of the statement take a much longer time to display this error message. Alternatively, you can use the SQL:StmtCompleted event class to record the duration for every execution of the statement. The duration of some executions of the statement are much longer than the duration of the rest.
This example shows a scenario in which you encounter this problem. This problem may be observed with other error numbers in addition to error 2627.
For a list of all publicly released SQL Server 2000 post-Service Pack 4 hotfixes, click the following article number to view the article in the Microsoft Knowledge Base:
894905?(http://support.microsoft.com/kb/894905/) Cumulative list of the hotfixes that are available for SQL Server 2000 Service Pack 4

Symptoms
Running CHECKDB against a database with at least 122,000 objects, causes a stack overflow exception. The number of objects is represented by the count of rows in SYSOBJECTS, NOT the number of tables in the database.
The stack overflow exception may cause Microsoft SQL Server to shutdown. If it does the client will receive a message that the connection has been broken. If the exception does not shutdown Microsoft SQL Server, the client will appear to be still running the DBCC.

Resolution
Instead of using CHECKDB, use a script to run DBCC CHECKALLOC as well as a loop that executes DBCC CHECKTABLE for all system and user tables. Following is a script that performs this task, that can be scheduled and run using ISQL:

DBCC CHECKALLOCGOdeclare @tabname sysnamedeclare @exec_string varchar(300)declare tabcr cursor forselect name from sysobjects where type = ‘S’ or type = ‘U’ order by nameopen tabcrfetch tabcr into @tabnameselect @exec_string = “dbcc checktable(‘” + @tabname + “‘)”select @exec_string = rtrim(@exec_string)exec(@exec_string)while (@@fetch_status = 0)begin fetch tabcr into @tabname select @exec_string = “dbcc checktable(‘” + @tabname + “‘)” exec(@exec_string)endclose tabcrdeallocate tabcr

Symptoms
A stack overflow exception error may occur when you attempt either of the following tasks:
You insert 250 KB or more of data into a text or image column while SQL Server Profiler is running either of the following profiler events:
TSQL:SQL:StmtStarting
-or-
TSQL:SQL:StmtCompleted

You run a stored procedure that inserts 250 KB or more of data into a text or image column.
Resolution
Reduce the amount of data that you insert into the text or image column.

Symptoms
When using SQLDMO BULKCOPY object in a Visual Basic application, BULKCOPY.ImportData fails if BULKCOPY.SuspendIndexing = True. The symptoms are different depending on whether or not there are indexes on the destination table.
With no index on the destination table, the application terminates with the following exception access violation:

Run time error ‘-2147221499 (80040005):
[SQL-DMO]Code Execution Exception:
EXCEPTION_ACCESS_VIOLATIONWith an existing index, the application terminates with the following message:

Run time error ‘-2147221296 (800400d0):
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name ‘table’where ‘table’ is the name of the destination table into which the data is imported.
Resolution
Set BULKCOPY.SuspendIndexing = False. Then in the Visual Basic code, explicitly drop and recreate the indexes on the table.

Symptoms
The SQLAgent process may terminate with with following error messages:

2001-01-14 19:21:11 – ! [LOG] Exception 5 caught at line 335 of file ..\src\job.c.SQLServerAgent initiating self-termination
2001-01-14 19:21:29 – + [098] SQLServerAgent terminated (forcefully)
Resolution
SQLAgent creates a memory structure to support the xp_sqlagent_enum_jobs action. With SQL Server 2000 the structure is 588 bytes plus 4 bytes for overhead. However, an incorrect CAST to a LPTSTR, instead of a LPBYTE, results in an incorrect 4 byte shift.
When the (number of created jobs on the system * 588) + 4 = NT Page boundary the incorrect 4 byte cast results in the exception.

Symptoms
The SQL Server process may unexpectedly terminate while attempting a ResetFromBatch after performing sp_OA based activities. ResetFromBatch is an internal routine used by SQL Server to make sure that a server process ID (spid) has successfully completed the processing of the current batch and is set up to receive the next client request.
If this error occurs the following stack trace may be seen in the SQL Server error log:

77F97AC6 Module(ntdll+00017AC6) (ZwGetContextThread+0000000B)009261F2 Module(sqlservr+005261F2) (ex_raise2(int,int,int,int,void *,char *)+000003F7)00926302 Module(sqlservr+00526302) (ex_raisecontrol(class SQLError,…)+00000087)00925A26 Module(sqlservr+00525A26) (ex_trans_cexcept(unsigned int,struct _EXCEPTION_POINTERS *)+000001FC)7800AFC3 Module(MSVCRT+0000AFC3) (__CxxLongjmpUnwind+00000115)7800A739 Module(MSVCRT+0000A739) (__unDNameEx+00004D0A)7800A708 Module(MSVCRT+0000A708) (__unDNameEx+00004CD9)7800A559 Module(MSVCRT+0000A559) (__unDNameEx+00004B2A)7800AE9E Module(MSVCRT+0000AE9E) (__CxxFrameHandler+00000026)77F92538 Module(ntdll+00012538) (RtlSetDaclSecurityDescriptor+00000174)77F8AF89 Module(ntdll+0000AF89) (RtlConsoleMultiByteToUnicodeN+0000045C)77F9FB9A Module(ntdll+0001FB9A) (KiUserExceptionDispatcher+0000000E)22DF38FF Module(odsole70+000038FF) (CCachedTypeInfo::`scalar deleting destructor’(unsigned int)+0000000F)22DF29DE Module(odsole70+000029DE) (CODSOLEThreadInfo::UnlinkCTypeInfo(class CCachedTypeInfo *)+00000046 Line 118+0000001C)22DF27DD Module(odsole70+000027DD) (CODSOLEThreadInfo::~CODSOLEThreadInfo(void)+0000007A Line 41+0000000F)22DF6BFF Module(odsole70+00006BFF) (CODSOLEThreadInfo::`scalar deleting destructor’(unsigned int)+0000000F)22DF49EB Module(odsole70+000049EB) (OAUninitialize(void *)+0000003D Line 214+0000001C)0086A624 Module(sqlservr+0046A624) (PSS::ResetFromBatch(class ExecutionContext *,struct srv_proc *)+00000020)00459B11 Module(sqlservr+00059B11) (language_exec(struct srv_proc *)+000006BF) 004175D8 Module(sqlservr+000175D8) (process_commands(struct srv_proc *)+000000E0)410735D0 Module(UMS+000035D0) (ProcessWorkRequests(class UmsWorkQueue *)+00000264)4107382C Module(UMS+0000382C) (ThreadStartRoutine(void *)+000000BC)7800BEA1 Module(MSVCRT+0000BEA1) (_beginthread+000000CE)77E92CA8 Module(KERNEL32+00012CA8) (CreateFileA+0000011B)
Resolution
The ResetFromBatch code line does not implement appropriate structured exception handling, which leads to an unhanded exception condition.