September 13, 2011 at 9:11 am
If I run the following, there are no errors:
exec LINKEDSERVER1.DATABASE1.dbo.SPROC1 @parm1 = 'my_value'
However, if I run this:
declare @my_table table
(
value1 int
)
insert into @my_table
exec LINKEDSERVER1.DATABASE1.dbo.SPROC1 @parm1 = 'my_value'
I get error:
Msg 3930, Level 16, State 1, Line 1
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
I am executing this script in a read-write database. The remote procedure I'm calling via the linked server is a standby/read-only database. We have a similar setup in DEV, which is working.
I've triple checked that my table variable's schema matches the columns coming back from the stored procedure call. I also thought maybe it had something to do with MSDTC, but that seems to be configured correctly. If I point the linked server to a read-write copy of the database, it works, so it definitely seems that it has something to do with calling the read-only database.
Any help is greatly appreciated.
September 13, 2011 at 9:29 am
Is it possible the stored procedure has multiple result sets, or that the remote server has MARS enabled? (Multiple Active Result Sets = MARS)
In either case, it will work for Exec, but not for Insert Exec.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 13, 2011 at 11:28 am
Thanks for the quick response. It's definitely only a single resultset coming back. Remember, the query works if I point it to a read-write copy of the database. I would have thought it would fail if multiple resultsets were coming back.
September 14, 2011 at 5:22 am
Is MARS enabled on the server? Won't matter if it's a single resultset, it will still fail the insert, as per Insert Exec in BOL/MSDN.
A proc that returns multiple datasets will work just fine if you execute it through SSMS. It will just have problems with Insert Exec. That's not the case here, so don't worry about that for now.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy