Replication Error of MS SQL server2k

Distribution Agents come with a error and stop between two SQL2000 server.
The error messages is:

The query processor could not produce a query plan from the optimizer
because a query cannot update a text, ntext, or image column and a
clustering key at the same time. (Source: Some Server (Data source); Error number:
8629)

How can I fix it?Is it transactional replication?
If yes - and you are using Identity fields (with not for replication) then check prsp_MSupd_**** stored procedures on subscriber and remove updating Identity field.

Replication between SQL2000 and SQL2005

I have a distributor setup on SQL Server 2005 (9.0.3042) and am trying to create a publication on SQL Server 2000 (8.0.2040) which receives the following error in my production environment:

Msg 8526, Level 16, State 2, Procedure sp_addpublication, Line 802

Cannot go remote while the session is enlisted in a distributed transaction that has an active savepoint.

The interesting part of this equation is that I was able to get this to work without error in my DEV (development) environment and well as my QAT (test) environment. This end result was that my distributor was SQL 2005, my publisher was SQL 2000 and my pull subscriber was SQL 2005. I have been diligently comparing our production environment to my other environment and have yet to find differences.

Has anyone else seen an error similiar to this? Any insight would be appreciated.

Thanks in advance,

Max

http://support.microsoft.com/kb/295027

HTH

Vishal

|||I'm having the same problem, and it's persisting even after setting the 8599 trace on. Were you able to get yours to work? If so, how?
sql

replication between SQL 2000 and 2005?

We currently have a SQL2000 replication in place and we're considering
moving one of the servers to SQL2005 x64.
is 2000/2005 replication interoperable?.
is the x64 2005 different in handling replication or is it interoperable
with 32bit versions?(be it 2005 x64 to 2005 32b or 2000 32b)
thanks in advance
Guillermo,
for the question about using different versions, please see:
http://msdn2.microsoft.com/en-us/library/ms143241(en-US,SQL.90).aspx.
For the other part, a MS developer recently pointed out that 64-bit and
32-bit are nearly interoperable, but as the Microsoft Jet engine is not
supported, Microsoft Jet push subscriptions for merge replication are not
supported. Unless the subscriber provides a 64-bit ODBC or OLE DB driver,
transactional or snapshot push subscriptions for ODBC or OLE DB subscribers
are not supported. Because of the unavailability of 64-bit Data
Transformation Services (DTS), transformable push subscriptions are not
supported.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||if transactional and push replication aren't supported, then what
replication methods do i havce left?
i'm not using jet replication, but push between remote servers across a VPN
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ORH3Utc9FHA.3208@.TK2MSFTNGP11.phx.gbl...
> Guillermo,
> for the question about using different versions, please see:
> http://msdn2.microsoft.com/en-us/library/ms143241(en-US,SQL.90).aspx.
> For the other part, a MS developer recently pointed out that 64-bit and
> 32-bit are nearly interoperable, but as the Microsoft Jet engine is not
> supported, Microsoft Jet push subscriptions for merge replication are not
> supported. Unless the subscriber provides a 64-bit ODBC or OLE DB driver,
> transactional or snapshot push subscriptions for ODBC or OLE DB
subscribers
> are not supported. Because of the unavailability of 64-bit Data
> Transformation Services (DTS), transformable push subscriptions are not
> supported.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Guillermo,
I have no way of testing this, but pull subscriptions and merge would seem
to be ok. If you give it a try please let us know how it works out.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||i've been told we're using merge subscription, so there would be no problem
with sql 2005 64 to sql2000 it seems
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23FE0coo9FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Guillermo,
> I have no way of testing this, but pull subscriptions and merge would seem
> to be ok. If you give it a try please let us know how it works out.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>

replication and upgrade of SQL7 to SQL2000

Hi all,
We are preparing to upgrade from SQL 7 to SQL 2000 and we have transactional replication running from this server to Sybase 12.5. We also have couple of batch jobs puling data from Sybase and Oracle to this SQL & server. I'd like to here your experience a
bout moving from SQL7 to SQL2000, especially with replication upgrades. What should be our major concern about the whole upgrade?
Thanks very much
I have migrated many SQL 7 to SQL 2000 servers which have been publishers/subscribers/distributors.
The correct upgrade sequence is update the distributor, publisher and then subscribers (you won't have to do this in your case).
I had no problems with the upgrades, but had a few while applying SP3 - which really is essential when running SQL 2000.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||What is actually the major change in replication of the SQL server 2000?
|||SQL Server 2000 builds upon SQL 7 replication. It offers enhanced performance and greater functionality as well as new functionality. Check out replication enhancements in BOL for more details.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Replication 2 part update in SQL2005?

SQL2000 used to send a 2 part update using sp_msdel followed by sp_msins instead of just calling sp_msupd. I thought this used to happen on tables with compostie primary keys. Does any know know if this still occurs in SQL2005 and maybe why?

I think I found the answer as deferred updates, or Singleton pairs. See http://support.microsoft.com/kb/238254/en-us

Replication 2 part update in SQL2005?

SQL2000 used to send a 2 part update using sp_msdel followed by sp_msins instead of just calling sp_msupd. I thought this used to happen on tables with compostie primary keys. Does any know know if this still occurs in SQL2005 and maybe why?

I think I found the answer as deferred updates, or Singleton pairs. See http://support.microsoft.com/kb/238254/en-us

Replicating MSDE & SQL2000

Hi All,
What is the best way to merge/replicate SQL2000 & MSDE? We have an app that
laptop users will be running locally when not connected to the network.
When they come into the office we want their local DB on their laptop which
would be MSDE to connect to the SQL2000 Server and the 2 databases to update
each other.
Any references/websites addressing this would be great.
Thanks
Lee
I think you will want to use merge replication. It was designed for just
this kind of situation. Replication is a rather broad topic. The BOL has
lots of information. I would recommend setting up a couple of test databases
and experimenting with replication, just to get familiar with it.
Jim
"Lee Trotter" <latrotter@.@.@.sympatico.ca> wrote in message
news:epJHkFLVEHA.1292@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> What is the best way to merge/replicate SQL2000 & MSDE? We have an app
that
> laptop users will be running locally when not connected to the network.
> When they come into the office we want their local DB on their laptop
which
> would be MSDE to connect to the SQL2000 Server and the 2 databases to
update
> each other.
> Any references/websites addressing this would be great.
> Thanks
> Lee
>