Creating Linked Servers using SQL Server 2005

Creating Linked Servers using SQL Server:

Linked servers are used for Importing and Exporting Data from Different Servers on Different Databases like Oracle, Excel, and Access etc.
Creating Linked Server in SQL server can be done in two ways.
Either by Querying or by SSMS. (SQL Server Management studio.);
Using SQL Server Management Studio:



Open SQL Server Management Studio.
Click on Server Objects  Linked Servers Right Click




Click on new Linked Server.




Enter the name you want give for Linked server.



Enter the Provider Name for SQL server we will give SQLNLCLI and Provide the Connection string for the Provider string. Catalog means Databasename.and click ok.





Created new linked server.



Then Right click Properties. Then click on.




Select the Security and Give user name and password and check impersonate and click ok.
As shown below.



Then the authentication also provided. If you to change Server options select the third step.




Then execute the query. Like
select * from [SF-PC035].[SlickTicket].[dbo].[district];
Here:
SF-PC035 Linked Server
SlickTicket Database
Dboschema
DistrictTable.
It will Display Error message if the connection string is not correct.
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'sa'.
OLE DB provider "SQLNCLI" for linked server "SF-PC035" returned message "Invalid connection string attribute".
Check the connection string is correct.and Provide correct connection string and Execute.Some times this type of Errors also Occurred.
OLE DB provider "SQLNCLI" for linked server "SF-PC035" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "SF-PC035" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 2, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [2].
OLE DB provider "SQLNCLI" for linked server "SF-PC035" returned message "Invalid connection string attribute".
This means Login timed out Expired.
or
Remote Server IP address or hostname is changed.
Or
The remote Linked server is shut down.
Or
User id or Password is incorrect or Either the Remote Services are stopped.
Provide correct connection string. Provide correct userid and password .Execute this query like the above shown .we will get result.




Using Query:

The Second way to create the linked server is execute the following queries.

USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@server = 'SF-PC035',
@srvproduct = '',
@provider = 'SQLNCLI',
@provstr = 'DRIVER={SQL Server};SERVER=SF-PC035\sqlexpress;userid=sa;password="telemed";'

EXEC sp_addlinkedsrvlogin 'SF-PC035', 'false', NULL, 'sa', 'password'
sp_addlinkedserver is for creating linked server.
sp_addlinkedsrvlogin is for Providing authentication for the linked server.
Then Linked server created sucessfully.