locked
passing a list as parameter to sync adapter commands RRS feed

  • Question

  • Hi all,

    I have created this stored procedure for the SelectIncrementalInsertsCommand.
    I need to pass a list as parameter so I have used a temporary table named #Holder, on sync this exception is being thrown:

    because the schema for table 'Processes' could not be retrieved by the GetSchema() method of DbServerSyncProvider. Make sure that you can establish a connection to the client database and that either the SelectIncrementalInsertsCommand property or the SelectIncrementalUpdatesCommand property of the SyncAdapter is specified correctly. ---> System.Data.SqlClient.SqlException: Invalid object name '#Holder'.

    =========================================================================================
    CREATE PROCEDURE sp_processes_incIns_3
         (@sync_last_received_anchor binary(8),@sync_new_received_anchor binary(8),@login_user_usersListFilter NVARCHAR(MAX))
    AS
    DECLARE @Temp  AS NVARCHAR(MAX)
    CREATE TABLE #HOLDER(CreatedByValue NVARCHAR(MAX))
    WHILE LEN(@login_user_usersListFilter) > 0
        BEGIN
           IF CHARINDEX(',', @login_user_usersListFilter) =0
              BEGIN
                 SET @TEMP = @login_user_usersListFilter 
                 SET @login_user_usersListFilter = ''
                 INSERT INTO #Holder (CreatedByValue) VALUES(@Temp)
              END
           ELSE
              BEGIN
                 SET @Temp = LEFT(@login_user_usersListFilter, CHARINDEX(',', @login_user_usersListFilter)-1)     
                 INSERT INTO #Holder (CreatedByValue) VALUES(@Temp)
                 SET @login_user_usersListFilter = RIGHT(@login_user_usersListFilter, LEN(@login_user_usersListFilter)-LEN(@Temp)-1)
           END
        END
    SELECT Id,WorkflowId,TypeId,CreatedBy,CreationDate,Subject,Priority,CompletionDate,ParentId,Requester,Company FROM [Processes]
    WHERE create_timestamp > @sync_last_received_anchor
    AND create_timestamp <= @sync_new_received_anchor
    AND CreatedBy IN (SELECT CreatedByValue FROM #Holder)
    ORDER BY create_timestamp desc

    =========================================================================================

    Please note that this stored procedure is being executed successfully in the management studio.

    Any idea how to solve this exception to be able to have a list as filter??

    Thank you,
    bernadet

    • Moved by Tina_Tian Friday, April 22, 2011 7:58 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, October 23, 2008 3:16 PM

All replies

  • Hi again,

    I dont know the reason but it seems that the sync services cannot
    recognize the local temporary table #Holder, although the temp tables are connected to the database session and they are dropped when the session is closed...

    is there any method to solve this issue without using a permanent table?

    Thank you,
    bernadet
    Friday, October 24, 2008 7:48 AM