SqlServer calls external programs for Data Synchronization

Source: Internet
Author: User
First, create two databases: SyncA is the data source and SyncB is the database for SyncA synchronization. Create Source and Target tables in SyncA and SyncB respectively. In actual business, the structures of the two tables are mostly different. Create a project for the Class Library: MySync (note that the project version. net4.0 and later versions not supported by Sql08). The following is the same

First, create two databases: SyncA is the data source and SyncB is the database for SyncA synchronization. Create Source and Target tables in SyncA and SyncB respectively. In actual business, the structures of the two tables are mostly different. Create a project for the Class Library: MySync (note that the project version,. net 4.0 and later versions not supported by Sql08). The following is the same

First, create two databases: SyncA is the data source and SyncB is the database for SyncA synchronization.

Create Source and Target tables in SyncA and SyncB respectively. In actual business, the structures of the two tables are mostly different.

Create a project for the Class Library: MySync (note the project version. net 4.0 and later versions not supported by Sql08)

The following is the synchronization program code:

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)] public 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; } }}

Next, sign the class library project and compile the [project] After signing ]:

Enable CLR: By default, the CLR in SQL Server is disabled, so we need to execute the following command to open the CLR OF THE SyncA database.

exec sp_configure 'clr enabled',1  reconfigure  go

Register DLL:

To call the method we wrote, you need to register the DLL we just compiled in SQL Server. Before that, you must know that in this project, if you want to access resources other than the server, you need to configure permissions. If this parameter is not configured, an error similar to the following will appear in the subsequent operations. I found the content about authorization configuration: connection.

Create a login name and secret. If the Assembly has changed, delete the key and login name and create a new one:

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 SQLCLRSyncLoginDROP ASYMMETRIC KEY SQLCLRSyncKey

Create an assembly. After the DLL is changed, delete it and recreate it:

USE SyncA; GO  create ASSEMBLY MySync FROM 'C:\MySync.dll'WITH PERMISSION_SET = EXTERNAL_ACCESS;GO 

Then create a function to call this DLL:

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

First, let's test and execute the query in SyncA:

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

SyncB adds a data entry:

The following uses the trigger to automatically synchronize data from SyncA to SyncB. The tt table is created temporarily to save the results returned by the trigger call:

Create Trigger tr_sourceon [Source] for INSERTASbegindeclare @ strSql nvarchar (max) select @ strSql = 'insert into Target (Id, Name, SyncTime) values (''' + cast (Id as nvarchar) + ''', ''' + Title + ''', getdate ()) 'from inserted -- run declare @ result nvarchar (max) select @ result = dbo. fun_sync (@ strSql) insert into tt (tt) values (@ result) end

There is no problem in directly executing the function, but an exception occurs when the trigger calls the function:

False: System. Data. SqlClient. SqlException: other sessions are using the transaction context. In System. data. sqlClient. sqlConnection. onError (SqlException exception, Boolean breakConnection) in System. data. sqlClient. sqlInternalConnection. onError (SqlException exception, Boolean breakConnection) in System. data. sqlClient. tdsParser. throwExceptionAndWarning (TdsParserStateObject stateObj) in System. data. sqlClient. tdsParser. run (RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) in System. data. sqlClient. tdsParser. bytes (Byte [] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest) in System. data. sqlClient. sqlInternalConnectionTds. propagateTransactionCookie (Byte [] cookie) in System. data. sqlClient. sqlInternalConnection. enlistNonNull (Transaction tx) in System. data. sqlClient. sqlInternalConnection. enlist (Transaction tx) in System. data. sqlClient. sqlInternalConnectionTds. activate (Transaction transaction) in System. data. providerBase. dbConnectionInternal. activateConnection (Transaction transaction) in System. data. providerBase. dbConnectionPool. getConnection (DbConnection owningObject) in System. data. providerBase. dbConnectionFactory. getConnection (DbConnection owningConnection) in System. data. providerBase. dbConnectionClosed. openConnection (DbConnection outerConnection, DbConnectionFactory connectionFactory) in System. data. sqlClient. sqlConnection. open () in MySync. syncDataBase. sync (String strSql)

This error contains a value of false, indicating that the trigger can go to the DLL step during call. Considering that the function is directly executed in the query, it is no error to go To the DLL step. Then the error occurs in the conflict between the trigger and the DLL call, and the conflict occurs in accessing the database. I have not found the reason for further exploration.

The following uses another method to implement synchronization. Because the error is that the trigger conflicts with the DLL database access, I will bypass the database access. Save the SQL script generated by the trigger to a directory, listen to this directory through other programs, execute the script file, and synchronize.

Class library code

using System;using System.Data;using System.Data.Sql;using Microsoft.SqlServer.Server;using System.Data.SqlClient;using System.Data.SqlTypes;using System.IO;namespace MySync{    public class SyncDataBase    {        [SqlFunction(SystemDataAccess = SystemDataAccessKind.Read, DataAccess = DataAccessKind.Read)]        public static string Sync(string strSql)        {            string result = "true";            try            {                if (!Directory.Exists("c:\\SyncLog"))                {                    Directory.CreateDirectory("c:\\SyncLog");                }                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);                }            }            catch (Exception ex)            {                result = "false:" + ex.ToString();            }            return result;        }    }}

Create another listener: MyListen

Using System; using System. data; using System. data. SQL; using System. data. sqlClient; using System. data. sqlTypes; using System. configuration; using System. threading; using System. IO; namespace MyListen {class Program {static void Main (string [] args) {string connSync = ConfigurationManager. connectionStrings ["connSync"]. toString (); string filePath = ConfigurationManager. deleetask[ "filePath"]; while (true) {// All txt files string [] fileList = DirFile. getFileNames (filePath ,"*. 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 {using (SqlConnection connection = new SqlConnection (connSync) {connection. open (); SqlCommand command = new SqlCommand (strSql, connection); command. commandType = CommandType. text; command. executeNonQuery (); connection. close () ;}} catch (Exception ex) {Console. writeLine (ex. toString ();} File. delete (f);} // scan Thread every 10 seconds. sleep (5*1000 );}}}}

You only need to open the listener to synchronize data. Download projects and databases.

Refer:

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.