Prev: Synchronise replication agents
Next: Error ' Could not obtain information about Windows NT group '
From: LPR-3rd on 24 Feb 2010 10:50
All servers are running SQL 2005.

I created a UDF that returns one value
~~~~~
create FUNCTION dbo.WhatLocationIsThis() RETURNS numeric AS
begin
declare @return as numeric
SELECT @return = Location
FROM storemain..tblLocationTable
return @return
end
~~~~~

I use the UDF in a filter statment in a publication
~~~~~
-- Enabling the replication database
use master
exec sp_replicationdboption @dbname = N'INSURANCE', @optname =
N'publish', @value = N'true'
GO

exec [INSURANCE].sys.sp_addlogreader_agent @job_login = N'rouses.com
\sqladmin', @job_password = null, @publisher_security_mode = 1
GO
exec [INSURANCE].sys.sp_addqreader_agent @job_login = N'ROUSES.COM
\sqladmin', @job_password = null, @frompublisher = 1
GO
-- Adding the transactional publication
use [INSURANCE]
exec sp_addpublication @publication = N'Ins_test_lpr', @description =
N'Transactional publication of database ''INSURANCE'' from Publisher
''R00S-SQLA''.', @sync_method = N'concurrent', @retention = 0,
@allow_push = N'true', @allow_pull = N'true', @allow_anonymous =
N'false', @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', @repl_freq = N'continuous',
@status = N'active', @independent_agent = N'true', @immediate_sync =
N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false',
@allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl =
1, @allow_initialize_from_backup = N'false', @enabled_for_p2p =
N'false', @enabled_for_het_sub = N'false'
GO


exec sp_addpublication_snapshot @publication = N'Ins_test_lpr',
@frequency_type = 1, @frequency_interval = 0,
@frequency_relative_interval = 0, @frequency_recurrence_factor = 0,
@frequency_subday = 0, @frequency_subday_interval = 0,
@active_start_time_of_day = 0, @active_end_time_of_day = 235959,
@active_start_date = 0, @active_end_date = 0, @job_login = N'rouses.com
\sqladmin', @job_password = null, @publisher_security_mode = 1
exec sp_grant_publication_access @publication = N'Ins_test_lpr',
@login = N'username_here'
GO

-- Adding the transactional articles
use [INSURANCE]
exec sp_addarticle @publication = N'Ins_test_lpr', @article =
N'INCIDENT_TEST', @source_owner = N'dbo', @source_object =
N'INCIDENT_TEST', @type = N'logbased', @description = N'',
@creation_script = N'', @pre_creation_cmd = N'drop', @schema_option =
0x000000000803509F, @identityrangemanagementoption = N'manual',
@destination_table = N'INCIDENT_TEST', @destination_owner = N'dbo',
@status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL
[sp_MSins_dboINCIDENT_TEST]', @del_cmd = N'CALL
[sp_MSdel_dboINCIDENT_TEST]', @upd_cmd = N'SCALL
[sp_MSupd_dboINCIDENT_TEST]', @filter_clause = N'[LOCATION] =
(select master.dbo.WhatLocationIsThis())'

-- Adding the article filter
exec sp_articlefilter @publication = N'Ins_test_lpr', @article =
N'INCIDENT_TEST', @filter_name = N'FLTR_INCIDENT_TEST_1__173',
@filter_clause = N'[LOCATION] =
(select master.dbo.WhatLocationIsThis())', @force_invalidate_snapshot
= 1, @force_reinit_subscription = 1

-- Adding the article synchronization object
exec sp_articleview @publication = N'Ins_test_lpr', @article =
N'INCIDENT_TEST', @view_name = N'SYNC_INCIDENT_TEST_1__173',
@filter_clause = N'[LOCATION] =
(select master.dbo.WhatLocationIsThis())', @force_invalidate_snapshot
= 1, @force_reinit_subscription = 1
GO

-- Adding the transactional subscriptions
use [INSURANCE]
exec sp_addsubscription @publication = N'Ins_test_lpr', @subscriber =
N'ROUSE17SERVERB', @destination_db = N'INSURANCE', @subscription_type
= N'Pull', @sync_type = N'replication support only', @article =
N'all', @update_mode = N'read only', @subscriber_type = 0
GO


~~~~~

I created a pull subscription
~~~~~
-- Adding the transactional pull subscription

/****** Begin: Script to be run at Subscriber ******/
use [INSURANCE]
exec sp_addpullsubscription @publisher = N'R00S-SQLA', @publication =
N'Ins_test_lpr', @publisher_db = N'INSURANCE', @independent_agent =
N'True', @subscription_type = N'pull', @description = N'',
@update_mode = N'read only', @immediate_sync = 0

exec sp_addpullsubscription_agent @publisher = N'R00S-SQLA',
@publisher_db = N'INSURANCE', @publication = N'Ins_test_lpr',
@distributor = N'R00S-SQLA', @distributor_security_mode = 1,
@distributor_login = N'', @distributor_password = N'',
@enabled_for_syncmgr = N'False', @frequency_type = 4,
@frequency_interval = 1, @frequency_relative_interval = 1,
@frequency_recurrence_factor = 0, @frequency_subday = 8,
@frequency_subday_interval = 1, @active_start_time_of_day = 1100,
@active_end_time_of_day = 235959, @active_start_date = 0,
@active_end_date = 0, @alt_snapshot_folder = N'', @working_directory =
N'', @use_ftp = N'False', @job_login = N'rouses.com\sqladmin',
@job_password = null, @publication_type = 0
GO
/****** End: Script to be run at Subscriber ******/

/****** Begin: Script to be run at Publisher ******/
/*use [INSURANCE]
-- Parameter @sync_type is scripted as 'automatic', please adjust when
appropriate.
exec sp_addsubscription @publication = N'Ins_test_lpr', @subscriber =
N'ROUSE17SERVERB', @destination_db = N'INSURANCE', @sync_type =
N'Automatic', @subscription_type = N'pull', @update_mode = N'read
only'
*/
/****** End: Script to be run at Publisher ******/


~~~~~

When the subscription runs, no data is replicated though on the
subscriber, there is data that meets the filter criteria.....WHY???


AHIA,
Larry....
 | 
Pages: 1
Prev: Synchronise replication agents
Next: Error ' Could not obtain information about Windows NT group '