Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data Distribution - Part 2


Data Distribution - Part 2

Author
Message
LP-181697
LP-181697
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 143
mishaluba (10/15/2010)
Thank you for the article, Leo! I found it interesting. A couple of questions please. Could you clarify what is behind this line:
raiserror (51080, 10, 1); -- Alert that will start job
I am just curious about possible implementations. I have been starting jobs from T-SQL code using sp_start_job procedure, but is there a way to actually trigger it from the alert?

Secondly, I would like to better understand the mechanism for transferring changed records. Here is my understanding. We have a control table on the Destination server and, based on this table, we know the ID's of the records we need to bring in (where Processed_FLG is 'N'). We then wrap these ID's into an XML and pass this XML to the stored procedures on the Source server. When these procedures run, they grab necessary records and somehow push them to the Destination (via linked server or how?). Please let me know if this is correct.

Thank you!


raiserror (51080, 10, 1); - when you registering alert you associate it with the error message. I created custom message 51080. It means when this error is raised the alert is fired by sql server. Then alert can be associated with a job. E.g. when alert is raised it fired the job. But then job runs completely independently from the transaction that was fired the raiserror command.
Idea of the process is simple: control table that is keeping changes is on the source. When job fired it transferring knowledge about changes to the destination server where it written in similar control table with status not processed. And then it processing changes. There are many variations how to organize the process. It can be done by using centralized hub if you have to distribute changes to many servers or it can be done directly server to server if you need it only for one server.
Patrick2525
Patrick2525
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 Visits: 180
Sorry for joining this conversation late...

Is there something missing in the code when you are setting the XML variable:

declare @xmlvar1 xml;
declare @emp table (emp_id int primary key);

set @xmlvar1 = '
1


2
'

insert into @emp(emp_id)
select T1.lst.value('.','int') as emp_id
FROM @xmlvar1.nodes('/emp/id') T1(lst);

select * from @emp;



It doesn't look like XML, and when I try to run this I'm not getting the expected results.

Thanks!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search