spen_lang
asked on
MS SQL Query Over 2 Different Instances
Hi,
I would like to query two sql databases that are on the same server but in different SQL instances.
For example:
SELECT * FROM instance1.Database1.Table1
INNER JOIN instance2.Database2.Table2 ON (Table1.Col1 = Table2.Col2)
How would I do this? Would I have to configure a linked server by using sp_addlinkedserver?
Thanks
I would like to query two sql databases that are on the same server but in different SQL instances.
For example:
SELECT * FROM instance1.Database1.Table1
INNER JOIN instance2.Database2.Table2
How would I do this? Would I have to configure a linked server by using sp_addlinkedserver?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great response, thanks.
I thought I posted the links already:
generate topic:
msdn.microsoft.com/en-us/l ibrary/aa2 13778(SQL. 80).aspx
sp_addlinkedserver:
http://msdn.microsoft.com/en-us/library/aa259589(SQL.80).aspx
generate topic:
msdn.microsoft.com/en-us/l
sp_addlinkedserver:
http://msdn.microsoft.com/en-us/library/aa259589(SQL.80).aspx
using linked server we can do this...
below is the syntax to add the linked server...
once you add the linked server on an instance to access the database on another instance... then you can run the queries as below
SELECT * FROM Database1.Table1 t1
INNER JOIN <'remoteip\instance'>.Data base2.Tabl e2 t2 ON (t1.Col1 = t2.Col2)
let me know if you have any issues...
below is the syntax to add the linked server...
once you add the linked server on an instance to access the database on another instance... then you can run the queries as below
SELECT * FROM Database1.Table1 t1
INNER JOIN <'remoteip\instance'>.Data
let me know if you have any issues...
exec sp_addlinkedserver 'remoteip\instance' -- accept defaults for all other params
exec sp_addlinkedsrvlogin @rmtsrvname='remoteip\instance', @useself=false, @locallogin=null, @rmtuser='username', @rmtpassword='password'
ASKER