May 11, 2011 at 1:57 pm
Need some assistance with deciding which way to move forward, here is the scenario
Trying to replicate table A (a1,a2,a3,a4,a5,a6) to table B(b1,b2,b3,b4). a5 and a6 do not exist in tableB. I updated the replication sps to ignore these columns but keep getting an error in the replication monitor. "Invalid column name a5 and a6, error 207". After doing some research found out that this cant be done.
So I am left with 2 options
create a view with the required columns on the publisher side, replicate that to a staging table, and then have a trigger on the staging table to update the target table (table B).
add the the two new columns on the target table and replicate straight from tableA to tableB, (this will need some more changes on the application side)
I guess what I really want to know is, is it better to populate the target table through a trigger or through replication via a bulk update at the publisher ( we wont be running the snapshot as not all the columns in tableB are on table A). The publisher and subscriber are different servers.
Thank you
May 11, 2011 at 6:09 pm
you should avoid(actually...i say never) trying to move data to another database or another server from within a trigger; the trigger could fail and rollback if the database/network/latency issues arise. depending on the way you handle errors, one unwanted result is ghost/lost records that SHOULD have been inserted end up missing, without a decent explanation as to WHY they didn't save.
replication is the best answer...
you have to replicate the same number of columns, so either change the source to be only 4 columns, or, as you described, change the destination to have the six columns you want
Lowell
May 12, 2011 at 8:19 am
Lowell, thanks for the response. I plan to put the trigger on the staging table on the subscriber, so it ll fire on the same server as the target table.
Also we need columns a5 and a6 to maintain data integrity on the target table. They dont have to be in the target table but we cant do an insert in it unless those values are present in a third table (on the subscriber server). My replication scripts (sps) will be doing that check, or if I use a trigger it ll be the trigger.
May 13, 2011 at 9:49 am
Did you by any chance look at Service broker to help you with this?
-Roy
May 17, 2011 at 1:38 pm
I dont get it. You CAN publish a table and filter vertically.
i.e. decide which columns to replicate along with the PK.
Are you able to explain further as to why this isnt possible in this case?
May 20, 2011 at 6:05 pm
The column names are different. I get an error when I start replication. "invalid column". Havent played much with service broker but will look into it. For now we are going to change the column names to match.
May 20, 2011 at 6:24 pm
Also if you are doing vertical partitioning all columns must be present on the subscriber. In my case they are not. So if table A is a publisher and I am replicating a1,a2,a6 table B only has a1,a2, but I need a6 for satisfying a constraint. If a6 exists on the subscriber in table C then do insert in table B.
May 21, 2011 at 3:51 am
Pankaj Shere (5/20/2011)
Also if you are doing vertical partitioning all columns must be present on the subscriber.
That is an incorrect statement. Its also incorrect if you meant horizontal partitioning.
This would be valid dml for a vertically partitioned subscriber. It would work as the subscriber includes the primary key column.
Publisher Table
USE [AdventureWorks]
GO
/****** Object: Table [Production].[ProductDescription] Script Date: 05/21/2011 10:45:20 ******/
/* Publisher Table */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Production].[ProductDescription](
[ProductDescriptionID] [int] IDENTITY(1,1) NOT NULL,
[Description] [nvarchar](400) NOT NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_ProductDescription_ProductDescriptionID] PRIMARY KEY CLUSTERED
(
[ProductDescriptionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Subscriber Table
USE [AdventureWorks]
GO
/****** Object: Table [Production].[ProductDescription] Script Date: 05/21/2011 10:45:20 ******/
/* Subscriber Table */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Production].[ProductDescription](
[ProductDescriptionID] [int] IDENTITY(1,1) NOT NULL,
[Description] [nvarchar](400) NOT NULL
CONSTRAINT [PK_ProductDescription_ProductDescriptionID] PRIMARY KEY CLUSTERED
(
[ProductDescriptionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Procs for adding a vertically partitioned article.
Pankaj Shere (5/20/2011)
The column names are different. I get an error when I start replication. "invalid column". Havent played much with service broker but will look into it. For now we are going to change the column names to match.
I would have done this anyway. I would set the subscriber destination to an alternative object name and create view with the alternate column names
May 22, 2011 at 9:48 am
Pankaj Shere (5/20/2011)
Also if you are doing vertical partitioning all columns must be present on the subscriber. In my case they are not. So if table A is a publisher and I am replicating a1,a2,a6 table B only has a1,a2, but I need a6 for satisfying a constraint. If a6 exists on the subscriber in table C then do insert in table B.
What I meant was all the columns that you are replicating need to be on the subscriber. In the eg. you gave the 2 columns on the subscriber are in the publisher. Lets say we are also replicating modified date column and the subscriber does not have that column, the replication will error out, even if you have modified the sps.
The primary key column is the same in both the tables in your eg. It was different in our case, but that has been changed now.
May 22, 2011 at 9:52 am
And the reason I need that column is to satisfy a constraint on the table at the subscriber. So for eg. If modifieddate exists in a third table(on the subscriber) only then insert record in the subscriber table.
May 25, 2011 at 3:50 am
Hi ,
Replication allow you to have a different destination table name, column name, NUMBER OF COLUMNS... but you will need to do it by using TSQL not the GUI.
Also it's a very bad idea to put a triger or extra constraint at the subscriber as the triger/constraint can cause the replication transaction to fail and this transaction will be retry time and time again by the distributor util fixed.
you will be better to have a job at the subsciber that will look at the data and do whatever the triger would do.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply