Replication does not work properly

Hi,
I have a problem with rows deleted at subscriber could not get deleted at
publisher in particular case where article published say table1 has row
filter like
"SELECT <published_columns> FROM [dbo].[TABLE1]
WHERE ([FILED1]='XYZ ' OR [FIELD4]= 'XYZ ') and FIELD3 in(select
[FIELD3] from [TABLE2] A where A.[FIELD1]=[FIELD1] and A.[FIELD2]=[FIELD2] )"
Rest of the Articles published with simpler row filter works perfectly.
Please Reply,
Thanking You
fatiya
are both Table1 and Table2 published? are there any constraints between teh
two tables? Are you enforcing these constraints for replication? Cascading
Updates/Deletes?
Have you used the conflict viewer to see if there are any conflicts?
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
"Fatiya" <Fatiya@.discussions.microsoft.com> wrote in message
news:6A20CE4B-7D05-4202-BCEE-79AC26E28326@.microsoft.com...
> Hi,
> I have a problem with rows deleted at subscriber could not get deleted
at
> publisher in particular case where article published say table1 has row
> filter like
> "SELECT <published_columns> FROM [dbo].[TABLE1]
> WHERE ([FILED1]='XYZ ' OR [FIELD4]= 'XYZ ') and FIELD3 in(select
> [FIELD3] from [TABLE2] A where A.[FIELD1]=[FIELD1] and
A.[FIELD2]=[FIELD2] )"
> Rest of the Articles published with simpler row filter works perfectly.
> Please Reply,
> Thanking You
> fatiya
>
>
|||Dear Hilary
Thanks for reply
Yes both table 1 and table2 are published No there are no contrainst between
them
we have not used any cascading
We are getting replicaion conflicts that the row was inserted at subscriber
but
at publisher we are getting primary key constrains.
Please advice
Fatiya
"Hilary Cotter" wrote:

> are both Table1 and Table2 published? are there any constraints between teh
> two tables? Are you enforcing these constraints for replication? Cascading
> Updates/Deletes?
> Have you used the conflict viewer to see if there are any conflicts?
> --
> 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
> "Fatiya" <Fatiya@.discussions.microsoft.com> wrote in message
> news:6A20CE4B-7D05-4202-BCEE-79AC26E28326@.microsoft.com...
> at
> A.[FIELD2]=[FIELD2] )"
>
>
sql

Replication

Hello all,
I am setup a replication from DB_A(publisher) to DB_C(subscriber), but I got
a problem after replication created
Example:
Table1 in DB_A, i tried to add a new data field. but DB_C can't see new data
field after snopshot done
Is any method can synchronizate new scheme and new table to subscriber(DB_C)
automatically?
Thanks in advanced.Schema changes are replicated in sql server 2005 (there are some
restrictions)
In sql server 2000 you can add/drop columns using system stored procedures
(sp_addmergecolumn and sp_dropmergecolumn) so they get replicated.
If this doesnt answer your question, please LMK
MC
"beachboy" <stanley@.javacatz.com> wrote in message
news:OxM2RVWLGHA.720@.TK2MSFTNGP14.phx.gbl...
> Hello all,
> I am setup a replication from DB_A(publisher) to DB_C(subscriber), but I
> got
> a problem after replication created
> Example:
> Table1 in DB_A, i tried to add a new data field. but DB_C can't see new
> data
> field after snopshot done
> Is any method can synchronizate new scheme and new table to
> subscriber(DB_C)
> automatically?
> Thanks in advanced.
>
>|||Assuming we're talking about SQL 2000, the correct stored procedures to call
are sp_repladdcolumn and sp_repldropcolumn.
Using these procs, there is no need to create a snapshot for the existing
subscribers - the column changes will occur as you synchronize.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Urgh.
Thank you for correcting me, it serves me right for not checking before
posting.
My apologies beachboy.
MC
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Assuming we're talking about SQL 2000, the correct stored procedures to
> call are sp_repladdcolumn and sp_repldropcolumn.
> Using these procs, there is no need to create a snapshot for the existing
> subscribers - the column changes will occur as you synchronize.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>|||Thank you for your informations
Sorry that I am not a professional of sql server
Yes. I am using SQL 2000 standard edition. How to I call those procedure
(sp_repladdcolumn and sp_repldropcolumn)'
from DB_A or from DB_C?
How about if new table added?
Thanks in advanced.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> ¼¶¼g©ó¶l¥ó·s»D:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Assuming we're talking about SQL 2000, the correct stored procedures to
> call are sp_repladdcolumn and sp_repldropcolumn.
> Using these procs, there is no need to create a snapshot for the existing
> subscribers - the column changes will occur as you synchronize.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>|||You should execute the procedures on publisher. When you synch databases,
changes will be propagated to the subscriber.
Adding new table to replication means you must generate new snapshot and
re-initialize subscriptions.
MC
"beachboy" <jpsteambun@.yahoo.com.hk> wrote in message
news:O4DtH8XLGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Thank you for your informations
> Sorry that I am not a professional of sql server
> Yes. I am using SQL 2000 standard edition. How to I call those procedure
> (sp_repladdcolumn and sp_repldropcolumn)'
> from DB_A or from DB_C?
> How about if new table added?
> Thanks in advanced.
>
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com>
> ¼¶¼g©ó¶l¥ó·s»D:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.gbl...
>> Assuming we're talking about SQL 2000, the correct stored procedures to
>> call are sp_repladdcolumn and sp_repldropcolumn.
>> Using these procs, there is no need to create a snapshot for the existing
>> subscribers - the column changes will occur as you synchronize.
>> Cheers,
>> Paul Ibison SQL Server MVP, www.replicationanswers.com
>> (recommended sql server 2000 replication book:
>> http://www.nwsu.com/0974973602p.html)
>>
>|||oic
for new scheme modification: execute store procedure, and corrected scheme
will send at next push
for new table on database: set a new "Push New Publcation" and then
re-initialize subscription
but do I need to remove the old snopshot?
Thanks.
"MC" <marko_culo#@.#yahoo#.#com#> ¼¶¼g©ó¶l¥ó·s»D:eS2rOAYLGHA.536@.TK2MSFTNGP09.phx.gbl...
> You should execute the procedures on publisher. When you synch databases,
> changes will be propagated to the subscriber.
> Adding new table to replication means you must generate new snapshot and
> re-initialize subscriptions.
> MC
> "beachboy" <jpsteambun@.yahoo.com.hk> wrote in message
> news:O4DtH8XLGHA.1676@.TK2MSFTNGP09.phx.gbl...
>> Thank you for your informations
>> Sorry that I am not a professional of sql server
>> Yes. I am using SQL 2000 standard edition. How to I call those procedure
>> (sp_repladdcolumn and sp_repldropcolumn)'
>> from DB_A or from DB_C?
>> How about if new table added?
>> Thanks in advanced.
>>
>> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> ¼¶¼g©ó¶l¥ó·s»D:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.gbl...
>> Assuming we're talking about SQL 2000, the correct stored procedures to
>> call are sp_repladdcolumn and sp_repldropcolumn.
>> Using these procs, there is no need to create a snapshot for the
>> existing subscribers - the column changes will occur as you synchronize.
>> Cheers,
>> Paul Ibison SQL Server MVP, www.replicationanswers.com
>> (recommended sql server 2000 replication book:
>> http://www.nwsu.com/0974973602p.html)
>>
>>
>|||Adding a new table doesn't require a reinitialization. The new snapshot will
contain just the new table (transactional) or all articles (merge) but in
either case synchronising will just propagate the new table and not all the
other articles.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||There is a difference between adding an article (table) to the existing
replication (publication) and adding a new publication to the setup. If you
add the article, run the snapshot and then synch if you left the option to
recreate objects. If you dont recreate objects then youll need to do some
more work. LMK if thats the case.
MC
"beachboy" <jpsteambun@.yahoo.com.hk> wrote in message
news:uZZu0GZLGHA.1124@.TK2MSFTNGP10.phx.gbl...
> oic
> for new scheme modification: execute store procedure, and corrected scheme
> will send at next push
> for new table on database: set a new "Push New Publcation" and then
> re-initialize subscription
> but do I need to remove the old snopshot?
> Thanks.
>
> "MC" <marko_culo#@.#yahoo#.#com#>
> ¼¶¼g©ó¶l¥ó·s»D:eS2rOAYLGHA.536@.TK2MSFTNGP09.phx.gbl...
>> You should execute the procedures on publisher. When you synch databases,
>> changes will be propagated to the subscriber.
>> Adding new table to replication means you must generate new snapshot and
>> re-initialize subscriptions.
>> MC
>> "beachboy" <jpsteambun@.yahoo.com.hk> wrote in message
>> news:O4DtH8XLGHA.1676@.TK2MSFTNGP09.phx.gbl...
>> Thank you for your informations
>> Sorry that I am not a professional of sql server
>> Yes. I am using SQL 2000 standard edition. How to I call those procedure
>> (sp_repladdcolumn and sp_repldropcolumn)'
>> from DB_A or from DB_C?
>> How about if new table added?
>> Thanks in advanced.
>>
>> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com>
>> ¼¶¼g©ó¶l¥ó·s»D:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.gbl...
>> Assuming we're talking about SQL 2000, the correct stored procedures to
>> call are sp_repladdcolumn and sp_repldropcolumn.
>> Using these procs, there is no need to create a snapshot for the
>> existing subscribers - the column changes will occur as you
>> synchronize.
>> Cheers,
>> Paul Ibison SQL Server MVP, www.replicationanswers.com
>> (recommended sql server 2000 replication book:
>> http://www.nwsu.com/0974973602p.html)
>>
>>
>>
>

Replicating Primary Key value onto the Foreign key value

Hey Guys,

I am just new to SQL Server. I have just created 2 tables Table1 and Table2. Table1 has a primary key (Table1.ID), which has been set on identity values.Both tables have different columns except the ID columns.

I have also used Table1.ID as foreign key and primary key on Table2 as Table2.ID.

I am just wondering guys if it is possible that when I enter or insert a data on Table1, can the same value (Table1.ID) be instantaneously copied to Table2.ID for the next data entry for Table2.

Please could you show me how this can be done using SQL Server Express 2005.

After you do the insert into table 1

INSERT INTO Table1 (<ColumnList>) VALUES(<ValueList>)

You can capture the newly created Identity value by doing the following

SET @.Variable = Scope_Identity()

You would then use this value for the ID column when you insert the row in Table2.

|||Thank you very much for your response. Like I expressed I am just new to these, and I hope you do pardon my ignorance as I am still learning.

I do follow the first part of your response but kinda clueless from the
'SET @. ..........................

Thanks for your patience.

|||

The SET statement captures the identity created when data was inserted in table 1.

That is the value to use in Table2.ID.

Code Snippet

DECLARE @.Variable <datatype of ID column>

INSERT INTO Table1 (<ColumnList>) VALUES(<ValueList>)

SET @.Variable = Scope_Identity()

INSERT INTO Table2 (ID, <remaining ColumnList>) VALUES(@.Variable, <remaining values>)

|||Thank you very much for your response. I'm just wondering if there is a way the code you provided above can be incorporated into a trigger. Thanks once again.
|||

If you want to do it in a trigger, there is a different method to use.

You would put the trigger on table1. Inside that insert trigger, you would use the virtual table named inserted which will contain a row for each row just inserted into table1, including the newly generated identity value for each row.

If you post your table create staements, and a descripttion of what you want to do, we can likely help you in writing the trigger.

|||

Code Snippet

IF OBJECT_ID ('dbo.Table1InsertTrigger', 'TR') IS NOT NULL

DROP TRIGGER dbo.Table1InsertTrigger

GO

CREATE TRIGGER dbo.Table1InsertTrigger

ON dbo.Table1

AFTER INSERT

IF ( @.@.ROWCOUNT = 0 )

RETURN

INSERT INTO Table2 (ID, <remaining ColumnList>)

SELECT ID, <remaining ColumnList>

FROM INSERTED

GO

|||Hi,

Here is the create statement for the tables.

CREATE TABLE [T1] (
[ID] IDENTITY(1000, 2) NOT NULL,
[NAME] VARCHAR(100),
[SERV_TYPE] VARCHAR(40),
[NWT] VARCHAR(40),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [T1_ID] PRIMARY KEY ([T1_ID])
)
GO

CREATE TABLE [T2] (
[ID] INT NOT NULL,
[CONTACT_TYPE] VARCHAR(1),
[CONTACT_NAME] VARCHAR(40),
[CONTACT_1] VARCHAR(100),
[CONTACT_2] VARCHAR(50),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [PK_T2] PRIMARY KEY ([ID])
)
GO

ALTER TABLE [T2] ADD CONSTRAINT [T1_T2]
FOREIGN KEY ([ID]) REFERENCES [T1] ([ID])
GO

Thanks for you help.
|||

(Unless I am confused, which does happen) What you are wanting to do doesn't really make sense in SQL Server storage. To do this, you would be creating partial rows. It would likely be a bad practice to automatically create a row with null values that might just end up as a useless row.

What you are describing sounds more of a task that might be done in a user interface, in order to display a new row for a person to edit. If you want to create a row in T1 and T2 simultaneously, you could use a stored procedure and build the rows together in the context of a transaction.

|||Well, Louis. How can I get T1.ID automatically available or copied into T2.ID when I have entered data into in T2.
|||Hi,

Here is the create statement for the tables.

CREATE TABLE [T1] (
[ID] IDENTITY(1000, 2) NOT NULL,
[NAME] VARCHAR(100),
[SERV_TYPE] VARCHAR(40),
[NWT] VARCHAR(40),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [T1_ID] PRIMARY KEY ([T1_ID])
)
GO

CREATE TABLE [T2] (
[ID] INT NOT NULL,
[CONTACT_TYPE] VARCHAR(1),
[CONTACT_NAME] VARCHAR(40),
[CONTACT_1] VARCHAR(100),
[CONTACT_2] VARCHAR(50),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [PK_T2] PRIMARY KEY ([ID])
)
GO

ALTER TABLE [T2] ADD CONSTRAINT [T1_T2]
FOREIGN KEY ([ID]) REFERENCES [T1] ([ID])
GO

I still have have not had this to work so far. My problem still remains how can I get T1.ID automatically available or copied into T2.ID, when I have opened the table, T2 for data entry.

Thanks you guys for your help.|||

Here ya go.

But like Louis, I wonder why you're creating a placeholder. I would think the application would have the ID of the associated T1 entry when the user is working on the T2 entry.

Code Snippet

CREATE TABLE [T1] (

[ID] int IDENTITY(1000, 2) NOT NULL,

[NAME] VARCHAR(100),

[SERV_TYPE] VARCHAR(40),

[NWT] VARCHAR(40),

[ROWVERSION] TIMESTAMP,

CONSTRAINT [T1_ID] PRIMARY KEY ([ID])

)

GO

CREATE TABLE [T2] (

[ID] INT NOT NULL,

[CONTACT_TYPE] VARCHAR(1),

[CONTACT_NAME] VARCHAR(40),

[CONTACT_1] VARCHAR(100),

[CONTACT_2] VARCHAR(50),

[ROWVERSION] TIMESTAMP,

CONSTRAINT [PK_T2] PRIMARY KEY ([ID])

)

GO

ALTER TABLE [T2] ADD CONSTRAINT [T1_T2]

FOREIGN KEY ([ID]) REFERENCES [T1] ([ID])

GO

IF OBJECT_ID ('dbo.Table1InsertTrigger', 'TR') IS NOT NULL

DROP TRIGGER dbo.Table1InsertTrigger

GO

CREATE TRIGGER dbo.Table1InsertTrigger

ON dbo.T1

AFTER INSERT

AS

IF ( @.@.ROWCOUNT = 0 )

RETURN

INSERT INTO T2 (ID)

SELECT ID

FROM INSERTED

GO

insert into t1( [NAME] , [SERV_TYPE], [NWT] )

values ('Name1', 'Service1', 'what is nwt?')

select * from t1

select * from t2