Dev Lecture: Adding & Querying A MySQL Linked Server In SQL Server

This is a walk-through of how to add a linked server to SQL Server and query it using OPENQUERY.

Below is a link to the code shown in the video:
https://sites.google.com/site/sqlserv...

---

Closed Caption:

hi my name is nutrition pulley and i'm
making this lecture sequel server 2008
lecture on how to add linked server to
your sequel server database in today's
example be adding a by sequel database
21 and this sequel batteries will be
able to access it to my sequel database
through ms equal what are the benefits
of using a linked server which is really
just an external server or other
database that you can access through
your ms sequel management's toodles
the benefits are that you can text you
can access easily external mon sequel
databases from within sequel server and
you could use t sequel commands on your
non sequel database and have them
translated to your non sequel database
language well this is partially true
because you're still going to be using
the site X of the actual language work
with in this case and plus you'll be
using a special command called openquery
in order to access the linked servers
downsides of using a linked server is
that your code will not be it'll be a
little bit more bulky to access it
it's not practical to make sore rocks
and stuff using management studios
however as a bonus if you have a linked
server in your sequel called with
another database you can access your
linked databases through the link server
so you won't be just confined to all
for instance could also use remote
instances
ok so now I'll show you how to add a
link server to your database so reversed
we have to go to control panel
i'm using new windows seven
however I think that this steps could be
slightly generalized you click on system
and security you would go to
administrative tool you click on data
sources or odbc double click that and I
yet you order your drivers and check to
see you have appropriate odbc driver for
the database you're going to be
connecting to my case is some connection
to a my sequel database I need my sequel
odbc driver
however if you wanted to connect to
oracle database you need oracle DPC
driver if you're connecting to
let's say db2 database you would need a
db2 odbc driver and so the driver
specific to what database you're trying
to connect to you could find these
drivers by googling it on google and
just downloading it installing it and
after you install the driver it should
show up your ODBC data source
administrator under drivers tab once
that is installed and as we can see here
i already have it installed you can go
to system DSN and add the driver
so I'm going to add my sequel DBC driver
finish and asked me for the connection
parameters
I'm gonna call my lord my data source
name my sequel it's important to
remember that name from using it to
reference it later on my CP IP server or
the location of the database server is
on my local computer so that would be a
local host or 127 00 about one and
access it through port 3306 the user
would be routed and now i'm going to
connect to
I have no password local instance the
database someone access is gbank i'm
going to test that connection successful
then i go to ok and it showed up right
now I go to okay once again now I will
go to my management studios and if you
connect to your server you'll see that
you have a local well collect whatever
server management studios that you need
to connect to my development server
local death and then you go to server
objects folder and then you would go to
linked servers and then you would
actual students remote so there are two
functions that I'll use to add server
the first i will use the stored proc
espeon score add linked server the
server side at ms equal stars for Brock
my server name is my sequel my service
product is is my sequel the provider for
my database connection which is also
important you don't within the
appropriate provider it will connect you
need MS gasps ooh-aah my data sources of
my sequel database here i'm referencing
the driver i'm going to be using to
connect with my sequel database is my
sequel odbc 5.2 a driver that is just
the name
WBC and you would start so let me show
you cuz
so if we look over here my driver's name
is my sequel odbc 5.2 a driver i just
put that in there i'm connecting to
localhost middle computer appropriate
port my databases gbank what I'm going
to connect to which is right over here
this is a
people were mentioned connecting to the
database
and
my username is root I don't have a
password and with option that is my
connection string to connect with my
sequel database my remote servers name
is my sequel
my local login that all books and
connecting local login with your route
and I wouldn't have a password now use
this command SP underscore adding server
login to connect to the server instance
in my sequel there's another thing to
note a lot of times you need to grant
privileges to connect to my sequel from
sequel server so you could just do this
in case grant all on start start to your
account you're going to be connecting to
buy my sequel route and from back at
localhost which is just
your IP address i'll be putting all this
in the comment box below this video but
just keep that in mind
so now i'm going to run this i'm going
to add my linked server so it's been
added and if we look over here and
refresh it I now have my linked server
there which is my single i'm going to
test the connection i tested the
connections linked server and it was
successful and now i go over here i'm
using select star from openquery bracket
this is the name of my server my sequel
comma as you can see the name is the
same and now in comments on putting the
actual command so in this case I am
going from the j banks database and the
g backstab base has the table all chops
so if we do carry on that table let's
start from jobs i have four records in
that table so right here I will do a
query on it executes it and as you can
see i have the same for roles return and
that is basically how you add a linked
server to your ms equal instance so you
could use my sequel with more than one
type of database and access remote
servers from my sequel database engine
is really useful and it makes things a
lot more practical i noticed that on the
internet they're not have many good
tutorials on how to do this at least
this is a working example at least
written tutorials they seem to leave a
lot of things though
another thing to keep note of is one
actually that's all I you have any
questions please feel free to ask and i
hope you learned something from this
video thank you