SQLServer——触发器调用外部程序实现数据同步

发布时间：2020-12-12 13:25:04 所属栏目：MsSql教程来源：网络整理

导读：SqlServer调用外部程序实现数据同步首先创建两个数据库：SyncA是数据源，SyncB是对SyncA进行同步的数据库。在SyncA和SyncB中分别创建Source表和Target表，实际业务中，两张表的结构大多不相同。 ? ? 然后创建一个类库的项目：MySync（注意项目的版本，Sql0

SqlServer调用外部程序实现数据同步

首先创建两个数据库：SyncA是数据源，SyncB是对SyncA进行同步的数据库。

在SyncA和SyncB中分别创建Source表和Target表，实际业务中，两张表的结构大多不相同。

? ?

然后创建一个类库的项目：MySync（注意项目的版本，Sql08不支持的.net 4.0及更高版本）

下面是同步程序代码：

复制代码

using System;
using System.Data;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace MySync
{
    public class SyncDataBase
    {
       [SqlFunction(SystemDataAccess = SystemDataAccessKind.Read,DataAccess = DataAccessKind.Read)]
        static string Sync(string strSql)
        {
            string result = "true";

            string strConn = @"Data Source=localhost;Initial Catalog=SyncB;User ID=sa;Password=123@abc;";
            try
            {
                using (SqlConnection connection = new SqlConnection(strConn))
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand(strSql,connection);
                    command.CommandType = CommandType.Text;
                    command.ExecuteNonQuery();
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                result = false:" + ex.ToString();
            }

            return result;
        }
    }
}

复制代码

接下来要对类库项目进行签名，签名后编译【项目】：

启用CLR功能：默认情况下，Sql Server中的CLR是关闭的，所以我们要执行如下命令打开SyncA数据库的CLR。

exec sp_configure 'clr enabled',1  
reconfigure  
go

注册DLL：

为了调用我们写的那个方法，需要在SQL Server中注册我们刚刚编译好的那个DLL。在此之前，要知道在这个项目中如果要访问服务器之外的资源是要配置权限的。如果不配置，后面操作中会出现类似下面的错误。我找到的关于授权配置的内容：连接。

创建登录名和密钥，如果程序集有变更，要删除密钥和登录名重新创建：

USE master; GO CREATE ASYMMETRIC KEY SQLCLRSyncKey FROM EXECUTABLE FILE = C:MySync.dll' CREATE LOGIN SQLCLRSyncLogin FROM ASYMMETRIC KEY SQLCLRSyncKey GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRSyncLogin; GO

复制代码

DROP LOGIN SQLCLRSyncLogin DROP ASYMMETRIC KEY SQLCLRSyncKey

创建程序集，DLL变更后要删除重新创建：

USE SyncA; GO create ASSEMBLY MySync FROM ' WITH PERMISSION_SET = EXTERNAL_ACCESS;

复制代码

然后创建一个函数用于调用这个DLL：

CREATE FUNCTION dbo.fun_sync ( @strSql nvarchar(max) ) RETURNS max) AS EXTERNAL NAME [MySync].MySync.SyncDataBaseSync]

先来测试一下，在SyncA中执行查询：

SELECT dbo.fun_sync(insert into Target(Id,Name,SyncTime) values (null,null,getdate())')

SyncB中添加了一条数据：

下面使用触发器自动的从SyncA中将数据同步到SyncB中，其中的tt表是我临时创建的，用于保存触发器调用返回的结果：

create Trigger tr_source on Source] for INSERT AS begin declare max) select @strSql='''+cast(Id as nvarchar)''',+Title' from inserted --执行 @result @result=dbo.fun_sync(@strSql) insert into tt(tt) values (@result) end

复制代码

直接执行函数没有问题，但是触发器去调用函数执行却出现异常：

复制代码

false:System.Data.SqlClient.SqlException: 其他会话正在使用事务的上下文。     
在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception,Boolean breakConnection)     
在 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,Boolean breakConnection)     
在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)     
在 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,SqlCommand cmdHandler,SqlDataReader dataStream,BulkCopySimpleResultSet bulkCopyHandler,TdsParserStateObject stateObj)     
在 System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer,TransactionManagerRequestType request,String transactionName,TransactionManagerIsolationLevel isoLevel,Int32 timeout,SqlInternalTransaction transaction,TdsParserStateObject stateObj,Boolean isDelegateControlRequest)     
在 System.Data.SqlClient.SqlInternalConnectionTds.PropagateTransactionCookie(Byte[] cookie)     
在 System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)     
在 System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)     
在 System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)    
在 System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)     
在 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)     
在 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)     
在 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection,DbConnectionFactory connectionFactory)     
在 System.Data.SqlClient.SqlConnection.Open()     
在 MySync.SyncDataBase.Sync(String strSql)

复制代码

这个错误中包含了一个false值，说明触发器调用时已经可以走到DLL这一步了。考虑到在查询中直接执行函数，走到DLL这一步是没有错误的。那么错误就发生在触发器和DLL调用产生的冲突，冲突在访问数据库上面，再深入的原因，我也没有找到。

下面使用另外一种方式实现同步，因为错误是触发器和DLL的数据库访问冲突，那么我就绕过数据库的访问。将触发器产生的SQL脚本保存到某个目录下面，然后通过其他程序监听这个目录，执行脚本文件，实现同步。

类库代码

using System.Data.SqlTypes; using System.IO; class SyncDataBase { [SqlFunction(SystemDataAccess = SystemDataAccessKind.Read,DataAccess = DataAccessKind.Read)] if (!Directory.Exists(c:SyncLog")) { Directory.CreateDirectory("); } string fileName = c:SyncLog" + DateTime.Now.ToString(yyyyMMddHHmmss") + .txt"; if (File.Exists(fileName)) File.Delete(fileName); using (StreamWriter sw = File.CreateText(fileName)) { sw.WriteLine(strSql); } }

复制代码

另外创建一个监听程序：MyListen

using System.Configuration; using System.Threading; namespace MyListen { class Program { void Main(string[] args) { string connSync = ConfigurationManager.ConnectionStrings[connSync"].ToString(); string filePath = ConfigurationManager.AppSettings[filePath"]; while (true) { //所有txt文件 string[] fileList = DirFile.GetFileNames(filePath,0); line-height:1.5!important">*.txt",true); foreach (var f in fileList) { string strSql = ""; using (StreamReader sr = new StreamReader(f)) { string line; while ((line = sr.ReadLine()) != null) { strSql += line + " "; } sr.Close(); } try { new SqlConnection(connSync)) { connection.Open(); SqlCommand command = CommandType.Text; command.ExecuteNonQuery(); connection.Close(); } } catch (Exception ex) { Console.WriteLine(ex.ToString()); } File.Delete(f); } 每10秒扫描一次 Thread.Sleep(5 * 1000); } } } }

复制代码

只要将监听程序打开，就可以实现对数据的同步。项目和数据库下载。

参考：

http://msdn.microsoft.com/zh-cn/library/Microsoft.SqlServer.Server.SqlFunctionAttribute_properties(v=vs.100).aspx

http://blog.sina.com.cn/s/blog_59c41d0d0100esjn.html

http://www.cnblogs.com/wshcn/archive/2011/12/02/2271630.html

http://www.cnblogs.com/edong/archive/2010/03/10/1682172.html

http://www.cnblogs.com/hsrzyn/archive/2013/05/28/1976555.html

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!