Distribution times out...

I have a transactional replication working fine till to date. I have
the publisher on ServerA and subscriber on ServerB. The transactional
replication is running fine...
I need to get a copy of the subscriber to another server and replicate
the Publisher to that serverC while the replication on ServerB is
running.
ServerA.Publisher --> ServerB.Subscriber (runs transactional
replication)
ServerA.Publisher --> ServerC.NewSuscriber (This need to run without
impacting ServerB.Subscription). I cannot resynch the databases as It
will take days to resynch and will also kill some of my data on the
ServerB.subscriber that is not in ServerA.publisher
I went with the way they had explained on MSKB 320499. I stoped the
transactions coming into the publisher and copied a full backup of
publisher to ServerC. I created the new subscription wihout re-synch
option to ServerC. I also copied all the sp's that are needed from
ServerB.Subscriber.
When i try to enable the distribution agent between ServerA and ServerC
i get the following error..
{call sp_MSget_repl_commands(11, ?, 0, 7500000)}
Timeout expired
Timeout expired
(Source: ODBC SQL Server Driver (ODBC); Error number: S1T00)
------
The replication agent from ServerA to ServerB is running fine..! (Thank
God..!)
Can anyone help me figure out how to fix this issue between
ServerA.Publisher to ServerC.Subscriber?
Thanks in advance...!
Please can you confirm that you have the latest service pack on each server
(there were issues with "sp_MSget_repl_commands" before).
If this is not a service pack issue, try increasing the -QueryTimeOut
parameter of the distribution agent and also enable logging:
http://support.microsoft.com/?id=312292 to trap any more details for us.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul,
I have SP4 Installed on both servers. SQL 2000 Standard Edition on a
Windows 2003 Enterprise server.
Paul Ibison wrote:
> Please can you confirm that you have the latest service pack on each server
> (there were issues with "sp_MSget_repl_commands" before).
> If this is not a service pack issue, try increasing the -QueryTimeOut
> parameter of the distribution agent and also enable logging:
> http://support.microsoft.com/?id=312292 to trap any more details for us.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||OK - it's not that then
In that case try increasing the QueryTimeout parameter and do some logging
if there are any issues.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Distributed Transactions between SQL2005 and SQL2000

Hi there,

We have two servers, one (we'll call 'SERVERA') has SQL2005 running on it. The second (we'll call 'YELLOWSTEONE') is running both SQL2000 and SQL2005 on it. The SQL instances on YELLOWSTONE are 'YELLOWSTONE\SQL2000' and 'YELLOWSTONE\SQL2005'. As a linked server, I have an entry for YELLOWSTONE which then links to the SQL Server of YELLOWSTONE\SQL2000 on the server network name of YELLOWSTONE. By them selves they seem to run fine. However, if I have trigger that Runs on SERVERA to do a distributed transaction on 'YELLOWSTONE\SQL2000', I get the following error:

OLE DB provider "SQLNCLI" for linked server "YELLOWSTONE" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "YELLOWSTONE" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

Msg 2, Level 16, State 1, Line 0

Named Pipes Provider: Could not open a connection to SQL Server [2].

If You can provide me any assistance, I would greatly appreciate it. Thanks! - Eric -

Ok, Figured it out. I needed to define the Server type as 'SQL Server' instead of 'Other' and then picking the SQL Server connector. Ooops!

distributed transaction error

Hi,
I am excuting an insert query (in an update trigger) from a database
on ServerA to insert records in another database on different server,
ServerB. It was running fine until i moved it to different machine
where it is giving Distributed Transaction Error. I've narrowed down
the problem and now when i run the following query in Query Analyzer
it gives that error:
SET REMOTE_PROC_TRANSACTIONS OFF
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRAN
INSERT INTO ServerB.MyDatabase.dbo.MyTable
(Field1, Field2)
VALUES ('value1', 'value2')
COMMIT TRAN
Error is:
Server: Msg 7391, Level 16, State 1, Line 4
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].
BTW, when run only this part in Query Analyser:
INSERT INTO ServerB.MyDatabase.dbo.MyTable
(Field1, Field2)
VALUES ('value1', 'value2')
it runs fine.
Any Help'
ThanksWhat is the OS of ServerA and Server B?
======= .NETXpert ==========
url: http://www.dotnetxpert.com
eml : kevin@.dotnetxpert.com
msn: kevin025@.magicn.com
==========================
"TF" <faridt@.coned.com> wrote in message
news:ae1ce536.0402031242.381d223b@.posting.google.com...

quote:

---

> Hi,
> I am excuting an insert query (in an update trigger) from a database
> on ServerA to insert records in another database on different server,
> ServerB. It was running fine until i moved it to different machine
> where it is giving Distributed Transaction Error. I've narrowed down
> the problem and now when i run the following query in Query Analyzer
> it gives that error:
> SET REMOTE_PROC_TRANSACTIONS OFF
> SET XACT_ABORT ON
> BEGIN DISTRIBUTED TRAN
> INSERT INTO ServerB.MyDatabase.dbo.MyTable
> (Field1, Field2)
> VALUES ('value1', 'value2')
> COMMIT TRAN
> Error is:
> Server: Msg 7391, Level 16, State 1, Line 4
> 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].
> BTW, when run only this part in Query Analyser:
> INSERT INTO ServerB.MyDatabase.dbo.MyTable
> (Field1, Field2)
> VALUES ('value1', 'value2')
> it runs fine.
> Any Help'
> Thanks

---

|||> What is the OS of ServerA and Server B?

quote:

---

>

---

The OS on both ServerA and ServerB is Windows 2000 with SQL Server 2000|||maybe, I think that your problem will be solved with this article.
http://support.microsoft.com/defaul...kb;en-us;306843
If not solved with that, reply one more.
======= .NETXpert ==========
url: http://www.dotnetxpert.com
eml : kevin@.dotnetxpert.com
msn: kevin025@.magicn.com
==========================
"TF" <faridt@.coned.com> wrote in message
news:ae1ce536.0402040451.1bac44c8@.posting.google.com...

quote:

---

> The OS on both ServerA and ServerB is Windows 2000 with SQL Server 2000

---

distributed transaction error

Hi,
I am excuting an insert query (in an update trigger) from a database
on ServerA to insert records in another database on different server,
ServerB. It was running fine until i moved it to different machine
where it is giving Distributed Transaction Error. I've narrowed down
the problem and now when i run the following query in Query Analyzer
it gives that error:
SET REMOTE_PROC_TRANSACTIONS OFF
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRAN
INSERT INTO ServerB.MyDatabase.dbo.MyTable
(Field1, Field2)
VALUES ('value1', 'value2')
COMMIT TRAN
Error is:
Server: Msg 7391, Level 16, State 1, Line 4
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].
BTW, when run only this part in Query Analyser:
INSERT INTO ServerB.MyDatabase.dbo.MyTable
(Field1, Field2)
VALUES ('value1', 'value2')
it runs fine.
Any Help'
ThanksWhat is the OS of ServerA and Server B?
--
======= .NETXpert ==========url: http://www.dotnetxpert.com
eml : kevin@.dotnetxpert.com
msn: kevin025@.magicn.com
==========================
"TF" <faridt@.coned.com> wrote in message
news:ae1ce536.0402031242.381d223b@.posting.google.com...
> Hi,
> I am excuting an insert query (in an update trigger) from a database
> on ServerA to insert records in another database on different server,
> ServerB. It was running fine until i moved it to different machine
> where it is giving Distributed Transaction Error. I've narrowed down
> the problem and now when i run the following query in Query Analyzer
> it gives that error:
> SET REMOTE_PROC_TRANSACTIONS OFF
> SET XACT_ABORT ON
> BEGIN DISTRIBUTED TRAN
> INSERT INTO ServerB.MyDatabase.dbo.MyTable
> (Field1, Field2)
> VALUES ('value1', 'value2')
> COMMIT TRAN
> Error is:
> Server: Msg 7391, Level 16, State 1, Line 4
> 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].
> BTW, when run only this part in Query Analyser:
> INSERT INTO ServerB.MyDatabase.dbo.MyTable
> (Field1, Field2)
> VALUES ('value1', 'value2')
> it runs fine.
> Any Help'
> Thanks|||> What is the OS of ServerA and Server B?
>
The OS on both ServerA and ServerB is Windows 2000 with SQL Server 2000|||maybe, I think that your problem will be solved with this article.
http://support.microsoft.com/default.aspx?scid=kb;en-us;306843
If not solved with that, reply one more.
--
======= .NETXpert ==========url: http://www.dotnetxpert.com
eml : kevin@.dotnetxpert.com
msn: kevin025@.magicn.com
==========================
"TF" <faridt@.coned.com> wrote in message
news:ae1ce536.0402040451.1bac44c8@.posting.google.com...
> > What is the OS of ServerA and Server B?
> >
> The OS on both ServerA and ServerB is Windows 2000 with SQL Server 2000