PRB: ODBC Syntax Required for Retrieving Parameter Information

• ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.1 SP2

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

Visual Basic Code

   Dim cmd As New ADODB.Command
   Dim rs As New ADODB.Recordset   
   Dim param1 As Parameter
   Dim strValue As String
   cmd.ActiveConnection = "DSN=SQLServer;UID=sa;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

   Dim cmd As New ADODB.Command
   Dim rs As New ADODB.Recordset   
   Dim param1 As Parameter
   Dim strValue As String

   cmd.ActiveConnection = "DSN=SQLServer;UID=sa;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 behavior is by design.

REFERENCES

For additional information about refreshing ADO parameters for stored procedures, please see the following article in the Microsoft Knowledge Base:

Q174223 HOWTO: Refresh ADO Parameters for a Stored Procedure

Additional query words:

Keywords : kbADO kbADO200 kbDatabase kbGenInfo kbSQLServ kbStoredProc kbVBp kbVC kbVJ kbGrpVBDB kbGrpMDAC kbDSupport kbIIS kbMDAC200 kbADO210sp2
Version : WINDOWS:1.5,2.0,2.1 SP2
Platform : WINDOWS
Issue type : kbprb