- 爱易网页
-
MSSQL教程
- sql server 2008 中cdc功能,该怎么处理
日期:2014-05-17 浏览次数:20493 次
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