User defined table type throws error when passed from MVC

Question

I'm using a user-defined table type parameter to a stored procedure in SQL Server 2016 for inserting multiple records from ASP.NET MVC.
On debugging from Visual Studio 2013(local dev system), execution gets completed successfully but wherein when the same web page is accessed from a dev server url, it throws below error.
The table type parameter '@Customers' must have a valid type name.
I have explicitly given the type name in c# code. This logic works from local dev system but not after publishing to a dev site even though the site points to same database.
Can anyone please help on this?
.NET Framework : 4.5
SQL Server Version : 2016
User-defined table type:
CREATE  TYPE [dbo].[CustomerType]  AS   TABLE (       [CustId] [ int ]  NULL ,       [CustName] [ varchar ](100)  NULL ,       [Country] [ varchar ](50)  NULL    )   Stored procedure: Create   PROCEDURE  spInsertCustomers   @Customers dbo.CustomerType READONLY   AS    BEGIN         SET  NOCOUNT  ON ;        INSERT   INTO  Customers        SELECT  *  FROM  @Customers   END    MVC Code:
public  ActionResult Index()          {               try               {                                  TestUDFTable();                  ViewBag.Message =  "SUCCESS" ;              }               catch  (Exception ex)              {                  ViewBag.Message = ex.Message;              }                           return  View();          }   public   void  TestUDFTable()           {                 string  connectionString =  string .Empty;               connectionString = ConfigurationManager.ConnectionStrings[ "appcon" ].ToString();                  DataTable dt =  new  DataTable();               dt.Columns.Add( "CustId" );               dt.Columns.Add( "CustName" );               dt.Columns.Add( "Country" );                   int  counter = 1;                while  (counter              {                   DataRow row = dt.NewRow();                   row[ "CustId" ] = counter;                   row[ "CustName" ] =  "Customer-"  + counter;                   row[ "Country" ] =  "USA" ;                      dt.Rows.Add(row);                   counter++;               }               SqlParameter Parameter =  new  SqlParameter( "@Customers" , dt);               Parameter.TypeName =  "dbo.CustomerType" ;               Parameter.SqlDbType = SqlDbType.Structured;                   using  (var connection =  new  SqlConnection(connectionString))               {                   var cmd =  new  SqlCommand();                   cmd.Connection = connection;                   cmd.Parameters.Add(Parameter);                   cmd.CommandType = CommandType.StoredProcedure;                   cmd.CommandText =  "spInsertCustomers" ;                   connection.Open();                   cmd.ExecuteNonQuery();                   connection.Close();               }                       }

Amit Kumar · Answer

Hi,    You have define Cusitd int in the pocedure but when you add it in table then by default it take string type so you will have to change datatype of it. use like. Example : dt.Columns.Add("Custid",typeof(Int32));   Regards, Amit

Sri Ram · Answer

still its not working..

Laxmidhar Sahoo · Answer

sir when add the columns in the table   dt.Columns.Add("CustId"); dt.Columns.Add("CustName"); dt.Columns.Add("Country");   these columns by default string type.But sir you take in sql type Custid is int.   So the change the column type explicitly

Laxmidhar Sahoo · Answer

sir please check the colums ot the table Customers
whether the custid should not set identity
means no auto increment

Dharmraj Thakur · Answer

In order to use a user-defined type (UDT) in Microsoft SQL Server, you must register it. Registering a UDT involves registering the assembly and creating the type in the database in which you wish to use it.   Did you register UDT in sql server.   this may help you...   https://technet.microsoft.com/en-us/library/ms131079(v=sql.110).aspx

Sri Ram · Answer

Hi Amit, thanks for ur reply.
Actually, i had already included that datatype change in my code and missed out the same while posting the question. It still didn't work... dt.Columns.Add( "CustId" ,  typeof ( int ));   dt.Columns.Add( "CustName" ,  typeof ( string ));   dt.Columns.Add( "Country" ,  typeof ( string ));   As per my understanding with the exception, SQL server procedure is not able to identify the parameter being passed as the intended user-defined table type, even though below code does that mapping.
Parameter.TypeName =  "dbo.CustomerType" ;     Parameter.SqlDbType = SqlDbType.Structured;
I have tried this same solution on sql server 2008 and it works pretty charm. I'm not sure why this is causing the exception in sql 2016?
Do u have any thoughts?

Sri Ram · Answer

Hi Dharmraj Thakur,
Thanks for ur kind reply..
Actually, i have implemented the same solution on SQL Server 2008 without this registering process, everything went fine. It was a straight-forward sql scripts execution for creating the UDT and using it with a SP.
Anyways, I will try this registering process and let you know..