insufficient number of arguments - CDC

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

insufficient number of arguments - CDC

Author

Topic

rafaltrus
Starting Member

1 Post

Posted - 2011-06-21 : 13:06:20

Hey guys,

I was asked by my boss to develop a SQL query that would compare data from two different sources and daily check for any updates. I figured I could use the Change Data Capture feature (I am using SQL 2008). Since I am new to SQL, I am taking baby-steps, although I am still not able to run this feature properly.

I have enabled cdc both on my databse and on my table (called 'sample').

exec sp_cdc_enable_table
	@source_schema = 'dbo',
	@source_name = 'sample',
	@role_name = 'cdc',
	@capture_instance = 'dbo_sample'

When I run the code below, I get this error:
"Msg 313, Level 16, State 3, Line 8
An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ..."


declare @begin_time datetime, @end_time datetime, @from_lsn binary(10), @to_lsn binary(10);
set @begin_time = GETDATE() - 1;
set @end_time = GETDATE();
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 les than or equal', @end_time);
select @begin_time, @end_time, @from_lsn, @to_lsn;

select * from cdc.fn_cdc_get_all_changes_dbo_sample(@from_lsn, @to_lsn, 'all');

go

I know that if the specified LSN range does not fall within the change tracking timeline for the capture instance I will get this error, but it doesn't seem like it is out of range.

This is an exact code from this website: http://channel9.msdn.com/Blogs/ashishjaiman/CDC-Change-Data-Capture-SQL-Server-2008
I am not sure why it is not working; I have followed all of the instructions...

Thanks!

lappin
Posting Yak Master

182 Posts

Posted - 2011-07-05 : 06:13:45

Are you doing auditing or updating a table with new /changed data?
If you are updating a table it might be easier to set up a stored procedure which uses MERGE to update the destination table based on the source table. Then just schedule the procedure to run daily (or more often if required).

CREATE PROCEDURE [dbo].[INS_A_From_B] AS

BEGIN

IF EXISTS(SELECT * FROM TableB)
BEGIN

Merge into TableA as Target
Using (

SELECT [a],[b],[c]

from TableB

) As Source

ON Target.id=Source.id

when matched then
update set Target.[a]=Source.[a],
Target.[b] = Source.[b],
Target.[c]=Source.[c]

WHEN NOT MATCHED BY TARGET THEN
INSERT ([a],[b],[c]) values
( Source.[a], Source.[b], Source.[c]);

END
End

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources