Returning OUTPUT variables from a stored procedure via OPENROWSET

  • Good day,

    Please help with suggestions.

    I have a stored procedure that runs on a remote server and inserts some record. Procedure returns the ID of the new record.

    Error handling is implemented in a way that if error occurs, record gets inserted with an error status, and RAISEERROR is raised.

    I am running this procedure remotely using OPENROWSET command:

    SELECT *

    FROM OPENROWSET ('SQLOLEDB','Server=remote_srv;TRUSTED_CONNECTION=YES;',' declare @ID int

    exec dbo.Proc_InsertRecord /..some parameters......, @ID output select @ID')

    Now if all goes well with no error, the ID of a newly inserted record is returned via @ID.

    If an error occurs, only the error is displayed and no ID is returned - although new record is created with error status.

    I want to get both the error and the ID from the remote server in case of error. I understand that OPENROWSET is limited in a way that it returns just the first result set, presumably error in this case.

    Can I work around this somehow? I appreciate any suggestions.

  • I believe thru store procedure you can handle this.

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

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