How to connect to a DB on another server using SQL?

Question

I am trying to write a SQL script that will pull up information on where certain data is stored (i.e. which server and DB it is in). I have the part where it is pulling up the information and putting it into a separate DB on the same server, but much of the data that I'm wanting to collect is on other servers. Is there a way in the same script to connect to several different DB's on several different servers to get the data that I want. Essentially I'm trying to get Names, emails and phone numbers for people in our various DB's. Here is what I have so far:

if OBJECT_ID('tempdb..#TrainingSessionInfo') is not null
    drop table #TrainingSessionInfo

if OBJECT_ID('PSBackup.dbo.InvestigoUniversitySessions','U') is not null
    drop table PSBackup.dbo.InvestigoUniversitySessions

declare @StartDate date = null
        ,@EndDate date = null

set @StartDate = '1/1/2014'
set @EndDate = '6/17/2014'
--exec spGetTrainingSessionInfo @StartDate,@EndDate

create table #TrainingSessionInfo(cntID int,datDate datetime,strDesc varchar(255),strFirmID varchar(255),strTrainer varchar(255)
        ,meetingID varchar(255),Attendees int)
insert into #TrainingSessionInfo
exec spGetTrainingSessionInfo @StartDate,@EndDate

create table PSBackup.dbo.InvestigoUniversitySessions (lngSessionID int
        ,strUserID int
        ,strFirmID varchar(255)
        ,cntIF int
        ,strDSNServer varchar(255))
insert into PSBackup.dbo.InvestigoUniversitySessions (lngSessionID,strUserID,strFirmID,cntIF,strDSNServer)
select lngSessionID
        ,strUserID
        ,strFirmID
        ,cntID
        ,s.strDSNServer
from tblTrainingSessionSignUp tss
join Sites s on s.FirmID = tss.strFirmID
where tss.lngSessionID in(select cntID from #TrainingSessionInfo)

select * from PSBackup.dbo.InvestigoUniversitySessions order by lngSessionID desc,strFirmID,strUserID desc

in short yes you can. you need to set up linked servers and use the format [server].[database].[schema].[table]. there is probably a better solution to your problem since it sounds like you have multiple storage devices and maintaining this type of script will get frustrating for you or anyone after you. — Bozman
– Bozman, Commented Jun 18, 2014 at 13:54

Paolo · Accepted Answer · 2014-06-18 14:48:44Z

1

you can use a linked server object.
the linked server objects on the single central sql server machine you are working on must be configured in advance but once they are properly set you can access remote data with the usual syntax:

select top 10 * from [linked server name].[remote database name].[schema].[table]

no change is required on the remote servers.

edited Jun 18, 2014 at 14:48

answered Jun 18, 2014 at 13:55

Paolo

2,2731 gold badge17 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Mike Over a year ago

The servers are already in place, I can't change them. I can only access them.

Paolo Over a year ago

i don't get your comment... why change your servers?

Mike Over a year ago

You said that they have to be configured. I don't have access privileges that will allow me to make any changes to the servers.

Paolo Over a year ago

... please find in my edit the explanation of what a linked server is.

Mike Over a year ago

Okay, this I think I can do. I do have another question though. Once this link is created how do you remove it? It doesn't specify in the link you sent.

|

Collectives™ on Stack Overflow

How to connect to a DB on another server using SQL?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related