How to use stored procedure to query data in anohter server?

Question

Post reply

How to use stored procedure to query data in anohter server?

x1029cloud

SSC Rookie

Points: 36
More actions
May 13, 2014 at 10:21 am

#286615

Hi
I am a newbie to sql server so please help me!
Say I have to server, A and B.
Now I want to write a stored procedure in server A, to retrive data from server B.
How could I manage to do that?
Thousands thanks in advance!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

generally, you create a linked server.

then your procedure calls the four part name of the object on the other server,

like this:

SELECT ID,ColumnList FROM FinanceServer.FinDatabase.dbo.ProductsSoldTable

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

LutzM SSC Guru Points: 107049 More actions · Answer 2

Depending on the process it might be more efficient to perform joins, apply filters and/or aggregate data on the "source server" (Server A), e.g. by writing a stored procedure on Server A to provide the results based on input values and then just call the sproc from server B.

Especially when dealing with joins between (filtered) data located on different servers it might provide a performance gain to load the data from the source server to the target server into an indexed temp table.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ocean3300 SSChasing Mays Points: 621 More actions · Answer 3

Use openrowset

[Code]

SELECT a.*

FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',

'SELECT GroupName, Name, DepartmentID

FROM AdventureWorks2012.HumanResources.Department

ORDER BY GroupName, Name') AS a;

[/Code]

x1029cloud SSC Rookie Points: 36 More actions · Answer 4

Thx for everyone's reply. I have learned a lot.

I eventually solve that by use synonymy, which provides the advantage for dealing future changes in source table.