FIX: A Text Parameter May Return an Error If the SQL Server and the Client Code Pages Are Different (317956)

SYMPTOMS

SQL Server 2000 may return an error when a client tries to send long text data as an RPC parameter if all of the following conditions are true:

The client is using the Microsoft OLE DB Provider for SQL Server that is included with Microsoft Data Access Components (MDAC) 2.6 or later.
The client is sending data to SQL Server as an RPC parameter that is bound with an SQL data type of text (non-Unicode string data that is longer than 8000 bytes).
The ANSI code page of the client computer is not the same as the default code page of the SQL Server 2000 instance.

Here is the error message that SQL Server may return:

Code page translations are not supported for the text data type. From: 1252 To: 850.

This is SQL Server error number 450. In this error message, code page 1252 is the ANSI code page of the client computers, and code page 850 is the default code page of the SQL Server 2000 instance. These code page numbers may vary in the error message that you receive.

CAUSE

This problem occurs because SQL Server 2000 does not support server-side code page conversion for text data types.

Unlike SQL Server 7.0, SQL Server 2000 supports multiple collations in a single instance, which means that the client (SQLOLEDB) does not know which code page the text or varchar parameters must be converted to. When you have connected to a SQL Server 2000 instance, the SQL Server OLE DB provider defers code page conversion of RPC parameters to the server. SQL Server 2000 correctly handles conversion of char or varchar data types, but it does not support server-side code page conversion for text data types.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How To Obtain the Latest SQL Server 2000 Service Pack

WORKAROUND

To work around this problem, use any one of the following three methods:

Use the Microsoft SQL Server ODBC driver instead of the Microsoft OLE DB Provider for SQL Server (SQLOLEDB). If you are using ActiveX Data Objects (ADO), you can use the Microsoft OLE DB Provider for ODBC (MSDASQL) to access the Microsoft SQL Server ODBC driver.
Bind the text parameter as Unicode ntext instead of non-Unicode text. For example, if you are using ADO, you specify the parameter's data type as adVarWChar or adLongVarWChar instead of as adVarChar or adLongVarChar.
Use an alternate means to insert the text data in SQL Server. For example, non-parameterized queries are not affected by this problem.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 3.