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

• Microsoft SQL Server, version 6.5 Service Pack 2

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

ARTICLE-ID: 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

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.

ARTICLE-ID:Q154966 BUG #: 15743 (2.65.0201)

SYMPTOMS

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.

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

   Communication link failure.

WORKAROUND

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

FIX: SQLDescribeCol Returns Error on Prepared Outer Join

ARTICLE-ID: Q157588 BUG #: 16200 (2.65.0201)

SYMPTOMS

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

WORKAROUND

Call SQLDescribeCol after running the prepared statement.

FIX: SQLBindParameter Fails Under Simplified Chinese Win95

ARTICLE-ID: Q157732 BUG #: 16266 (6.50)

SYMPTOMS

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"

   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);

ARTICLE-ID: Q158605 BUG #: 16166 (2.65.0201)

SYMPTOMS

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"

ARTICLE-ID: Q158665 BUG #: 15946 (WINDOWS SQLBUG 2.65.0212)

SYMPTOMS

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

ARTICLE-ID: Q158763 BUG #: 16076 (NT: 6.5)

SYMPTOMS

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.

WORKAROUND

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

ARTICLE-ID:Q159470 BUG#: 15749 (2.65.0201)

SYMPTOMS

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.

WORKAROUND

Use the Column nullability information derived from SQLColumns.

REPLICATION COMPONENTS

FIX: Fkey NOT FOR REPLICATION Doesn't Work Correctly

ARTICLE-ID: Q148819 BUG #: 13700 (Windows NT: 6.5)

SYMPTOMS

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

WORKAROUND

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:

   ARTICLE-ID: Q135871
   TITLE     : INF: Update Methods Used in SQL Server 6.0

FIX: Replication Tasks Affect Query Timeout for Other SQLExec

ARTICLE-ID: Q153228 BUG #: 15540 (6.0) 15573 (6.5) (sqlserver)

SYMPTOMS

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>

TSQL tasks may encounter the following error as a Failure:

   SQL Server connection timed out. (Error 10024)

WORKAROUND

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

ARTICLE-ID: Q160146 BUG #: 15807

SYMPTOMS

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

ARTICLE-ID: Q160180 BUG #: 15829 (Windows NT: 6.5)

SYMPTOMS

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.

WORKAROUND

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

ARTICLE-ID: Q159265 BUG #: 16108

SYMPTOMS

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.

WORKAROUND

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

ARTICLE-ID: Q159646 BUG #: 15942 (6.5)

SYMPTOMS

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, use the native mode BCP for either automatic synchronization or manual synchronization.