Post reply

Cross Server Procedure Exection

  • June 24, 2004 at 11:55 am

    #360160

    I need to use OPENROWSET() to execute a stored procedure on a separate, linked server. Any Ideas?

  • June 25, 2004 at 11:11 am

    #512011

    If the server is a 'linked' server you don't need to use OPENROWSET(), you just refer to the stored procedure using the full 4 part reference. eg

    exec linkedservername.database.dbo.storedprocname

    The interesting thing is that the stored procedure actually runs on the linked server.

    If the other server isn't linked, the format for OPENROWSET() is:

    Using ODBC:

    SELECT * FROM OPENROWSET('MSDASQL',   'DRIVER={SQL Server};SERVER=servername;UID=userid;PWD=password',   databasename.dbo.tablename)

     

    I am not sure if you can change this to run a stored procedure.

     

    Using OLEDB:

    SELECT 'SomeText', A.*     FROM OPENROWSET('SQLOLEDB','servername';'userid';'password',  'exec databasename.dbo.storedprocname') AS A

     

    substitute the servername, username and password as required.

     

    Hope this helps

     

    Peter

  • June 25, 2004 at 11:15 am

    #512013

    thanks pete...that's what worked (full four part reference of the table)

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

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