Replication Comparison Chart for SQL2005

Anyone know of a chart on the Microsoft Site that compares all the
Replication types for SQL 2005?
Trans Shipping, Mirroring, Publication and Subscription and all that
stuff?
Here is a pretty good one on the Dell site.
www.dell.com/downloads/global/power/ps1q06-20050325-Sankaran.pdf
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"jughead" <mike.aarset@.gmail.com> wrote in message
news:1174056604.344365.92450@.o5g2000hsb.googlegrou ps.com...
> Anyone know of a chart on the Microsoft Site that compares all the
> Replication types for SQL 2005?
> Trans Shipping, Mirroring, Publication and Subscription and all that
> stuff?
>

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 between 7.0 and 2005

Is it true that merge replication between SQL7.0 and 2005 is not supported?
I would like to setup SQL2005 as a publisher and distributor and SQL7.0 as a
subscriber.
Can someone confirm that please...
Other options in that case would be to use transactional replication or to
upgrade 7.0 to 2000 and then use merge replication.
Tom
Hi Tom,
Merge Replication in SQL Server 2005 does indeed support SQL 2005
distributor and SQL 2005 publisher with SQL 70 subscribers.
You'll want to check out the following topic in SQL Server 2005 BOL:
"Using Multiple Versions of SQL Server in a Replication Topology"
Hope this helps,
Tom
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom" wrote:

> Is it true that merge replication between SQL7.0 and 2005 is not supported?
> I would like to setup SQL2005 as a publisher and distributor and SQL7.0 as a
> subscriber.
> Can someone confirm that please...
> Other options in that case would be to use transactional replication or to
> upgrade 7.0 to 2000 and then use merge replication.
> Tom
>
>

replication and 'syntax error near E'

I use SQL2005 with bunch of stored procedures. All worked fine. Then I set u
p
push transactional replication with sqlexpress so all agent were running on
publisher. Replication ran without errors and replicated articles. After som
e
time (about 1 hour) few stored procedures failed with 'Incorrect syntax near
E' error.
When I remove replication same stored procedures work without any error.
Every time when replication is set up error occurs on same procedures.
Stored procedures work with cursor, create and update temporary tables using
EXEC statement with dinamicaly build query.
When I remove replication same stored procedures work without any error, so
I guess it's not SQL.
I havent found nothing on this on Technet, MSDN or on internet.
Thank you for any hint.Enable logging to see exactly what is causing this error. Refer to this kb
article for more info on how to enable logging.
http://support.microsoft.com/kb/312292/en-us
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"janez" <janez@.discussions.microsoft.com> wrote in message
news:18698F9A-84F2-446F-9175-0B753EA01A99@.microsoft.com...
>I use SQL2005 with bunch of stored procedures. All worked fine. Then I set
>up
> push transactional replication with sqlexpress so all agent were running
> on
> publisher. Replication ran without errors and replicated articles. After
> some
> time (about 1 hour) few stored procedures failed with 'Incorrect syntax
> near
> E' error.
> When I remove replication same stored procedures work without any error.
> Every time when replication is set up error occurs on same procedures.
> Stored procedures work with cursor, create and update temporary tables
> using
> EXEC statement with dinamicaly build query.
> When I remove replication same stored procedures work without any error,
> so
> I guess it's not SQL.
> I havent found nothing on this on Technet, MSDN or on internet.
> Thank you for any hint.|||Thanks for advice. It will be usefuul in further analysis of the cause.
By trying I found setting scheduled snapshot to cause error. Transactional
replication with only initial snapshot works without any problem.
"Hilary Cotter" wrote:

> Enable logging to see exactly what is causing this error. Refer to this kb
> article for more info on how to enable logging.
> http://support.microsoft.com/kb/312292/en-us
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "janez" <janez@.discussions.microsoft.com> wrote in message
> news:18698F9A-84F2-446F-9175-0B753EA01A99@.microsoft.com...
>
>

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

REPLICATION

DEAR ALL,

I SET UP A PUBLICATION SERVER (SQL 2005) AND A SUBSCRIPTION SERVER SQL2005 BOTH WITH SERVICE PACK. THE SETTINGS SEEM TO BE OK , AND I ALREADY SET THE TABLE WHICH I WANT TO REPLICATE. I AM USING THE TRANSACTIONAL MODE. AT THE BEGINING I COULD INSERT RECORD IN PUBLIC SERVER AND THERE WERE VISIBLE IN SUB SERVER. NEVERTHELESS WHEN I WAS TRYING TO INSERT IN THE TABLE FROM THE SUB SERVER I WAS NOT ABLE TO VIEW THE CHANGES IN PUBLISHER. ANY IDEAS PLS ?

MOREOVER, NOW I CAN NOT REPLICATE AT ALL. I DO NO KNOW IF I CHANGED ANYTHING, BUT I CAN NOT FIND IT. ANY HELP ON THIS PLS

THANKS IN ADVANCE FOR YOUR TIME

GREG

You need to use merge replication. With transactional replication you should not make changes on your subscriber - you should treat is as read only. Also check to see if your change on the subscriber has caused the distribution agent to fail.

You might want to switch to the continue on data consitency error profile to clear the problem.

Replicating twice

I have a question that google seems eerily quite on. I'm running SQL
2005 and am replicating (merge via the web) from an intermediate box
which is also running 05. The intermediate box is replicating in some
fashion (unsure since it isn't under my control) from a SQL 2K box.
My issue is that when the job executes it says "Merge completed with no
data changes processed." even though the intermediate box has updated
data on it. Is this common? Is the answer so obvious that everyone is
laughing at me now?
Thanks
Jeremiah
It should work. How is your global priority configured? You might want to
check the conflict viewer to see if these conflicts are logged.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JC" <jbzcooper@.gmail.com> wrote in message
news:1141343267.632060.296050@.u72g2000cwu.googlegr oups.com...
>I have a question that google seems eerily quite on. I'm running SQL
> 2005 and am replicating (merge via the web) from an intermediate box
> which is also running 05. The intermediate box is replicating in some
> fashion (unsure since it isn't under my control) from a SQL 2K box.
> My issue is that when the job executes it says "Merge completed with no
> data changes processed." even though the intermediate box has updated
> data on it. Is this common? Is the answer so obvious that everyone is
> laughing at me now?
> Thanks
> Jeremiah
>
|||Jeremiah,
If I understand correctly, your SQL 2005 is subscribing from an intermediate
box, which subscribes from a SQL 2K server? Please note that for merge
replication, the publisher database version has be to newer than the
subscriber version, so basically SQL Server 2005 cannot be a subscriber of a
SQL 2000 publisher.
Yi Chen
"JC" <jbzcooper@.gmail.com> wrote in message
news:1141343267.632060.296050@.u72g2000cwu.googlegr oups.com...
>I have a question that google seems eerily quite on. I'm running SQL
> 2005 and am replicating (merge via the web) from an intermediate box
> which is also running 05. The intermediate box is replicating in some
> fashion (unsure since it isn't under my control) from a SQL 2K box.
> My issue is that when the job executes it says "Merge completed with no
> data changes processed." even though the intermediate box has updated
> data on it. Is this common? Is the answer so obvious that everyone is
> laughing at me now?
> Thanks
> Jeremiah
>
|||Are there any filters on the publisher that make the data not qualify for
the subscriber?
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"JC" <jbzcooper@.gmail.com> wrote in message
news:1141343267.632060.296050@.u72g2000cwu.googlegr oups.com...
> I have a question that google seems eerily quite on. I'm running SQL
> 2005 and am replicating (merge via the web) from an intermediate box
> which is also running 05. The intermediate box is replicating in some
> fashion (unsure since it isn't under my control) from a SQL 2K box.
> My issue is that when the job executes it says "Merge completed with no
> data changes processed." even though the intermediate box has updated
> data on it. Is this common? Is the answer so obvious that everyone is
> laughing at me now?
> Thanks
> Jeremiah
>