Re: Table-Valued Parameters to MS SQL Server stored procedure

It appears as if Microsoft SQL Server Native Client 10 and 11 have 
support for this in their ODBC implementation. At least according to 
http://technet.microsoft.com/en-us/library/bb522663(v=sql.105).aspx 
<http://technet.microsoft.com/en-us/library/bb522663%28v=sql.105%29.aspx> The 
second half of http://www.codemag.com/article/0712172 discusses an 
implementation of TVPs in ODBC in general, which might be relevant.

I've tried to call a stored procedure with a TVP in the way I think 
makes the most sense: using a bound array ref (where the actual rows has 
been both hash refs and array refs) but got the error

[DBD::ODBC:st bind_param failed: [Microsoft][SQL Server Native Client 
11.0] Restricted data type attribute violation (SQL-07006)

which, if I've understood what I've managed to google properly, means 
that it can't convert my array ref into the specific TVP type. (Perhaps 
it could also have something to do with the READONLY declaration in the 
stored procedure.)


Using temporary tables, or any other database workaround, isn't an 
option for me, so unless there's a way to use TVPs with stored 
procedures I'll probably have to abandon Perl (which, except for being 
extremely sad for me and very satisfying for the non-Perl believers, 
will be extremely time consuming).

JW


On 2013-12-30 11:14, Martin J. Evans wrote:
> On 29/12/2013 22:20, Jürgen Wenzel wrote:
>> Hello,
>>
>> I'm adapting a Windows perl program to work with a new MS SQL Server
>> database and it involves making calls to stored procedures that takes
>> Table-Valued Parameters. Is this possible with DBD::ODBC or any other
>> DBI? Or at all with (strawberry) perl?
>>
>> Would really appreciate some help -- answers and perhaps even a short
>> example -- since trying to figure this out is really starting to wear on
>> me.
>>
>> Thanks in advance!
>>
>> JW
>>
>> .
>>
>
> I don't believe you can do that with DBD::ODBC. In fact, I don't think 
> you can even do that with ODBC (if you know different and can point me 
> at it I'll take a look). The traditional way around this is to use 
> temporary tables.
>
> Martin