Microsoft KB Archive/941233

= FIX: Error message when you use the SQL Server Native Client OLE DB provider to connect to an instance of SQL Server 2005: &quot;Conversions not allowed using bcp_moretext&quot; =

Article ID: 941233

Article Last Modified on 10/15/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Workgroup Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition

-



SYMPTOMS
Consider the following scenario:
 * You use the SQL Server Native Client OLE DB provider to connect to an instance of Microsoft SQL Server 2005.
 * You call the bcp_bind function to bind data from a program variable to a table column.
 * The data type of the table column is XML.

In this scenario, you receive the following error message:

FAIL: ISSUE #1: bcp_bind: SQLState=07006, NativeError=0, Msg=[Microsoft][SQL Native Client]Conversions not allowed using bcp_moretext

This problem occurs even if you set the eDataType parameter of the bcp_bind function to one of the following values:
 * 0 (zero)

Note When you set the eDataType parameter to zero, no conversion of data occurs when you call the bcp_sendrow function.
 * SQLXML
 * NVARCHAR



CAUSE
This problem occurs because of a bug in the SQL Server Native Client OLE DB provider.

When the data type of a table column is XML, the SQL Server Native Client OLE DB provider sets the eDataType parameter of the bcp_bind function to SQLNCHAR. The SQL Server Native Client OLE DB provider does this even if you set the eDataType parameter to zero. In this situation, if you set the pData parameter to NULL, the SQL Server Native Client OLE DB provider uses the bcp_moretext function to send data to SQL Server 2005 in chunks. Additionally, the SQL Server Native Client OLE DB provider uses the IsMoreTextCompatibleBinding function to check the compatibility between the data type that is defined in the eDataType parameter and the data type of the table column in the database. If the data type of the table column in the database is XML, the data type that is defined in the eDataType parameter must be SQLXML.



Cumulative update information
To resolve this problem, obtain cumulative update package 2 for SQL Server 2005 Service Pack 2 (build 3175). For more information about how to obtain cumulative update package 2 (build 3175), click the following article number to view the article in the Microsoft Knowledge Base:

936305 Cumulative update package 2 for SQL Server 2005 Service Pack 2 is available



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.



MORE INFORMATION
After you install cumulative update package 2 for SQL Server 2005 Service Pack 2, you can specify the following data types in the eDataType parameter if the data type of the table column in the database is XML:
 * SQLCHAR
 * SQLVARCHAR
 * SQLNCHAR
 * SQLNVARCHAR
 * SQLBINARY
 * SQLVARBIN

In SQL Server 2005, you must call the bcp_moretext function in the following syntax to inform SQL Server 2005 that all data chunks are copied: bcp_moretext(, 0, &quot;&quot;) In SQL Server 2005 or in later versions of SQL Server, when you call the bcp_bind function or the bcp_collen function, the function sets the total length of all data parts to be copied to the table column in SQL Server. If you try to send SQL Server more bytes than the number of bytes that is specified in the bcp_bind function or in the bcp_collen function, you encounter an error.

