Selecting data from two different servers in SQL Server

Question

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

User · Answer

Querying across 2 different databases is a distributed query  Here is a list of some techniques plus the pros and cons    Linked servers  Provide access to a wider variety of data sources than SQL Server replication provides Linked servers  Connect with data sources that replication does not support or which require ad hoc access Linked servers  Perform better than OPENDATASOURCE or OPENROWSET 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 OPENQUERY  Doesn t support variables All are T-SQL solutions  Relatively easy to implement and set up All are dependent on connection between source and destionation which might affect performance and scalability

User · Answer

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    Server Objects -  Linked Servers -  New Linked Server Provide Remote Server Name  Select Remote Server Type  SQL Server or Other   Select Security -  Be made using this security context and provide login and password of remote server  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

User · Answer

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

User · Answer

sp addlinkedserver  servername     so its should go like this -  select   from table1 unionall select   from  server1   database   dbo   table1

User · Answer

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

User · Answer

These are all fine answers  but this one is missing and it has it s own powerful uses  Possibly it doesn t fit what the OP wanted  but the question was vague and I feel others may find their way here  Basically you can use 1 window to simultaneously run a query against multiple servers  here s how   In SSMS open Registered Servers and create a New Server Group under Local Server Groups    Under this group create New Server Registration for each server you wish to query  If the DB names are different ensure to set a default for each in the properties   Now go back to the Group you created in the first step  right click and select New Query  A new query window will open and any query you run will be executed on each server in the group  The results are presented in a single data set with an extra column name indicating which server the record came from  If you use the status bar you will note the server name is replaced with multiple

User · Answer

select     from  ServerName IP    DatabaseName   dbo   TableName

User · Answer

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

User · Answer

What you are looking for are Linked Servers  You can get to them in SSMS from the following location in the tree of the Object Explorer   Server Objects-- gt 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

User · Answer

I had same issue to connect an SQL server 2008 to an SQL server 2016 hosted in a remote server  Other answers didn t worked for me straightforward  I write my tweaked solution here as I think it may be useful for someone else   An extended answer for remote IP db connections   Step 1  link servers  EXEC sp addlinkedserver  server  SRV NAME       srvproduct N        provider N SQLNCLI          datasrc N aaa bbb ccc ddd    EXEC sp addlinkedsrvlogin  SRV NAME    false   NULL   your remote db login user    your remote db login password       where SRV NAME is an invented name  We will use it to refer to the remote server from our queries  aaa bbb ccc ddd is the ip address of the remote server hosting your SQLserver DB   Step 2  Run your queries For instance   SELECT   FROM  SRV NAME  your remote db name dbo your table      and that s it   Syntax details  sp addlinkedserver and sp addlinkedsrvlogin

User · Answer

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

User · Answer

Simplified solution for adding linked servers  First server  EXEC sp addlinkedserver  server  ip port instancename    Second Login  EXEC sp addlinkedsrvlogin  ip port instancename    false   NULL   remote db loginname    remote db pass    Execute queries from linked to local db  INSERT INTO Tbl  Col1  Col2  Col3  SELECT Col1  Col2  Col3 FROM  ip port instancename   linkedDBName   linkedTblSchema   linkedTblName

User · Answer

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

User · Answer

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

User · Answer

As  Super9 told about OPENDATASOURCE using SQL Server Authentication with data provider   SQLOLEDB   I am just posting here a code snippet for one table is in the current sever database where the code is running and another in other server  192 166 41 123   SELECT top 2   from dbo tblHamdoonSoft  tbl1 inner JOIN   OpenDataSource  SQLOLEDB   Data Source 192 166 41 123 User ID sa Password hamdoonsoft     TestDatabase   dbo   tblHamdoonSoft1  tbl2 on tbl1 id   tbl2 id

[sql] Selecting data from two different servers in SQL Server

Examples related to sql

Examples related to sql-server