index > SQL Server Everywhere Edition > Setting up publication for merge replication using Stored Procedu...

Setting up publication for merge replication using Stored Procedu...

Hello.I am trying to use Transact SQL to prepare publication for replication (SQL Server 2000 SP4 and SQL Mobile). I can easily create publication, add filters etc. but there is one thing which I can't find : parameter which tells me subscriber type and allow me to change subscriber type for publication. I don't know where I can change (or even if I can change) this property.
If someone made publication for mobile devices and replication with filters,resolvers etc. will it be a problem to share experiences here? Maybe some sample code with script or something what will guide me. Thanks in advance. Maciej Wysocki

Wysek

I'd suggest creating the publication using SQL Management Studio, verify that it works as you want it to, and then generate a script using SQL Management Studio to recreate the pub using TSQL. Looking at a similar script from one of my own publications, there is no explicit option for subscriber type. The combination of allowing web synch and allowing push and pull tells the publisher everything it needs to know to support SQL Mobile.

An example:

use [efinity]
exec sp_addmergepublication @publication = N'efinity_pub', @description = N'Merge publication of database ''efinity'' from Publisher ''DELLXPS\SQLSERVER''.', @sync_mode = N'character', @retention = 14, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @dynamic_filters = N'true', @conflict_retention = 14, @keep_partition_changes = N'false', @allow_synctoalternate = N'false', @validate_subscriber_info = N'HOST_NAME()', @max_concurrent_merge = 0, @max_concurrent_dynamic_snapshots = 0, @use_partition_groups = N'true', @publication_compatibility_level = N'90RTM', @replicate_ddl = 1, @allow_subscriber_initiated_snapshot = N'false', @allow_web_synchronization = N'true', @allow_partition_realignment = N'true', @retention_period_unit = N'days', @conflict_logging = N'both', @automatic_reinitialization_policy = 0
GO

-Darren

.NET Compact Framework MVP

Darren Shaffer

Ok. I tried this way and everything works fine but now I got another problem with dynamic filters. I need to create specific snapshot (merge partition) determined on PocketPC Device Name (this is the same as host_name). I try to get rows from table with column matched with this host_name. Here are my scripts for publication creation :

DECLARE @distributor_name as varchar(30);
DECLARE @publisher_name as varchar(30);
DECLARE @distribution_name as varchar(30);
DECLARE @database_folder as varchar (250);
DECLARE @database_file as varchar(20);
DECLARE @databaselog_file as varchar(20);
DECLARE @snapshot_folder as varchar(250);
DECLARE @replication_db_name as varchar(20);
DECLARE @publication_name as varchar(20);
DECLARE @IIS_login as varchar(40);

SET @distributor_name = '\\PROGRAMISTA2';
SET @publisher_name = 'PROGRAMISTA2';
SET @distribution_name = 'MiniPOSDistribution';
SET @database_folder = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data';
SET @database_file = 'gastronomia';
SET @databaselog_file = 'gastronomia_log';
SET @snapshot_folder = 'C:\SnapshotShare';
SET @replication_db_name = 'gastronomia';
SET @publication_name = 'MiniPOSReplicationDB';
SET @IIS_login = 'LASERGEO\mwysocki';

USE master
EXEC sp_adddistributor @distributor_name;

EXEC sp_adddistributiondb @database=@distribution_name , @data_folder=@database_folder,
@data_file= @database_file,--@data_file_size=data_file_size,
@log_folder=@database_folder, @log_file=@databaselog_file,
-- @log_file_size=log_file_size, @min_distretention=min_distretention,
-- @max_distretention=max_distretention, @history_retention=history_retention,
@security_mode=1,
-- , @login='sa', @password='',
@createmode=1

EXEC sp_adddistpublisher @publisher=@publisher_name , @distribution_db=@distribution_name , @security_mode=1,
-- @login='login',@password='password',
@working_directory=@snapshot_folder , @trusted='true'--,
-- @encrypted_password=encrypted_password,@thirdparty_flag=thirdparty_flag

EXEC sp_replicationdboption @dbname=@replication_db_name , @optname='merge publish' , @value='true'--,
-- @ignore_distributor=ignore_distributor,@from_scripting=from_scripting

USE gastronomia

EXEC sp_addmergepublication @publication=@publication_name , @description='Custom publication.',@retention=14,
@sync_mode='character',@allow_push='true',@allow_pull='true',@allow_anonymous='true', @enabled_for_internet='false',@centralized_conflicts='true',
@dynamic_filters = 'true',@snapshot_in_defaultfolder='true',
-- @alt_snapshot_folder='NULL',@pre_snapshot_script='NULL',
-- @post_snapshot_script='NULL',
@compress_snapshot='false',
-- @ftp_address='ftp_address',@ftp_port=ftp_port,@ftp_subdirectory='ftp_subdirectory',
-- @ftp_login='ftp_login',@ftp_password='ftp_password',
@conflict_retention=14,
@keep_partition_changes='true',@allow_subscription_copy='false',
@allow_synctoalternate='false',@validate_subscriber_info=N'HOST_NAME()'--,
-- @add_to_active_directory='add_to_active_directory',@max_concurrent_merge=maximum_concurrent_merge,
-- @max_concurrent_dynamic_snapshot

EXEC sp_addpublication_snapshot @publication=@publication_name,
@frequency_type=4,
@frequency_interval=1,
@frequency_subday=4,
@frequency_subday_interval=5,
@frequency_relative_interval=1,
@frequency_recurrence_factor=0,
@active_start_date=0,
@active_end_date=99991231,
@active_start_time_of_day=0,
@active_end_time_of_day=235959--,
-- @snapshot_job_name='snapshot_agent_name'

EXEC sp_addmergearticle @publication=@publication_name,
@article='MPprodukty_zamowione',
@source_object='MPprodukty_zamowione',
@type='table',
@description='MPprodukty_zamowione',
@column_tracking='false',
@status='unsynced',
@pre_creation_cmd='drop'

EXEC sp_addmergearticle @publication=@publication_name,
@article='MPrachunki',
@source_object='MPrachunki',
@type='table',
@description='MPrachunki',
@column_tracking='false',
@status='unsynced',
@pre_creation_cmd='drop'

EXEC sp_addmergearticle @publication=@publication_name,
@article='MPmenu',
@source_object='MPmenu',
@type='table',
@description='MPmenu',
@column_tracking='false',
@status='unsynced',
@pre_creation_cmd='drop'

EXEC sp_addmergearticle @publication=@publication_name,
@article='MPklienci',
@source_object='MPklienci',
@type='table',
@description='MPklienci',
@column_tracking='false',
@status='unsynced',
@pre_creation_cmd='drop'

EXEC sp_addmergearticle @publication=@publication_name,
@article='MPparametry',
@source_object='MPparametry',
@type='table',
@description='MPparametry',
@column_tracking='false',
@status='unsynced',
@pre_creation_cmd='drop',
@subset_filterclause = N'[wartosc] = HOST_NAME()';

EXEC sp_addmergearticle @publication=@publication_name,
@article='MPprodukty',
@source_object='MPprodukty',
@type='table',
@description='MPprodukty',
@column_tracking='false',
@status='unsynced',
@pre_creation_cmd='drop'

EXEC sp_addmergearticle @publication=@publication_name,
@article='MPmenu_produkty',
@source_object='MPmenu_produkty',
@type='table',
@description='MPmenu_produkty',
@column_tracking='false',
@status='unsynced',
@pre_creation_cmd='drop'

EXEC sp_addmergearticle @publication=@publication_name,
@article='MPopakowania',
@source_object='MPopakowania',
@type='table',
@description='MPopakowania',
@column_tracking='false',
@status='unsynced',
@pre_creation_cmd='drop'

EXEC sp_addmergearticle @publication=@publication_name,
@article='MPtypy_platnosci',
@source_object='MPtypy_platnosci',
@type='table',
@description='MPtypy_platnosci',
@column_tracking='false',
@status='unsynced',
@pre_creation_cmd='drop'

EXEC sp_addmergearticle @publication=@publication_name,
@article='MPuzytkownicy',
@source_object='MPuzytkownicy',
@type='table',
@description='MPuzytkownicy',
@column_tracking='false',
@status='unsynced',
@pre_creation_cmd='drop'

EXEC sp_addmergearticle @publication=@publication_name,
@article='MPinw_mag_poz',
@source_object='MPinw_mag_poz',
@type='table',
@description='MPinw_mag_poz',
@column_tracking='false',
@status='unsynced',
@pre_creation_cmd='drop'

EXEC sp_addmergearticle @publication=@publication_name,
@article='MPinw_mag_nagl',
@source_object='MPinw_mag_nagl',
@type='table',
@description='MPinw_mag_nagl',
@column_tracking='false',
@status='unsynced',
@pre_creation_cmd='drop'

EXEC sp_addmergearticle @publication=@publication_name,
@article='uwagi',
@source_object='uwagi',
@type='table',
@description='uwagi',
@column_tracking='false',
@status='unsynced',
@pre_creation_cmd='drop'

EXEC sp_addmergearticle @publication=@publication_name,
@article='uwagi_do_potraw',
@source_object='uwagi_do_potraw',
@type='table',
@description='uwagi_do_potraw',
@column_tracking='false',
@status='unsynced',
@pre_creation_cmd='drop'

EXEC sp_addmergearticle @publication=@publication_name,
@article='uwagi_do_zamowien',
@source_object='uwagi_do_zamowien',
@type='table',
@description='uwagi_do_zamowien',
@column_tracking='false',
@status='unsynced',
@pre_creation_cmd='drop'

EXEC sp_addmergearticle @publication=@publication_name,
@article='stoliki',
@source_object='stoliki',
@type='table',
@description='stoliki',
@column_tracking='false',
@status='unsynced',
@pre_creation_cmd='drop'

EXEC sp_addmergearticle @publication=@publication_name,
@article='sekcje',
@source_object='sekcje',
@type='table',
@description='sekcje',
@column_tracking='false',
@status='unsynced',
@pre_creation_cmd='drop'

if not exists (select * from master.dbo.syslogins where loginname = @IIS_login)
exec sp_grantlogin @IIS_login
exec sp_defaultdb @IIS_login, @replication_db_name
exec sp_defaultlanguage @IIS_login, N'us_english'

--if not exists (select * from master.dbo.syslogins where loginname = N'LASERGEO\mwysocki')
-- exec sp_grantlogin N'LASERGEO\mwysocki'
-- exec sp_defaultdb N'LASERGEO\mwysocki', N'gastronomia'
-- exec sp_defaultlanguage N'LASERGEO\mwysocki', N'us_english'

if not exists (select * from dbo.sysusers where name = @IIS_login)
EXEC sp_grantdbaccess @IIS_login, N'mwysocki'

EXEC sp_grant_publication_access @publication=@publication_name , @login=@IIS_login

EXEC sp_changemergepublication @publication = @publication_name,
@property = 'snapshot_ready',
@value = 'true'

Publication is created, I can synchronize, I can get data from tables but when I doesn't put value into parameter

@validate_subscriber_info=N'HOST_NAME()' and I leave it blank I get nothing in MPparametry (empty table) but when I put this value I get all rows. Anyone can explain why this aint working?

I put host name in C# code:

rep.HostName = System.Net.Dns.GetHostName();

I checked - this host is exact the same as device name. Maybe it's wrong, but the same device name I put as subscriber in replication. Please help me if someone knows what's wrong here or if someone did dynamic rows filtering in SQL Server 2000 SP4 and SQL Mobile 2005.

Wysek

reply 3