|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Simple distributed transaction example does not workhoping that someone can help. As the simplest test I can imagine, I have added a linked server pointing to the local server by doing EXEC sp_addlinkedserver @server='pubsserver', @provider='SQLOLEDB', @srvproduct='', @datasrc='' I verify that this link works by doing SELECT au_lname, au_fname FROM pubsserver.pubs.dbo.authors However, when I wrap this in a transaction, I get the dreaded Server: Msg 7391, Level 16, State 1, Line 8 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. The exact statements I am running are BEGIN TRANSACTION SELECT au_lname, au_fname FROM pubsserver.pubs.dbo.authors COMMIT TRANSACTION I have also tried BEGIN DISTRIBUTED TRANSACTION and doing SET XACT_ABORT ON. I know that DTC is running, and I see a new transaction when I run BEGIN DISTRIBUTED TRANSACTION by itself. I am running Sql2k SP4 and Win2k SP4. Does anyone else get different behavior or know what I can change to get this to work? TIA -- Scott Nichol How to troubleshoot error 7391 that occurs when you use a linked server in
SQL Server http://support.microsoft.com/newsgroups/default.aspx AMB Show quoteHide quote "Scott Nichol" wrote: > I cannot get distributed transactions to work on a single server. I am > hoping that someone can help. > As the simplest test I can imagine, I have added a linked server pointing to > the local server by doing > > EXEC sp_addlinkedserver @server='pubsserver', @provider='SQLOLEDB', > @srvproduct='', @datasrc='' > > I verify that this link works by doing > > SELECT au_lname, au_fname > FROM pubsserver.pubs.dbo.authors > > However, when I wrap this in a transaction, I get the dreaded > > Server: Msg 7391, Level 16, State 1, Line 8 > The operation could not be performed because the OLE DB provider 'SQLOLEDB' > was unable to begin a distributed transaction. > [OLE/DB provider returned message: New transaction cannot enlist in the > specified transaction coordinator. ] > OLE DB error trace [OLE/DB Provider 'SQLOLEDB' > ITransactionJoin::JoinTransaction returned 0x8004d00a]. > > The exact statements I am running are > > BEGIN TRANSACTION > SELECT au_lname, au_fname > FROM pubsserver.pubs.dbo.authors > COMMIT TRANSACTION > > I have also tried BEGIN DISTRIBUTED TRANSACTION and doing SET XACT_ABORT ON. > I know that DTC is running, and I see a new transaction when I run BEGIN > DISTRIBUTED TRANSACTION by itself. > > I am running Sql2k SP4 and Win2k SP4. > > Does anyone else get different behavior or know what I can change to get > this to work? > > TIA > -- > Scott Nichol > > > I think Alejandro wanted to send you this link:
http://support.microsoft.com/kb/306212/en-us here is another link that might help http://support.microsoft.com/?kbid=839279 Show quoteHide quote "Alejandro Mesa" wrote: > How to troubleshoot error 7391 that occurs when you use a linked server in > SQL Server > http://support.microsoft.com/newsgroups/default.aspx > > > AMB > > "Scott Nichol" wrote: > > > I cannot get distributed transactions to work on a single server. I am > > hoping that someone can help. > > As the simplest test I can imagine, I have added a linked server pointing to > > the local server by doing > > > > EXEC sp_addlinkedserver @server='pubsserver', @provider='SQLOLEDB', > > @srvproduct='', @datasrc='' > > > > I verify that this link works by doing > > > > SELECT au_lname, au_fname > > FROM pubsserver.pubs.dbo.authors > > > > However, when I wrap this in a transaction, I get the dreaded > > > > Server: Msg 7391, Level 16, State 1, Line 8 > > The operation could not be performed because the OLE DB provider 'SQLOLEDB' > > was unable to begin a distributed transaction. > > [OLE/DB provider returned message: New transaction cannot enlist in the > > specified transaction coordinator. ] > > OLE DB error trace [OLE/DB Provider 'SQLOLEDB' > > ITransactionJoin::JoinTransaction returned 0x8004d00a]. > > > > The exact statements I am running are > > > > BEGIN TRANSACTION > > SELECT au_lname, au_fname > > FROM pubsserver.pubs.dbo.authors > > COMMIT TRANSACTION > > > > I have also tried BEGIN DISTRIBUTED TRANSACTION and doing SET XACT_ABORT ON. > > I know that DTC is running, and I see a new transaction when I run BEGIN > > DISTRIBUTED TRANSACTION by itself. > > > > I am running Sql2k SP4 and Win2k SP4. > > > > Does anyone else get different behavior or know what I can change to get > > this to work? > > > > TIA > > -- > > Scott Nichol > > > > > > Greg Larsen,
Thanks for the link. Seems that the copy & paste operation failed. AMB Show quoteHide quote "Greg Larsen" wrote: > I think Alejandro wanted to send you this link: > http://support.microsoft.com/kb/306212/en-us > > here is another link that might help > > http://support.microsoft.com/?kbid=839279 > > "Alejandro Mesa" wrote: > > > How to troubleshoot error 7391 that occurs when you use a linked server in > > SQL Server > > http://support.microsoft.com/newsgroups/default.aspx > > > > > > AMB > > > > "Scott Nichol" wrote: > > > > > I cannot get distributed transactions to work on a single server. I am > > > hoping that someone can help. > > > As the simplest test I can imagine, I have added a linked server pointing to > > > the local server by doing > > > > > > EXEC sp_addlinkedserver @server='pubsserver', @provider='SQLOLEDB', > > > @srvproduct='', @datasrc='' > > > > > > I verify that this link works by doing > > > > > > SELECT au_lname, au_fname > > > FROM pubsserver.pubs.dbo.authors > > > > > > However, when I wrap this in a transaction, I get the dreaded > > > > > > Server: Msg 7391, Level 16, State 1, Line 8 > > > The operation could not be performed because the OLE DB provider 'SQLOLEDB' > > > was unable to begin a distributed transaction. > > > [OLE/DB provider returned message: New transaction cannot enlist in the > > > specified transaction coordinator. ] > > > OLE DB error trace [OLE/DB Provider 'SQLOLEDB' > > > ITransactionJoin::JoinTransaction returned 0x8004d00a]. > > > > > > The exact statements I am running are > > > > > > BEGIN TRANSACTION > > > SELECT au_lname, au_fname > > > FROM pubsserver.pubs.dbo.authors > > > COMMIT TRANSACTION > > > > > > I have also tried BEGIN DISTRIBUTED TRANSACTION and doing SET XACT_ABORT ON. > > > I know that DTC is running, and I see a new transaction when I run BEGIN > > > DISTRIBUTED TRANSACTION by itself. > > > > > > I am running Sql2k SP4 and Win2k SP4. > > > > > > Does anyone else get different behavior or know what I can change to get > > > this to work? > > > > > > TIA > > > -- > > > Scott Nichol > > > > > > > > > BOL says
>>>> Loopback connections to the same instance of SQL Server are not supportedwhen inside an implicit or explicit transaction or distributed partitioned view. <<<< Therefore, what I was trying to do is documented as being disallowed. -- Show quoteHide quoteScott Nichol "Scott Nichol" <reply_to_newsgr***@scottnichol.com> wrote in message news:%23EL1KPwiGHA.4368@TK2MSFTNGP03.phx.gbl... > I cannot get distributed transactions to work on a single server. I am > hoping that someone can help. > As the simplest test I can imagine, I have added a linked server pointing to > the local server by doing > > EXEC sp_addlinkedserver @server='pubsserver', @provider='SQLOLEDB', > @srvproduct='', @datasrc='' > > I verify that this link works by doing > > SELECT au_lname, au_fname > FROM pubsserver.pubs.dbo.authors > > However, when I wrap this in a transaction, I get the dreaded > > Server: Msg 7391, Level 16, State 1, Line 8 > The operation could not be performed because the OLE DB provider 'SQLOLEDB' > was unable to begin a distributed transaction. > [OLE/DB provider returned message: New transaction cannot enlist in the > specified transaction coordinator. ] > OLE DB error trace [OLE/DB Provider 'SQLOLEDB' > ITransactionJoin::JoinTransaction returned 0x8004d00a]. > > The exact statements I am running are > > BEGIN TRANSACTION > SELECT au_lname, au_fname > FROM pubsserver.pubs.dbo.authors > COMMIT TRANSACTION > > I have also tried BEGIN DISTRIBUTED TRANSACTION and doing SET XACT_ABORT ON. > I know that DTC is running, and I see a new transaction when I run BEGIN > DISTRIBUTED TRANSACTION by itself. > > I am running Sql2k SP4 and Win2k SP4. > > Does anyone else get different behavior or know what I can change to get > this to work? > > TIA > -- > Scott Nichol > >
Other interesting topics
Help! Users can't connect to database
Bring back Query Analyser HOW "TOP" is interpreted?? which query is more efficent? (oppinion time!) Use of SELECT...,0 FROM Select all Rows on distinct only on one column Need help with this Calculated columns... Comparing databases ADO Connection terminates automatically after one hour |
|||||||||||||||||||||||