Hello all -
I have a network with multiple SQL Servers set up so that, to transfer data from one SQL Server to another, I have to go through a "hub" server, (like a bicycle wheel). I have a need to execute an insert from a given spoke to any of the others depending on a certain set of criteria. The spokes cannot access each other for security reasons. I've been trying to do this with T-SQL using a stored procedure that is housed in a database on the hub and called from any one of the "spokes". I got it to work this way, but the performance when parsing the SP on the calling server is just horrible. Questions are:
1. Is there a way to optimize the operation on the calling server?
2. Is there a better (faster) way to do this, say through an extended stored procedure?
Any help would be greatly appreciated.
TIA,
Steve
I have a network with multiple SQL Servers set up so that, to transfer data from one SQL Server to another, I have to go through a "hub" server, (like a bicycle wheel). I have a need to execute an insert from a given spoke to any of the others depending on a certain set of criteria. The spokes cannot access each other for security reasons. I've been trying to do this with T-SQL using a stored procedure that is housed in a database on the hub and called from any one of the "spokes". I got it to work this way, but the performance when parsing the SP on the calling server is just horrible. Questions are:
1. Is there a way to optimize the operation on the calling server?
2. Is there a better (faster) way to do this, say through an extended stored procedure?
Any help would be greatly appreciated.
TIA,
Steve
