locked
Change Data Capture gives error: invalid number of arguments

    Question

  • Hi all,

    I'm piloting CDC in Oslo. This article tells what to do: http://msdn.microsoft.com/en-us/library/dd129574(VS.85).aspx

    So, after running through that article, im getting a strange error message:

    Msg 313, Level 16, State 3, Line 7:

    An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ... .


    I thought the message was truncated by management studio; but i think its not. After digging and digging through the functions for CDC, i stubled upon sys.fn_cdc_check_parameters. However nice that may be; i cannot get a resultset from CDC; i need such a resultset for a demo.

    Im convinced i missed out something trivial; but i've scanned the msdn page 10 (decimal) times.

    So now, how am i going to try out CDC?


    I also have another question, since that is what i'm working on now: how can i see who (what connection / repository user) changed some data with CDC?
    (as i can get the change-date back from an LSN, can i also get other information?)
    Performing my Final Project, looking into codename "Oslo".
    • Edited by Elger [Centric] Sunday, September 13, 2009 12:50 PM 1 error message
    Sunday, September 13, 2009 12:35 PM

Answers

  • This is partially the case of a misleading error message. The LSN range that we're sending to the [TestSchema].[PeopleChanges] function is invalid. Because of some difficulties in raising custom error messages from table-value functions, we instead get this misleading error message that there was an insufficient number of arguments.

    After testing a few things on my test machine, I noticed that sys.fn_cdc_get_max_lsn() returns NULL for me. This was puzzling until more research revealed that this can happen when SQL Server Agent is not running. Some of the CDC functionality depends on SQL Server Agent jobs. I started SQL Server Agent, and now I don't get the error and the samples seem to work.  By the way, I'm sure you noticed the missing space in the first query between "__$operation" and "when" in the case statement. I'll get that fixed too.

    Let me know if starting SQL Server Agent in the SSMS Object Explorer window solves your error too. Thanks.

    Tuesday, September 15, 2009 5:42 PM

All replies

  • I try to find some answers for you here.

    .Kraig
    Tuesday, September 15, 2009 3:40 PM
  • Sorry for the trouble you're having following the documentation on this feature. I'm testing this out right now to try to reproduce the problem you're seeing. Are you using the May 2009 CTP (http://www.microsoft.com/downloads/details.aspx?FamilyID=827122a5-3ca0-4389-a79e-87af37cbf60d&displaylang=en)?

    Jason Roth
    Tuesday, September 15, 2009 4:19 PM
  • Sorry for the trouble you're having following the documentation on this feature. I'm testing this out right now to try to reproduce the problem you're seeing. Are you using the May 2009 CTP (http://www.microsoft.com/downloads/details.aspx?FamilyID=827122a5-3ca0-4389-a79e-87af37cbf60d&displaylang=en)?

    Jason Roth

    I am using the May 2009 CTP. Ill also try to reproduce it myself again with a fresh and clean repository, maybe ill have better luck this time.
    Performing my Final Project, looking into codename "Oslo".
    Tuesday, September 15, 2009 5:00 PM
  • I see the error you're getting. I'll investigate and get back to you with an answer soon.  Thanks.

    Jason
    Tuesday, September 15, 2009 5:08 PM
  • This is partially the case of a misleading error message. The LSN range that we're sending to the [TestSchema].[PeopleChanges] function is invalid. Because of some difficulties in raising custom error messages from table-value functions, we instead get this misleading error message that there was an insufficient number of arguments.

    After testing a few things on my test machine, I noticed that sys.fn_cdc_get_max_lsn() returns NULL for me. This was puzzling until more research revealed that this can happen when SQL Server Agent is not running. Some of the CDC functionality depends on SQL Server Agent jobs. I started SQL Server Agent, and now I don't get the error and the samples seem to work.  By the way, I'm sure you noticed the missing space in the first query between "__$operation" and "when" in the case statement. I'll get that fixed too.

    Let me know if starting SQL Server Agent in the SSMS Object Explorer window solves your error too. Thanks.

    Tuesday, September 15, 2009 5:42 PM
  • Hi Jason,

    Ah, i did read SQL Server agent needs to be active; but i ignored it since i had some trouble starting that service. As i'm digging deeper into it (http://msdn.microsoft.com/en-us/library/ms191454.aspx) i get an error message:

    "cannot start service, disabled or does not connect to other devices (freely translated)" 0x80070422

    Searching for a solution scares me to death, it cannot be that complicated?
    http://social.msdn.microsoft.com/forums/en-US/sqlsetupandupgrade/thread/568cb88a-3a1f-420c-afa4-82686e192c0b/

    What is the quickest (and dirtiest) way to get it working?
    Performing my Final Project, looking into codename "Oslo".
    Tuesday, September 15, 2009 7:11 PM
  • Unfortunately, I'm not sure whether there is a simple way to get this working. The brute force method would be to remove SQL Server 2008 off of your machine entirely and then try a reinstall (or possibly just repair SQL Server). But there is no guarantee that would work. I'll need a little more information to help. Could you provide me with the following?

    1. A copy or contents of your latest SQL Server Error log (immediately after trying to start SQL Server Agent unsuccessfully). Mine is located at "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG" (no extension).

    2. The results from the following query on your server:

    declare @agentvalue as int
    set @agentvalue = cast((select value from sys.configurations where name like 'Agent XPs') as int)
    print @@version
    print 'Agent XPs value = ' + cast(@agentvalue as nvarchar(max))

    3. At an administrator command prompt, run the command "sc query state= all > scquery.txt". Send me the scquery.txt file.

    4. At the same command prompt, run the command "sc qc SQLSERVERAGENT > qcagent.txt". Send me the qcagent.txt file.

    5. At the same command prompt, run the command "sc qc MSSQLSERVER > qcss.txt". Send me the qcss.txt file.

    I'll take a look at all of this configuration information and see if there is anything that stands out to me for why the agent service won't start. If you have another clean machine to test SQL Server on, you might be able to get the agent working on that machine right away in order to run your CDC tests. But we can continue to try to get the agent working on this machine.

    Thanks.

    Jason
    Wednesday, September 16, 2009 10:56 AM
  • Forgot to give you my email. Please send it to jroth_at_microsoft.com (replace _at_ with @).  Thanks.

    Jason

    Wednesday, September 16, 2009 5:00 PM
  • Hello Elger:

    What edition of SQL are you using?

    To get this information,

    1. Connect to the SQL Server instance using SQL Server Management Studio (ssms)
    2. Open a New Query window
    3. Execute the following:

      select @@version;

    Please post the results of the above query.

    Wednesday, November 25, 2009 12:17 AM