1

I am passing in a SQL parameter which is of a user defined table type (idlist) to a stored procedure - all was working well till I added Column Encryption Setting=Enabled; to the web.config to make the app work with SQL's Always Encrypted feature.

var paramFilterFacilityList = new SqlParameter();
paramFilterFacilityList.ParameterName = "@f";
paramFilterFacilityList.TypeName = "dbo.idlist";
paramFilterFacilityList.Value = fValue;
paramFilterFacilityList.SqlDbType = SqlDbType.Structured;

Exception raised is :

The table type parameter "@f" must have a valid type name.

Any ideas on what could be going on here?

Nikhil Vithlani - Microsoft
  • 797
  • 5
  • 13
F.S.
  • 333
  • 1
  • 3
  • 13

2 Answers2

3

Always Encrypted doesn't support TVPs. When you use enable "Column Encryptin Setting" you can't use TVPs at all.

Victoria Malaya
  • 518
  • 2
  • 8
0

At the moment we do not have inbuilt support for TVPs and Always Encrypted in any of the supported drivers (.NET / ODBC / JDBC.) Please review my blog post at https://blogs.msdn.microsoft.com/sqlcat/2016/08/09/using-table-valued-parameters-with-always-encrypted-in-sql-server-2016-and-azure-sql-database/ which proposes a workaround.

Arvind Shyamsundar
  • 11
  • 2