Post reply

calling stored proc from a linked server...

  • October 9, 2003 at 12:29 am

    #160167

    I am a bit confused by BOL. I have a second SQL server for another team in our company and they want to be able to update a field in one of our tables when one of their records is updated.

    I have a stored procedure on my server that executes with very minimal permissions and will accomplish what they are wanting to do.

    We have linked servers setup (and prefer not to do RPC) and I want them to only be able to call only that stored procedure. (As you can tell I don't trust them very much--long story here...). BOL is a bit confusing on the subject but can you call a stored procedure on one machine from another when they are linked together? If so, what is the SQL syntax? Would this work:

    Select * from OPENQUERY (LnkSvrName 'EXEC svname.dbname.dbo.usp_storedproc @ID, @int')

    Thanks

    SJ

    Accepting all invites

  • October 9, 2003 at 1:15 am

    #477663

    You can use the Fully Qualified Name

    SERVER.Database.Owner.Procedure name

    annd execute the procedure on another server once you have added it as linked server.

    Also remote stored procedures are not within the scope of a transaction (unless issued within a BEGIN DISTRIBUTED TRANSACTION statement).

    Hope this help you.

    He who knows others is learned but the wise one is one who knows himself.


    He who knows others is learned but the wise one is one who knows himself.

  • October 10, 2003 at 1:39 am

    #477806

    You can't pass parameters with OPENQUERY. You have to build the string first and then send it.

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

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