sqlclr tvf will only insert into table variable?
Can the results of a SQLCLR tvf only be inserted into a table variable? I have tried with a normal table and a temp table. In both of those instances the table was left empty. I switched to a table variable and it worked. Am i missing something that everyone else just knows?
There have been some interesting bugs and issues with SQL CLR, particularly table-valued functions, in the past. I seem to remember the 2008/R2 era as being particularly interesting. In most cases though, we would see an error message, or a connection break. I might even have seen your issue before – it does ring a very faint bell – but memory can be deceiving, and it has been a long time.
To explore your particular scenario, we would need a full repro and probably source code. If the library you are using is commercial, that may not possible. In any case, if you can provide that level of detail, I would suggest you ask the question on Database Administrators (a Stack Exchange site). The author of the SQL# library hangs out there regularly, and knows a great deal about SQLCLR.
I have proven that this works in sql2014 but broken in 2008r2 (Microsoft SQL Server 2008 R2 (SP3) – 10.50.6220.0 (X64) )
Here is a 2008r2 database that exhibits…
Ok so i think i figured it out sorta.
Using the context connection, procs called from a clrtvf cannot have set statements including SET NOCOUNT ON (functions can't have side-effects). Removing that line from the proc being called returned the data for both the table variable and the normal table.
SqlConnection conn = new SqlConnection(@"Context Connection=true");
SqlConnection conn = new SqlConnection(@"server=.sql2008r2; database=dbname; integrated security=true")
Will allow the clrtvf to return the data into a table variable but not a normal table. That is a bizarre behavior. I will now need to try this on the 2014 version. I discovered this when i restored my backup to the 2014 instance and it wouldn't connect (because the database and servername were from the 2008r2 backup).