FIX: SQL Server 6.5 Service Pack 2 Fixlist (Part 1 of 3)

ID: Q160731

The information in this article applies to:

Microsoft SQL Server version 6.5 Service Pack 2 and later

SUMMARY

The following is a list (Part 1 of 3) of fixes and other various improvements that have been made in Microsoft SQL Server version 6.5 Service Pack 2, now available from your primary support provider. For more information, contact your primary support provider.

Please note that workarounds described in these articles have been provided for your information only. It is not necessary to implement these workarounds if you have the updated software.

LIST OF PROBLEMS CORRECTED IN SERVICE PACK 2 (Part 1 of 3)

ODBC COMPONENTS

Q154295: FIX: TDS Errors in a Multithreaded ODBC Application
Q154966: FIX: Sp_cursoropen Error With More Than One Parameter Marker
Q157588: FIX: SQLDescribeCol Returns Error on Prepared Outer Join
Q157732: FIX: SQLBindParameter Fails Under Simplified Chinese Win95
Q158605: FIX: Driver Not Capable Error: SQL_COPT_SS_CONNECTION_DEAD
Q158665: FIX: 16-Bit Driver Keyset Cursor Operations with Query Timeout
Q158763: FIX: Server Out of Memory when Inserting into Text/Image Fields
Q159470: FIX: SQLDescribeCol/SQLColAttributes Report Nullability Wrong

REPLICATION COMPONENTS

Q148819: FIX: Fkey NOT FOR REPLICATION Doesn't Work Correctly
Q153228: FIX: Replication Tasks Affect Query Timeout for Other SQLExec
Q160146: FIX: Replication of Japanese DBCS Character Fails
Q160180: FIX: Replicated UPDATE to Char Column May Cause Error 803
Q159265: FIX: SQL Executive Service Becomes Unstable While Replicating
Q159646: FIX: Character Mode BCP Causes a Memory Leak in SQL Executive

Below are excerpts from each of the articles listed above. For the full text of the articles, search for the article number in the Microsoft Knowledge Base.

ODBC COMPONENTS

FIX: TDS Errors in a Multithreaded ODBC Application

Q154295 BUG #: 15705 (SQLBUG_65)

SYMPTOMS

When two or more threads share the same database connection (but different hstmts), a call to SQLExecDirect or SQLExecute fails, and one of the following errors occurs:

[Microsoft][ODBC SQL Server Driver][SQL Server] Received an unrecognized datatype 0 from TDS data stream

[Microsoft][ODBC SQL Server Driver]Unknown token received from SQL Server

[Microsoft][ODBC SQL Server Driver]Protocol error in TDS stream

The error(s) received depends on which network library (TCP/IP sockets or named pipes) is being used.

When you use server side cursors, SQL Server Driver invokes the extended stored procedure Sp_cursoropen with incorrect parameters when the following conditions are met:

The number of parameter markers in the WHERE clause of a SELECT statement are more than one.

The parameter markers correspond to CHAR or VARCHAR datatypes.

The network packet size is set to 512 bytes in the server configuration/options.

In addition, SQL Server Driver generates the following error message:

szSqlState = "37000", *pfNativeError = 16903,
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
sp_cursoropen procedure called with incorrect number of parameters".

The following error message appears if any further activity occurs on the client side:

Communication link failure.

If SQLDescribeCol is called after preparing an outer join statement, it returns the following error:

szSqlState = "37000", *pfNativeError = 107, *pcbErrorMsg = 135
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]
The column prefix 'c1' does not match with a table name or
alias name used in the query."

The SQLBindParameter fails after binding a parameter with SQL_C_DEFAULT and SQL_TINYINT under Simplified Chinese Win95. The following error is generated:

szErrorMsg="[Microsoft][ODBC SQL Server Driver]Restricted data type attribute violation"

The following statements demonstrate the problem scenario:


   SQLExecDirect(hstmt, "Create table mytable(id int)", SQL_NTS);
   SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_DEFAULT, SQL_TINYINT,
   1, 0, &sId, 0, &cbId );
   SQLExecDirect(hstmt, "select * from mytable where id=?", SQL_NTS);

FIX: Driver Not Capable Error: SQL_COPT_SS_CONNECTION_DEAD

Q158605 BUG #: 16166 (2.65.0201)

Users who attempt to call SQLGetConnectOption for the Microsoft SQL Server ODBC Driver specific connection option SQL_COPT_SS_CONNECTION_DEAD receive the following error:

szSqlState = "S1C00", *pfNativeError = 0 szErrorMsg="[Microsoft][ODBC SQL Server Driver] Driver not capable"

FIX: 16-Bit Driver Keyset Cursor Operations with Query Timeout

Q158665 BUG #: 15946 (WINDOWS SQLBUG 2.65.0212)

When you use the client-side SPX Net-Library for Windows (Dbmsspx3.dll), the Microsoft SQL Server 16-bit ODBC Driver version 2.65 may perform significantly slower in operations involving both keyset-driven cursors and the setting of a query timeout. Microsoft has observed a difference up to a factor of six when Dbmsspx3.dll is used, versus the other available net- libraries. Note that for this problem to occur, both a query timeout must be set and keyset-driven cursors must be in use with Dbmsspx3.dll.

FIX: Server Out of Memory when Inserting into Text/Image Fields

Q158763 BUG #: 16076 (NT: 6.5)

Using the 32-bit SQL Server Open Database Connectivity (ODBC) driver to insert chunks of data into text/image columns with the SQLPutData API may cause error 701, "There is insufficient memory to execute the query," from SQL Server 6.5.

When an identity column is added to an existing table using the ALTER TABLE statement, SQLColAttributes and SQLDescribeCol report that the identity column allows NULLS.

If you set up a FOREIGN KEY constraint to reference a table that is populated by replication (that is, a subscribed table), a constraint violation may occur if an UPDATE is run against the published table. This will cause the distribution task to fail with the following error:

Msg 547, %s statement conflicted with %s constraint '%.*s'. The conflict occurred in database '%.*s', table '%.*s'%s%.*s%s

The NOT FOR REPLICATION option should allow changes submitted by the Distribution task on columns referenced by a FOREIGN KEY constraint from another table that otherwise would violate the constraint.

Any SQLExec task that uses DB-Library can have its query timeout affected by a replication task. Replication tasks use a DB-Library function to globally set the query timeout for the SQLExecutive process, thereby affecting all "DB-Library based" tasks including TSQL, LogReader, SYNC, and Distribution tasks.

Because of this problem, replication tasks may encounter the following message in the Task History:

SQL Server connection timed out. Unable to successfully execute <command> on <server>

SQLExecutive will automatically retry the replication task, but the task may never run if the query timeout is not long enough.

TSQL tasks may encounter the following error as a Failure:

SQL Server connection timed out. (Error 10024)

When you use SQL Server replication with the code page 932 installed, some replication commands may not be successfully run, resulting in some Japanese characters being mistakenly replicated in the subscribing table.

FIX: Replicated UPDATE to Char Column May Cause Error 803

Q160180 BUG #: 15829 (Windows NT: 6.5)

LogReader fails with error 803. The errorlog and the task history shows the following message:

Error : 803, Severity: 20, State: 1
Unable to place buffer %*.d holding logical page %*.d in sdes for object 'syslogs' - either there is no room in sdes or buffer already in requested slot.

When you are replicating a table with a text column, and character mode Bulk Copy is used as the mode of synchronization, the SQL Executive service may become unstable. The distribution task is left in a sleeping state, and the SQL Executive service cannot be controlled. The server must be restarted to reactivate the task.

When this problem occurs, the SQL Executive service on the distributor becomes unstable. Also, Enterprise Manager stops responding if you try to click on the running tasks, because the server will not be able to run extended stored procedures related to the SQL Executive service.

Not all tables with a text column will experience this problem. However, tables with a text column are more likely candidates for failure, because the character mode of synchronization is used for such tables by default.

Character mode bulk copy program (BCP) replication synchronization causes a memory leak in SQL Executive.

The amount of memory leaked builds up each time a BCP character mode job is run by the distribution task. Once the system becomes sufficiently low on memory, the distribution task may fail or stop responding. If the task fails, it will return the error "Failed to allocate memory" to the task history. If the distribution task stops responding, it will be listed in the Sysprocesses table as a sleeping process with the command "select * from <table_name>."

Other possible symptoms include SQL Executive unhandled exception errors and other SQL Executive memory allocation failures.

WORKAROUND

To work around this problem, do one of the following:

Use a Critical Section object around SQLExecDirect or SQLExecute.

Use a different connection for each thread, rather than sharing the same connection.

Add a Sleep function for about 1,000 to 5,000 milliseconds before SQLExecDirect or SQLExecute.

FIX: Sp_cursoropen Error With More Than One Parameter Marker

Q154966 BUG #: 15743 (2.65.0201)

Set the Network Packet Size greater than 512 bytes in Server Configuration/Options using Sp_configure.

FIX: SQLDescribeCol Returns Error on Prepared Outer Join

Q157588 BUG #: 16200 (2.65.0201)

Call SQLDescribeCol after running the prepared statement.

FIX: SQLBindParameter Fails Under Simplified Chinese Win95

Q157732 BUG #: 16266 (6.50)

The amount of data inserted into the image/text field can be increased by increasing the amount of memory allocated to SQL Server and the amount of memory allocated to the procedure cache.

FIX: SQLDescribeCol/SQLColAttributes Report Nullability Wrong

Q159470 BUG#: 15749 (2.65.0201)

Use the Column nullability information derived from SQLColumns.

REPLICATION COMPONENTS

FIX: Fkey NOT FOR REPLICATION Doesn't Work Correctly

Q148819 BUG #: 13700 (Windows NT: 6.5)

Change your UPDATE statement to run as an "on-page delete/insert." For additional information, please see the following article in the Microsoft Knowledge Base:

Q135871 INF: Update Methods Used in SQL Server 6.0

If you cannot change your UPDATE statement to meet this criteria, you may not be able to create a FOREIGN KEY constraint in the subscribed database to reference a subscribed table.

FIX: Replication Tasks Affect Query Timeout for Other SQLExec

Q153228 BUG #: 15540 (6.0) 15573 (6.5) (sqlserver)

Override the default query timeout for any SYNC or Distribution task with the -q0 parameter value (This is only needed for SQL Server 6.5 because the default query timeout for these tasks is 30 seconds, but is 0 for SQL Server 6.0). This can force all tasks to use an infinite query timeout (which is the default for TSQL and LogReader tasks).

For TSQL tasks, you could instead use a CmdExec task using Isql.exe with the -t parameter to force a query timeout.

FIX: Replication of Japanese DBCS Character Fails

Q160146 BUG #: 15807

To work around this problem, drop the index on the character column that is being updated. If this is not possible, use Scheduled Table Refresh for tables that are required to be published using partitioning at least seven times.

FIX: SQL Executive Service Becomes Unstable While Replicating

Q159265 BUG #: 16108

While doing the synchronization, use the native mode of Bulk Copy. The native mode can only be used for SQL Server subscribers when the table being published has at least one text column.

FIX: Character Mode BCP Causes a Memory Leak in SQL Executive

Q159646 BUG #: 15942 (6.5)

To work around this problem, use the native mode BCP for either automatic synchronization or manual synchronization.

Additional query words: SP2 database patch

Keywords : SSrvGen
Version : winnt:6.5 Service Pack 2 and later
Platform : winnt
Issue type :