Passing TVP to SqlDataSource

[Graham]

Hi Guys,

I'm evaluating Reporting 2011 Q2
Does anybody know if there is a way to pass table-valued parameter to a stored procedure?

When I'm trying to do that I'm getting the following exception thrown:

System.Data.SqlClient.SqlException (0x80131904): The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Table-valued parameter 7 ("@Hours"), row 0, column 0: Data type 0xF3 (user-defined table type) has a non-zero length database name specified.  Database name is not allowed with a table-valued parameter, only schema name and type name are valid.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
.. skipped..
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at Telerik.Reporting.Processing.Data.SqlDataEnumerable.<GetEnumerator>d__0.MoveNext()

According to comment in MSDN that could be caused by DeriveParameters method agains SqlCommandBuilder.

Indeed, the call exists in the reporting engine source code (SqlCommandProvider, ln.152):



var arguments = new[] { command };
methodInfo.Invoke(builder, arguments);

Can you please confirm the bug or suggest a right approach for passing TVP?

Regards, Maxim Tebenev.
Mobile Systems International

[Ivan]

Hi Graham,

Thank you for your feedback.

According to our tests, the problem is caused by a bug in Microsoft's ADO.NET provider for SQL Server that is preventing us from discovering the correct type of the table-valued parameter. You can find a more detailed explanation regarding this in the support ticket you have sent to us. Since the problem is introduced by the Microsoft's ADO.NET provider and is not related to Telerik Reporting, our suggestion is to contact Microsoft about fixing that instead.

In case this is preventing you from implementing your report, our advice is to consider a different approach to pass a multi-value parameter to your stored procedure. For example, you can concatenate all the values in a single comma-separated string and pass that string to the procedure instead. Then you can extract the values from the string with code in the procedure - not as elegant as table-valued parameter, but proven to work always.

Best wishes,
Ivan
the Telerik team