locked
not possible to pass custom parameters to incrInsertCommand (stored procedure) ? RRS feed

  • Question

  •  

    Hi,

    I am using Sync Services for the first time and maybe I overlooked something.

    We are building an ocassionally connected application (c#) for pocket pc which has a local SQL CE 3.5 DB that should sync with the backend SQL 2008 in a n-tier scenario over a webservice proxy.

     

    So far so good, I wanted to move away from coding the necessary incrInsert/incrUpdates/... commands into the service and move to server-side stored procedures instead. For not having to implement numerous procedures per table, I tried to write some generic procedure using Dynamic SQL, which could be used at least for most of the SyncAdapters. Actually there is a nice solution in LOB Accelerator Sample where one T-SQL fits all tables, and I tried to port that to a stored procedure.

     

    The procedure itself seems to work, the problem I am facing now is that I am not able to pass any custom parameters to the SqlCommand. As far as I understand, the defined SyncSession parameters like @sync_last_received_anchor are populated implicitly, as well as parameters in form @myColumn - if a column "myColumn" exists in that table. But is it truly not possibe to add custom parameters? Adding parameters by command.Parameters.Add(...) or command.Parameters.AddWithValue(...) does not throw me any exceptions, but at runtime on synchronization the provider throws a

    Code Snippet
    [Microsoft.Synchronization.Data.SessionVariableException] = {"Unable to set session parameters in DbServerSyncProvider. Cannot obtain the value for command parameter '@columns'."}mailto:'@columns'">'@columns'."}
    <P< A> align=left> 

     

    Can somebody please give me a hint If I did something wrong or if it is generally not possible to populate custom parameters with values? Thanks in advance!

     

    Kind Regards, Andreas

     

     

    My procedure:

    Code Snippet

    CREATE PROCEDURE sp_sync_genericIncrementalChanges

    -- Add the parameters for the stored procedure here

    @columns varchar(255),

    @tablename varchar(50),

    @idcolumn varchar(50),

    @changeoperation char(1),

    @sync_initialized int,

    @sync_last_received_anchor int,

    @sync_new_received_anchor int

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- we use dynamic sql, so declare variable holding the query

    DECLARE @query varchar(2000)

    IF @sync_initialized = 0

    BEGIN

    set @query = 'SELECT ' + @columns + ' FROM ' + @tablename + ' as originalTable LEFT OUTER JOIN ' +

    'CHANGETABLE(CHANGES ' + @tablename + ', ' + CAST(@sync_last_received_anchor as varchar(50)) + ') ct ' +

    ' ON ct.' + @idcolumn + ' = originalTable.' + @idcolumn + ' ';

    END

    ELSE

    BEGIN

    set @query = 'SELECT ' + @columns + ' FROM ' + @tablename + ' as originalTable JOIN ' +

    'CHANGETABLE(CHANGES ' + @tablename + ', ' + CAST(@sync_last_received_anchor as varchar(50)) + ') ct ' +

    ' ON ct.' + @idcolumn + ' = originalTable.' + @idcolumn +

    ' WHERE ct.SYS_CHANGE_OPERATION = ' + @changeoperation + 'AND ct.SYS_CHANGE_CREATION_VERSION <= ' + @sync_new_received_anchor + ' ';

    END

    -- execute the query

    exec(@query)

    END

     

     

    The code for the SyncAdapter looks as follows:

     

    Code Snippet

     

    SqlCommand incrementalInsertCommand = new SqlCommand();

    incrementalInsertCommand.CommandText = "sp_sync_genericIncrementalChanges";

    incrementalInsertCommand.CommandType = CommandType.StoredProcedure;

    incrementalInsertCommand.Parameters.Add("@" + SyncSession.SyncInitialized, SqlDbType.Int);

    incrementalInsertCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt);

    incrementalInsertCommand.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt);

    incrementalInsertCommand.Parameters.AddWithValue("@columns", columnString);

    incrementalInsertCommand.Parameters.AddWithValue("@tablename", tableName);

    incrementalInsertCommand.Parameters.AddWithValue("@idcolumn", idColumnName);

    incrementalInsertCommand.Parameters.AddWithValue("@changeoperation", OPERATION_INSERT);

     

    syncAdapter.SelectIncrementalInsertsCommand = incrementalInsertCommand;

     

     

    • Moved by Max Wang_1983 Friday, April 22, 2011 7:40 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, May 15, 2008 1:04 PM

Answers

  • I see, the link I provided above is only to show how to use the session parameter. the scenarion you described here is actually a NOT currently supported feature for the current release-- dynamicaly partition data. you can do that by re-initalize you client db after the parameter value changes or keep two set of SSC client DBs on the client machine.

     

    thanks

    Yunwen 

    Tuesday, May 20, 2008 1:39 AM
    Moderator

All replies

  •  

    HI, this is a case with Dynamic filters on the sync tables. you can try to use the session variable to pass in the values for the parameters for the SP. code snipet can be found under the BOL from.

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1219265&SiteID=1

     

    thanks

    Yunwen

    Friday, May 16, 2008 12:04 AM
    Moderator
  • Hi Yunwen,

    thank you for your answer. I went again through "filtering" chapter in BOL, but I am quite not sure about it. Would be nice if you could point me the correct direction again.

     

    If I got it right, I can also define a SyncParameter by

    Code Snippet
    this.Configuration.SyncParameters.Add

     

     

    but this parameter is kind of "global" for the whole sync setup. So I define it once for the Configuration, and this parameter's value is populated in all SQL during Synchronization, so it can e.g. be used in filter clauses.

     

    What I wanted to implement in my setup is adding custom parameters to hand over "values" other than the defined session variables to a stored procedure but for a specific table. So it will not suffice if I define a SyncParameter "tablename" or "idColumn", if I can set its value only globally for all SyncAdapters. I have to set a specific value for each SyncAdapter, to make the idea with only one stored procedure work.

     

    So again - is it possible to hand a parameter "myParam" to a stored-procedure for SqlCommand, which is populated with a specific value for every SyncAdapter? So on Sync of say 2 SyncTables the same stored procedure is called but one time with "@myParam" = "customers" and the other time with "@myParam" = "salespersons"?

     

    Thanks,

    Andreas

    Monday, May 19, 2008 6:20 AM
  • I see, the link I provided above is only to show how to use the session parameter. the scenarion you described here is actually a NOT currently supported feature for the current release-- dynamicaly partition data. you can do that by re-initalize you client db after the parameter value changes or keep two set of SSC client DBs on the client machine.

     

    thanks

    Yunwen 

    Tuesday, May 20, 2008 1:39 AM
    Moderator