In the sql server to run distributed query, you have to configure linked server.
Basically linked server configuration allows Sql Sevrer to execute commands against OLE DB data sources on different servers. Through linked server you can access Remote server,issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.The ability to query diverse data sources similarly.
You can create or delete a linked server definition with stored procedures or through SQL Server Enterprise Manager.
Using SQL Server Enterprise Manager:
Open SQL Server Enterprise Manager application, expand the object tree under the connected sql server for that linked server is created, expand the Server Object nod and than right click on Linked Server nod and choose new linked server option from the shortcut menu and follow the steps.
Using SPs: following list of Sps are used for managing linked servers.
1. sp_linkedservers - Shows information of all the linked servers.
2. sp_addlinkedserver - used for creating linked server.
Like:
USE master;
GO
EXEC sp_addlinkedserver
'Server Name',
N'SQL Server'
GO
3. sp_addlinkedsrvlogin - Creates or updates a mapping between logins on the local instance of SQL Server and remote logins on the linked server.
LIke:
EXEC sp_addlinkedsrvlogin 'AccountsServer', 'false', 'Domain\username', 'Username', 'password'
Let me give you complete example:
Suppose I want to connect to a database 'RnD' on the sql server 'HEX_SANJAY\HEXSANJAY' with the username 'sa' and password 'kkak'.
Execute the following code with your values for all the above mentioned settings to setup linked server.
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'HEXSANJAY',
@srvproduct=N'SQL',
@provider=N'SQLNCLI',
@datasrc=N'HEX_SANJAY\HEXSANJAY',
@catalog=N'RnD'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'HEXSANJAY',
@locallogin = NULL ,
@useself = N'False',
@rmtuser = N'sa',
@rmtpassword = N'kkak'
GO
EXEC master.dbo.sp_serveroption @server=N'HEXSANJAY', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'HEXSANJAY', @optname=N'rpc out', @optvalue=N'true'
GO
4. sp_dropserver - Removes a server from the list of known remote and linked servers on the local instance of SQL Server.
Like : sp_dropserver 'ACCOUNTSSERVER', 'droplogins';
An error has occurred while establishing a connection to the server.
Lots of people come across with the following error:
"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.".
The reason for this error is that by default SQL Server allows only local client connection. So to enable it for remote connection you can use SQL Server 2005 Surface Area Configuration application.
Here are the steps:
1. Open SQL Server 2005 Surface Area Configuration.
2. Click on Surface Area Configuration for Services and Connections link.
3. Expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.
You will get a message, click on ok and restart the Database Engin service.
Thats it....
"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.".
The reason for this error is that by default SQL Server allows only local client connection. So to enable it for remote connection you can use SQL Server 2005 Surface Area Configuration application.
Here are the steps:
1. Open SQL Server 2005 Surface Area Configuration.
2. Click on Surface Area Configuration for Services and Connections link.
3. Expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.
You will get a message, click on ok and restart the Database Engin service.
Thats it....
