Need up to 30 seconds to load.
what's up everybody Welcome to my
channel in this video today I will be
sharing with you how you can set up an
oracle link server in SQL Server so in
order to achieve this you need to do a
couple of things first you need to
install the Oracle client and after you
install the Oracle client you need to
set up the DNS file right this will
allow you to connect to your Oracle
server and then you create a link server
so the first step is to head over to
Oracle e-delivery and download the
Oracle client so once you're there all
you need to do is sign in so the sign in
is complete then you'll search for
Oracle client now the Oracle client is
just a drivers are software that allows
you to connect to the Oracle database so
scroll down in the list and I like to go
with the latest version so you can go
with 21. so it don't load
and then from the card select continue
select your platform I'm using Windows
then select continue I set the terms and
agreement and continue
select close you don't want to download
the Gold version so uncheck and then
select download however if you don't
have the Oracle download manager install
it will ask you to install it so this is
the Oracle install manager so open and
then complete the installation and then
the Oracle client will be downloaded
using the Oracle Download Manager now
the next step you want to create a
directory right where you are going to
do the installation and then you're
going to copy the file to that directory
so I have a folder called the app folder
and within the Oracle folder you should
extract the client right so this is
considered as your client own and then
the Oracle app Oracle will be considered
as the base directory so the next step
is to install the client
continuous administrator
use the windows built-in account and
select next you can keep Oracle base
right or you can change it to the folder
which you had created so in my case it
was the D drive
up and Oracle
and then it automatically changes
software folder if you select next it
will continue so here's a brief summary
of all information so select install
this may take a few minutes to complete
so the installation has been completed
the next thing you want to do is grab
your TNS file from your Oracle server
you can copy it or you can copy all the
content whichever works for you so I'm
logging on to my server as the Oracle
user
within the home directory under the
networks folder you can find a TNS file
if you view the tns.ora file you should
be seeing the TNS names for your host so
mine is dev2 so copy this information
no go to the home directory of your
installation
product
client and then within the network
folder
open the admin folder open sample
edit the TNS names file
and then you can get rid of all of this
and then paste the TNS information there
save the file and then copy
the TNS names to the network directory
if you bring up comma and prompt and do
a TNA spring of the Alias you should get
a response so the Alias name was dev2 so
no listener that means The Listener and
my database server is not running so I
need to start the listener so my
database cannot accept connections so if
we do a TN spring again to Dev we should
no longer get the hero that the listener
is not running
so now let's connect to SQL Server
management Studio once you are connected
to your management Studio expand server
objects expand link servers right and
under the provider section right click
on the Oracle provider select properties
and then select allow in process this
allows you to run process on the Oracle
database
then select ok now it's time to create
the link server so I'm going to show you
two ways how you can do this first right
click new server
specify a name for the link server I'm
going to call it div 2.
change data source to Oracle provider
and for the data source name specify Dev
tool so what is happening here that is
like it's going over to the hurricane
client and taking all that information
within the TNS file and plugging it
right here so the Second Step I'll
pretty much show you what happens
indirectly
for the security connection select B
made with using the security context
specify the user and the password on the
Oracle database now remember this is the
remote server that you're trying to
access and do not use system for these
because this is just a test environment
if you expand the link server expand
catalogs select the default then you
should be seeing a list of tables and
objects that you have access to now the
second option is the manually specify
the data sources like you're doing a
direct connection when you use this
method so here I have a script that you
can use specify the username and the
password and for the data source if you
look carefully in the description list
right it has the connection details that
would be in the TNS file I'll also leave
the script in the comment section of the
video so if you did not set up the TNS
file and you use this data source within
your link server then you'll still be
able to connect to your server so let's
do a test connection
and the connection is successful so
that's it for now guys don't forget to
like this video and comment for more
tutorials like this because this channel
may help you in the future again that's
it for now thank you for watching see
you in the next video
[Music]
