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.
(edit)
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.
Changing this
SqlConnection conn = new SqlConnection(@"Context Connection=true");
to
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).
"If you use the context connection and try to execute a side-effecting T-SQL statement through it (e.g., an UPDATE statement) you'll get a SqlException from ADO.NET. However, we cannot detect it when you perform side-effecting operations through regular (non-context) connections."
That explains the behavioral difference between the context and non-context connections. This is the error: "Invalid use of a side-effecting operator 'SET ON/OFF' within a function"
Using the non-context connection and inserting into a table variable works without error. The same connection on a real table however throws this error: "MSDTC on server 'DHOLMES40SQL2008R2' is unavailable". That makes sense since the table variable doesn't elist in a transaction. However after i started MSDTC on my machine i was left with this error: "Transaction context in use by another session". That one i haven't figured out yet.(edit) This is likely what is talked about here: http://blogs.msdn.com/b/asiatech/archive/2009/08/10/system-transaction-may-fail-in-multiple-thread-environment.aspx
all of this started because when i wrote this code i solved it with a clr proc first. When i wanted a tvf version, i called the proc code from inside the tvf. That meant the side-effects that didn't apply to the proc were now in my function implementation.