Configuring a Linked Server on SQL 2005 Server

Configuring a Linked Server on SQL 2005 Server

A] Configuring a Linked Microsoft Access Server on SQL 2005 Server - Using the Management Studio to set up a Linked Server

If you highlight the Linked Servers node and right click on the mouse, you can add a linked server by choosing the New Linked Server... ellipsis link as shown.

This pops up the dialog New Linked Server with three tabs as shown, with the General one in the default view. You must give a name to the linked server, in this case, NWIND11 (this is a copy of Northwind  from the Office 11 samples folder). From the drop-down choose the Provider as shown.

If you do not provide a product name you may generate an error as shown.

However you may give any irrelevant product name and no error is raised. This next picture shows such an example for a linked server, NWIND12.

NWID12 is configured with the default security, which is shown in the next picture. For NWIND11 the Security context was that of the external login and no mappings were made to local logins.

The Server options for the NWIND12 linked server are revealed by the Server Options tab as shown in this picture. Running a query similar to the 'AccessDb' returns the same results as in the case of 'AccessDb' (both are Northwind databases).

Note: with same procedure to set up database also

B]

First verify the "Distributed Transaction Coordinator" Service is

Running on both database server computer and client computers

1.      Go to "Administrative Tools > Services"

2.      Turn on the "Distributed 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"

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", "Enable TIP"

7.      The service will restart

8.      You might have to restart the computer(s)

C]

Now execute the following lines on the Source server database.

sp_configure 'remote query timeout', 0

go

reconfigure with override

go