SQLNCLI creates invalid SQL against 2008(r2) when the parameters are refreshed by danholmes

Active

Type:	Bug
ID:	779618
Opened:	2/20/2013 7:51:38 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

the SQLNCL 9-11 produces incorrect syntax when it is refreshing the parameters from ADO.

The following events are shown in Profiler.
SQL:StmtStarting: SET NO_BROWSETABLE OFF
SQL:Stmtcompleted: SET NO_BROWSETABLE OFF
SQL:BatchCompleted: SET NO_BROWSETABLE OFF
SP:StmtStarting: select PROCEDURE_CATALOG ...
SP:StmtCompleted: select PROCEDURE_CATALOG ...
RPC:Completed: exec [?].[sys].sp_procedure_params_90_rowset N'spCustomerSelect',1,NULL,NULL
SQL:StmtStarting: SET NO_BROWSETABLE ON
SQL:Stmtcompleted: SET NO_BROWSETABLE ON
SQL:BatchCompleted: SET NO_BROWSETABLE ON
User Error Message: Procedure or function 'spCustomerSelect' expects parameter '@ContextStr', which was not supplied.
RPC:Completed: exec spCustomerSelect default,default,default

In addition to these sequences, the following thread in the forum details a profiler trace that is similar and also produces an error based on a metadata request from the driver.
http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/0f288a6d-da06-4730-8ed5-db24a036998e

Details (expand)

English

SQL Server 2008 SP3

SNAC

Windows 7 Professional

US English

Create this database and procedure.

CREATE DATABASE test;
GO
USE test;
GO
CREATE PROCEDURE dbo.spCustomerSelect @ContextStr VARCHAR(255), @CustomerID INT, @AgencyID SMALLINT
AS
SELECT 1

This is VBS that demonstrates the error.
myconnectionstring = "Provider=SQLNCLI;Initial Catalog=test;Data Source=?\sql2008;Trusted_Connection=yes"
adOpenStatic = 3
adoOpenKeySet = 1
adLockBatchOptimistic = 4
adLockOptimistic = 3
adUseClient = 3
adLockReadOnly = 2
SET objConnection = createobject("ADODB.Connection")
objConnection.Open(myconnectionstring)
set customerselectcmd = Createobject("ADODB.Command")
set ctxparam = customerselectcmd.CreateParameter("@ContextStr", 200, 1,
255,":V:^R8DataAccess#1.5^:Z:^&N&#RMBus#&S&#L#&UID&#13#&AGN&#1#&DFC&#101#^")
set agencyparam = customerselectcmd.CreateParameter("@AgencyID", 200, 1, 255, "1")
set customeridparam = customerselectcmd.CreateParameter("@CustomerID", 200, 1, 255, "1")
customerselectcmd.CommandType = 4
customerselectcmd.CommandText = "spCustomerSelect"
customerselectcmd.Parameters.append ctxparam
customerselectcmd.Parameters.append agencyparam
customerselectcmd.Parameters.append customeridparam
set customerselectcmd.ActiveConnection = objConnection
customerselectcmd.parameters.refresh

set rs = Createobject("ADODB.Recordset")
rs.CursorLocation = adUseClient
rs.open customerselectcmd, , adoOpenstatic , adLockReadOnly
rc = rs.recordcount
set rs.activeconnection = nothing

for each p in customerselectcmd.Parameters
s = p.value
next
rs.Close()
objConnection.Close()

User Error Message: Procedure or function 'spCustomerSelect' expects parameter '@ContextStr', which was not supplied.

no errors

X64

0 attachments

Posted by Microsoft on 4/15/2013 at 4:29 PM

Thanks for your feedback. Unfortunately we are not able to reproduce the issue.

Microsoft