在SQL Server中集成c＃类库

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)

我正在尝试在SQL Server 2012中测试CLR功能的功能。我在网上找到了一个关于如何基本上这样做并让它在我的服务器上工作的教程。（https://www.skylinetechnologies.com/Blog/Skyline-博客/日至2013 / CLR函数式-SQL-Server的A-教程）

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:

现在，我想创建一个不返回表而是返回字符串的函数。根据我的理解，SQL Server需要某种对象来使用，所以我尝试使用以下测试方法：

 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:

在SQL服务器上，我执行了以下操作：

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:

SQL Server在执行测试方法时抛出错误，称“对象引用未设置为对象的实例”，并进一步：

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

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.

任何人都可以告诉我我的概念错在哪里或者可能提供一个好教程的链接？直到现在我都找不到一个。提前致谢。

1 个解决方案

#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?

好吧，我自己找到答案，问题是“nvarchar（max）”作为返回类型。您必须为nvarchar定义长度或使用变通方法，然后它才能正常工作。相关：如何使用Nvarchar（max）参数创建CLR存储过程？

秒客网

在SQL Server中集成c＃类库

1 个解决方案

#1

#1

相关文章