It is possible to perform operations on SQL Server using different databases. While doing this easily on the same server, Linked Server is used to access different servers on the network.
Making Linked Server settings:
First, log in to the Sql Server and connect to the server to be processed.
Then, right-click on the Linked Servers area under the Server Objects menu and select the New Linked Server option to open the server creation wizard.
New Linked Server Wizard
The fields to be filled on the wizard that opens;
Linked server: Enter the name of the connection to be introduced in this field.
Provider: This field is selected as Microsoft OLE DB Provider for SQL Server since the connection will be made with SQL.
Product name: This field is populated as SQL.
Data source: In this field, if the operation will be done on the same machine, the Server name circled in the example, the IP address of that computer if it is to be connected to the SQL Server on another computer via Network.
Provider string: This field can be left blank.
Catalog: In this field, the name of the database should be entered to which database is desired to be connected to on the server to be connected.
Data Source field must be filled.
Then switch to the Security tab. If the operation will be performed on the same server, "Be made using login's current security context" is selected. However, if the database to which we will connect is on a different server, after selecting the "Be made using this security context" field, the "Remote login" and "With Password" fields should be filled with the information of a user who has access to the database to which we will connect.
Finally, click the Server Options tab. For the connection, it will be sufficient to mark the top five options as True, as can be seen in the picture below.
By clicking the OK button, the connection process is completed.
How to write a query on Linked Server?
In fact, it is not much different from other query operations. More than one spelling can be used for this. Two different spellings below will give the same result.
select * from Openquery(XPODASERVER, 'select * from XPODA_CLIENT_USERS')
select * from XPODASERVER.XPODA.dbo.XPODA_CLIENT_USERS