通过CLR同步SQL Server和Sharepoint List数据（三）

写在前面

    本系列文章一共分为四部分：

        1. CLR概述。

        2. 在Visual Studio中进行CLR集成编程并部署到SQL Server，包括存储过程、触发器、自定义函数、自定义类型和聚合。

        3. CLR集成编程的调试和所遇到的问题。

        4. 利用CLR同步SQL Server表和Sharepoint List（来源于实际项目应用）。

 

    本系列文章建立在以下软件环境的基础上：

• Windows Server 2003 Enterprise Edition Service Pack 2
• Microsoft Visual Studio Team System 2008
• Microsoft SQL Server 2008
• Microsoft Office Sharepoint Server 2007

 

CLR集成编程的调试

    前面已经提到了在Visual Studio中进行SQL CLR集成编程开发，同时Visual Studio也都提供了对CLR集成编程的调试，使开发更加简便，下面介绍两种具体的调试方法。

在Server Explorer窗口中使用单步调试

    1. 在Visual Studio中打开SQL CLR工程，打开cs文件并在你想调试的代码上设置断点，此时Visual Studio会提示你是否允许SQL CLR进行调试，点击“是”。同时，在Server Explorer窗口中，右键单击已连接的数据库节点，会看到Application Debugging和Allow SQL/CLR Debugging已经被选中。

    2. 在Server Explorer窗口中，打开已连接的数据库节点，找到已部署成功的数据库类型（存储过程、函数、触发器等），单击右键选择Step Into Function。此时Visual Studio会自动开始执行该方法并命中断点开始调试。注意，采用该调试方法前必须先编译并部署程序集到指定的SQL Server上，否则在Server Explorer窗口中将找不到刚创建的类型。

 

使用测试脚本调试

    除了上面介绍的方法外，还可以在Visual Studio中使用测试脚本进行代码调试。

    在Solution Explorer窗口中，打开Test Scripts目录下的Test.sql文件，该文件是Visual Studio在工程中自动创建的专门用于测试SQL脚本的。将执行SQL的语句写在里面，代码中设置好断点，直接点击F5，Visual Studio就会根据你写的SQL脚本执行，并自动命中断点进行调试。当然，也可以在Test.sql文件上单击右键，选择Debug Script手动启动调试。

    另外，还可以自己编写应用程序对SQL CLR集成编程进行调试，不过对于简单和少量的代码就没有必要了。还有一点需要注意，调试前必须设置断点，SQL CLR集成编程中代码一旦出现错误会自动跳出，而不指向出错的代码行，但在SQL Server端执行的时候CLR还是会给出错误内容的。

 

CLR集成编程中遇到的问题

    1. 当CLR程序集需要访问外部资源时必须将程序集的属性Permission Level的值设置为External，有的时候可能会遇到下面这样的错误提示。并且已经将Permission Level的值设置成External，而且数据库也将Trustworthy打开了。

Msg 10314, Level 16, State 11, Line 2
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'AssemblyName, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A) System.IO.FileLoadException:
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)

或者

Server: Msg 10327, Level 14, State 1, Line 1
CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.

    发生该问题的原因可能是你登录当前SQL Server的账户没有权限访问相应的资源，切换到SQL Server管理员账户即可。

USE <DatabaseName>
GO
EXEC sp_changedbowner ‘sa’

    2. CLR中数据访问被拒绝，尽管使用了SqlContext上下文进行数据库连接，或者指定了正确的数据库连接字符串。

A .NET Framework error occurred during execution of user defined routine or aggregate 'p_TESTNAME': System.Data.SqlClient.SqlException: Login failed for user ‘NT AUTHORITY\NETWORK SERVICE'.

    大部分时候你可能会使用NT账户去连接SQL Server而不是SQl Server服务账户，这时就需要在代码中使用SqlContext.WindowsIndentity属性，下面的代码片段说明了如何使用这个属性。

 public static void p_TESTNAME()
     {
         WindowsIdentity newId = new SqlContect.WindowsIdentity;
         WindowsImpersinationContext impersonatedUser = newId.Impersonate();
 
         try
     { 
         using (SqlConnection conn = new SqlConnection(“Server=RemoteServer;Integrated Security=true”))
         {
         conn.Open();
         //Do something…
         conn.Close();
         }
         }
     finally
     {
         impersonatedUser.Undo();
     }
     }
 

    impersonatedUser.Undo()方法必须执行（无论什么情况下）。当使用SqlContext上下文的方式进行连接时必须在该方法完成之后，否则会出现连接失败的错误，这不仅仅是在采用SqlContext进行数据库连接时，在使用SqlContext.Pipe方法时也是如此。所以，务必确认在Undo方法完成之后，再调用SqlContext对象。

    3. 有的时候在CLR的宿主服务器A上需要访问另外一个数据库服务器B，而B采用的Windows身份验证，这时可以在A中创建一个Linked Servers，用来指向B，使用的时候在数据库对象前加上Linked Servers名称和数据库名称，即：

<LinkedServers>.<DBName>.<[dbo].TableName>

    因为不能直接使用SqlContext在A中访问B，通过给定数据库连接字符串也不能成功在A中访问B，采用Linked Servers方法可以很好地解决这个问题。有关如何创建Linked Servers，读者可以参考SQL Server的帮助文档。

http://msdn.microsoft.com/zh-cn/library/ms162234(SQL.90).aspx

    4. 在进行CLR集成开发时有时会生成附属程序集，如*.XmlSerializers.dll，部署的时候需要将附属程序集与主程序集一同部署，先部署主程序集，后部署附属程序集。删除的时候先删除附属程序集，后删除主程序集。

    使用SQL CLR的过程中可能还会遇到很多奇怪的问题，我在项目中使用CLR时就遇到了很多莫名其妙的问题，通过查帮助文档和Google，后来问题基本都解决了。以后实际应用中再遇到问题再补上，目前只想到这么多了 :) 下面顺便给出一个手动部署dll到SQL Server服务器的脚本，方便大家将调试好的dll部署到生产环境中。

 

CLR集成编程的部署

    下面是手动将程序集部署到SQL Server的一个示例，通过该示例，可以帮助读者理解如何将程序集部署到SQL Server服务器上。

 --exec sp_configure 'clr enabled', 1
 --reconfigure with override
 --ALTER DATABASE TEST SET trustworthy ON
 --use TEST
 --go
 --Exec sp_changedbowner 'sa'
 
 --Dropping Everything
 if OBJECT_ID('dbo.SyncHierarchy') is not null
 begin
    print 'dropping procedure dbo.SyncHierarchy'
    drop procedure dbo.SyncHierarchy
 end
 IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'SqlClrSharePointSynchronizerXML')
 begin
    print 'dropping assembly SqlClrSharePointSynchronizerXML'
    DROP ASSEMBLY SqlClrSharePointSynchronizerXML with NO DEPENDENTS;
 end
 IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'SqlClrSharePointSynchronizer')
 begin
    print 'dropping assembly SqlClrSharePointSynchronizer'
    DROP ASSEMBLY SqlClrSharePointSynchronizer with NO DEPENDENTS;
 end
 go
 
 --creating everything
 declare @path nvarchar(1000)
 set @path = 'E:\Import\SqlClrSharePointSynchronizer\SqlClrSharePointSynchronizer\bin\Debug\'
 print 'creating assembly SqlClrSharePointSynchronizer'
 exec ('CREATE ASSEMBLY [SqlClrSharePointSynchronizer] FROM ''' + @path + 'SqlClrSharePointSynchronizer.dll''
 WITH PERMISSION_SET = EXTERNAL_ACCESS')
 
 print 'creating assembly SqlClrSharePointSynchronizerXML'
 exec ('CREATE ASSEMBLY [SqlClrSharePointSynchronizerXML] FROM ''' + @path + 'SqlClrSharePointSynchronizer.XmlSerializers.dll''
 WITH PERMISSION_SET = EXTERNAL_ACCESS')
 go
 
 
 CREATE PROCEDURE [dbo].[SyncHierarchy]
     @siteUrl [nvarchar](4000),
     @linkServer [nvarchar](4000)
 WITH EXECUTE AS CALLER
 AS
 EXTERNAL NAME [SqlClrSharePointSynchronizer].[Synchronizer].[SyncHierarchy]
 GO

 

结语

    本篇介绍了CLR集成编程在Visual Studio中的代码调试，以及运行和部署过程中遇到的一些问题，和对CLR集成编程中dll的手动部署，下一篇将向大家介绍一个SQL CLR在实际应用中的例子——如何通过CLR同步SQL Server与Sharepoint List数据。

1

2

3

4