|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Phantom Transactionis sat a stored procedure SP1 and on S2 is sat another, SP2. SP2 attempts to insert into a temprary table from SP1 using the following syntax: insert into #temptable exec opendatasource('SQLOLEDB','blah blah').db.dbo.SP1 When ran an error is returned stating that a distributed transaction couldn't be started. However, in no place in any of the code which is running is a transaction exlpicitly started (no begin or end statements and definitely no begin transaction statements). In fact this line on it's own executed in isolation attempts to start a distributed transaction. If I change SP1 to a view returning the same data and use this code: insert into #temptable select * from opendatasource('SQLOLEDB','blah blah').db.dbo.view no transactions are started and everything runs. why? Just out of curiosity, does SP1 do any data modification?
Show quote "rocket salad" <rocketsa***@discussions.microsoft.com> wrote in message news:D339288B-4342-4CFD-AFD6-D564D4A40859@microsoft.com... > Picture this setup: 2 SQL Servers, S1 and S2 are physically seperate. On > S1 > is sat a stored procedure SP1 and on S2 is sat another, SP2. > > SP2 attempts to insert into a temprary table from SP1 using the following > syntax: > > insert into #temptable > exec opendatasource('SQLOLEDB','blah blah').db.dbo.SP1 > > When ran an error is returned stating that a distributed transaction > couldn't be started. However, in no place in any of the code which is > running > is a transaction exlpicitly started (no begin or end statements and > definitely no begin transaction statements). In fact this line on it's own > executed in isolation attempts to start a distributed transaction. > > If I change SP1 to a view returning the same data and use this code: > > insert into #temptable > select * from opendatasource('SQLOLEDB','blah blah').db.dbo.view > > no transactions are started and everything runs. > > why? No - I had chosen a stored procedure instead of a view initially because
there is a high likelyhood of other processing needing to be done when the select is carried out. It seemed logical to have an sp and call the extra processing from there, but at that time all SP1 was doing was a select. Show quote "Peter W. DeBetta" wrote: > Just out of curiosity, does SP1 do any data modification? > > -- > Peter DeBetta, MVP - SQL Server > http://sqlblog.com > -- > "rocket salad" <rocketsa***@discussions.microsoft.com> wrote in message > news:D339288B-4342-4CFD-AFD6-D564D4A40859@microsoft.com... > > Picture this setup: 2 SQL Servers, S1 and S2 are physically seperate. On > > S1 > > is sat a stored procedure SP1 and on S2 is sat another, SP2. > > > > SP2 attempts to insert into a temprary table from SP1 using the following > > syntax: > > > > insert into #temptable > > exec opendatasource('SQLOLEDB','blah blah').db.dbo.SP1 > > > > When ran an error is returned stating that a distributed transaction > > couldn't be started. However, in no place in any of the code which is > > running > > is a transaction exlpicitly started (no begin or end statements and > > definitely no begin transaction statements). In fact this line on it's own > > executed in isolation attempts to start a distributed transaction. > > > > If I change SP1 to a view returning the same data and use this code: > > > > insert into #temptable > > select * from opendatasource('SQLOLEDB','blah blah').db.dbo.view > > > > no transactions are started and everything runs. > > > > why? > > > On Fri, 1 Sep 2006 03:57:01 -0700, rocket salad wrote:
Show quote >Picture this setup: 2 SQL Servers, S1 and S2 are physically seperate. On S1 Hi rocket,>is sat a stored procedure SP1 and on S2 is sat another, SP2. > >SP2 attempts to insert into a temprary table from SP1 using the following >syntax: > >insert into #temptable >exec opendatasource('SQLOLEDB','blah blah').db.dbo.SP1 > >When ran an error is returned stating that a distributed transaction >couldn't be started. However, in no place in any of the code which is running >is a transaction exlpicitly started (no begin or end statements and >definitely no begin transaction statements). In fact this line on it's own >executed in isolation attempts to start a distributed transaction. You did start a transaction - each data modification statement always runs inside a transaction. If you never explicitly start one, then the execution of the INSERT statement will implicitly start a transaction. And the SET IMPLICIT_TRANSACTION options determines if it's also implicitly committed at the end of the statement. Since SQL Server on server S2 has no idea if SP1 is going to do any modification or not, it has to assume the worst and hence will expect the possibility of data modifications from S2. >If I change SP1 to a view returning the same data and use this code: There's still a transaction started (for the insert), but there's no> >insert into #temptable >select * from opendatasource('SQLOLEDB','blah blah').db.dbo.view > >no transactions are started and everything runs. distributed transaction. With a SELECT statement, SQL Server *knows* that there's not going to be any data modification on the other server, so the transaction can remain local. -- Hugo Kornelis, SQL Server MVP And so fails. I've done another test which was to carry out the 'exec
opendatasource' statement in isolation (without the insert into) and this executes fine. From this, am I right in thinking that because all S2 is doing is triggering SP1, that it doesn't care if it runs correctly or not (no transactions have been explicitly started so there's no different processing which needs to occur whether SP1 runs or not). When there is an 'insert into', however, a transaction is needed because if the exec fails then the insert should fail also. Is this right? Show quote "Hugo Kornelis" wrote: > On Fri, 1 Sep 2006 03:57:01 -0700, rocket salad wrote: > > >Picture this setup: 2 SQL Servers, S1 and S2 are physically seperate. On S1 > >is sat a stored procedure SP1 and on S2 is sat another, SP2. > > > >SP2 attempts to insert into a temprary table from SP1 using the following > >syntax: > > > >insert into #temptable > >exec opendatasource('SQLOLEDB','blah blah').db.dbo.SP1 > > > >When ran an error is returned stating that a distributed transaction > >couldn't be started. However, in no place in any of the code which is running > >is a transaction exlpicitly started (no begin or end statements and > >definitely no begin transaction statements). In fact this line on it's own > >executed in isolation attempts to start a distributed transaction. > > Hi rocket, > > You did start a transaction - each data modification statement always > runs inside a transaction. If you never explicitly start one, then the > execution of the INSERT statement will implicitly start a transaction. > And the SET IMPLICIT_TRANSACTION options determines if it's also > implicitly committed at the end of the statement. > > Since SQL Server on server S2 has no idea if SP1 is going to do any > modification or not, it has to assume the worst and hence will expect > the possibility of data modifications from S2. > > >If I change SP1 to a view returning the same data and use this code: > > > >insert into #temptable > >select * from opendatasource('SQLOLEDB','blah blah').db.dbo.view > > > >no transactions are started and everything runs. > > There's still a transaction started (for the insert), but there's no > distributed transaction. With a SELECT statement, SQL Server *knows* > that there's not going to be any data modification on the other server, > so the transaction can remain local. > > -- > Hugo Kornelis, SQL Server MVP > On Mon, 4 Sep 2006 01:20:03 -0700, rocket salad wrote:
>And so fails. I've done another test which was to carry out the 'exec Hi Rocket,>opendatasource' statement in isolation (without the insert into) and this >executes fine. From this, am I right in thinking that because all S2 is doing >is triggering SP1, that it doesn't care if it runs correctly or not (no >transactions have been explicitly started so there's no different processing >which needs to occur whether SP1 runs or not). When there is an 'insert >into', however, a transaction is needed because if the exec fails then the >insert should fail also. > >Is this right? Yes. A transaction is needed because the insert modifies data, and has to be rolled back in case of error. A distributed transaction is needed because the stored procedure MIGHT change data, and in case of error the changes on both servers have to be rolled back. -- Hugo Kornelis, SQL Server MVP  |
|||||||||||||||||||||||
Other interesting topics