|
|
|
create table "admin"."account" (
"id" integer not null,
"person_id" integer,
"balance" float (8),
"obs" varchar (128),
primary key ("id")
);
insert into "admin"."account" values('1','1','99.23','None');
insert into "admin"."account" values('2','2','12.11',NULL);
insert into "admin"."account" values('3','1','73.34','Secondary');
insert into "admin"."account" values('4','3','155.84','Primary');
insert into "admin"."account" values('5','3','12.19',NULL);
insert into "admin"."account" values('6','4','0.18','None');
commit work;
select * from OPENQUERY(CTREESQL, 'select * from account where id > 2')
select * from CTREESQL..admin.account where id > 2
CREATE TABLE [dbo].[person](
[id] [int] NOT NULL,
[name] [char](32) NOT NULL,
CONSTRAINT [PK_person] PRIMARY KEY CLUSTERED
(
[id] ASC
)
) ON [PRIMARY]
GO
insert into person values(1, 'Mary')
insert into person values(2, 'Rick')
insert into person values(3, 'Jack')
insert into person values(4, 'Julia')
select p.name, a.id, a.balance, a.obs
from person p, OPENQUERY(CTREESQL, 'select * from account') a
where a.person_id = p.id
order by p.name
select p.name, a.id, a.balance, a.obs
from person p, CTREESQL..admin.account a
where a.person_id = p.id
order by p.name
CREATE VIEW [dbo].[account_view]
AS
SELECT p.name, a.id, a.balance, a.obs
FROM dbo.person AS p INNER JOIN
OPENQUERY(CTREESQL, 'select * from account') AS a ON p.id = a.person_id
The view can be executed as any ordinary SQL Server statement:
select * from account_view where name = 'Mary'
exec ('create table "admin"."holiday" (
"id" integer not null,
"description" varchar (32),
"hol_month" integer not null,
"hol_day" integer not null,
primary key ("id")
)') at CTREESQL
GO
exec ('insert into "admin"."holiday" values (1, ''Christmas'', 12, 25)') at CTREESQL
GO
exec ('insert into "admin"."holiday" values (2, ''New Year'', 1, 1)') at CTREESQL
GO
select * from OPENQUERY(CTREESQL, 'select * from holiday')
CREATE TABLE [dbo].[holiday](
[id] [int] NOT NULL,
[description] [varchar](32) NULL,
[hol_month] [int] NOT NULL,
[hol_day] [int] NOT NULL,
CONSTRAINT [PK_holiday] PRIMARY KEY CLUSTERED
(
[id] ASC
)
) ON [PRIMARY]
GO
insert into holiday values(1, 'Christmas', 12, 25)
GO
insert into holiday values(2, 'New Year', 1, 2)
GO
select * from holiday
select * into holiday_queue from holiday where 1 = 2
GO
alter table holiday_queue add action char(1)
GO
alter table holiday_queue add prev_id integer
GO
CREATE TRIGGER holidayINS ON holiday
AFTER INSERT
AS
INSERT holiday_queue
SELECT *, 'I', NULL
FROM inserted
GO
CREATE TRIGGER holidayDEL ON holiday
AFTER DELETE
AS
INSERT holiday_queue
SELECT *, 'D', id
FROM deleted
GO
CREATE TRIGGER holidayUPD ON holiday
AFTER UPDATE
AS
INSERT holiday_queue
SELECT *, 'U', (select id from deleted)
FROM inserted
GO
-----------------------------------------------------
-- This stored procedure retrieves the actions queued
-- and "replicates" the modifications in the linked
-- server
-----------------------------------------------------
CREATE PROCEDURE usp_sync_linkedsrv
AS
DECLARE @err_message nvarchar(255)
-------------------------
-- holiday replication --
-------------------------
DECLARE @id int
DECLARE @description varchar(32)
DECLARE @hol_month int
DECLARE @hol_day int
DECLARE @action char(1)
DECLARE @previd int
-- declare cursor for reading all the holiday events
DECLARE holiday_queue_cursor CURSOR FOR
SELECT * FROM holiday_queue
FOR UPDATE
-- open cursor
OPEN holiday_queue_cursor
-- retrieve the data from the cursor
FETCH FROM holiday_queue_cursor
INTO @id, @description, @hol_month, @hol_day, @action, @previd
WHILE @@FETCH_STATUS = 0
BEGIN
IF @action = 'I'
-- process the INSERT event
INSERT CTREESQL..admin.holiday (
id,
description,
hol_month,
hol_day )
VALUES (
@id,
@description,
@hol_month,
@hol_day )
ELSE
BEGIN
IF @action = 'U'
-- process the UPDATE event
UPDATE CTREESQL..admin.holiday
SET id = @id,
description = @description,
hol_month = @hol_month,
hol_day = @hol_day
WHERE id = @previd
ELSE
BEGIN
-- process the DELETE event
IF @action = 'D'
DELETE CTREESQL..admin.holiday
WHERE id = @previd
ELSE
BEGIN
SET @err_message = 'Invalid action: ' + @action
RAISERROR (@err_message,10, 1)
END
END
END
-- remove the current event from the queue
DELETE FROM holiday_queue WHERE CURRENT OF holiday_queue_cursor
-- retrieve the next event
FETCH NEXT FROM holiday_queue_cursor
INTO @id, @description, @hol_month, @hol_day, @action, @previd
END
-- close cursor
CLOSE holiday_queue_cursor
-- deallocate cursor
DEALLOCATE holiday_queue_cursor
GO
exec msdb.dbo.sp_add_job
@job_name = 'CTREESQL replication',
@enabled=1
GO
exec msdb.dbo.sp_add_jobstep
@job_name = 'CTREESQL replication',
@step_name = 'Check for changes to be replicated',
@subsystem = 'TSQL',
@command = 'exec dbo.usp_sync_linkedsrv',
@database_name = 'ctreeTest'
GO
exec msdb.dbo.sp_add_schedule
@schedule_name = 'CTREESQL replication schedule',
@enabled = 1,
@freq_interval = 1,
@freq_type = 4,
@freq_subday_type = 2,
@freq_subday_interval = 10
GO
exec msdb.dbo.sp_attach_schedule
@job_name = 'CTREESQL replication',
@schedule_name = 'CTREESQL replication schedule'
GO
exec msdb.dbo.sp_add_jobserver
@job_name = 'CTREESQL replication',
@server_name = 'ENRICO-PC'
GO
|
|
|
