Output variables (cross linked servers)

[munchen]

Here's what I currently have:

ServerA, dbA, spTestA
ServerB, dbB, spTestB

The spTestA looks at a table called tblA and the spTestB looks at a table called tblB.

In spTestA I want to set the field tblA.Notes as an output variable and send this to spTestB on the linked server (ServerB).
Once in spTestB I will then apply a function on this variable which will come in as an input variable and then return this as an output variable BACK TO spTestA which will then come back in as an input variable.

Is this possible to send this data across linked servers? Any ideas on how this is possible?

The reason for this is because ServerB allows UDF's whilst ServerA does not.

Any help would be appreciated!

 

[mrdenny]

Yes you can execute stored procedures on linked servers. You need to edit the linked server and on the options tab enable RPC and RPC OUT.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[munchen]

mrdenny

thanks for your reply.

I've already linked the two servers its the actual code in the two sp's that I am confused with.

Any examples?

 

[onpnt]

You can interact between the servers without a problem by using the correct referencing

[server].[database].[schema].

I recommend reading this
over well.

_{____________ signature below ______________
You may get help by means of code here. Just recall what happens to a rat when he eats the cheese off a rat trap while you're Ctrl+C/Ctrl+V'ing that code as your own}