It is rare for a single data source to stand as an island within an organization. The ability to connect disparate data sources among one another is an increasingly important task today. Data sharing among diverse applications leverages the strengths of each. This how-to describes a basic link from Microsoft SQL Server to c-treeACE SQL.
- Start c-treeACE SQL as a Windows service. If both c-treeACE SQL and SQL Server are on the same machine, they will use a shared memory protocol. Since Windows Vista, both Microsoft SQL Server and c-treeACE SQL must be started as Windows services to establish a Named Pipe connection.
- Set up an ODBC “System Data Source”. The “User Data Source” type is not applicable for a linked database.
- Create the “account” table in the c-treeACE SQL database.
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;
- Set up c-treeACE SQL as a Linked Server in Microsoft SQL Server. Using the Microsoft SQL Server Management Studio (SSMS), execute the following steps.
- In the “Object Explorer”, right click on “Server Objects / Linked Servers” and select the “New Linked Server” option.
- Enter a “Linked Server name”, select the “OLE DB Provider for ODBC drivers” as the provider, “product name” and the “System Data Source” name created in item 2.
- Click the “Security” option and add a map to the remote (c-tree) authentication. After clicking “Add”, select the authentication option on the “Local Login” and enter your c-treeACE SQL User ID and Password in the “Remote User” and “Remote Password” boxes.
- Click the “Server Options” page, enable “RPC” and “RPC Out” options, and confirm.
- Right click on the “CTREESQL” linked server and select the “Test Connection” option.
- Check that the “account” c-treeACE SQL table is present in the linked server.
- Query a c-treeACE SQL table in SSMS. Execute the following query in SSMS.
select * from OPENQUERY(CTREESQL, 'select * from account where id > 2')
select * from CTREESQL..admin.account where id > 2
- Create Microsoft SQL data. Execute the following commands.
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')
- Execute a join between Microsoft SQL and c-treeACE SQL tables with the following query.
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 a view with a table in Microsoft SQL Server and another in c-treeACE SQL with the following commands.
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'
- Create a c-treeACE SQL table in SSMS. Execute the following commands.
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 a similar table in Microsoft SQL Server. Execute the following commands.
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
- Create queue table for “holiday” in Microsoft SQL Server. This table will store the modifications to be replicated to the “linked server”. Execute the following commands.
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 triggers for “holiday” in Microsoft SQL Server. To create triggers for Insert, Update and Delete operations to populate the “holiday_queue” table, execute the following commands.
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
- Create a Stored Procedure to sync “linked server” table. To create stored procedures that reads the “holiday_queue” rows and execute the actions in the “linked server” table, execute the following commands.
-----------------------------------------------------
-- 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
- Create a Job to execute the linked server table sync. To create and schedule a Job for calling the “usp_sync_linkedsrv” stored procedure created in the previous item to replicate the table changes from the Microsoft SQL Server to c-treeACE SQL every 10 seconds, execute the following commands.
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
Islands are great for vacations but not for your data. Try setting up a linked server and see what new insights can be gained by bringing together your previously isolated datasets.
