Create SQL Server Database Link
SQL to create the database link:
EXEC sp_addlinkedserver @server = N'LinkName',
@srvproduct = N' ',
@provider = N'SQLOLEDB',
@datasrc = N'some.domain.com or Server ip',
@catalog = N'database_name'
EXEC sp_addlinkedsrvlogin N'LinkName', false, N'ServerDomain\Administrator', N'user_on_remotedb', N'password_on_remote_db'
-- N'ServerDomain\Administrator' can replace with NULL
-- user must allow to execute master procedure (sp_addlinkedserver and sp_addlinkedsrvlogin belong to master)
When you run a distributed transaction against an instance of SQL Server, Error message you might have:
OLE DB provider SQLOLEDB was unable to begin a distributed transaction
Server: Msg 7391, Level 16, State 1, Line 1
The partner transaction manager has disabled its support for remote/network transactions
Solution:
First verify the "Distribute Transaction Coordinator" Service is running on both database server computer and client computers
1. Go to "Administrative Tools -> Services"
2. Turn on the "Distribute Transaction Coordinator" Service if it is not running
If it is running and client application is not on the same computer as the database server, on the computer running database server
1. Go to "Administrative Tools -> Component Services"
2. On the left navigation tree, go to "Component Services -> Computers -> My Computer" (you may need to double click and wait as some nodes need time to expand)
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client", "Allow Inbound/Outbound" (Some option may not be necessary, have a try to get your configuration)
7. Check "No Authentication Required" if Mutual and Incoing Caller Authentication Required doesn't work for you
8. The service will restart
9. Add Firewall Excepton for MSDTC C:\WINDOWS\system32\msdtc.exe
10. You may need to reboot your server if it still doesn't work
On your client computer use the same above procedure to open the "Security Configuration" setting, make sure you check "Network DTC Access", "Allow Inbound/Outbound" option,
restart service and computer if necessary.
helpful links:
http://msdn.microsoft.com/en-us/library/aa561924%28BTS.10%29.aspx (Troubleshooting Problems with MSDTC)
http://support.microsoft.com/kb/816701
http://support.microsoft.com/kb/839279
http://support.microsoft.com/kb/306212
EXEC sp_addlinkedserver @server = N'LinkName',
@srvproduct = N' ',
@provider = N'SQLOLEDB',
@datasrc = N'some.domain.com or Server ip',
@catalog = N'database_name'
EXEC sp_addlinkedsrvlogin N'LinkName', false, N'ServerDomain\Administrator', N'user_on_remotedb', N'password_on_remote_db'
-- N'ServerDomain\Administrator' can replace with NULL
-- user must allow to execute master procedure (sp_addlinkedserver and sp_addlinkedsrvlogin belong to master)
When you run a distributed transaction against an instance of SQL Server, Error message you might have:
OLE DB provider SQLOLEDB was unable to begin a distributed transaction
Server: Msg 7391, Level 16, State 1, Line 1
The partner transaction manager has disabled its support for remote/network transactions
Solution:
First verify the "Distribute Transaction Coordinator" Service is running on both database server computer and client computers
1. Go to "Administrative Tools -> Services"
2. Turn on the "Distribute Transaction Coordinator" Service if it is not running
If it is running and client application is not on the same computer as the database server, on the computer running database server
1. Go to "Administrative Tools -> Component Services"
2. On the left navigation tree, go to "Component Services -> Computers -> My Computer" (you may need to double click and wait as some nodes need time to expand)
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client", "Allow Inbound/Outbound" (Some option may not be necessary, have a try to get your configuration)
7. Check "No Authentication Required" if Mutual and Incoing Caller Authentication Required doesn't work for you
8. The service will restart
9. Add Firewall Excepton for MSDTC C:\WINDOWS\system32\msdtc.exe
10. You may need to reboot your server if it still doesn't work
On your client computer use the same above procedure to open the "Security Configuration" setting, make sure you check "Network DTC Access", "Allow Inbound/Outbound" option,
restart service and computer if necessary.
helpful links:
http://msdn.microsoft.com/en-us/library/aa561924%28BTS.10%29.aspx (Troubleshooting Problems with MSDTC)
http://support.microsoft.com/kb/816701
http://support.microsoft.com/kb/839279
http://support.microsoft.com/kb/306212
Labels: SQL Server
