Selecting data from two different servers in SQL Server

Selecting data from two different servers in SQL Server

How can I select data in the same query from two different databases that are on two different servers in SQL Server?

Answer by RBarryYoung for Selecting data from two different servers in SQL Server

Created a Linked Server definition in one server to the other (you need SA to do this), then just reference them with 4-part naming (see BOL).

Answer by Eric for Selecting data from two different servers in SQL Server

Yes you can.

I think you're asking how, so I'll answer that.

What you are looking for are Linked Servers. You can get to them in SSMS under

Server Objects-->Linked Servers

or you can use sp_addlinkedserver.

You only have to set up one. Once you have that, you can call a table on the other server like so:

select      *  from      LocalTable,      [OtherServerName].[OtherDB].[dbo].[OtherTable]  

Note that the owner isn't always dbo, so make sure to replace it with whatever schema you use.

Answer by RSolberg for Selecting data from two different servers in SQL Server

SELECT          *  FROM          [SERVER2NAME].[THEDB].[THEOWNER].[THETABLE]  

You can also look at using Linked Servers. Linked servers can be other types of data sources too such as DB2 platforms. This is one method for trying to access DB2 from a SQL Server TSQL or Sproc call...

Answer by super9 for Selecting data from two different servers in SQL Server

Querying across 2 different databases is a distributed query. Here is a list of some techniques plus the pros and cons:

1. Linked servers: Provide access to a wider variety of data sources than SQL Server replication provides
2. Linked servers: Connect with data sources that replication does not support or which require ad hoc access
3. Linked servers: Perform better than OPENDATASOURCE or OPENROWSET
4. OPENDATASOURCE and OPENROWSET functions: Convenient for retrieving data from data sources on an ad hoc basis. OPENROWSET has BULK facilities as well that may/may not require a format file which might be fiddley
5. OPENQUERY: Doesn't support variables
6. All are T-SQL solutions. Relatively easy to implement and set up
7. All are dependent on connection between source and destionation which might affect performance and scalability

Answer by ugio for Selecting data from two different servers in SQL Server

sp_addlinkedserver('servername')  

so its should go like this -

select * from table1  unionall  select * from [server1].[database].[dbo].[table1]  

Answer by Anna Karthi for Selecting data from two different servers in SQL Server

try this:

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=YOUR SERVER;Trusted_Connection=yes;','SELECT * FROM Table1') AS a  UNION  SELECT * FROM OPENROWSET('SQLNCLI', 'Server=ANOTHER SERVER;Trusted_Connection=yes;','SELECT * FROM Table1') AS a  

Answer by Raging Bull for Selecting data from two different servers in SQL

Server

You can do it using Linked Server.

Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel.

Linked servers offer the following advantages:

• The ability to access data from outside of SQL Server.
• The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
• The ability to address diverse data sources similarly.

Read more about Linked Servers.

Follow these steps to create a Linked Server:

1. Server Objects -> Linked Servers -> New Linked Server

2. Provide Remote Server Name.

3. Select Remote Server Type (SQL Server or Other).

4. Select Security -> Be made using this security context and provide login and password of remote server.

5. Click OK and you are done !!

Here is a simple tutorial for creating a linked server.

OR

You can add linked server using query.

Syntax:

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]        [ , [ @provider= ] 'provider_name' ]       [ , [ @datasrc= ] 'data_source' ]        [ , [ @location= ] 'location' ]        [ , [ @provstr= ] 'provider_string' ]        [ , [ @catalog= ] 'catalog' ]   

Read more about sp_addlinkedserver.

You have to create linked server only once. After creating linked server, we can query it as follows:

select * from LinkedServerName.DatabaseName.OwnerName.TableName  

Answer by user3586922 for Selecting data from two different servers in SQL Server

Server 2008:

When in SSMS connected to server1.DB1 and try:

SELECT * FROM [server2].[DB2].[dbo].[table1]

as others noted, if it doesn't work it's because the server isn't linked...

I get the Error: Could not find server DB2 in sys.servers. Verify that the correct server name was specified. If necessary, execute stored procedure sp_addlinkedserver to add the server to sys.servers.

To add the server: reference: To add server using sp_addlinkedserver Link: [1]: To add server using sp_addlinkedserver

To see what is in your sys.servers just query it:

SELECT * FROM [sys].[servers]  

Answer by Sameh for Selecting data from two different servers in SQL Server

Server Objects-----> linked server---> new linked server In linked server write server name or IP address for other server and choose SQL Server In Security select (be made using this security context ) Write login and password for other server

Now connected then

Select * from [server name or ip addresses ].databasename.dbo.tblname

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72