CODEWITHSUNDEEP
.netsql-serversqlclr
AK3800
AK3800

Reputation: 2298

SQL CLR function returning error trying to use context to create table and insert rows

I am running on SQL Server 2012 and Visual Studio 2012 with .NET 4.5, trying to build a CLR function that creates a table and inserts results into it using the context connection.

I'm getting the error:

An error occurred in RunFetchXML: Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.

UPDATE: I was able to get past the error above, I found that the assembly didn't update as expected after I added the DataAccessKind.Read attribute, so after updating that I tried again and got this error:

An error occurred in RunFetchXML: Invalid use of a side-effecting operator 'CREATE TABLE' within a function.

I'm trying to set up a CLR function that will take in FetchXML as an input string, the CLR function will need to take that FetchXML and query an external system, then create a table and dump the results into it. I don't think I can use a table-valued function with FillRow, etc, because I would need to be able to handle whatever was defined in the FetchXML, which means the end result could be anything, its not a fixed data structure.

So what I want to do is from within the CLR make a connection back to SQL Server using the current context, create a table that supports the fields returned by the FetchXML, and then dump the query results into the table.

I'm running in to the above mentioned error trying this, and I'm not sure this is even possible yet, any help or suggestions would be greatly appreciated.

Here's my current code for the CLR function, this is simplified to the point of just trying to create an empty table, and return a string result with "Success" or the error message, that's where I'm getting the above error from:

Imports System.Data
Imports System.Data.SqlTypes
Imports System.Data.Sql
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient

Public Class Retrieve

   <SqlFunction(DataAccess:=DataAccessKind.Read)> _
   Public Shared Function RunFetchXML(pstrFetchXML As SqlString) As SqlString
      Dim lstrResult As SqlString
      Try
         Using lsqlConn As New SqlConnection("context connection=true")
            lsqlConn.Open()
            Using lsqlCmd As New SqlCommand("CREATE TABLE #test (vchField1 varchar(200), iField2 int)", lsqlConn)
               lsqlCmd.CommandType = CommandType.Text
               lsqlCmd.ExecuteNonQuery()
            End Using
         End Using

         lstrResult = "Success"
      Catch ex As Exception
         lstrResult = "An error occurred in RunFetchXML: " + ex.Message
      End Try
      Return lstrResult
   End Function
End Class

And here's the pertinent portion of the SQL commands I am using to register this:

CREATE ASSEMBLY [TestAssembly]
FROM 'C:\Mssql\CLR\TestAssembly.dll'
WITH PERMISSION_SET = UNSAFE
GO

CREATE FUNCTION RunFetchXML(@pstrFetchXML nvarchar(MAX)) RETURNS nvarchar(MAX)
AS 
EXTERNAL NAME TestAssembly.[TestAssembly.Retrieve].RunFetchXML
GO

Grant Execute on RunFetchXML TO Public;

I run this command to test it and get the error message as the return value:

DECLARE @vchOutput nvarchar(MAX)
EXEC @vchOutput = dbo.RunFetchXML['test']
SELECT @vchOutput

Am I just missing something here or is it not possible to write (create a table and insert to it) from a CLR function?

Upvotes: 1

Views: 1427

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48836

Functions, whether T-SQL or SQLCLR, cannot modify state of the server. There is a whole list of things that cannot be done, such as any SET commands, calling NEWID(), etc. Check out the MSDN page for Create User-defined Functions (Database Engine) for the list of "Limitations and Restrictions". The only difference for SQLCLR functions is that they can execute T-SQL stored procedures, but only if they in turn don't violate any of those restrictions.

So you could stay with using a Function but change the SqlConnection to use a real/external connection as that will be seen as any other client connection and not have such restrictions. Of course, this would block your ability to create a local temporary table as that temp table would be on a different Session and would disappear once the Function ends.

Instead, switch the Function to be a Stored Procedure:

  • Procs have no such restrictions. The only real downside is that they are not nearly as easy to use in queries or interact with their results.
  • This would allow you to create a local temp table, though it might not exist once the proc is done since it is being created in a subprocess, and just like Dynamic SQL that creates a temporary object, they don't exist once that EXEC ends.
  • You wouldn't really need to create a temp table since Stored Procs can return dynamic result sets that are not hard-coded into the app code.

Additional notes:

  • dbo.RunFetchXML['test'] is an odd syntax, even for executing a function like a stored procedure (which is fine, but still odd syntax for the parameter). It should be SET @vchOutput = dbo.RunFetchXML('test');

  • What does any of this have to do with XML? And if you are just returning a result set to the client, there is an even easier way using SqlContext.Pipe.ExecuteAndSend() where you can submit a SqlCommand that has a SqlConnection to the remote machine. As long as you don't need to intercept the results to do anything with them, this would get around the need to define the result set structure.

Upvotes: 3

Related Questions