Shulou(Shulou.com)06/01 Report--

When you encounter such a situation in your work, you need to update TableB (library DatabaseB in server ServerA 172.16.8.101) while updating the table TableA (library Database An in server ServerB 172.16.8.100).

TableA has the same structure as TableB, but the amount of data is not necessarily the same, so it is possible that TableC is also updating TableB. Because the data is not updated frequently, I thought of using the trigger Tirgger for simplicity. Record some of the problems encountered:

1. Access remote database

Create a link server to ServerB in ServerA and map your account. The addlinkedserver stored procedure creates a linked server. See the official documentation for details of the parameters. The first parameter LNK_ServerA is the custom name; the second parameter product name, if it is SQL Server, does not need to be provided; the third parameter is the driver type; the fourth parameter is the data source, where the SQL Server server address is written

Exec sp_addlinkedserver 'LNK_ServerB_DatabaseB','','SQLNCLI','172.16.8.101'

After configuring the linked server, you will log in to the remote database using the same local account by default. If the account is different, you also need to map the account. For more information on sp_addlinkedsrvlogin parameters, please see the official documentation. The first parameter is the same as above; the second parameter false logs in using the user password provided by the following parameters; the third parameter null enables all local accounts to log in to the linked server using the following user password; if the third parameter is set to a local SQL Server login user name, then only this user can log in to the linked server using the remote account; the last two are the users and passwords that log in to the remote server.

Exec sp_addlinkedsrvlogin 'LNK_ServerB_DatabaseB','false',null,'user','password'

If you want to delete the above configuration, you can do the following

Exec sp_droplinkedsrvlogin 'LNK_ServerB_DatabaseB',nullexec sp_dropserver' LNK_ServerB_DatabaseB','droplogins'

The above configuration can be queried by LinkedServers in SQL Server Management Studio manager under Server Objects. If all the links are normal, you can directly open the library table on the linked server.

It is worth noting that the above two stored procedures cannot appear in the trigger code, but run and complete the configuration in advance in the server ServerA, otherwise the trigger implicit transaction requirements will report an error "The procedure 'sys.sp_addlinkedserver' cannot be executed within a transaction."

two。 Configure distributed transactions

SQL Server triggers implicitly use transactions, and the linked server is a remote server. Distributed transaction processing needs to be turned on between the local server and the remote server, otherwise a "The partner transaction manager has disabled its support for remote/network transactions" error will be reported. I turned on the distributed transaction coordinator in both ServerA and ServerB and configured it appropriately to support distributed transactions. ServerA and ServerB are both Windows Server 2012 R2, and other versions of the server are similar.

(1) first, confirm that Distributed Transaction Coordinator is enabled in Services.msc. Other versions of the server may not be installed by default. You need to install windows features to install this feature first.

(2) find Component Services in the server management tool Administrative Tools, and configure it in the attribute Security tab in Local DTC as follows. Open the relevant security settings, and restart the service after completion. There are also documents saying that the server needs to be restarted, but not at least 2012 R2.

(3) configure the firewall and enable both Inbound and Outbound.

3. Processing of database fields text and ntext

One of the Content fields in the table TableA in the business is of type ntext. When synchronizing to TableB, you need to replace the content. For text and ntext types are outdated types, Microsoft officially recommends replacing them with (N) VARCHAR (MAX), which can be found here. You can consider it when designing in the future, and here we consider dealing with ntext.

But in the trigger, the inserted and deleted tables are not allowed to deal with the text/ntext/image type. Here we use a curve-saving method to read the records from the database into the temporary table, and then complete the string replacement processing through the textptr and patindex functions and the updatetext command.

If exists (select * from tempdb..sysobjects where id=object_id ('tempdb..#temp_tablea')) drop table # temp_tableaselect * into # temp_tablea from TableA where ID = @ IDdeclare @ s varchar, @ d varchar @ swords = "/ _ target/',@d='=" / _ replacement/'declare @ p varbinary (16), @ postion int,@l intselect @ p=textptr (Content), @ l=len (@ s), @ postion=patindex ('%'+ @ slots%') Content)-1 from # temp_tableawhile @ postion > 0begin updatetext # temp_tablea.Content @ p @ postion @ l @ d select @ postion=patindex ('%'+ @ swatches% creative content)-1 from # temp_tableaend

Pay special attention to the above code when dealing with Chinese characters of text type. Because text stores non-unicode data, patidex interprets Chinese characters as 1 character, while updatetext command interprets Chinese characters as 2 characters. Versions above SQL Server 2005 can be replaced as follows:

Update # temp_tablea set Content=cast (replace (cast (Content as nvarchar (max)), @ smaine (max) as text)

4. Perform remote database operations

When configuring the linked server, we can access the remote database table directly, as follows

Insert into LNK_ServerB_DatabaseB.DatabaseB.dbo.TableB... update LNK_ServerB_DatabaseB.DatabaseB.dbo.TableB set...

However, rudimentary SQL editors tend to report syntax errors, and in order to facilitate programming, we hope to gain more flexibility through exec sp_executesql. In fact, exec can execute the sql statement directly, but it is more difficult if there is a return value. As follows, you can use the output keyword to define a variable as a return variable from the remote server after querying the table TableB through ID, where @ Name output is the return variable and @ ID is the incoming variable.

Declare @ sql nvarchar, @ Name nvarchar (50), @ ID nvarchar (40) set @ SQL=N'select @ Name=Name from LNK_ServerB_DatabaseB.DatabaseB.dbo.TableB where ID=@ID'exec sp_executesql @ SQL,N'@Name nvarchar (50) output,@ID nvarchar (40)', @ Name output,@ID

In addition, exec executes sql statements directly, essentially executing concatenated sql strings. Sometimes it is much more difficult to concatenate variables into strings (how many single quotes are needed), while sp_executesql is much clearer.

Declare @ SQL nvarchar, @ Name nvarchar (50), @ Count int,@ID nvarchar (40) set @ Name=N'Cat'set @ ID=N' {00000000-0000-0000-0000-000000000000} 'set @ SQL=N'update TableA set Name='''+@Name+''', Count='+@Count+' where ID='''+@ID+''''exec (@ SQL) set @ SQL=N'update TableA set Name=@Name,Count=@Count where ID=@ID'exec sp_executesql @ SQL, N'@Name nvarchar (50), @ Count int @ ID nvarchar (40)', @ Name,@Count,@ID

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.