locked
DBServerSyncProvider decides to append an extra SQL parameter when calling specified stored procedure RRS feed

  • Question

  • This is a very strange behaviour.  Hopefully someone out there has come across this and resolved the issue before.

    I'm using Sync Services for ADO.NET v2.0 and DBServerSyncProvider for Upload only synchronization.

    The DBServerSyncProvider has a SyncAdapter specified.  The SyncAdapter also has the InsertCommand, UpdateCommand and DeleteCommand to match corresponding stored procedures.  And for the InsertCommand, I have a SQL input parameter @name (varchar) specified.  The last sql parameter is the row count (output).

    However, the Insert update on the server side always fails with the SQL exception "Too many argument supplied to the Insert stored procedures".  Using SQL Profiler to trace the t-sql received, it revealed that the "exec" call on the Insert procedure indeed has an extra input parameter.  It's "@name = default" as the last parameter after the row count parameter.  And "@name = value" has already supplied as specified by the InsertCommand of the SyncAdapter.

    This has to be instigated by the DBServerSyncProvider itself.  But Why? How? This is very puzzling indeed.
    • Moved by Hengzhe Li Friday, April 22, 2011 3:07 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, June 4, 2009 12:24 AM

Answers

  • You are spot on!!!  Thanks for your help.
    • Marked as answer by MichaelZh Friday, June 5, 2009 1:56 AM
    Friday, June 5, 2009 1:56 AM

All replies

  • Did you maybe have a copy & paste error?

    Have a look at your SyncAdapter class.

    There you add the InsertCommand Parameters like:

    this.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@DEVICE_UID", System.Data.SqlDbType.UniqueIdentifier));
    this.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@name", System.Data.SqlDbType.UniqueIdentifier));

    //....



    Maybe you copied one of these lines to your UpdateCommand parameters:
    this.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@DEVICE_UID", System.Data.SqlDbType.UniqueIdentifier));
    this.InsertCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@name", System.Data.SqlDbType.UniqueIdentifier)); // here is the error! change
         ^^^^^^^^^^^^

    and forgot to change the "this.InsertCommand" to "this.UpdateCommand".


    If this was not your bug.... It would be better if you post the code of your SyncAdapter here.


    Regards,

    Martin

    • Proposed as answer by macap Friday, June 5, 2009 7:19 AM
    Thursday, June 4, 2009 12:37 PM
  • You are spot on!!!  Thanks for your help.
    • Marked as answer by MichaelZh Friday, June 5, 2009 1:56 AM
    Friday, June 5, 2009 1:56 AM
  • Hi,

    great! I had the same mistake some weeks ago so ... I thought it ´s maybe also your problem.
    Copy&Paste is sometimes a pain in the a... ;-)


    Regards from Cologne,

    Martin


    PS: Normally you have to mark the answer to a question in the board here as "answer" and not the answer you give if it has worked or not.
    Friday, June 5, 2009 7:21 AM