Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Aleksandar
Starting Member
1 Post |
 Posted - 2010-01-28 : 04:36:36
|
| Good day,Please help with suggestions.I have a stored procedure that runs on a remote server and inserts a record. Procedure returns the ID of the new record through OUTPUT parameter.Exception handling is implemented in a way that if exception occurs, record gets inserted with an error status, and RAISEERROR is raised. Procedure runs using OPENROWSET as:SELECT *FROM OPENROWSET ('SQLOLEDB','Server=remote_srv;TRUSTED_CONNECTION=YES;',' declare @ID intexec dbo.Proc_InsertRecord /..some parameters....../, @out_ID = @ID output select @ID')Now if all goes well without exception, the ID of a newly inserted record is returned via @ID.If exception occurs, only the error message is displayed and no ID is returned - although new record is inserted with error status.I want to get both the error message and the ID from the remote server in case of exception. I understand that OPENROWSET is limited in a way that it returns just the first result set, presumably error message in this case.Is there a work around this, without linking remote server? I appreciate any suggestions. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-28 : 06:48:26
|
Have Proc_InsertRecord, on remote_srv, SELECT the @ID instead of using an OUTPUT parameter?Ghastly hack, sorry about that, but if it works ...  |
 |
|
|
|
|
|
|
|
Using OPENROWSET to get OUTPUT parameters