+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+---- Forum: Microsoft SQL Server (https://0day.red/Forum-Microsoft-SQL-Server)
+---- Thread: Selecting data from two different servers in SQL Server (/Thread-Selecting-data-from-two-different-servers-in-SQL-Server)
Pages:
1
2
Selecting data from two different servers in SQL Server - misoneistic480632 - 07-31-2023
How can I select data in the same query from two different databases that are on two different servers in SQL Server?
RE: Selecting data from two different servers in SQL Server - southward834 - 07-31-2023
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).
RE: Selecting data from two different servers in SQL Server - Drbenefactor3 - 07-31-2023
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...
RE: Selecting data from two different servers in SQL Server - cornelleiaexyjmrkj - 07-31-2023
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
RE: Selecting data from two different servers in SQL Server - optional439 - 07-31-2023
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
1. **Linked servers:** Connect with data sources that replication does not support or which require ad hoc access
1. **Linked servers:** Perform better than OPENDATASOURCE or OPENROWSET
1. **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
1. **OPENQUERY**: Doesn't support variables
1. **All** are T-SQL solutions. Relatively easy to implement and set up
1. **All** are dependent on connection between source and destionation which might affect performance and scalability
RE: Selecting data from two different servers in SQL Server - unbelted225348 - 07-31-2023
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**][1].
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**][2] 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**][3].
You have to create linked server **only once**. After creating linked server, we can query it as follows:
select * from LinkedServerName.DatabaseName.OwnerName.TableName
[1]:
[To see links please register here]
[2]:[To see links please register here]
[3]:[To see links please register here]
RE: Selecting data from two different servers in SQL Server - collusivenessrbqz - 07-31-2023
sp_addlinkedserver('servername')
so its should go like this -
select * from table1
unionall
select * from [server1].[database].[dbo].[table1]
RE: Selecting data from two different servers in SQL Server - cermetjd - 07-31-2023
select *
from [ServerName(IP)].[DatabaseName].[dbo].[TableName]
RE: Selecting data from two different servers in SQL Server - upsetting336 - 07-31-2023
I know this is an old question but I use synonyms. Supposedly the query is executed within database server A, and looks for a table in a database server B that does not exist on server A. Add then a synonym on A database that calls your table from server B. Your query doesn't have to include any schemas, or different database names, just call the table name per usual and it will work.
There's no need to link servers as synonyms per say are sort of linking.
RE: Selecting data from two different servers in SQL Server - cantata611 - 07-31-2023
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 use
Select * from [server name or ip addresses ].databasename.dbo.tblname