PRB: ODBC Syntax Required for Retrieving Parameter Information (183059)

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=<username> and PWD=<strong password> 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=<username>;PWD=<strong password>;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

   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=<username>;PWD=<strong password>;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