You are exactly right. I got the same results from the profiler. THis is nuts. It does work if you change the select to RTRIM(?cCustomerID). Here is what I posted on a MS NG:
OK, here is the inane workaround. The SELECTCMD for that test is:
SELECT * FROM Customers WHERE CustomerID LIKE RTRIM(?cCustomerID ) ;
ORDER BY CompanyName
For some reason the provider is passing the parameter RPADDED with the number of spaces to fit the CustomerID field which is a NCHAR(5) data type. The other fields are NVARCHAR data types. Is this the way it is supposed to work? I do not know why it would RPAD a value. I could understand if it took this value from a cursor whose field is C(5). Here is what I got from the profiler:
.NULL.
-- network protocol: TCP/IP
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7
SET FMTONLY ON select CompanyName from Customers SET FMTONLY OFF
SET NO_BROWSETABLE ON
declare @P1 int
set @P1=1
exec sp_prepare @P1 output, N'@P1 nvarchar(40)', N'SELECT * FROM Customers WHERE CompanyName LIKE @P1 ORDER BY CompanyName', 1
select @P1
exec sp_unprepare 1
exec sp_executesql N'SELECT * FROM Customers WHERE CompanyName LIKE @P1 ORDER BY CompanyName', N'@P1 nvarchar(40)', N'M%'
SET FMTONLY ON select CustomerID from Customers SET FMTONLY OFF
declare @P1 int
set @P1=2
exec sp_prepare @P1 output, N'@P1 nchar(5)', N'SELECT * FROM Customers WHERE CustomerID LIKE @P1 ORDER BY CompanyName', 1
select @P1
exec sp_execute 2, N'M% ' ****** note RPADded spaces. This is nuts!
exec sp_unprepare 2
.NULL.
.NULL.
Mark McCasland
Midlothian, TX USA