0

I want to know how to execute sql query from two database, one database is a remote server and next one is my local database, so from both the database table I want to use JOIN query.

Is it possible to do in SQL server management studio 2016 ?

If it is possible please guide how to do so.

Thanks, Manu.

Improve this question
2
0

As @Akina mentioned linked server might be a way to go. if you create linked server with name RemoteServer your query may look like:

  Select * from myLocalDatabase.dbo.myLocalTable  mlt 
    join RemoteServer.RemoteDatabase.dbo.myRemoteTable mrt
    on mlt.col1 = mrt.col1

Be aware of performance penalty when running remote joins. in certain cases you might be better importing data to local server and then joining it especially if this is going to repeat more often.

As for creation of linked server you could try this to add another SQL Server 

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'RemoteServerDescription', @srvproduct=N'', @provider=N'SQLNCLI11', @datasrc=N'RemoteServername_or_IP', @catalog=N'databasename'

USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'RemoteServerDescription', @locallogin = NULL , @useself = N'True'
GO
Improve this answer
3
  • Thanks @Grzegorz and Akina for the response, I have gone through that link of sp_addlinkedserver, but I've no idea how to get the details for these as given below - – Manu S Panicker May 8 '19 at 11:11
  • sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] [ , [ @provider= ] 'provider_name' ] [ , [ @datasrc= ] 'data_source' ] [ , [ @location= ] 'location' ] [ , [ @provstr= ] 'provider_string' ] [ , [ @catalog= ] 'catalog' ] – Manu S Panicker May 8 '19 at 11:11
  • Please go through below link for how to add linked server in SQL --> sqlshack.com/… – Learning_DBAdmin May 14 '19 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.