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:
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.