sqlclr tvf will only insert into table variable?

Dan Holmes 2015-09-08 15:08:31

This is the weirdest thing i have seen nor do i find it documented. I tried to ask this on msdn but the first answer was a bit off. Msdn discussion

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?

SQLkiwi 2015-09-08 21:26:48
It's certainly not a general restriction. I tested it with a streaming SQL CLR TVF from the SQL# library (SQL#.String_Split) and it worked as you would expect with table variables, temporary variables, and real tables.

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.

Dan Holmes 2015-09-10 12:28:48
If you are interested i can create a database backup that contains only the small set of data. I can give the source away too since it is really an adaptation of a SQLSat session i did.

(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).

Dan Holmes 2015-09-10 13:37:57
Same behavior on 2014.
Dan Holmes 2015-09-10 14:55:04
Based on this phrase from https://technet.microsoft.com/en-US/library/ms345135%28v=SQL.90%29.aspx?f=255&MSPPError=-2147217396.

"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.

SQLkiwi 2015-09-10 19:26:30
Thanks for the extra information, Dan. This reflect my own limited experience with SQLCLR. One has to follow all the rules to the letter to avoid unpredictable behaviour. They did a good job of catching the easy cases, but it is still possible to write stuff with subtle bugs.