4

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.

Alexander Rothländer
  • 103
  • 7
  • 2
    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 Jan 08 '18 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 Jan 09 '18 at 07:16

1 Answers1

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?

Alexander Rothländer
  • 103
  • 7