Integration of a c# class library in SQL Server

Question

I am trying to test the functionality of CLR Functions in SQL Server 2012. I found a tutorial online on how to basically do this and got it to work on my server.(https://www.skylinetechnologies.com/Blog/Skyline-Blog/March-2013/CLR-Functions-in-SQL-Server-A-Tutorial)

Now, i wanted to create a function that doesnt return a table but a string instead. In my understanding, the SQL Server needs some kind of object to work with, so i tried it with the following test method:

 public static class  TestSingleValue
{
  [SqlFunction(DataAccess = DataAccessKind.None, FillRowMethodName = "MyFillRowMethod", IsDeterministic = true)]

  public static SqlChars Test123()
    {
        SqlChars test = new SqlChars("teststring");
        return test;

    }

}

On the SQL server, i did the following:

ALTER ASSEMBLY ClassLibrary2 from 'D:\SQL\TestCLR\ClassLibrary2.dll' with Permission_set = SAFE
CREATE FUNCTION TestCLR()
  returns nvarchar(max)
    AS 
       EXTERNAL name ClassLibrary2.[CLRTest.TestSingleValue].Test123
 GO 

Execute TestCLR

The SQL Server throws an error when executing the test method, saying that an "Object reference not set to an instance of an object" and further:

System.NullReferenceException: System.Data.SqlServer.Internal.ClrLevelContext.GetCurrentContextForLobAccess(>>CClrLobContext* pLobContext) System.Data.SqlServer.Internal.ClrLevelContext.GetXvarWlobStream(CXVariantBasepxvarSource, XvarLOBStreamInitCode eCode, Int64 lcid, SqlCompareOptions compareOpts, CClrLobContext pLobContext).

Can anyone tell me where i got the concept wrong or maybe provide a link to a good tutorial? I couldnt find one until now. Thanks in advance.

Are the namespaces and class names correct(matching)? CLRTest.TestEinzelwert vs ?.TestSingleValue (might only be a copy/translate/paste error). Your code is also missing the static before the class — FrankM
– FrankM, Commented Jan 8, 2018 at 15:56
Sorry, missed this part when translating the code for the website. Checked it but neither this nor the missing static was the problem. Anyways, thank you! — Alexander Rothländer
– Alexander Rothländer, Commented Jan 9, 2018 at 7:16

Alexander Rothländer · Accepted Answer · 2018-01-09 10:38:28Z

1

Ok i found the answer myself, the problem is with "nvarchar(max)" as return type. You got to define a length to the nvarchar or use a workaround, then it works just fine. Related: How to create CLR stored procedure with Nvarchar(max) parameter?

answered Jan 9, 2018 at 10:38

Alexander Rothländer

1037 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Integration of a c# class library in SQL Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related