replication error on script

I am trying to replicate a database between 2 servers...I
have others running fine between these servers so I know
user and password are fine...I get this error everytime I
try
Error:
The schema script '\\CORPSQL\C$\Program Files\Microsoft
SQL
Server\MSSQL\ReplData\unc\CORPSQL_IRIMSAdmin_IRIMSAdmin\200
30923132049\ChangeUserPassword_316.sch' could not be
propagated to the subscriber. The step failed.
Here is the code:
SET QUOTED_IDENTIFIER ON
GO
drop procedure [dbo].[ChangeUserPassword]
GO
CREATE PROCEDURE [dbo].[ChangeUserPassword]
@.UserID int,
@.NewPassword varchar(50)
AS
DECLARE @.PasswordHistory int
DECLARE @.PasswordCount int
DECLARE @.Password varchar(50)
DECLARE @.DuplicateFlag bit
DECLARE @.ErrorMessage varchar(200)
DECLARE Password_Cursor CURSOR FOR
SELECT Password
FROM tblHistoryPasswords
WHERE UserRef = @.UserID
ORDER BY CreatedDateTime DESC
--SELECT @.USERID=USERID FROM USERDATA WHERE NTLOGON LIKE
@.NTLogon
SET @.DuplicateFlag=0
SET @.PasswordCount=0
SELECT @.PasswordHistory = CAST (varvalue AS int) FROM
tblsystemvariables WHERE varid = 'PasswordHistory'
OPEN Password_Cursor
FETCH NEXT FROM Password_Cursor
INTO @.Password
WHILE @.@.FETCH_STATUS = 0 AND @.DuplicateFlag = 0
BEGIN
SET @.PasswordCount = @.PasswordCount + 1
IF @.PasswordCount <= @.PasswordHistory
BEGIN
IF @.Password = @.NewPassword
SET @.DuplicateFlag=1
END
IF @.DuplicateFlag=0
BEGIN
IF @.PasswordCount = @.PasswordHistory
BEGIN
UPDATE tblHistoryPasswords
SET Password = @.NewPassword, CreatedDateTime=GetDate()
WHERE CURRENT OF Password_Cursor
END
IF @.PasswordCount > @.PasswordHistory
BEGIN
DELETE FROM tblHistoryPasswords
WHERE CURRENT OF Password_Cursor
END
FETCH NEXT FROM Password_Cursor
INTO @.Password
END
END
CLOSE Password_Cursor
DEALLOCATE Password_Cursor
IF @.DuplicateFlag=1
BEGIN
--raise error
SET @.ErrorMessage = "IRIMS Admin DB: User password has to
be different from last " + CAST(@.PasswordHistory AS varchar
(2)) + " passwords."
RaisError (@.ErrorMessage,16,1)
RETURN 1
END
ELSE
IF @.PasswordCount < @.PasswordHistory OR @.PasswordHistory
= 0
INSERT INTO tblHistoryPasswords (UserRef,Password)
VALUES (@.UserID,@.NewPassword)
RETURN 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOHi Jamie,
Thanks for your feedback. Providing definite problem and detailed
description of the problem helps us troubleshoot the issue more
efficiently. As I understand, the SQL Server version is SQL Server 2000 Sp1
and you have a merge replication with 2 machines. One is as publication and
distribution, the other is as pull subscription. Do you mean the error
occurs when you configure the subscription? In other words, you did not
finish setting up your subscription, or was it that applying a snapshot to
a subscriber failed with this error?
What is your type of your pull subscription, named subscription or
anonymous subscription? You can check it in the General tab in the property
dialog of the subscription.
What is the kind of snapshot, FTP or UNC? You can check it in the Snapshot
file location tab in the property dialog of the subscription.
Based on my research, this can occur if the publication includes a stored
procedure that references fields that do not exist in the underlying table.
Please check to see who the owner of the stored procedure is and who the
underlying tables tblsystemvariables and tblHistoryPasswords are. If they
are different, please specifying the owner of the underlying table in the
stored procedure. Please try to run the snapshot agent for the publication
by right-clicking it and then clicking Start on the shortcut menu.
Please let me know if this solves your problem, or if you would like
further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||work around...I am only merging the tables...the sp are
not neccessary on this database...merging the tables seem
to be working fine....
Quick question
when setting up the merge part it ask if you want to
do vertical or horizontal, is it best practices to select
both or just one of them..if one of them, which one?|||Hi Jamie,
Thanks for your feedback. I am glad to hear that this problem has been
resolved.
Regarding your further question, horizontal and vertical enable you to
create partitions of data to be published. The selection of them depends on
you requirements. By filtering the published data, you can:
1. Minimize the amount of data sent over the network.
2. Reduce the amount of storage space required at the Subscriber.
3. Customize publications and applications based on individual Subscriber
requirements.
4. Avoid or reduce conflicts because the different data partitions can be
sent to different Subscribers (no two Subscribers will be updating the same
data values).
Row and column filters can be used with snapshot, transactional, and merge
publications. Row filters use the WHERE clause of a SQL statement and
restrict the rows included in a publication based on specific criteria.
Column filters restrict the columns that are included in a publication.
For more information regarding this issue, please refer to the following
articles on SQL Server Books Online.
Topic:" Filtering Published Data"
Topic:" Filter Data"
Thank for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Replication conflict resolver

Hi Everyone!
Here another post on replication.

When conflict occur, I want the user to be able to select witch row to keep and witch to delete.

I've look for system stored procedure, that could help me do the resolution but I've founded nothing.

So I've thought that I could do it by hand, with UPDATE/DELETE query
Take the row in the %TABLE_NAME%_Conflict table and copy it into the real table, than delete the row.

I this the good way of doing it?

Is there any other way?

Thanks !No one, here have a custom conflict resolver ?|||

Hi Zakary,

It sounds like you are wanting to replicate - excuse the pun - the interactive conflict resolver.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/repref9/html/56cb122d-9b1e-4f79-81de-0118023d1004.htm

and ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/172c60c7-f605-4eb5-b185-54ae9e9d3c60.htm

|||

Thanks For your reply!

Those help topic, is about SQL 2005 and I’m using MSDE(SQL 2000).

I don't want to use SQL Conflict resolver, but a custom VB .NET Front-end, to resolve conflict. And I was wondering if my approach is appropriate or not.

Thanks

|||

Here is a sql 2000 link.

http://msdn2.microsoft.com/en-us/library/aa179421(SQL.80).aspx

I was thinking that although this might not give you exactly what you want you might be able to copy how it works functionally.

Cheers, James

Replication between two servers without db_owner

Hello there
I have destination database which i'm not db_owner on it but guest
The user i'm using to access between two servers is:
1. Source is connected by linked server to destination
2. on the destination none of the servers roles are active for the user i'm
trying to access
3. the user has only access to one of my databases whith permittion of:
db_public, db_ddladmin
, db_datareader, db_datawriter
What else i need to use replication between two sides?
You'll need rights to create tables. There are two system tables which are
necessary and you should be able to precreate these using an account in the
ddl_admin role.
This is a push subscription right? What version of SQL Server is running on
the publisher and subscriber?
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
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uu6vAScGGHA.3864@.tk2msftngp13.phx.gbl...
> Hello there
> I have destination database which i'm not db_owner on it but guest
> The user i'm using to access between two servers is:
> 1. Source is connected by linked server to destination
> 2. on the destination none of the servers roles are active for the user
> i'm trying to access
> 3. the user has only access to one of my databases whith permittion of:
> db_public, db_ddladmin
> , db_datareader, db_datawriter
> What else i need to use replication between two sides?
>
|||Thankes Hilary.
Now the replication succedded on the other side. In fact it gave me db_owner
to do that.
The problem now is that it create new tables what replicated with the
username owner that i used to access the subscriber, instead of entering the
data to my existing databses with dbo owners.
How can i cause the subscriber to transfare data to the original tables with
dbo owners?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e$6nsyfGGHA.1180@.TK2MSFTNGP09.phx.gbl...
> You'll need rights to create tables. There are two system tables which are
> necessary and you should be able to precreate these using an account in
> the ddl_admin role.
> This is a push subscription right? What version of SQL Server is running
> on the publisher and subscriber?
> --
> 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
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:uu6vAScGGHA.3864@.tk2msftngp13.phx.gbl...
>
|||Right click on your publication and select properties, click on the articles
tab, and select the browse button to the right of each table, in the general
tab, specify destination table owner name to be dbo.
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
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%23xLlJkCHGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Thankes Hilary.
> Now the replication succedded on the other side. In fact it gave me
> db_owner to do that.
> The problem now is that it create new tables what replicated with the
> username owner that i used to access the subscriber, instead of entering
> the data to my existing databses with dbo owners.
> How can i cause the subscriber to transfare data to the original tables
> with dbo owners?
> --
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e$6nsyfGGHA.1180@.TK2MSFTNGP09.phx.gbl...
>
|||Thankes A lot hilary
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u6j6N8EHGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Right click on your publication and select properties, click on the
> articles tab, and select the browse button to the right of each table, in
> the general tab, specify destination table owner name to be dbo.
> --
> 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
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:%23xLlJkCHGHA.1032@.TK2MSFTNGP11.phx.gbl...
>

replication and user triggers

Hello,
The SQL Server manual states you need special handling
when you have a user trigger (at publisher only in my
config) and immediate updating:
"If both the user-defined trigger and the immediate
updating trigger apply an update to the same row and you
have not included a subroutine for special case handling,
the transaction could terminate. Without special handling,
the update process continues in a loop with each trigger
update firing the other trigger until the maximum nesting
level (32) is reached and the transaction terminates. "
This is my problem. MS gives a solution in the online
manual, which I cannot get to work:
DECLARE @.retcode int, @.trigger_op char(10)
EXEC @.retcode = sp_check_for_sync_trigger @.table_id,
@.tablename sysname, @.trigger_op OUTPUT
IF @.retcode = 1 RETURN
A sample would be much appreciated, as the help does not
include the @.tablename parameter
for "sp_check_for_sync_trigger".
Tx
Todd
Todd,
I think this documentation is incorrect (as it is in a page on BOL -
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\rep
lsql.chm::/reploptions_4pdf.htm).
In the code for the system procedure you're calling, the correct argument
list is:
create proc sp_check_for_sync_trigger
(
@.tabid int,
@.trigger_op char(10) = NULL OUTPUT
)
You can get the tabid using the object_id() function. The output argument
returns ins, upd or del, depending on where this is called from.
HTH,
Paul Ibison
|||Paul, thank you.

>--Original Message--
>Todd,
>I think this documentation is incorrect (as it is in a
page on BOL -
>mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%
20Server\80\Tools\Books\rep
>lsql.chm::/reploptions_4pdf.htm).
>In the code for the system procedure you're calling, the
correct argument
>list is:
>create proc sp_check_for_sync_trigger
>(
> @.tabid int,
> @.trigger_op char(10) = NULL OUTPUT
>)
>You can get the tabid using the object_id() function. The
output argument
>returns ins, upd or del, depending on where this is
called from.
>HTH,
>Paul Ibison
>
>.
>
|||Hello,
I'm still having problems with the trigger continually
calling itself. I know this has something to do with
replication, because the triggers work fine before
enabling replication. I have a set of nested triggers, one
on a BANNER_TAB table, and the second on a BANNER_PARENT
table. The BANNER_TAB update trigger causes the
BANNER_PARENT trigger to fire, but then BANNER_PARENT gets
caught in a continous loop until it reaches the max 32
loops. The BANNER_PARENT update trigger is updating a
field that is part of the primary key. Here's my triggers,
any suggestions?
/*--*/
create trigger smis.tU_BANNER_TAB on smis.BANNER_TAB after
update NOT FOR REPLICATION
as
begin
SET NOCOUNT ON --required due to bug on VB recordset
update -- see Microsoft knowledgebase article 294160
/*check to see if replication immediate updating trigger
has fired the user trigger, if so, exit*/
DECLARE @.retcode int, @.table_id int, @.trigger_op char(10)
select @.table_id = object_id('BANNER_PARENT')
print 'BEGIN BANNER_TAB UPDATE'
print 'table_id = ' + cast(@.table_id as char(22))
EXEC @.retcode = sp_check_for_sync_trigger @.table_id,
@.trigger_op OUTPUT
print 'retcode = ' + CAST(@.retcode AS char(4))
print 'trigger_op = ' + @.trigger_op
IF @.retcode = 1 RETURN
/*start user trigger*/
declare @.numrows integer
declare @.old_banner_key integer
declare @.new_banner_key integer
select @.old_banner_key=banner_key from deleted
select @.new_banner_key=banner_key from inserted
if @.new_banner_key <> @.old_banner_key
begin
begin transaction
print 'Updating TRADE table banner key'
update smis.trade set banner_key=@.new_banner_key
where banner_key=@.old_banner_key
print 'Updating SITE_PRIORITY table banner key'
update smis.site_priority set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
print 'Updating DOTSUMMARY table banner key'
update smis.dotsummary set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
print 'Updating BAN_ATT table banner key'
update smis.ban_att set banner_key=@.new_banner_key
where banner_key=@.old_banner_key
print 'Updating ANALOGUE table banner key'
update smis.analogue set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
print 'Updating BANNER_PARENT table banner key'
update smis.banner_parent set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
print 'Updating STORE_MEASURE table banner key'
update smis.store_measure set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
print 'Updating PROPOSED table banner key'
update smis.proposed set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
print 'Updating ANA_STATUS table banner key'
update smis.ana_status set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
print 'Updating CONTRACT table banner key'
update smis.contract set
banner_key=@.new_banner_key where banner_key=@.old_banner_key
commit transaction
end
if update(date_open)
begin
begin transaction
--if (select ban_status from deleted) = 'ACT'
--begin
-- synchronize first store_measure record with
date_open
if (select count(*) from smis.store_measure as
sm, inserted as i
where sm.banner_key=i.banner_key and
sm.date_updat=
(select min(date_updat)from
smis.store_measure
where banner_key=i.banner_key) and
sm.date_updat=i.date_open) = 0
begin
print 'Updating STORE_MEASURE table for
date_open'
update smis.STORE_MEASURE set
date_updat=b.date_open
from smis.store_measure sm,
smis.banner_tab b, inserted as i
where b.banner_key=i.banner_key
and b.banner_key=sm.banner_key
and sm.date_updat=(select min(date_updat)
from smis.store_measure
where banner_key=i.banner_key)
end
-- synchronize date_updat in banner_parent
if (select count(*)from smis.banner_parent as
bp, inserted as i
where bp.banner_key=i.banner_key and
bp.ban_parent_dt=
(select min(ban_parent_dt) from
smis.banner_parent
where banner_key=i.banner_key) and
bp.ban_parent_dt=i.date_open) = 0
begin
print 'Updating BANNER_PARENT table for
date_open'
update smis.banner_parent set
ban_parent_dt=b.date_open,date_updat=getdate()
from smis.banner_parent bp,banner_tab
b,inserted i
where b.banner_key=i.banner_key
and b.banner_key=bp.banner_key
and bp.ban_parent_dt=(select min
(ban_parent_dt) from smis.banner_parent
where banner_key=i.banner_key)
end
--end
commit transaction
end
print 'END BANNER_TAB UPDATE'
end
GO
/*--*/
create trigger smis.tU_BANNER_PARENT on smis.BANNER_PARENT
for update NOT FOR REPLICATION
as
begin
SET NOCOUNT ON --required due to bug on VB recordset
update -- see Microsoft knowledgebase article 294160
/*check to see if replication immediate updating trigger
has fired the user trigger, if so, exit*/
DECLARE @.retcode int, @.table_id int, @.trigger_op char(10)
select @.table_id = object_id('BANNER_PARENT')
print 'BEGIN BANNER_PARENT UPDATE'
print 'nest level = ' + cast(@.@.nestlevel as char(4))
print 'table_id = ' + cast(@.table_id as char(22))
EXEC @.retcode = sp_check_for_sync_trigger @.table_id,
@.trigger_op OUTPUT
print 'retcode = ' + CAST(@.retcode AS char(4))
print 'trigger_op = ' + @.trigger_op
IF @.retcode = 1 RETURN
/*start user trigger*/
declare @.cnt as smallint
-- test if the original date_updat is equal to the first
entry date_updat
select @.cnt = count(*) from smis.banner_parent bp
inner join inserted i on
bp.banner_key=i.banner_key where
bp.ban_parent_dt=(select min(ban_parent_dt) from
smis.banner_parent
where banner_key=i.banner_key) and
bp.ban_parent_dt=i.ban_parent_dt
if (@.cnt = 1)
begin
print 'There is only one parent company record --
resetting it to match the banner date open'
update smis.banner_parent set
ban_parent_dt=b.date_open
from smis.banner_parent bp, smis.banner_tab b,
inserted i2
where b.banner_key=bp.banner_key
and b.banner_key=i2.banner_key
and bp.ban_parent_dt=i2.ban_parent_dt
end
print 'END BANNER_PARENT UPDATE'
end
GO
|||Todd,
my suspicion is that there is an error in the stored procedure provided:
sp_check_for_sync_trigger. This procedure will work on the subscriber but
not the publisher. Even though it works on hte subscriber, the maximum
nesting level is reached on the publisher so the update still fails.
I have just tested this. In the code I noticed that it looks for names
starting with 'trg_MSsync_upd'; this type of trigger name applies to the
subscriber, but on the publisher the name is more like
"sp_MSsync_upd_trig_tTrigger_". I believe you'll have to create your own
version of this procedure and call it yourself to have this work. This is
actually quite easy if you look at the code, as you just need another three
queries built like the three provided, but with modified trigger names.
HTH,
Paul Ibison

Replication and User Defined Trigger

Hi, all:
We have 5 databases which contains invoice and invoiceHistory tables.
We have an update and insert trigger on invoice table to record every
modification into InvoiceHistory table. The invoiceHistory table is not
read only, it has some fields that the end user can edit (comments,
date ...etc). At the same time, we using scheduled snapshots every 5
minutes to 'refresh' the table at the suscribers. The problem is when
the replicate server replicate table invoice to a subscribe, the
trigger on invoice table will be triggered and update/insert
InvoiceHistory table in the subscribe. Then when the replicate server
replicate table InvoiceHistory, we will get duplicate record in
InvoiceTable, one is from Invoice table trigger and another is from
InvoiceHistory replicate.
If we do not replicate InvoiceHistory table, then those fields that end
user updated can not be sync to subscribes.
Can I disable the trigger on invoice table when replication?
Does anybody there have a better idea how to do something like this?
ThanksCheck for the NOT FOR REPLICATION option. If you put it, trigger will not be
fired for replication.
MC
<rockdale.green@.gmail.com> wrote in message
news:1143418413.367012.182670@.v46g2000cwv.googlegroups.com...
> Hi, all:
> We have 5 databases which contains invoice and invoiceHistory tables.
> We have an update and insert trigger on invoice table to record every
> modification into InvoiceHistory table. The invoiceHistory table is not
> read only, it has some fields that the end user can edit (comments,
> date ...etc). At the same time, we using scheduled snapshots every 5
> minutes to 'refresh' the table at the suscribers. The problem is when
> the replicate server replicate table invoice to a subscribe, the
> trigger on invoice table will be triggered and update/insert
> InvoiceHistory table in the subscribe. Then when the replicate server
> replicate table InvoiceHistory, we will get duplicate record in
> InvoiceTable, one is from Invoice table trigger and another is from
> InvoiceHistory replicate.
>
> If we do not replicate InvoiceHistory table, then those fields that end
> user updated can not be sync to subscribes.
> Can I disable the trigger on invoice table when replication?
> Does anybody there have a better idea how to do something like this?
>
> Thanks
>

Replication and User Defined Trigger

Hi, all:
We have 5 databases which contains invoice and invoiceHistory tables.
We have an update and insert trigger on invoice table to record every
modification into InvoiceHistory table. The invoiceHistory table is not
read only, it has some fields that the end user can edit (comments,
date ...etc). At the same time, we using scheduled snapshots every 5
minutes to 'refresh' the table at the suscribers. The problem is when
the replicate server replicate table invoice to a subscribe, the
trigger on invoice table will be triggered and update/insert
InvoiceHistory table in the subscribe. Then when the replicate server
replicate table InvoiceHistory, we will get duplicate record in
InvoiceTable, one is from Invoice table trigger and another is from
InvoiceHistory replicate.
If we do not replicate InvoiceHistory table, then those fields that end
user updated can not be sync to subscribes.
Can I disable the trigger on invoice table when replication?
Does anybody there have a better idea how to do something like this?
Thanks
Check for the NOT FOR REPLICATION option. If you put it, trigger will not be
fired for replication.
MC
<rockdale.green@.gmail.com> wrote in message
news:1143418413.367012.182670@.v46g2000cwv.googlegr oups.com...
> Hi, all:
> We have 5 databases which contains invoice and invoiceHistory tables.
> We have an update and insert trigger on invoice table to record every
> modification into InvoiceHistory table. The invoiceHistory table is not
> read only, it has some fields that the end user can edit (comments,
> date ...etc). At the same time, we using scheduled snapshots every 5
> minutes to 'refresh' the table at the suscribers. The problem is when
> the replicate server replicate table invoice to a subscribe, the
> trigger on invoice table will be triggered and update/insert
> InvoiceHistory table in the subscribe. Then when the replicate server
> replicate table InvoiceHistory, we will get duplicate record in
> InvoiceTable, one is from Invoice table trigger and another is from
> InvoiceHistory replicate.
>
> If we do not replicate InvoiceHistory table, then those fields that end
> user updated can not be sync to subscribes.
> Can I disable the trigger on invoice table when replication?
> Does anybody there have a better idea how to do something like this?
>
> Thanks
>

Replication and User Defined Trigger

Hi, all:
We have 5 databases which contains invoice and invoiceHistory tables.
We have an update and insert trigger on invoice table to record every
modification into InvoiceHistory table. The invoiceHistory table is not
read only, it has some fields that the end user can edit (comments,
date ...etc). At the same time, we using scheduled snapshots every 5
minutes to 'refresh' the table at the suscribers. The problem is when
the replicate server replicate table invoice to a subscribe, the
trigger on invoice table will be triggered and update/insert
InvoiceHistory table in the subscribe. Then when the replicate server
replicate table InvoiceHistory, we will get duplicate record in
InvoiceTable, one is from Invoice table trigger and another is from
InvoiceHistory replicate.
If we do not replicate InvoiceHistory table, then those fields that end
user updated can not be sync to subscribes.
Can I disable the trigger on invoice table when replication?
Does anybody there have a better idea how to do something like this?
ThanksCheck for the NOT FOR REPLICATION option. If you put it, trigger will not be
fired for replication.
MC
<rockdale.green@.gmail.com> wrote in message
news:1143418413.367012.182670@.v46g2000cwv.googlegroups.com...
> Hi, all:
> We have 5 databases which contains invoice and invoiceHistory tables.
> We have an update and insert trigger on invoice table to record every
> modification into InvoiceHistory table. The invoiceHistory table is not
> read only, it has some fields that the end user can edit (comments,
> date ...etc). At the same time, we using scheduled snapshots every 5
> minutes to 'refresh' the table at the suscribers. The problem is when
> the replicate server replicate table invoice to a subscribe, the
> trigger on invoice table will be triggered and update/insert
> InvoiceHistory table in the subscribe. Then when the replicate server
> replicate table InvoiceHistory, we will get duplicate record in
> InvoiceTable, one is from Invoice table trigger and another is from
> InvoiceHistory replicate.
>
> If we do not replicate InvoiceHistory table, then those fields that end
> user updated can not be sync to subscribes.
> Can I disable the trigger on invoice table when replication?
> Does anybody there have a better idea how to do something like this?
>
> Thanks
>

Replication and triggers

Does anyone know if when applying the initial snapshot for merge replication if it fires the insert or update user written triggers?Does anyone know Yep

if it fires the insert or update user written triggers? No it does'nt|||Since I could not get an answer I went ahead and applied the snapshot. When it had completed there were no records that my triggers would have generated so I guess the answer is NO IT DOES NOT FIRE THE TRIGGERS.

Replication - distribution agents?

When distribution is scheduled to run at specific times for specific user, but that user is not connected to the server. Distribution tries to find that subscription, the error comes back:

The step was retried the requested number of times(10) without succeeding.

Where/how can I change the requested number to try two or three times.

My problem is that it takes 10-15 minutes for those re-trys and which sometimes causes overlap with the next subscription coming in.Check sp_update_jobstep in BOL, look for @.retry_attempts and @.retry_interval
The best way would be to use a PULL subscription at the subscriber instead of PUSH at the publisher.

Originally posted by cruzin
When distribution is scheduled to run at specific times for specific user, but that user is not connected to the server. Distribution tries to find that subscription, the error comes back:

The step was retried the requested number of times(10) without succeeding.

Where/how can I change the requested number to try two or three times.

My problem is that it takes 10-15 minutes for those re-trys and which sometimes causes overlap with the next subscription coming in.|||You can also identify the job in Enterprise Manager (Management->SQL Server Agent-> Jobs) then find the step and change the retry attempts there.

Originally posted by dbadelphes
Check sp_update_jobstep in BOL, look for @.retry_attempts and @.retry_interval
The best way would be to use a PULL subscription at the subscriber instead of PUSH at the publisher.

Replication

I'm trying to update a row in a subscriber, and I get an error like this:
"Another user has modified this table or view content; the row you are trying to modifiy does not longer exists in the database".
Can anybody help me to solve this problem? Thanks...Originally posted by berto00
I'm trying to update a row in a subscriber, and I get an error like this:
"Another user has modified this table or view content; the row you are trying to modifiy does not longer exists in the database".
Can anybody help me to solve this problem? Thanks...

try restarting the snapshot agent again, you have a row that perhaps exists in the subscriber and not in the publisher

regards,
-blackpearl|||Originally posted by blackpearl
try restarting the snapshot agent again, you have a row that perhaps exists in the subscriber and not in the publisher

regards,
-blackpearl

I have checked all rows (they're only 10 rows...) and they're all the same. I try to update the value of a column of this row, but then that error occurs...|||Originally posted by berto00
I have checked all rows (they're only 10 rows...) and they're all the same. I try to update the value of a column of this row, but then that error occurs...

ok

Go to the snapshot agents folder and restart your respective snapshot agent (wait till it ends creating the snapshot), then select the the subscriber in your publications folder and click on "Reinitialize".

regards,
-blackpearl|||Originally posted by blackpearl
ok

Go to the snapshot agents folder and restart your respective snapshot agent (wait till it ends creating the snapshot), then select the the subscriber in your publications folder and click on "Reinitialize".

regards,
-blackpearl

ok, I'll try it out... thanks|||Originally posted by berto00
ok, I'll try it out... thanks

I've tried what you told me, but still the same... I don't know if it's important, but the error message also includes this:
"[Microsoft][ODBC Sql Server driver][Sql Server][OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]

And the two servers (publisher and subscriber) are connected through adsl...

Replicating users and privileges...

Hi,
In my publisher database I have defined an user with restricted privileges.
This user is "used" by the user interface application to connect to the
database. How can I replicate this user and restrictions to the subscriber?
Should I script this on a post-snapshot script? Is any other automatic
solution?
Thanks in advance
Faustino Dina
If my email address starts with two 'f'
drop the first 'f' when mailing me.
Faustino,
there's no automatic solution for this, as we can't replicate system tables.
You could use :
a pre-snapshot script,
a post-snapshot script,
sp_addscriptexec,
DTS,
linked servers.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replicating User Defined Data Types

Hey guys,
Thanks so much for this great newsgroup btw, helped me hugely through doing
replication via handheld units etc.
I'm running into a bit of a problem between server 2 server replication.
Both databases have user defined data types. All the tables using them are
fine, but once the snapshot is applied to the subscriber, and the replication
is hooked up to the publisher, all the data types are converted to their base
types (char 10 etc)
Is there any way to stop this? The subscriber needs to keep the user defined
data types.
I noticed in the publication wizard there is an option that says "Keep the
existing table unchanged" but the default value for this is "DROP the
existing table and re-create it" this is all under the Snapshot tab of the
article properties.
If I set it to "Keep the existing table unchanged" will that fix my problems
or is that not the proper fix for this issue?
Thanks and take care!
Simon,
on the article propertuies at the bottom there is the option to change user
defined datatypes to base types and by default this is enabled. Also, you
can add the user defined types themselves to a prescript.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replicating Sql Server to Express Edition as backup application

Hello!

We are running a business critical client-server application that needs to have high availability to the end user.
I was wondering if it would be possible to setup a replication/data transfer from an existing Sql Server 7.0/8.0/9.0 (regular online environment running on server) TO an instance of Sql 2005 Express Edition (backup env. running on klient PC).
In case of server failure the application would redirect - accessing the local PC Express edition.

Does anyone have any input on this, and if it would be a possible alternative to use as extra standby- system. ?

Kind regards
TomasYes this is possible but there are certain exceptions.
And it depends on what kind of replication technology you are looking at.
SQL Server 2005 Books Online has more information on supported topologies.

Search for "Using Multiple Versions of SQL Server in a Replication Topology"|||

Hi Tomas.

It would appear that I have a similar type of need. See this post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=677662&SiteID=1 which describes my situation. If you've got any further info on how this is achieved please respond. I'll do the same. We were able to create the windows form app and use Sql2005 Std to maintain a 'gold' standard db on our publisher/distributor machine. We have to replace our db daily with an entirely new dataset that is sent to us via a foreign host. We take that data an bulk copy it into our db. Then, we setup a snapshot replication scenario, installing SqlExpress w/ Advance Tools on the subscriber machine.

We install our offline viewer windows smart client application on the subscriber, and are able to bring in a view of the needed data. However, we need this view to be available offline, for use when the prefferred application is unavailable or when the network is down. At this point, if the network is down our replicated db is not available either. So, I'm lost with the way I've got it working now. I'm not sure if it is a setup issue or if I've misunderstood how replication works. At any rate, our need appears to be similar in scope to yours. I'll check back here, and on my post to update the status.

Right now, we are examining our setup of the replication subscription and whether there is a way to achieve the goal desired, or if we have to employ a bulk copy insert into a local database for our subscriber downtime usage.

|||

Hi,

You might want to talk to us about a product that we sell (WANSync) which provides real-time asynchronous data replication, full tree replication topologies to protect from server and site failures. Should an SQL database server fail for any reason, a geographically remote SQL replica takes it's place and will allow you to keep working.

If you want more information or want to trial this solution for free, please email me at paul.maziere@.dataplex-systems.com

Regards, Paul.

Replicating Sql Server to Express Edition as backup application

Hello!

We are running a business critical client-server application that needs to have high availability to the end user.
I was wondering if it would be possible to setup a replication/data transfer from an existing Sql Server 7.0/8.0/9.0 (regular online environment running on server) TO an instance of Sql 2005 Express Edition (backup env. running on klient PC).
In case of server failure the application would redirect - accessing the local PC Express edition.

Does anyone have any input on this, and if it would be a possible alternative to use as extra standby- system. ?

Kind regards
TomasYes this is possible but there are certain exceptions.
And it depends on what kind of replication technology you are looking at.
SQL Server 2005 Books Online has more information on supported topologies.

Search for "Using Multiple Versions of SQL Server in a Replication Topology"|||

Hi Tomas.

It would appear that I have a similar type of need. See this post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=677662&SiteID=1 which describes my situation. If you've got any further info on how this is achieved please respond. I'll do the same. We were able to create the windows form app and use Sql2005 Std to maintain a 'gold' standard db on our publisher/distributor machine. We have to replace our db daily with an entirely new dataset that is sent to us via a foreign host. We take that data an bulk copy it into our db. Then, we setup a snapshot replication scenario, installing SqlExpress w/ Advance Tools on the subscriber machine.

We install our offline viewer windows smart client application on the subscriber, and are able to bring in a view of the needed data. However, we need this view to be available offline, for use when the prefferred application is unavailable or when the network is down. At this point, if the network is down our replicated db is not available either. So, I'm lost with the way I've got it working now. I'm not sure if it is a setup issue or if I've misunderstood how replication works. At any rate, our need appears to be similar in scope to yours. I'll check back here, and on my post to update the status.

Right now, we are examining our setup of the replication subscription and whether there is a way to achieve the goal desired, or if we have to employ a bulk copy insert into a local database for our subscriber downtime usage.

|||

Hi,

You might want to talk to us about a product that we sell (WANSync) which provides real-time asynchronous data replication, full tree replication topologies to protect from server and site failures. Should an SQL database server fail for any reason, a geographically remote SQL replica takes it's place and will allow you to keep working.

If you want more information or want to trial this solution for free, please email me at paul.maziere@.dataplex-systems.com

Regards, Paul.