Remove From My Forums

Define filtered scope

Question
0

Sign in to vote

Hi,

I need to synchronize a server database with some client databases. For reason of privacy every client has to download from server only data related with him (in particular, a client is a medic, and he has to download only data from tables with his medicoId). I followed the guide to create a filtered scope, but when I synchronize, client downloads all the data, even if he can change only data defined by the filter scope. I don't want this, I want that client downloads only data defined by the filtered scope, how can I do?
Thanks and sorry for my bad english, I'm Italian

Saturday, December 8, 2012 2:56 PM

Answers

1

Sign in to vote
if you haven't provisioned before, then it would fail. it will only work if the database has been previously provisioned.

also, if you have provisioned and set either ObjectSchema or ObjectPrefix, you need to specify the same when deprovisioning.
- Marked as answer by biassabrod Friday, January 4, 2013 12:22 PM
Friday, January 4, 2013 12:16 PM

All replies

0

Sign in to vote

can you post how you configured the filter template and how you provisioned the corresponding scope?

Monday, December 10, 2012 5:34 AM

can you post how you configured the filter template and how you provisioned the corresponding scope?

Excuse me for taking so long to answer, but I had some problems with my internet connection. Meanwhile I continued my work, the first error was that I didn't use a template. Now I have a new problem, when I do the populateFromTemplate in the Client provision, occurs an exception "IndexOutOfRangeException" and it says me that "SqlParameterCollection doesn't contain SqlParameter with ParameterName = srnme". Here is my code:

SqlUtilities sqlUt = new SqlUtilities();

        public void ProvisionServer(string servAddr)
        {
            DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("Scope");
            DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Medic", sqlUt.getServerConn(servAddr));
            scopeDesc.Tables.Add(tableDesc);

            SqlSyncScopeProvisioning serverTemplate = new SqlSyncScopeProvisioning(sqlUt.getServerConn(servAddr), scopeDesc, SqlSyncScopeProvisioningType.Template);
            serverTemplate.ObjectSchema = "Sync";

            serverTemplate.SetCreateTableDefault(DbSyncCreationOption.Skip);

            serverTemplate.Tables["Medic"].AddFilterColumn("Surname");
            serverTemplate.Tables["Medic"].FilterClause = "[side].[Surname] = srnme";
            SqlParameter param = new SqlParameter("srnme", SqlDbType.VarChar, 50);
            serverTemplate.Tables["Medic"].FilterParameters.Add(param);

            serverTemplate.Apply();
        }

        public void ProvisionClient(string servAddr, string clAddr)
        {
         
            SqlSyncScopeProvisioning serverProv1 = new SqlSyncScopeProvisioning(sqlUt.getServerConn(servAddr));
            serverProv1.ObjectSchema = "Sync";
            serverProv1.PopulateFromTemplate("ClientScope", "Scope");
            serverProv1.Tables["Medic"].FilterParameters["srnme"].Value = "CORDA";
            serverProv1.Apply();

            DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("ClientScope", null, "Sync", sqlUt.getServerConn(servAddr));  
            SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(sqlUt.getClientConn(clAddr), scopeDesc);
            clientProvision.ObjectSchema = "Sync";
            clientProvision.SetCreateTableDefault(DbSyncCreationOption.CreateOrUseExisting);

            clientProvision.Apply();          
        }

        void printResults(SyncOperationStatistics syncStats)
        {
            Console.WriteLine("Avvio sincronizzazione: " + syncStats.SyncStartTime);
            Console.WriteLine("Uploads totali: " + syncStats.UploadChangesTotal);
            Console.WriteLine("Downloads totali: " + syncStats.DownloadChangesTotal);
            Console.WriteLine("Fine sincronizzazione: " + syncStats.SyncEndTime);
            Console.WriteLine(String.Empty);
        }

        public void ExecuteSync(string servAddr, string clAddr)
        {
            try
            {
                SyncOrchestrator Orch = new SyncOrchestrator();

                Orch.LocalProvider = new SqlSyncProvider("ClientScope", sqlUt.getClientConn(clAddr), null, "Sync");
                Orch.RemoteProvider = new SqlSyncProvider("ClientScope", sqlUt.getServerConn(servAddr), null, "Sync");
                Orch.Direction = SyncDirectionOrder.UploadAndDownload;

                SyncOperationStatistics syncStats = Orch.Synchronize();

                printResults(syncStats);
            }
            catch (Exception)
            {
                Console.WriteLine("Errore durante la sincronizzazione");
            }
        }

SqlUtilities is a class where I define the connections with Server Database and Client Database. I created the database schema Sync and I tried with different filtered scopes (firstly I tried with medicId, then with Surname) but the problem is the same. I don't understand why it doesn't work. Thanks for the help!

Saturday, December 29, 2012 4:36 PM

0

Sign in to vote

put an @ sign on your parameter... e.g. @srnme

Sunday, December 30, 2012 1:20 AM
0

Sign in to vote

put an @ sign on your parameter... e.g. @srnme

I tried, but occurs the same exception

Thursday, January 3, 2013 11:20 AM
0

Sign in to vote

did you put it every where you use srnme?

Thursday, January 3, 2013 11:33 AM
0

Sign in to vote

did you put it every where you use srnme?

yes, but it doesn't work. Initially I used the @ and I had the problem, then I tried without it and I posted the question (I didn't know that it was important)

Thursday, January 3, 2013 11:45 AM
0

Sign in to vote

can you locate the selectchanges stored proc for that table and see what's the parameter inside it. or query the scope_config table and have a look a the xml column and see what's in there as well.

Thursday, January 3, 2013 12:57 PM

Here is the xml code inside the column data_config of the table scope_config

<SqlSyncProviderScopeConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" IsTemplate="true">
  <Adapter Name="[Medico]" GlobalName="[Medico]" TrackingTable="[Sync].[Medico_tracking]" SelChngProc="[Medico_selectchanges]" SelRowProc="[Sync].[Medico_selectrow]" InsProc="[Sync].[Medico_insert]" UpdProc="[Sync].[Medico_update]" DelProc="[Sync].[Medico_delete]" InsMetaProc="[Sync].[Medico_insertmetadata]" UpdMetaProc="[Sync].[Medico_updatemetadata]" DelMetaProc="[Sync].[Medico_deletemetadata]" BulkTableType="[Sync].[Medico_BulkType]" BulkInsProc="[Sync].[Medico_bulkinsert]" BulkUpdProc="[Sync].[Medico_bulkupdate]" BulkDelProc="[Sync].[Medico_bulkdelete]" InsTrig="[Medico_insert_trigger]" UpdTrig="[Medico_update_trigger]" DelTrig="[Medico_delete_trigger]">
    <Col name="MedicoId" type="int" idSeed="0" idStep="1" param="@P_1" pk="true" />
    <Col name="Cognome" type="varchar" size="50" param="@P_2" />
    <Col name="Nome" type="varchar" size="50" param="@P_3" />
    <Col name="CodiceFiscale" type="varchar" size="20" param="@P_4" />
    <Col name="NumeroAlbo" type="varchar" size="20" param="@P_5" />
    <Col name="DataAlbo" type="datetime" null="true" param="@P_6" />
    <Col name="Specializzazione" type="varchar" size="100" null="true" param="@P_7" />
    <Col name="PartitaIva" type="varchar" size="20" null="true" param="@P_8" />
    <Col name="Note" type="varchar" size="2000" null="true" param="@P_9" />
    <Col name="Provincia" type="varchar" size="2" null="true" param="@P_10" />
    <Col name="Comune" type="varchar" size="50" null="true" param="@P_11" />
    <Col name="Via" type="varchar" size="50" null="true" param="@P_12" />
    <Col name="NumeroCivico" type="varchar" size="10" null="true" param="@P_13" />
    <Col name="Cap" type="varchar" size="10" null="true" param="@P_14" />
    <Col name="DataNascita" type="date" null="true" param="@P_15" />
    <Col name="LuogoNascita" type="varchar" size="100" null="true" param="@P_16" />
    <FilterParam name="@cognome" />
    <FilterClause>[side].[Cognome] = @cognome</FilterClause>
    <FilterCol>Cognome</FilterCol>
  </Adapter>
</SqlSyncProviderScopeConfiguration>

It's in italian so the parameter name is "@cognome", on this thread I used english names for better comprehension

Thursday, January 3, 2013 1:35 PM

0

Sign in to vote

and what's the content of this stored proc: Medico_selectchanges ?

there's also a scope_param table, what does it contain?

Thursday, January 3, 2013 1:51 PM
0

Sign in to vote
and what's the content of this stored proc: Medico_selectchanges ?

there's also a scope_param table, what does it contain?

How can I see the content of the stored procedure with management studio?

The scope_parameter table contains two columns: sync_scope_name and parameter_data, but has no data inside, only one line with all fields NULL
- Edited by biassabrod Thursday, January 3, 2013 2:20 PM
Thursday, January 3, 2013 2:16 PM
0

Sign in to vote

you right click on the stored proc and have it generate either a CREATE or ALTER script

Thursday, January 3, 2013 2:24 PM

ok, here is the stored procedure:

USE [p8108mi]
GO

/****** Object:  StoredProcedure [dbo].[Medico_selectchanges]    Script Date: 03/01/2013 15:30:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Medico_selectchanges]
	@sync_min_timestamp BigInt,
	@sync_scope_local_id Int,
	@sync_scope_restore_count Int,
	@sync_update_peer_key Int
AS
BEGIN
SELECT [side].[MedicoId], [base].[Cognome], [base].[Nome], [base].[CodiceFiscale], [base].[NumeroAlbo], [base].[DataAlbo], [base].[Specializzazione], [base].[PartitaIva], [base].[Note], [base].[Provincia], [base].[Comune], [base].[Via], [base].[NumeroCivico], [base].[Cap], [base].[DataNascita], [base].[LuogoNascita], [side].[sync_row_is_tombstone], [side].[local_update_peer_timestamp] as sync_row_timestamp, case when ([side].[update_scope_local_id] is null or [side].[update_scope_local_id] <> @sync_scope_local_id) then COALESCE([side].[restore_timestamp], [side].[local_update_peer_timestamp]) else [side].[scope_update_peer_timestamp] end as sync_update_peer_timestamp, case when ([side].[update_scope_local_id] is null or [side].[update_scope_local_id] <> @sync_scope_local_id) then case when ([side].[local_update_peer_key] > @sync_scope_restore_count) then @sync_scope_restore_count else [side].[local_update_peer_key] end else [side].[scope_update_peer_key] end as sync_update_peer_key, case when ([side].[create_scope_local_id] is null or [side].[create_scope_local_id] <> @sync_scope_local_id) then [side].[local_create_peer_timestamp] else [side].[scope_create_peer_timestamp] end as sync_create_peer_timestamp, case when ([side].[create_scope_local_id] is null or [side].[create_scope_local_id] <> @sync_scope_local_id) then case when ([side].[local_create_peer_key] > @sync_scope_restore_count) then @sync_scope_restore_count else [side].[local_create_peer_key] end else [side].[scope_create_peer_key] end as sync_create_peer_key FROM [Medico] [base] RIGHT JOIN [Medico_tracking] [side] ON [base].[MedicoId] = [side].[MedicoId] WHERE  ([side].[update_scope_local_id] IS NULL OR [side].[update_scope_local_id] <> @sync_scope_local_id OR ([side].[update_scope_local_id] = @sync_scope_local_id AND [side].[scope_update_peer_key] <> @sync_update_peer_key)) AND [side].[local_update_peer_timestamp] > @sync_min_timestamp
END
GO

Thursday, January 3, 2013 2:30 PM

0

Sign in to vote

the selectchanges doesn't have your parameter.

have you previously provisioned without a parameter?

I suggest you clean up your database of sync framework created objects by running DeprovisionStore. just look it up in the docs.

Thursday, January 3, 2013 2:55 PM
0

Sign in to vote

the selectchanges doesn't have your parameter.

have you previously provisioned without a parameter?

I suggest you clean up your database of sync framework created objects by running DeprovisionStore. just look it up in the docs.

Yes, in the first time I did some synchronizations without a filtered scope, then I simply deleted new tables created by the provisioning. Now I try to do what you said, i hope is the solution, by the way thank you very much!

Thursday, January 3, 2013 3:02 PM
0

Sign in to vote

the selectchanges doesn't have your parameter.

have you previously provisioned without a parameter?

I suggest you clean up your database of sync framework created objects by running DeprovisionStore. just look it up in the docs.

Today I tried with a copy of the clean database and it worked! But I've a problem with the deprovisionStore, it says me that the db is not provisioned or I've not the permission for the sync configuration tables, I think that is a problem of permissions, what I have to do?

Friday, January 4, 2013 11:49 AM
1

Sign in to vote
if you haven't provisioned before, then it would fail. it will only work if the database has been previously provisioned.

also, if you have provisioned and set either ObjectSchema or ObjectPrefix, you need to specify the same when deprovisioning.
- Marked as answer by biassabrod Friday, January 4, 2013 12:22 PM
Friday, January 4, 2013 12:16 PM
0

Sign in to vote

if you haven't provisioned before, then it would fail. it will only work if the database has been previously provisioned.

also, if you have provisioned and set either ObjectSchema or ObjectPrefix, you need to specify the same when deprovisioning.

Now it works! Thank you for being so helpful!

Friday, January 4, 2013 12:25 PM

Answered by:

Define filtered scope

Question

Answers

All replies