1

I need to call a stored procedure from VB.NET that takes a table valued parameter. The table structure has three columns, one of which is an sql_variant.

The DataTable I want to pass to this SP has some strings inside the sql_variant column, which converts fine from T-SQL -> .Net, but not the other way around.

The problem is that it's converting string to nvarchar(max) which is incompatible with sql_variant, but as far as I can tell, a conversion to nvarchar(len(s)) would be fine.

So, can I explicitly convert certain cells in a DataTable to a type that will implicitly convert to a finite length nvarchar? Or, can I alter how these .NET types are implicitly converted to SQL types?

Thanks in advance for any help!
Andy

Andyrooger
  • 6,748
  • 1
  • 43
  • 44
  • I see I can set a `MaxLength` property on the problem `DataTable` column and call the SP fine, but this only works if the `DataType` of the column is `string`. Unfortunately other values in the `sql_variant` column will be of other types. – Andyrooger Feb 22 '12 at 17:40
  • If anyone is interested, until I have a real solution I am working around this by splitting my table into rows with strings in the `sql_variant` column and those without. Then I can set `MaxLength` on the `string` table. In fact I appear to have to split into each separate datatype anyway as otherwise I get complaints that `Object` is not compatible with `sql_variant`. – Andyrooger Feb 24 '12 at 09:44

0 Answers0