Cross-server trigger

Exec sp_addrole server 'itsv', '', 'sqloledb', 'server2 \ server2005'
Exec sp_add1_srvlogin 'itsv', 'false', null, 'sa ', 'sa'

Select * From itsv. ZQ. DBO. resak

 

You can use this statement

SQL Code

1. Select * From OpenDataSource (
2. 'Sqlodb ',
3. 'Data source = 192.168.18.103; user id = sa; Password = 661'
4. ). Test. DBO. bbb

 
Select * From OpenDataSource ('sqloledb', 'Data source = 192.168.18.103; user id = sa; Password = 100'). Test. DBO. bbb
 

Access the content in the remote database,

 

When a trigger is written

SQL code

1. Message 15281, level 16, state 1, 1st rows
2. SQL Server blocks ComponentsThe statement 'openrowset/OpenDataSource 'of 'ad hoc distributed queries' is disabled because this component has been used as part of the security configuration of this server. The system administrator can enable 'ad hoc distributed queries 'by using sp_configure '. For more information about enabling 'ad hoc distributed querys', see "peripheral application configurator" in SQL Server books online ".

 
The SQL Server of message 15281, level 16, status 1, and 1st blocks access to the statement 'openrowset/OpenDataSource 'of the 'ad hoc distributed queries' component, this component is disabled as part of the server security configuration. The system administrator can enable 'ad hoc distributed queries 'by using sp_configure '. For more information about enabling 'ad hoc distributed querys', see "peripheral application configurator" in SQL Server books online ".
 

Enable

SQL code

1. Exec sp_configure 'show advanced options', 1 reconfigure exec sp_configure 'ad hoc distributed queries ', 1 reconfigure

 
Exec sp_configure 'show advanced options', 1 reconfigure exec sp_configure 'ad hoc distributed queries ', 1 reconfigure
 

Close

SQL code

1. Exec sp_configure 'ad hoc distributed queries ', 0 reconfigure exec sp_configure 'show advanced options', 0 reconfigure

 
Exec sp_configure 'ad hoc distributed queries ', 0 reconfigure exec sp_configure 'show advanced options', 0 reconfigure
 

Then it will be reported when triggered

SQL code

1. The ole db access interface "sqlncli10" of the linked server "(null)" returns a message. "This Transaction Manager has disabled its support for remote/network transactions. ".
2. Message 7391, level 16, status 2, process DDD, 15th rows
3. Unable to perform this operation because the linked server"(Null)" ole db access interface "sqlncli10" cannot start distributed transactions.

 
The ole db access interface "sqlncli10" of the linked server "(null)" returns a message. "This Transaction Manager has disabled its support for remote/network transactions. ". Message 7391, level 16, status 2, process DDD, and row 15th cannot perform this operation, because the ole db access interface "sqlncli10" of the linked server "(null)" cannot start distributed transactions.
 

You need to enable the MSDTC Service component service -- computer -- my computer -- Right-click -- MSDTC at the same time to enter the configuration check. Note that verification is not required.