Recently while I was testing an integration solution on a client that uses WCF-SQL to insert data on a SQL Server database I got the following error:
System.NotSupportedException: The SqlDbType “” is not supported. Modify your table, view, stored procedure, or function definition to avoid having parameters or columns of this type.
This was a surprise since everything was working properly in the development environment.
Cause
In this solution, we were doing bulk insert on a SQL Server database by using a User-Defined Types as the input to the stored procedure, in our case a User-Defined Table Type. There are other ways to do a SQL Server bulk insert or update in BizTalk Server but definitely, this is the best approach. And this is one of the reasons why we are getting on the error message with the keyword: SqlDbType.
This problem occurs because the user account that you used to access the database, in my case the BizTalk Host Instance Account, don’t have permissions on the User-Defined Type associated to the Store procedure that we were invoking.
Solution
To solve this issue, you must give access to the user, in my case BizTalk Host Instance Account to properly execute the stored procedure, you must:
- Open SQL Server Management Studio and connect to your server.
- In the Object Explorer, select and expand the desired database and expand the Security folder and then the Users.
- Right-click on the User, BizTalk Host Instance Account, and choose Properties.
- On the Database User windows, choose the Owned Schemas tab, and then on the Schemas owned by this user panel select the schema bind to your User-Defined Table Type.
- In our case: Material
- Click OK and try again to send the data to your SQL Server. It should work.
