sql server 2008 中cdc功能
以下代码中,我执行执行最后两句报错:
消息 313,级别 16,状态 3,第 1 行
为过程或函数 cdc.fn_cdc_get_all_changes_ ... 提供的参数数目不足。
请问如何解决
这是一本书里的实例:
/*
Programming Microsoft SQL Server 2008
Chapter 14 - Data Warehousing
*/
-- Change Data Capture (CDC)
-- Create test database
CREATE DATABASE CDCDemo
GO
USE CDCDemo
GO
-- Enable CDC on the database
EXEC sp_cdc_enable_db
-- Show CDC-enabled databases
SELECT name, is_cdc_enabled FROM sys.databases
-- View the new "cdc" user and schema
SELECT * FROM sys.schemas WHERE name = 'cdc'
SELECT * FROM sys.database_principals WHERE name = 'cdc'
-- Create Employee table
CREATE TABLE Employee(
EmployeeId int NOT NULL PRIMARY KEY,
EmployeeName varchar(100) NOT NULL,
EmailAddress varchar(200) NOT NULL)
-- Enable CDC on the table (SQL Agent *should* be running when you run this)
EXEC sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Employee',
@role_name = N'CDC_admin',
@capture_instance = N'dbo_Employee',
@supports_net_changes = 1
-- Show CDC-enabled tables
SELECT name, is_tracked_by_cdc FROM sys.tables
-- Insert some employees...
INSERT INTO Employee VALUES(1, 'John Smith', 'john.smith@ourcorp.com')
INSERT INTO Employee VALUES(2, 'Dan Park', 'dan.park@ourcorp.com')
INSERT INTO Employee VALUES(3, 'Jay Hamlin', 'jay.hamlin@ourcorp.com')
INSERT INTO Employee VALUES(4, 'Jeff Hay', 'jeff.hay@ourcorp.com')
-- Select them from the table and the change capture table
SELECT * FROM Employee
SELECT * FROM cdc.dbo_employee_ct
-- Delete Jeff
DELETE Employee WHERE EmployeeId = 4
-- Results from Delete
SELECT * FROM Employee
SELECT * FROM cdc.dbo_employee_ct
-- (Note: result of DELETE may take several seconds to show up in CT table)
-- Update Dan and Jay
UPDATE Employee SET EmployeeName = 'Dan P. Park' WHERE EmployeeId = 2
UPDATE Employee SET EmployeeName = 'Jay K. Hamlin' WHERE EmployeeId = 3
-- Results from update
SELECT * FROM Employee
SELECT * FROM cdc.dbo_employee_ct -- See note above
-- To access change data, use the CDC TVFs, not the change tables directly
DECLARE @begin_time datetime
DECLARE @end_time datetime
DECLARE @from_lsn binary(10)
DECLARE @to_lsn binary(10)
SET @begin_time = GETDATE() - 1
SET @end_time = GETDATE()
-- Map the time interval to a CDC LSN range
SELECT @from_lsn =
sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time)
SELECT @to_lsn =
sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time)
SELECT @begin_time AS BeginTime, @end_time AS EndTime
SELECT @from_lsn AS FromLSN, @to_lsn AS ToLSN
-- Return the changes occurring within the query window.
-- First, all changes that occurred...
SELECT *
FROM cdc.fn_cdc_get_all_changes_dbo_employee(@from_lsn, @to_lsn, N'all')
-- Then, net changes, that is, final state...
SELECT *
FROM cdc.fn_cdc_get_net_changes_dbo_employee(@from_lsn, @to_lsn, N'all')
消息 313,级别 16,状态 3,第 1 行
为过程或函数 cdc.fn_cdc_get_all_changes_ ... 提供的参数数目不足。
请问如何解决
这是一本书里的实例:
/*
Programming Microsoft SQL Server 2008
Chapter 14 - Data Warehousing
*/
-- Change Data Capture (CDC)
-- Create test database
CREATE DATABASE CDCDemo
GO
USE CDCDemo
GO
-- Enable CDC on the database
EXEC sp_cdc_enable_db
-- Show CDC-enabled databases
SELECT name, is_cdc_enabled FROM sys.databases
-- View the new "cdc" user and schema
SELECT * FROM sys.schemas WHERE name = 'cdc'
SELECT * FROM sys.database_principals WHERE name = 'cdc'
-- Create Employee table
CREATE TABLE Employee(
EmployeeId int NOT NULL PRIMARY KEY,
EmployeeName varchar(100) NOT NULL,
EmailAddress varchar(200) NOT NULL)
-- Enable CDC on the table (SQL Agent *should* be running when you run this)
EXEC sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Employee',
@role_name = N'CDC_admin',
@capture_instance = N'dbo_Employee',
@supports_net_changes = 1
-- Show CDC-enabled tables
SELECT name, is_tracked_by_cdc FROM sys.tables
-- Insert some employees...
INSERT INTO Employee VALUES(1, 'John Smith', 'john.smith@ourcorp.com')
INSERT INTO Employee VALUES(2, 'Dan Park', 'dan.park@ourcorp.com')
INSERT INTO Employee VALUES(3, 'Jay Hamlin', 'jay.hamlin@ourcorp.com')
INSERT INTO Employee VALUES(4, 'Jeff Hay', 'jeff.hay@ourcorp.com')
-- Select them from the table and the change capture table
SELECT * FROM Employee
SELECT * FROM cdc.dbo_employee_ct
-- Delete Jeff
DELETE Employee WHERE EmployeeId = 4
-- Results from Delete
SELECT * FROM Employee
SELECT * FROM cdc.dbo_employee_ct
-- (Note: result of DELETE may take several seconds to show up in CT table)
-- Update Dan and Jay
UPDATE Employee SET EmployeeName = 'Dan P. Park' WHERE EmployeeId = 2
UPDATE Employee SET EmployeeName = 'Jay K. Hamlin' WHERE EmployeeId = 3
-- Results from update
SELECT * FROM Employee
SELECT * FROM cdc.dbo_employee_ct -- See note above
-- To access change data, use the CDC TVFs, not the change tables directly
DECLARE @begin_time datetime
DECLARE @end_time datetime
DECLARE @from_lsn binary(10)
DECLARE @to_lsn binary(10)
SET @begin_time = GETDATE() - 1
SET @end_time = GETDATE()
-- Map the time interval to a CDC LSN range
SELECT @from_lsn =
sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time)
SELECT @to_lsn =
sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time)
SELECT @begin_time AS BeginTime, @end_time AS EndTime
SELECT @from_lsn AS FromLSN, @to_lsn AS ToLSN
-- Return the changes occurring within the query window.
-- First, all changes that occurred...
SELECT *
FROM cdc.fn_cdc_get_all_changes_dbo_employee(@from_lsn, @to_lsn, N'all')
-- Then, net changes, that is, final state...
SELECT *
FROM cdc.fn_cdc_get_net_changes_dbo_employee(@from_lsn, @to_lsn, N'all')
...全文
相关推荐
使用SQLServer2008的CDC功能实现数据变更捕获
最近由于工作需要,研究了一下2008CDC功能,觉得还不错,下面整理了一下研究过程,虽然比较粗略,但是基本上能用了,如果有补充请大家回复,我视情况修改原帖,谢谢。在SQLServer2008之前,对数据变更的捕获通常使用触发器、时间戳等低效高成本的功能来实现,所以很多系统都没有做数据变更或者仅仅对核心表做监控。自从SQLServer2008引入了数据变更捕获功能(后面简称为CDC)后,解决了很多烦恼问题,如客户2个月前更改了一个数据,但是由于服务器空间不足,经常只能保留一个月的备份。此时客户为了逃避因为更改数据带来的较大负面影响,往往会把责任推给软件开发商或者运维商,如果此时有变更捕获的话,就
SQL Server 2008的更改数据捕获:崭新的异步CDC解决方案能够提高性能.pdf
SQL Server 2008的更改数据捕获:崭新的异步CDC解决方案能够提高性能.pdf
SQLServer 2008 CDC功能实现数据变更捕获脚本
主要介绍了使用SQLServer 2008的CDC功能实现数据变更捕获的脚本,大家参考使用
SQL Server2008十大新功能.pdf
SQL Server2008十大新功能.pdf
CDC操作Sqlserver2008
CDC操作Sqlserver2008


新技术前沿
