Is it possible for a Stored Procedure to extract data from 2 SEPARATE servers?

[leon_laikan]

Hi, everybody

I use a stored procedure (say SProc1) within a Server (say Server1) to query data from 2 separate company databases (say Company1 and Company2). I use Union to group together certain fields from each of the 2 databases.

The output from the stored procedure is used to feed a Crystal Report. i.e. I use Sproc1 as datasource for the Crystal Report.

My report has worked well for years.

Recently, my SAP Consultant has moved Company2 database to another Server (say Server2).

Now, my Crystal Report no longer works.

I am trying to modify my SProc1 to see if I can make it work.

My Question:

Is it possible (within SProc1) to access data from Company2 which is on another server (but on same equipment)

How?

The diagram below may help you understand my problem.

BEFORE:

Server1 ------- SProc1 (Selects data from both Company1 and Company2)

(Both databases are in Server1)

AFTER:

Server1 ------ SProc1 (Selects data from both Company1 and Company2)

(But now Company1 is in Server1

and Company2 is in Server2)

Any help, suggestions or hints are most welcome.

Thanks

Leon

[former_member233854 · Accepted solution]

There is a feature on SQL called LinkedServer. See the images below

You can use the server IP as well

19-04-2018-9-31-55-am.png

19-04-2018-9-32-43-am.png

At the end you can query like this

select * from [SERVER2].[COMPANYDATABASE].dbo.[TABLE]

[leon_laikan · Accepted solution]

Hi, Danilo

Thanks a lot for your reply.

Using linked server works for my stored procedure.

However, I have a similar situation with a VIEW instead of a STORED PROCEDURE.

While Linked Server works perfectly with a Stored Procedure, it does not seem to work for a View.

Could you please advise?

NB: Technically, my question is fully answered, but I would like to read your comments before closing.

Anyway, I will close tomorrow.

Best Regards,

Leon

[leon_laikan · Accepted solution]

Hi, Danilo

I found my mistake: a stupid typing error.

Linked Server works with both SP and views.

Thanks for your great help, and Best Regards,

Leon