Determine updated and inserted rows

I'm working on an algorithm to backup data from pocket pc sql server 2.0
database involved in an merge replication with sql server 2000 sp3 with rda
in case that replication fails from whatever reason. My question is if I can
determine on a sql server ce 2.0 table involved in merge replication what
rows have been updated or inserted form last replication in order to
transfer only those rows on backup database. I've noticed that inserted rows
have s_Generation field 0 but I have no clue about updated rows.
Any changed (inserted or updated) row in ce database which is part of a
replicated table will have the s_generation column set to zero until the
next sync. So this is just an indicator of changed rows between two
successive syncs.
thanks
gopal

Determine sequence of transactions in replication

I am running transactional replication with a push transformable
subscription. I am running into a PK error. After researching the error I
have found two transactions tied to record in the subscription database. So I
use the following to determine which transaction is getting fired first.
select * from distribution.dbo.MSrepl_commands
where publisher_database_id = 29
and article_id = 30
and xact_seqno = 0x0000235000000EFA000100000000
This returns a list of 58 transactions with the command_id and the command.
But I am unable to determine what the command is because it is not readable.
If I use
distribution.dbo.Sp_browsereplcmds
it gives me the command but not the command_id (sequence number).
How can I determine what the sequence of transactions is and read the
command?
Thanks for your time. Mike
Mike,
the code in sp_browsereplcmds is not too difficult. You could create your
own custom script using this as a template, but which includes the
command_id.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Determine Replication Folder or Data Folder via SQL?

I have an application that has to do the following
1. Disable Replication
2. Run Database Changes Script
3. Enable Replication again.
I have the scripts to enable and/or disable replication, but I was wondering
if there was anyway to retrieve the data, log, and replication folder
directories via sql so that I can use them in my replication sql script
without having to hard code these values.
L Anthony Johnson
Newbie
sp_browsesnapshotfolder @.publication = 'PublicationName' will tell you the
snapshot folder name (as a UNC).
sp_helpdb 'DBName' will tell you the name of the db.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"L Anthony Johnson" <ppsyco2@.nospamm4m3bellsouth.net> wrote in message
news:%23BsT8$McEHA.3016@.tk2msftngp13.phx.gbl...
> I have an application that has to do the following
> 1. Disable Replication
> 2. Run Database Changes Script
> 3. Enable Replication again.
> I have the scripts to enable and/or disable replication, but I was
wondering
> if there was anyway to retrieve the data, log, and replication folder
> directories via sql so that I can use them in my replication sql script
> without having to hard code these values.
> --
> L Anthony Johnson
> Newbie
>

detect replication erros

Hi,
I'm running SQL Sever 2000 SP4 and am replicating a database.
This is the scenario:
Server1 : holds the source database to be replicated (publisher)
Server2: is the one that receives the replicated data (suscriptor)
Some times it happens that the replication goes down and we do not realise
but until an user calls us.
We cannot implemet email notifications on the server2 as due to some
technical restrictions, MS Outlook cannot be installed on that server
What we want is a way that from server1 we are able to detect replication
failures and as this server has got SLQ Mail we'd (via a store procedure)
sent a notification off to the DBAs.
Any ideas?you can send mail without outlook
insert your message into "message" table,
create job in vbscript using CDO.Message and send mail over any smtp server
--
Aleksandar Grbic
MCDBA
"FJC" wrote:
> Hi,
> I'm running SQL Sever 2000 SP4 and am replicating a database.
> This is the scenario:
> Server1 : holds the source database to be replicated (publisher)
> Server2: is the one that receives the replicated data (suscriptor)
> Some times it happens that the replication goes down and we do not realise
> but until an user calls us.
> We cannot implemet email notifications on the server2 as due to some
> technical restrictions, MS Outlook cannot be installed on that server
> What we want is a way that from server1 we are able to detect replication
> failures and as this server has got SLQ Mail we'd (via a store procedure)
> sent a notification off to the DBAs.
> Any ideas?

detect replication erros

Hi,
I'm running SQL Sever 2000 SP4 and am replicating a database.
This is the scenario:
Server1 : holds the source database to be replicated (publisher)
Server2: is the one that receives the replicated data (suscriptor)
Some times it happens that the replication goes down and we do not realise
but until an user calls us.
We cannot implemet email notifications on the server2 as due to some
technical restrictions, MS Outlook cannot be installed on that server
What we want is a way that from server1 we are able to detect replication
failures and as this server has got SLQ Mail we'd (via a store procedure)
sent a notification off to the DBAs.
Any ideas?you can send mail without outlook
insert your message into "message" table,
create job in vbscript using CDO.Message and send mail over any smtp server
Aleksandar Grbic
MCDBA
"FJC" wrote:

> Hi,
> I'm running SQL Sever 2000 SP4 and am replicating a database.
> This is the scenario:
> Server1 : holds the source database to be replicated (publisher)
> Server2: is the one that receives the replicated data (suscriptor)
> Some times it happens that the replication goes down and we do not realise
> but until an user calls us.
> We cannot implemet email notifications on the server2 as due to some
> technical restrictions, MS Outlook cannot be installed on that server
> What we want is a way that from server1 we are able to detect replication
> failures and as this server has got SLQ Mail we'd (via a store procedure)
> sent a notification off to the DBAs.
> Any ideas?sql

Detailed Procedure for implementing the SQL Server Replication

Can I have a detailed step to step guide for implementing the SQL Server
Replication.
Note : I am using SQL 2000 Server Ent. Edition. on Win2003 Server Platform
Shanthi
There are some books on my website (www.replicationanswers.com) and books on
line is also a good source. Apart from that, you might want to look here at
these basic guides: http://www.mssqlcity.com/Articles/Replic/Replic.htm
HTH,
Paul Ibison

Detaching databse to move to another location on the same server

Hi,
I am trying to detach a database to move to another locaion on the same
machine
The database has replication. I get the following message:
Cannot detach database while being replicated.
Any Idea how to detach database without removing replication.
Thanks
Have you tried a backup and restore method instead ?
"Paul k" <Paul k@.discussions.microsoft.com> wrote in message
news:2B485CBB-DA5D-4E7A-BB8F-1F5EF9ADC5EE@.microsoft.com...
> Hi,
> I am trying to detach a database to move to another locaion on the same
> machine
> The database has replication. I get the following message:
> Cannot detach database while being replicated.
> Any Idea how to detach database without removing replication.
> Thanks
>
|||you can stop MSSQL Server, and then copy the files to the other server, Then
re attach them there.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul k" <Paul k@.discussions.microsoft.com> wrote in message
news:2B485CBB-DA5D-4E7A-BB8F-1F5EF9ADC5EE@.microsoft.com...
> Hi,
> I am trying to detach a database to move to another locaion on the same
> machine
> The database has replication. I get the following message:
> Cannot detach database while being replicated.
> Any Idea how to detach database without removing replication.
> Thanks
>

Detaching and Attaching a database

Hi:
I need to detach the databse to change its location and then attach again.
If this database is a publisher in a replication, will detaching and
attaching affect anything? I don't think so but i just want to confirm it.
Thanks
You must remove all publications from the database and unpublish the
database to detach it.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:98E2311A-E92D-4041-ABDC-0ABA37C39D78@.microsoft.com...
> Hi:
> I need to detach the databse to change its location and then attach again.
> If this database is a publisher in a replication, will detaching and
> attaching affect anything? I don't think so but i just want to confirm
> it.
> Thanks
|||Thanks for replying. Can you please tell me how do I remove all publications
from the database and unpublish the database it? And after I attach it, how
do I put all all the publications back and publish the database again. Can
you please give me some instructions? Actually I am new to replication.
Thanks
Sohail
"Hilary Cotter" wrote:

> You must remove all publications from the database and unpublish the
> database to detach it.
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:98E2311A-E92D-4041-ABDC-0ABA37C39D78@.microsoft.com...
>
>

Detaching a replication databse

I am trying to detatch a database that is set up as a publisher. I have desabled the replication tasks on both the publisher and subscriber db and am still unable to detach the db. Any ideas?

The image below is an example of the message I receive. I have tried after hours so at that time the users are 0.

Thanks.Usually when I get these it is either residual connections from apps the end-users have not shut down, or connections from developers using EM or QA that have not been disconnected, or sometimes even a connection in EM or QA from a session on the server itself.

Have everyone log out completely from the connecting app as well as EM and QA.|||Kindly do not post multiple (Duplicate) posts, refer to this http://www.dbforums.com/t989046.html link to follow.

Detaching a replication databse

I am trying to detatch a database that is set up as a publisher. I have desabled the replication tasks on both the publisher and subscriber db and am still unable to detach the db. Any ideas?

The image below is an example of the message I receive. I have tried after hours so at that time the users are 0.

Thanks.You still have active connection to the database, that's why you can't detach the database. You also need to completely uninstalled the replication. As shown on the database properties, the database still replication enabled.|||Originally posted by joejcheng
You still have active connection to the database, that's why you can't detach the database. You also need to completely uninstalled the replication. As shown on the database properties, the database still replication enabled.

As stated, the image just shows the current message. After hours I have 0 connections but I still receive the replication in progress message.|||To confirm on replication status run sp_removedbreplication on the involved database.

Detach db file with replication on

I cannot detach a db because replication is enabled . I wanted to detach
this published db so that I can move the files to another drive .
But at the same time, dont want to drop replication to do this. Is there a
way to be able to detach the db without dropping replication . ?
Hassan,
you could use backup and restore, however if the new server name is
different to the original, practically speaking, you'll end up having to
recreate the publications.
HTH,
Paul Ibison, SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Backup and restore may be a good option.. I can use the with move option and
do it.. Thanks..
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ep2Ot2MLFHA.3064@.TK2MSFTNGP12.phx.gbl...
> Hassan,
> you could use backup and restore, however if the new server name is
> different to the original, practically speaking, you'll end up having to
> recreate the publications.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>