1

I am new to SQL Server. I need track the Data Changes in Database Table inside the Microsoft SQL Server Management Studio.If any Data insertion,Deletion and modification done..in Table ,the WCF service should receive notifications from SQL Server 2012 Database.

Is this possible to send instant notifications of Data Table from SQL server to WCF Service?

Done's and Studied:-

I am already refer some websites. I have an Idea about Change Data Capture and Change Tracking procedure in SQL Server.Then i were followed the Change Data Capture Procedure to Track the Data in SQL Server.Then i look over some information about Jobs in SQL Server Data Agent.With the help of jobs,possible to automate a query execution in sql server with a time interval?

Then i am created a Database in SQL server using DML and enabled the Change Data Capture Feature for Database and Table Level,specified number of Columns!

My New Database:-

CREATE DATABASE Testcdc_feature
GO
Use Testcdc_feature;
GO
CREATE TABLE dbo.one(
Name varchar(100),
Id  int,
Designation varchar(100),
Salary int,
);

Enabled Change Data Capture Feature for Database:-

use Testcdc_feature;
GO
-- enable CDC on the database
EXEC sys.sp_cdc_enable_db;
GO

Enabled CDC Feature for The Table and Specified Columns:-

USE Testcdc_feature;
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'one',
@role_name = NULL, -- Role gating not enabled
@filegroup_name = N'PRIMARY', -- should consider writing audit date to    separate filegroup
@captured_column_list = N'Name,Id,Designation,Salary'; 
GO

Inserted Values to the Table Columns:-

USE Testcdc_feature;
GO
SET NOCOUNT ON;
INSERT INTO dbo.one VALUES(
'thirunavukkarasu',2345,'TeamLeader',12000000
);
INSERT INTO dbo.one VALUES(
'Selva',3456,'Developer',30000);
INSERT INTO dbo.one VALUES(
'John',9876,'Application Tester',45000
);
INSERT INTO dbo.one VALUES(
'Anand',56789,'Developer',56000
);
INSERT INTO dbo.one VALUES(
'Priya',6709,'Developer',78000
);
INSERT INTO dbo.one VALUES(
'Swetha',8907,'Developer',100000
);

For Check the Data Changes the query is written as below(DEMO):-

USE Testcdc_feature;
GO
DECLARE @from_lsn binary(10), @to_lsn binary(10)
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_one')
SET @to_lsn = sys.fn_cdc_get_max_lsn()
SELECT
CT.__$start_lsn,
CT.__$operation,
CASE CT.__$operation
WHEN 1 THEN 'Delete'
WHEN 2 THEN 'Insert'
WHEN 3 THEN 'Update - Pre'
WHEN 4 THEN 'Update - Post'
END AS Operation,
CT.*,
LSN.tran_begin_time,
LSN.tran_end_time,
LSN.tran_id
FROM
cdc.fn_cdc_get_all_changes_dbo_one
(@from_lsn, @to_lsn, N'all update old') AS CT INNER JOIN
cdc.lsn_time_mapping AS LSN ON CT.__$start_lsn = LSN.start_lsn
GO

When Executing the Last Query i got output as below

This is the Result of Above Query

I need to automate the last Query (Demo) for a time interval and send it results as instant notifications of Data modifications from SQL server to WCF Service?

The WCF Should Receive the Insertion,Modification and Deletion of Data From SQL server.is this possible?

My Question:-

1.How to automate a query execution with a time interval in MS SQL Server 2012?

2.How to send the automated query results into wcf service?

How do i achieve this? Could anyone provide me a idea or solution? It Might me more Helpful!

Thiru Arasu
  • 293
  • 1
  • 5
  • 23

2 Answers2

1

By what I am understanding, all you want is an event triggering when you

"If any Data insertion,Deletion and modification done..in Table"

what you can do is create SQL triggers, you can set them to trigger(throw an event) when you delete from, update or insert into the table.

Bernard Walters
  • 391
  • 1
  • 4
  • 16
1

From what I understand. Your needs are to communicate to a WCF Service any transactions done to a table with a time interval.

What I recommand you is to create a trigger on your table that will insert in a specific table for transaction informations that you need (columns changed? is it INSERT/UPDATE/DELETE? previous and new values? a status flag?).

After that, you set up a new SQL job with your time constraints that will raise your WCF Service. Finally, your WCF Service will read any news data in your new transaction table.

Here is a link to setting up a Job.

EDIT : You will probably need indexes on your transaction tables particularly on the previous tables key and on your status flag. What a mean by a status flag is information related to your WCF Service (did it already read this line? If you implements a re-processing in case of error, which step this line is on your WCF Service?)

When your WCF Service will read your transaction table, to do it properly you may use transactions in order to re-process from the start. Here is a link to use transactions. I don't recommand this since your transaction table will be highly accessed and using a transaction will put locks on your table if not use properly.

Or you may Update your status flag and retrieve the data with an OUTPUT clause in order to re-process step-by-step. Here is a link to use output clause on update. (The example you are looking for is C.)

Lostblue
  • 129
  • 3