Returning OUTPUT variables from a stored procedure via OPENROWSET

• vatreni

  Old Hand

  Points: 324

  More actions

  January 27, 2010 at 5:22 am

  #214893

  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.

• artralv

  SSC Rookie

  Points: 39

  More actions

  January 27, 2010 at 5:41 am

  #1109224

  I believe thru store procedure you can handle this.