SQL CLR Table valued function to return table from DB

Justin Blake
Justin Blake used Ask the Experts™

I am trying to create a CLR function to allow users to grab data from a table that changes names each month (name changes are outside my control).

I have everything compiling and running without any errors but I never get any rows back.

I have simplified things as much as possible to try to find the problem and am inserting a DataRow into the DataTable I want to return to rule out problems with the select but still don't get any rows.

C# code
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;

public class TableFunctions

    [SqlFunction(FillRowMethodName = "FillCLRTest")]
    public static IEnumerable CLRTest()
        string query = "SELECT [id]" +
                "FROM table";

        DataTable results = new DataTable();

            using (SqlConnection conn = new System.Data.SqlClient.SqlConnection("context connection = true"))
            using (SqlCommand command = new SqlCommand(query, conn))
            using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))

            DataRow dummyRow = results.NewRow();

            dummyRow[0] = "1234";

            return results.Rows;

        catch (Exception e)


        return null;


    public static void FillCLRTest(Object obj
        , out SqlString id

        DataRow results = (DataRow)obj;

        id = new SqlString(results["id"].ToString());


Open in new window

--drop assembly CLR

create assembly CLR authorization dbo
from 'D:\networkshare\Projects\Dev\justin\NETProjects\CLR\CLR\bin\Release\CLR.dll'
with permission_set = unsafe

--drop function CLRTest

create function CLRTest()
returns table(id nvarchar(255))
as external name CLR.TableFunctions.CLRTest

select * from CLRTest()

Open in new window

I have already previously enabled CLR on my MSSQL Server.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I got rid of the try catch that I had foolishly copied in from elsewhere and now I am seeing an error. I was missing DataAccessKind.Read from the declaration of the function in C#.

Changed line

[SqlFunction(FillRowMethodName = "FillCLRTest")]

Open in new window


[SqlFunction(FillRowMethodName = "FillCLRTest", DataAccess = DataAccessKind.Read)]

Open in new window

and it is working now.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial