Send rows of data to stored procedure in linked server

Question

Post reply

Send rows of data to stored procedure in linked server

saum70

SSC Eights!

Points: 993
More actions
September 2, 2016 at 9:55 am

#329930

Hi,
Please guide me as to how to send set of data to stored procedure in linked server. The scenario is as below
Server A--> DB A and Server B -->DB B
In DB A in stored procedure A extract data and store it into a table A. now Call stored procedure B which present in Server B -->DB B which will access to data from table A and do further computation and returns the computed value back to Procedure A
I have used XML parameter when passing the data however it is not working in distributed enviornment.
Please help!!!
Regards,
Saumik

Viewing 8 posts - 1 through 7 (of 7 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

1) Insert the data into a table on the linked server and access it directly from there with the local sproc.

2) Use a table valued parameter to pass the data set. That is the second time I have recommended those today.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Procedure B could simply read records from Table A

_____________
Code for TallyGenerator

saum70 SSC Eights! Points: 993 More actions · Answer 3

wouldn't that introduce loop back problem. I have implemented suggestion 1 by Kevin.

Thanks all

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

What do you mean by a "loop back" problem?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Loop back is not so much of a problem as distributed transactions which hold exclusive lock on remote servers.

Try to avoid pushing data to a remote server, it always better to "ask" it to pull data from your server.

_____________
Code for TallyGenerator

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

I understand now and I agree. Server A can start a procedure running on Server B and Server B can pull the data across.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

MMartin1 One Orange Chip Points: 27795 More actions · Answer 7

I would really have to see the data flow here as a diagram. If proc B is calling proc A (after already being ran), what is proc A doing this time? Is it going to insert data back into table A? what happens to the data that was already residing there? Updated? Appened to? It seems like you would go into a loop here. I would venture to say that whatever that proc on server B is doing, you can create it over on Server A at the very least. I would have to know more to give sound advise.

----------------------------------------------------