Post reply

wrapping a RPC call with a local update query into a single transaction

  • July 6, 2004 at 2:57 am

    #162180

    I want to create a single stored procedure,

    which calls a remote server stored procedure (which returns an code if successful or not),

    based on the return value, call a local stored procedure to update a local record.

    All of this I want wrapping into a single transaction

    any ideas

     

    regards

  • July 7, 2004 at 9:04 am

    #513671

    Set up the remote server as a linked server. Then you can execute the stored procedure using the 4 part name. For instance if the remote server is linked with name RemoteSvr then you can:

    Declare @ret int

    Exec @ret = RemoteSvr.database.owner.procname

    Assuming that the procedure returns an integer return code.

    The procedure will run on the remote server, but return a value to your local stored proc. You can then check the value and do the update if required.

    You can put a begin transaction/commit around your code but it will only relate to the local server. If your remote procedure does some updating then you won't be able to undo using this method. I think you will have to have a separate remote procedure to undo what the first procedure did.

    Hope this helps

    Peter

     

  • July 8, 2004 at 2:54 am

    #513804

    Thanks peter

    I have the procedure running on a remote machine via a local procedure

    I read somewhere that you can begin a remote call with BEGIN REMOTE TRANSACTION or something like that.

    Would this allow me to wrap the the remote and local into a single trans

     

Viewing 3 posts - 1 through 3 (of 3 total)

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