June 28, 2013 at 8:18 am
Hello,
Am relatively new to Replications as such, the scenario I am dealing with is like as follows:
1. I am having multiple Databases say 10 for e.g. having same structure(exactly same objects tables,sp's etc) as such. Thus in each Database there is this one table with same schema say Friends.
2. Table Friends has following columns :
Column Name | Type
ID | int(pk,not null)
FirstName | varchar(100, not null)
MiddleName | varchar(100, not null)
LastName | varchar(100, not null)
IDProblem | int(not null)
3. I want to replicate this Friends table from all the 10 Databases to one central Subscriber.
4. I am using Transactional Publication to achieve the same, while creating the Publication for each Database, I change the article properties and set the Destination Object Name relevant to the Database and I also change the procedure names that it will render accordingly to prevent them from being overwritten. Thus for 10 databases I replicate the Friends table into 10 different tables in the subscriber and I have 10 sets of those msins,msupdate,msdelete procedures respectively. The whole reason for doing this is to know from which Database I actually get the data
5. I set an After Insert trigger on each of these Tables at the subscriber and there I actually insert all these columns into a FinalFreinds table along with the Database name from where it came hardcoded according to the table name.
6. I have mentioned this IDProblem column of type int in the schema above, which is replicated fine alright in each individual table, but the after trigger messes up with the column, what happens is sometimes I get the correct value for that column, and most other times it inserts 0 there, however the value in the replicated table was a non zero.
7. Means I get a value of 10 in the IDProblem column once, trigger fires, inserts everything fine including this value 10, and the immediate next record which gets replicated also has the value 10, my trigger inserts proper values for all other columns but this column although it was 10 again, the trigger inserts 0. This happens randomly, sometimes the correct value is inserted and most of the times 0 is inserted.
So now I have to solve this mess, firstly I would like to ask that can't I replicate all in one table at the subscriber, with one additional column DatabaseName which will tell me from which database it is coming from, this will solve all my problem as this is the only reason why I am having the triggers on each individual table.
If not this, then how can I fix the trigger, for it is not the case that trigger isn't working, it works for rest of the columns everytime, and for this IDProblem column sometimes, so I cannot figure out what is going wrong.
Please help me as I need to close this issue ASAP...:crying:
June 28, 2013 at 8:45 am
First of all, if I wanted to collect data from multiple sources into one table as it occurs I would consider a service broker solution. This methodology is a lot more scalable than the way you are doing it.... requiring all those individual publishers & subscribers and then more triggers to effect an insert into a result table.
I would consider writing the transactions to a service broker queue on each server, then have a single multi-threaded process on the subscribing server , one thread for each source ("publisher") server. Have a trigger on each source table that inserts a wake-up message into the broker queue that will cause the thread to snatch the new rows inserted and insert them over on the destination table, along with the server name (or serverID) from where they came.
This solution would be extremely tolerant of servers going up and down or the network connectivity interruptions because all of the source servers can continue to run, queuing up new rows even if the destination server is down, or the connectivity between them is down. When the connectivity comes back they will immediately reconnect and synch the new rows.
As far as why that column contains 0 sometimes instead of the correct value I can't begin to conjecture without looking at your code. That's my $.02
The probability of survival is inversely proportional to the angle of arrival.
June 28, 2013 at 9:57 pm
Thanks sturner, I would want to replace the mechanism with service broker or better off was thinking of an SSIS mechanism where in I can transform the data and add that extra bit of info i.e. the DBId while pushing it in the central DB.
But the thing is on live server now and I will need this quick fix. So related to the trigger, here is the code of the after insert trigger.
"
USE [CustomerSure]
GO
/****** Object: Trigger [dbo].[trgAfterInsert] Script Date: 06/25/2013 16:32:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trgAfterInsert] ON [dbo].[IncomingCalls]
FOR INSERT
AS
declare @CallerID varchar(15);
declare @Callstart datetime;
-- The Column with the issue.....
declare @user-id int;
declare @CallEnd Datetime;
declare @DataBaseID int;
select @CallerID = i.CallerID from inserted i;
select @Callstart= i.Callstart from inserted i;
-- I pick up the value from replicated row.....
select @user-id= i.UserID from inserted i;
select @CallEnd= i.CallEnd from inserted i;
select @DataBaseID = 42 from inserted i;
insert into Calls
(CallerId,CallStart,UserID,CallEnd,DatabaseID)
values(@CallerID,@CallStart,@UserID,@CallEnd,@DatabaseID);
PRINT 'AFTER INSERT trigger fired.'
"
Also one thing to mention here is the ColumnType in the Calls table where am inserting is nvarchar(50), whereas in the table which is being replicated, it is int, but I suppose this is not the issue for it works sometimes around, and I also tried Convert function as well to first convert the UserID into an nvarchar and then insert it, still it behaves the same, anything for a quick fix here???
June 30, 2013 at 10:39 am
Okay, for one thing your trigger will fail if more than one row was inserted. Think set-based. You don't need all those local variables.
All your trigger needs to do is this:
insert into Calls
(CallerId,CallStart,UserID,CallEnd,DatabaseID)
select i.CallerID, i.Callstart, i.UserID, i.CallEnd, 42
from inserted i
oh and you don't need a semi-colon after every statement. As a C++/C# programmer at heart I have a tendency to do that too but the semi-colon is only required in certain situations.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply