Microsoft KB Archive/183059

= PRB: ODBC Syntax Required for Retrieving Parameter Information =

Article ID: 183059

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1

-



This article was previously published under Q183059



SYMPTOMS
The following information only applies to the OLEDB Provider for ODBC:

When using the Refresh method on a Command object's Parameters Collection for retrieving provider-side parameter information of a stored procedure, parameter information may not be correct. For example, if a stored procedure is specified in the CommandText property along with a parameter, and the parameter type is a SQL Server Text type, you will not get the correct ActiveX Data Objects (ADO) type returned for that parameter.



CAUSE
Executing the following Visual Basic code on the stored procedure below produces the following error:

[Microsoft][ODBC SQL Server Driver]String data, right truncation

The error occurs because the type of the parameter retrieved by the Refresh method is adChar instead of adLongVarchar.

The wording of the error may vary slightly with newer versions of Microsoft Data Access Components.

Visual Basic Code
Note You must change Username= and PWD= to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the database. Dim cmd As New ADODB.Command Dim rs As New ADODB.Recordset Dim param1 As Parameter Dim strValue As String cmd.ActiveConnection = "DSN=SQLServer;Username= ;PWD= ;Database=pubs" cmd.CommandText = "sp_procedure1" cmd.CommandType = adCmdStoredProc

' Calling Refresh method to retrieve the parameters information. cmd.Parameters.Refresh

' Setting the value of the parameter to a string > 255 characters. strValue = String(260, "A") ' Set the parameter value to be passed. cmd.Parameters(1).Value = strValue Set RS = cmd.Execute

SQL Server Stored Procedure
CREATE TABLE Table1(field1 Text)

CREATE PROC sp_procedure1 @param1 TEXT as  INSERT INTO Table1 VALUES(@param1)



RESOLUTION
Executing the same procedure with the following code resolves the problem. Note here that the CommandText uses the ODBC syntax for calling stored procedures. The CommandType is set to adCommandText.

Code Example
Note You must change Username= and PWD= to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the database. Dim cmd As New ADODB.Command Dim rs As New ADODB.Recordset Dim param1 As Parameter Dim strValue As String

cmd.ActiveConnection = "DSN=SQLServer;Username= ;PWD= ;Database=Pubs" cmd.CommandText = "{call sp_procedure1 (?)}" cmd.CommandType = adCmdText

' Calling Refresh method to retrieve the parameters information. cmd.Parameters.Refresh

strValue = String(260, "A") ' Set the parameter value to be passed. cmd.Parameters(0).Value = strValue Set RS = cmd.Execute



STATUS
This problem applies to the versions of MDAC listed above in the applies to section. This problem does not occur in the 2.5 and later versions of MDAC.

