Tutorial on How to connect to a Linked Server

Linked Servers are a way that a SQL Server can talk to another ODBC database, such as another SQL Server instance or an Oracle database, with a direct T-SQL query.

In this article, I'm going to show you how to set up a linked server with simple steps!

Enviorments:

1. OS: windows
2. Database management system: SQL Server 2019 & SQLBase 12.2

What I am doing:

Connect SQL Server with Gupta SqlBase,so that you can view tables and data on sql server.

Step one:

Create ODBC connection @ODBC DATA SOURCE Administrator.

Open ODBC data source administrator panel by go to the Start menu under Windows Administrative Tools, choose ODBC Data Sources:

Step Two:

On the panel, choose System DSN over User DSN.

Why?

User DSN tab will show data source only for a currently logged user on this computer. While creating data sources under the System DSN, it will be available to any user that is logged on to this computer.

This will open the Create New Data Source dialog, from the list, choose a driver for which you want to set up a data source. In my case, it will be Gupta SQL Base for SQL Server:

1. Data Source Name: give a name to this connection
2. Database Name: the db name that will be retrieved
3. Server Name:the targeted db server
4. User Name and password: your choice of password

NOTES: Test connectivity on created DSN.

Step Three:

Navigate to SQL SERVER 2019, Generate a new script to create a new Link server.
The script is written as below:

Replace [Database Source Name] field with created data source name from step two.

USE [master]
GO
/****** Object:  LinkedServer [Database Source Name]    Script Date: 8/11/2021 1:07:38 PM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'Database Source Name', @srvproduct=N'Database Source Name', @provider=N'MSDASQL', @datasrc=N'Database Source Name'
 /* For security reasons the linked server remote logins password is changed with password */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Database Source Name',@useself=N'False',@locallogin=NULL,@rmtuser=N'SYSADM',@rmtpassword='password'
GO
EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'Database Source Name', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Step Four:

Refresh Server Object by navigate to Linked Servers under Server Object tab.
Check if the connection is appeared and you can expand to see all the tables from source database.

Step Five:

Verify if connection is successfully stored in sql server.

Option One:

Run below scripts:

sp_linkedservers;

This will show a list of linked servers, which means SQL Server recognized this connection and

Option Two: Using select statement on Linked server:

Script:

select * from OPENQUERY(LinkedServer ,'select * from desired_database.schema.table_name' )  Go

Step Seven:

Data Transfer:
Script:

INSERT INTO targeted_database.dbo.table_name 
SELECT * FROM OPENQUERY (LinkedServer, 'SELECT * FROM desired_database.schema.table_name)
Go

There you go!