Unable to access table variable in stored procedure

Question

I pass the datatable from code to the stored procedure this way.

DataTable table = CommonFunctions.ToDataTable(request); object[] spParams = new object[1]; spParams[0] = table; DbCommand dbCommand = db.GetStoredProcCommand("OS_UpdateOrgStructureDetails", spParams); 

I am trying to access this parameter in a stored procedure.

 CratePROCEDURE OS_UpdateOrgUnits @table OS_RenameNodeTable READONLY AS BEGIN UPDATE OrgUnit SET DetailName = ut.NewValue FROM @table ut INNER JOIN OrgUnit ou ON ou.OrgUnitID = ut.OrgUnitID END 

But when the call is made in the stored procedure, it throws an error.

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

Failed to fix the error.

Answer 1

Due to an error in the SqlCommandBuilder.DeriveParameters method, the TypeName property of the SqlParameter object for a parameter with a table value contains the database name (see http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.deriveparameters .aspx , comment "Table parameters not entered correctly").

To fix this, you can add this general purpose code immediately after creating the command:

 foreach (SqlParameter parameter in dbCommand.Parameters) { if (parameter.SqlDbType != SqlDbType.Structured) { continue; } string name = parameter.TypeName; int index = name.IndexOf("."); if (index == -1) { continue; } name = name.Substring(index + 1); if (name.Contains(".")) { parameter.TypeName = name; } } 

Answer 2

If you have only one or two table parameters, you do not need to iterate over all parameters. Instead, I wrote a function and passed this parameter to this function so that it corrects the file_name.

This is the function:

  Private Sub SetTypeNameForTableParameter(ByRef parameter As System.Data.SqlClient.SqlParameter) If parameter.SqlDbType = SqlDbType.Structured Then Dim name As String = parameter.TypeName Dim index As Integer = name.IndexOf(".") If index <> -1 Then name = name.Substring(index + 1) If name.Contains(".") Then parameter.TypeName = name End If End If End If End Sub 

This is the part of the code in which I call the database:

 'Get Parameters in stored proc Dim cmd As System.Data.Common.DbCommand = db.GetStoredProcCommand("MyStoredProc") db.DiscoverParameters(cmd) 'The first parameter is the return value. Remove it. Dim returnValueParam As Data.Common.DbParameter = cmd.Parameters(0) cmd.Parameters.Remove(returnValueParam) 'Set type name for every table parameter SetTypeNameForTableParameter(cmd.Parameters(1)) 'Assign values to the parameters cmd.Parameters(0).Value = id cmd.Parameters(1).Value = mydatatable 'Execute the command db.ExecuteNonQuery(cmd)