Saturday, February 05, 2011

Sql Server Exception: Database name is not allowed with a table-valued parameter

While working on persisting some business entities to a SQL Server database using “user-defined table-types” (UDTs), I was getting the following cryptic error.

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Table-valued parameter 1 ("@MyTable"), 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.”

In my case, I was using the Enterprise Library to call a stored procedure and was passing a DataTable to the stored procedure. The problem seems to be the fact that EntLib does not handle DataTable parameters that are being passed to a StoredProc that expects a UDT (especially when you use one of the Execute overloads that take an array of objects as its parameters.

Instead, I had to manually create the SqlParameters that needed to be sent to my stored procedure and also set the SqlDataType to Structured for the DataTable that was being passed to the StoreProc that expected a UDT. Once that was done, the above exception went away.

Here is some sample code: (where database is of type EnterpriseLibrary.Data.Database)

            DbCommand dbCommand = database.GetStoredProcCommand(procName);
                dbCommand.Parameters.Add(new SqlParameter(“UDTParam1”, dataTable) { SqlDbType = SqlDbType.Structured});
                DataSet result = database.ExecuteDataSet(dbCommand);
            catch (SqlException ex)

1 comment:

Beej said...

It's probably because Enterprise Library uses the very nifty routine "SqlCommandBuilder.DeriveParameters()"... and that routine appears to have a little snafu where it prefixes the SqlParameter.TypeName with the database name.

Find more database & developer oriented tips like this at Including a custom SqlClient data layer that corrects this bug :)