Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Error number 207 : invalid column name "***"

243 views
Skip to first unread message

Nick

unread,
Jan 17, 2005, 2:15:02 AM1/17/05
to
i got this error when i add a table as atricle

but i am pretty sure the column exist in this table,

anyone experences thos error before?

thanks for any comments


cheers

Tony Toker

unread,
Jan 17, 2005, 3:51:47 AM1/17/05
to

"Nick" <Ni...@discussions.microsoft.com> wrote in message
news:31DD5B76-93C8-4D1F...@microsoft.com...

Nick,

This looks like a SQL server bug, see this thread from a week or so back -

sp_repladdcolumn failure/Merge Replication Fails with Error 207 While
Generating a Snapshot/KBArticle 821535

Kestutis Adomavicius provides some good feedback.

TT


Paul Ibison

unread,
Jan 17, 2005, 5:25:38 AM1/17/05
to
Please can you post up the entire error message and tell
us the type of replication and service pack level on
publisher, subscriber and distributor. The reason I ask
for tis is that this error can have several causes and
the particular advice may depend on the factors above.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Hilary Cotter

unread,
Jan 17, 2005, 8:45:45 AM1/17/05
to
also can you script out the publication, and the schema of the
tables/objects you are replicating and post them here.


"Nick" <Ni...@discussions.microsoft.com> wrote in message
news:31DD5B76-93C8-4D1F...@microsoft.com...

Nick

unread,
Jan 17, 2005, 3:45:06 PM1/17/05
to
That's the entire error message when i try to generate snapshot.

I am using merge replication and publisher and distirbutor both on my local
computer.

Below is script:

-- Adding the distribution database
exec sp_adddistributiondb @database = N'MSales1_Distribution', @data_folder
= N'C:\Program Files\Microsoft SQL Server\MSSQL\Data', @data_file =
N'MSales1_Distribution.MDF', @data_file_size = 2, @log_folder = N'C:\Program
Files\Microsoft SQL Server\MSSQL\Data', @log_file =
N'MSales1_Distribution.LDF', @log_file_size = 0, @min_distretention = 0,
@max_distretention = 72, @history_retention = 48, @security_mode = 0, @login
= N'sa', @password = null
GO

-- Adding the distribution publisher
exec sp_adddistpublisher @publisher = N'NICK', @distribution_db =
N'MSales1_Distribution', @security_mode = 0, @login = N'sa', @password = N'',
@working_directory = N'\\NICK\C$\Program Files\Microsoft SQL
Server\MSSQL\ReplData', @trusted = N'false', @thirdparty_flag = 0
GO

/****** End: Script to be run at Distributor: NICK ******/

/****** Begin: Script to be run at Publisher: NICK ******/
-- Enabling the replication database
use master
GO

exec sp_replicationdboption @dbname = N'MSales1', @optname = N'merge
publish', @value = N'true'
GO

use [MSales1]
GO

-- Adding the merge publication
exec sp_addmergepublication @publication = N'MSales1_Publication',
@description = N'Merge publication of MSales1 database from Publisher NICK.',
@retention = 14, @sync_mode = N'character', @allow_push = N'true',
@allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet =
N'false', @centralized_conflicts = N'true', @dynamic_filters = N'true',
@snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false',
@ftp_port = 21, @ftp_login = N'anonymous', @conflict_retention = 14,
@keep_partition_changes = N'true', @allow_subscription_copy = N'false',
@allow_synctoalternate = N'false', @validate_subscriber_info =
N'HOST_NAME()', @add_to_active_directory = N'false', @max_concurrent_merge =
0, @max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @publication =
N'MSales1_Publication',@frequency_type = 4, @frequency_interval = 1,
@frequency_relative_interval = 1, @frequency_recurrence_factor = 0,
@frequency_subday = 1, @frequency_subday_interval = 5, @active_start_date =
0, @active_end_date = 0, @active_start_time_of_day = 500,
@active_end_time_of_day = 235959, @snapshot_job_name =
N'NICK-MSales1-MSales1_Publication-1'
GO

exec sp_grant_publication_access @publication = N'MSales1_Publication',
@login = N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @publication = N'MSales1_Publication',
@login = N'distributor_admin'
GO
exec sp_grant_publication_access @publication = N'MSales1_Publication',
@login = N'NICK\IUSR_NICK'
GO
exec sp_grant_publication_access @publication = N'MSales1_Publication',
@login = N'sa'
GO

-- Adding the merge articles
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'QaMaximumFlavourDistributor', @source_owner = N'dbo', @source_object =
N'QaMaximumFlavourDistributor', @type = N'table', @description = null,
@column_tracking = N'true', @pre_creation_cmd = N'drop', @creation_script =
null, @schema_option = 0x000000000000CFF1, @article_resolver = null,
@subset_filterclause = null, @vertical_partition = N'false',
@destination_owner = N'dbo', @auto_identity_range = N'false',
@verify_resolver_signature = 0, @allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true', @check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'DistributorProductFlavour', @source_owner = N'dbo', @source_object =
N'DistributorProductFlavour', @type = N'table', @description = null,
@column_tracking = N'true', @pre_creation_cmd = N'drop', @creation_script =
null, @schema_option = 0x000000000000CFF1, @article_resolver = null,
@subset_filterclause = null, @vertical_partition = N'false',
@destination_owner = N'dbo', @auto_identity_range = N'false',
@verify_resolver_signature = 0, @allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true', @check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'BonusDealFlavour', @source_owner = N'dbo', @source_object =
N'BonusDealFlavour', @type = N'table', @description = null, @column_tracking
= N'true', @pre_creation_cmd = N'drop', @creation_script = null,
@schema_option = 0x000000000000CFF1, @article_resolver = null,
@subset_filterclause = null, @vertical_partition = N'false',
@destination_owner = N'dbo', @auto_identity_range = N'false',
@verify_resolver_signature = 0, @allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true', @check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'CustomerDistributor', @source_owner = N'dbo', @source_object =
N'CustomerDistributor', @type = N'table', @description = null,
@column_tracking = N'true', @pre_creation_cmd = N'drop', @creation_script =
null, @schema_option = 0x000000000000CFF1, @article_resolver = null,
@subset_filterclause = null, @vertical_partition = N'false',
@destination_owner = N'dbo', @auto_identity_range = N'false',
@verify_resolver_signature = 0, @allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true', @check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'BonusDealProductState', @source_owner = N'dbo', @source_object =
N'BonusDealProductState', @type = N'table', @description = null,
@column_tracking = N'true', @pre_creation_cmd = N'drop', @creation_script =
null, @schema_option = 0x000000000000CFF1, @article_resolver = null,
@subset_filterclause = null, @vertical_partition = N'false',
@destination_owner = N'dbo', @auto_identity_range = N'false',
@verify_resolver_signature = 0, @allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true', @check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'BonusDealState', @source_owner = N'dbo', @source_object =
N'BonusDealState', @type = N'table', @description = null, @column_tracking =
N'true', @pre_creation_cmd = N'drop', @creation_script = null, @schema_option
= 0x000000000000CFF1, @article_resolver = null, @subset_filterclause = null,
@vertical_partition = N'false', @destination_owner = N'dbo',
@auto_identity_range = N'false', @verify_resolver_signature = 0,
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true',
@check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'QAMaximumProductDistributor', @source_owner = N'dbo', @source_object =
N'QAMaximumProductDistributor', @type = N'table', @description = null,
@column_tracking = N'true', @pre_creation_cmd = N'drop', @creation_script =
null, @schema_option = 0x000000000000CFF1, @article_resolver = null,
@subset_filterclause = null, @vertical_partition = N'false',
@destination_owner = N'dbo', @auto_identity_range = N'false',
@verify_resolver_signature = 0, @allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true', @check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'QAMaximumFlavour', @source_owner = N'dbo', @source_object =
N'QAMaximumFlavour', @type = N'table', @description = null, @column_tracking
= N'true', @pre_creation_cmd = N'drop', @creation_script = null,
@schema_option = 0x000000000000CFF1, @article_resolver = null,
@subset_filterclause = N'(CONVERT(DATETIME,CONVERT(VARCHAR, GETDATE(),10)) >=
BeginDate) AND (CONVERT(DATETIME,CONVERT(VARCHAR, GETDATE(),10)) <=
EndDate)', @vertical_partition = N'false', @destination_owner = N'dbo',
@auto_identity_range = N'false', @verify_resolver_signature = 0,
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true',
@check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'QALockedProduct', @source_owner = N'dbo', @source_object =
N'QALockedProduct', @type = N'table', @description = null, @column_tracking =
N'true', @pre_creation_cmd = N'drop', @creation_script = null, @schema_option
= 0x000000000000CFF1, @article_resolver = null, @subset_filterclause = null,
@vertical_partition = N'false', @destination_owner = N'dbo',
@auto_identity_range = N'false', @verify_resolver_signature = 0,
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true',
@check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'ProductGroupBannerDistributorState', @source_owner = N'dbo', @source_object
= N'ProductGroupBannerDistributorState', @type = N'table', @description =
null, @column_tracking = N'true', @pre_creation_cmd = N'drop',
@creation_script = null, @schema_option = 0x000000000000CFF1,
@article_resolver = null, @subset_filterclause = null, @vertical_partition =
N'false', @destination_owner = N'dbo', @auto_identity_range = N'false',
@verify_resolver_signature = 0, @allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true', @check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'ProductFlavour', @source_owner = N'dbo', @source_object =
N'ProductFlavour', @type = N'table', @description = null, @column_tracking =
N'true', @pre_creation_cmd = N'drop', @creation_script = null, @schema_option
= 0x000000000000CFF1, @article_resolver = null, @subset_filterclause =
N'ProductFlavourInactive=0', @vertical_partition = N'false',
@destination_owner = N'dbo', @auto_identity_range = N'false',
@verify_resolver_signature = 0, @allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true', @check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'BonusDealCondition', @source_owner = N'dbo', @source_object =
N'BonusDealCondition', @type = N'table', @description = null,
@column_tracking = N'true', @pre_creation_cmd = N'drop', @creation_script =
null, @schema_option = 0x000000000000CFF1, @article_resolver = null,
@subset_filterclause = null, @vertical_partition = N'false',
@destination_owner = N'dbo', @auto_identity_range = N'false',
@verify_resolver_signature = 0, @allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true', @check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'BonusDealProductCondition', @source_owner = N'dbo', @source_object =
N'BonusDealProductCondition', @type = N'table', @description = null,
@column_tracking = N'true', @pre_creation_cmd = N'drop', @creation_script =
null, @schema_option = 0x000000000000CFF1, @article_resolver = null,
@subset_filterclause = null, @vertical_partition = N'false',
@destination_owner = N'dbo', @auto_identity_range = N'false',
@verify_resolver_signature = 0, @allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true', @check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'QAMaximumProductDetail', @source_owner = N'dbo', @source_object =
N'QAMaximumProductDetail', @type = N'table', @description = null,
@column_tracking = N'true', @pre_creation_cmd = N'drop', @creation_script =
null, @schema_option = 0x000000000000CFF1, @article_resolver = null,
@subset_filterclause = null, @vertical_partition = N'false',
@destination_owner = N'dbo', @auto_identity_range = N'false',
@verify_resolver_signature = 0, @allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true', @check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'CustomerContact', @source_owner = N'dbo', @source_object =
N'CustomerContact', @type = N'table', @description = null, @column_tracking =
N'true', @pre_creation_cmd = N'drop', @creation_script = null, @schema_option
= 0x000000000000CFF1, @article_resolver = null, @subset_filterclause = null,
@vertical_partition = N'false', @destination_owner = N'dbo',
@auto_identity_range = N'false', @verify_resolver_signature = 0,
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true',
@check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'BonusDeal', @source_owner = N'dbo', @source_object = N'BonusDeal', @type =
N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd
= N'drop', @creation_script = null, @schema_option = 0x000000000000CFF1,
@article_resolver = null, @subset_filterclause =
N'(CONVERT(DATETIME,CONVERT(VARCHAR, GETDATE(),10)) >= BeginDate) AND
(CONVERT(DATETIME,CONVERT(VARCHAR, GETDATE(),10)) <= EndDate)',
@vertical_partition = N'false', @destination_owner = N'dbo',
@auto_identity_range = N'false', @verify_resolver_signature = 0,
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true',
@check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'BonusDealProduct', @source_owner = N'dbo', @source_object =
N'BonusDealProduct', @type = N'table', @description = null, @column_tracking
= N'true', @pre_creation_cmd = N'drop', @creation_script = null,
@schema_option = 0x000000000000CFF1, @article_resolver = null,
@subset_filterclause = N'(CONVERT(DATETIME,CONVERT(VARCHAR, GETDATE(),10)) >=
BeginDate) AND (CONVERT(DATETIME,CONVERT(VARCHAR, GETDATE(),10)) <=
EndDate)', @vertical_partition = N'false', @destination_owner = N'dbo',
@auto_identity_range = N'false', @verify_resolver_signature = 0,
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true',
@check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'QAMaximumProduct', @source_owner = N'dbo', @source_object =
N'QAMaximumProduct', @type = N'table', @description = null, @column_tracking
= N'true', @pre_creation_cmd = N'drop', @creation_script = null,
@schema_option = 0x000000000000CFF1, @article_resolver = null,
@subset_filterclause = N'(CONVERT(DATETIME,CONVERT(VARCHAR, GETDATE(),10)) >=
BeginDate) AND (CONVERT(DATETIME,CONVERT(VARCHAR, GETDATE(),10)) <=
EndDate)', @vertical_partition = N'false', @destination_owner = N'dbo',
@auto_identity_range = N'false', @verify_resolver_signature = 0,
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true',
@check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'Customers', @source_owner = N'dbo', @source_object = N'Customers', @type =
N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd
= N'drop', @creation_script = null, @schema_option = 0x000000000000CFF1,
@article_resolver = null, @subset_filterclause =
N'(Customers.CustomerInactive=0)AND (CAST(RepCode AS NVARCHAR)=HOST_NAME())',
@vertical_partition = N'false', @destination_owner = N'dbo',
@auto_identity_range = N'false', @verify_resolver_signature = 0,
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true',
@check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'DistributorWarehouse', @source_owner = N'dbo', @source_object =
N'DistributorWarehouse', @type = N'table', @description = null,
@column_tracking = N'true', @pre_creation_cmd = N'drop', @creation_script =
null, @schema_option = 0x000000000000CFF1, @article_resolver = null,
@subset_filterclause = null, @vertical_partition = N'false',
@destination_owner = N'dbo', @auto_identity_range = N'false',
@verify_resolver_signature = 0, @allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true', @check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'Products', @source_owner = N'dbo', @source_object = N'Products', @type =
N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd
= N'drop', @creation_script = null, @schema_option = 0x000000000000CFF1,
@article_resolver = null, @subset_filterclause = N'ProductInactive=0',
@vertical_partition = N'false', @destination_owner = N'dbo',
@auto_identity_range = N'false', @verify_resolver_signature = 0,
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true',
@check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'QALockedBanner', @source_owner = N'dbo', @source_object =
N'QALockedBanner', @type = N'table', @description = null, @column_tracking =
N'true', @pre_creation_cmd = N'drop', @creation_script = null, @schema_option
= 0x000000000000CFF1, @article_resolver = null, @subset_filterclause = null,
@vertical_partition = N'false', @destination_owner = N'dbo',
@auto_identity_range = N'false', @verify_resolver_signature = 0,
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true',
@check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'QALockedType', @source_owner = N'dbo', @source_object = N'QALockedType',
@type = N'table', @description = null, @column_tracking = N'true',
@pre_creation_cmd = N'drop', @creation_script = null, @schema_option =
0x000000000000CFF1, @article_resolver = null, @subset_filterclause = null,
@vertical_partition = N'false', @destination_owner = N'dbo',
@auto_identity_range = N'false', @verify_resolver_signature = 0,
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true',
@check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'DistributorState', @source_owner = N'dbo', @source_object =
N'DistributorState', @type = N'table', @description = null, @column_tracking
= N'true', @pre_creation_cmd = N'drop', @creation_script = null,
@schema_option = 0x000000000000CFF1, @article_resolver = null,
@subset_filterclause = null, @vertical_partition = N'false',
@destination_owner = N'dbo', @auto_identity_range = N'false',
@verify_resolver_signature = 0, @allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true', @check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'SpecialState', @source_owner = N'dbo', @source_object = N'SpecialState',
@type = N'table', @description = null, @column_tracking = N'true',
@pre_creation_cmd = N'drop', @creation_script = null, @schema_option =
0x000000000000CFF1, @article_resolver = null, @subset_filterclause =
N'(CONVERT(DATETIME,CONVERT(VARCHAR, GETDATE(),10)) >= SpecialBeginDate) AND
(CONVERT(DATETIME,CONVERT(VARCHAR, GETDATE(),10)) <= SpecialEndDate)',
@vertical_partition = N'false', @destination_owner = N'dbo',
@auto_identity_range = N'false', @verify_resolver_signature = 0,
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true',
@check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'Users', @source_owner = N'dbo', @source_object = N'Users', @type =
N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd
= N'drop', @creation_script = null, @schema_option = 0x000000000000CFF1,
@article_resolver = null, @subset_filterclause = N'CAST(RepCode AS
NVARCHAR)=HOST_NAME()', @vertical_partition = N'false', @destination_owner =
N'dbo', @auto_identity_range = N'false', @verify_resolver_signature = 0,
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true',
@check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'ProductGroupOrder', @source_owner = N'dbo', @source_object =
N'ProductGroupOrder', @type = N'table', @description = null, @column_tracking
= N'true', @pre_creation_cmd = N'drop', @creation_script = null,
@schema_option = 0x000000000000CFF1, @article_resolver = null,
@subset_filterclause = null, @vertical_partition = N'false',
@destination_owner = N'dbo', @auto_identity_range = N'false',
@verify_resolver_signature = 0, @allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true', @check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'ProductBrands', @source_owner = N'dbo', @source_object = N'ProductBrands',
@type = N'table', @description = null, @column_tracking = N'true',
@pre_creation_cmd = N'drop', @creation_script = null, @schema_option =
0x000000000000CFF1, @article_resolver = null, @subset_filterclause = null,
@vertical_partition = N'false', @destination_owner = N'dbo',
@auto_identity_range = N'false', @verify_resolver_signature = 0,
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true',
@check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'ProductGroups', @source_owner = N'dbo', @source_object = N'ProductGroups',
@type = N'table', @description = null, @column_tracking = N'true',
@pre_creation_cmd = N'drop', @creation_script = null, @schema_option =
0x000000000000CFF1, @article_resolver = null, @subset_filterclause = null,
@vertical_partition = N'false', @destination_owner = N'dbo',
@auto_identity_range = N'false', @verify_resolver_signature = 0,
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true',
@check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'SpecialNational', @source_owner = N'dbo', @source_object =
N'SpecialNational', @type = N'table', @description = null, @column_tracking =
N'true', @pre_creation_cmd = N'drop', @creation_script = null, @schema_option
= 0x000000000000CFF1, @article_resolver = null, @subset_filterclause =
N'(CONVERT(DATETIME,CONVERT(VARCHAR, GETDATE(),10)) >= SpecialBeginDate) AND
(CONVERT(DATETIME,CONVERT(VARCHAR, GETDATE(),10)) <= SpecialEndDate)',
@vertical_partition = N'false', @destination_owner = N'dbo',
@auto_identity_range = N'false', @verify_resolver_signature = 0,
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true',
@check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'States', @source_owner = N'dbo', @source_object = N'States', @type =
N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd
= N'drop', @creation_script = null, @schema_option = 0x000000000000CFF1,
@article_resolver = null, @subset_filterclause = null, @vertical_partition =
N'false', @destination_owner = N'dbo', @auto_identity_range = N'false',
@verify_resolver_signature = 0, @allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true', @check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'QAType', @source_owner = N'dbo', @source_object = N'QAType', @type =
N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd
= N'drop', @creation_script = null, @schema_option = 0x000000000000CFF1,
@article_resolver = null, @subset_filterclause = null, @vertical_partition =
N'false', @destination_owner = N'dbo', @auto_identity_range = N'false',
@verify_resolver_signature = 0, @allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true', @check_permissions = 0
GO
exec sp_addmergearticle @publication = N'MSales1_Publication', @article =
N'QALocked', @source_owner = N'dbo', @source_object = N'QALocked', @type =
N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd
= N'drop', @creation_script = null, @schema_option = 0x000000000000CFF1,
@article_resolver = null, @subset_filterclause =
N'(CONVERT(DATETIME,CONVERT(VARCHAR, GETDATE(),10)) >= BeginDate) AND
(CONVERT(DATETIME,CONVERT(VARCHAR, GETDATE(),10)) <= EndDate)',
@vertical_partition = N'false', @destination_owner = N'dbo',
@auto_identity_range = N'false', @verify_resolver_signature = 0,
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true',
@check_permissions = 0
GO

-- Adding the article subset filter
exec sp_addmergefilter @publication = N'MSales1_Publication', @article =
N'QaMaximumFlavourDistributor', @filtername =
N'QaMaximumFlavourDistributor_QAMaximumFlavour', @join_articlename =
N'QAMaximumFlavour', @join_filterclause =
N'QaMaximumFlavourDistributor.ProductCode = QAMaximumFlavour.ProductCode AND
QaMaximumFlavourDistributor.FlavourCode = QAMaximumFlavour.FlavourCode AND
QaMaximumFlavourDistributor.QAMaxTermNo = QAMaximumFlavour.QAMaxTermNo',
@join_unique_key = 0
GO

-- Adding the article subset filter
exec sp_addmergefilter @publication = N'MSales1_Publication', @article =
N'BonusDealFlavour', @filtername = N'FK_BonusDealFlavour_BonusDeal',
@join_articlename = N'BonusDeal', @join_filterclause =
N'[BonusDealFlavour].[ProductCode] = [BonusDeal].[ProductCode] and
[BonusDealFlavour].[BonusDealNo] = [BonusDeal].[BonusDealNo]',
@join_unique_key = 1
GO

-- Adding the article subset filter
exec sp_addmergefilter @publication = N'MSales1_Publication', @article =
N'CustomerDistributor', @filtername = N'CustomerDistributor_Customers',
@join_articlename = N'Customers', @join_filterclause =
N'CustomerDistributor.CustomerCode = Customers.CustomerCode',
@join_unique_key = 0
GO

-- Adding the article subset filter
exec sp_addmergefilter @publication = N'MSales1_Publication', @article =
N'BonusDealProductState', @filtername =
N'BonusDealProductState_BonusDealProduct', @join_articlename =
N'BonusDealProduct', @join_filterclause = N'BonusDealProductState.Productcode
= BonusDealProduct.ProductCode AND BonusDealProductState.BonusDealNo =
BonusDealProduct.BonusDealNo', @join_unique_key = 0
GO

-- Adding the article subset filter
exec sp_addmergefilter @publication = N'MSales1_Publication', @article =
N'BonusDealState', @filtername = N'FK_BonusDealState_BonusDeal',
@join_articlename = N'BonusDeal', @join_filterclause =
N'[BonusDealState].[ProductCode] = [BonusDeal].[ProductCode] and
[BonusDealState].[BonusDealNo] = [BonusDeal].[BonusDealNo]', @join_unique_key
= 1
GO

-- Adding the article subset filter
exec sp_addmergefilter @publication = N'MSales1_Publication', @article =
N'QAMaximumProductDistributor', @filtername =
N'QAMaximumProductDistributor_QAMaximumProduct', @join_articlename =
N'QAMaximumProduct', @join_filterclause =
N'QAMaximumProductDistributor.ProductCode = QAMaximumProduct.ProductCode AND
QAMaximumProductDistributor.QAMaxTermNo = QAMaximumProduct.QAMaxTermNo',
@join_unique_key = 0
GO

-- Adding the article subset filter
exec sp_addmergefilter @publication = N'MSales1_Publication', @article =
N'QALockedProduct', @filtername = N'QALockedProduct_QALocked',
@join_articlename = N'QALocked', @join_filterclause =
N'QALockedProduct.DistributorCode = QALocked.DistributorCode AND
QALockedProduct.StateCode = QALocked.StateCode AND QALockedProduct.QADealNo =
QALocked.QADealNo', @join_unique_key = 0
GO

-- Adding the article subset filter
exec sp_addmergefilter @publication = N'MSales1_Publication', @article =
N'BonusDealCondition', @filtername = N'FK_BonusDealCondition_BonusDeal',
@join_articlename = N'BonusDeal', @join_filterclause =
N'[BonusDealCondition].[ProductCode] = [BonusDeal].[ProductCode] and
[BonusDealCondition].[BonusDealNo] = [BonusDeal].[BonusDealNo]',
@join_unique_key = 1
GO

-- Adding the article subset filter
exec sp_addmergefilter @publication = N'MSales1_Publication', @article =
N'BonusDealProductCondition', @filtername =
N'BonusDealProductCondition_BonusDealProduct', @join_articlename =
N'BonusDealProduct', @join_filterclause =
N'BonusDealProductCondition.ProductCode = BonusDealProduct.ProductCode AND
BonusDealProductCondition.BonusDealNo = BonusDealProduct.BonusDealNo',
@join_unique_key = 0
GO

-- Adding the article subset filter
exec sp_addmergefilter @publication = N'MSales1_Publication', @article =
N'QAMaximumProductDetail', @filtername =
N'QAMaximumProductDetail_QAMaximumProduct', @join_articlename =
N'QAMaximumProduct', @join_filterclause =
N'QAMaximumProductDetail.ProductCode = QAMaximumProduct.ProductCode AND
QAMaximumProductDetail.QAMaxTermNo = QAMaximumProduct.QAMaxTermNo',
@join_unique_key = 0
GO

-- Adding the article subset filter
exec sp_addmergefilter @publication = N'MSales1_Publication', @article =
N'CustomerContact', @filtername = N'CustomerContact_Customers',
@join_articlename = N'Customers', @join_filterclause =
N'CustomerContact.CustomerCode = Customers.CustomerCode', @join_unique_key = 0
GO

-- Adding the article subset filter
exec sp_addmergefilter @publication = N'MSales1_Publication', @article =
N'QALockedBanner', @filtername = N'QALockedBanner_QALocked',
@join_articlename = N'QALocked', @join_filterclause =
N'QALockedBanner.DistributorCode = QALocked.DistributorCode AND
QALockedBanner.StateCode = QALocked.StateCode AND QALockedBanner.QADealNo =
QALocked.QADealNo', @join_unique_key = 0
GO

-- Adding the article subset filter
exec sp_addmergefilter @publication = N'MSales1_Publication', @article =
N'QALockedType', @filtername = N'QALockedType_QALocked', @join_articlename =
N'QALocked', @join_filterclause =
N'QALocked.DistributorCode=QALockedType.DistributorCode AND
QALocked.StateCode=QALockedType.StateCode AND
QALocked.QADealNo=QALockedType.QADealNo', @join_unique_key = 0
GO

/****** End: Script to be run at Publisher: NICK ******/


Paul Ibison

unread,
Jan 18, 2005, 4:27:45 AM1/18/05
to
Thanks Nick, but as Hilary says we'll need the table
schema also to reproduce it. Please post it up and
someone'll take a look.
Rgds,
Paul Ibison

Nick

unread,
Jan 18, 2005, 3:13:04 PM1/18/05
to
Hi paul:

Do you mean the relationship of tables?

Cheers

nick

Paul Ibison

unread,
Jan 19, 2005, 9:25:01 AM1/19/05
to
Nick,
you need to select the tables in Enterprise Manager and
right click them, all tasks, generate sql script. You
shouldn't need to add the PKs and FKs but might as well
for the sake of completion. This is on the options tab,
script primark keys....
Rgds,
Paul Ibison

Nick

unread,
Jan 19, 2005, 4:16:56 PM1/19/05
to
Hi Paul:

I post my product table script:

CREATE TABLE [dbo].[Products] (
[ProductCode] [int] IDENTITY (1, 1) NOT NULL ,
[ProductName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProductGroupCode] [smallint] NOT NULL ,
[ProductGroupOrderCode] [smallint] NOT NULL ,
[ProductBrandCode] [smallint] NOT NULL ,
[ProductCaseSize] [smallint] NOT NULL ,
[ProductUnitSize] [smallint] NOT NULL ,
[ProductPalletSize] [smallint] NOT NULL ,
[ProductInactive] [bit] NOT NULL ,
[ProductAllowUnits] [bit] NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Products] ADD
CONSTRAINT [DF_Products_ProductInactive] DEFAULT (0) FOR [ProductInactive],
CONSTRAINT [DF_Products_ProductAllowUnits] DEFAULT (0) FOR
[ProductAllowUnits],
CONSTRAINT [DF__Products__rowgui__5D21AF45] DEFAULT (newid()) FOR [rowguid],
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[ProductCode]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Products] ADD
CONSTRAINT [FK_Products_ProductBrands] FOREIGN KEY
(
[ProductBrandCode]
) REFERENCES [dbo].[ProductBrands] (
[ProductBrandCode]
),
CONSTRAINT [FK_Products_ProductGroupOrder] FOREIGN KEY
(
[ProductGroupOrderCode]
) REFERENCES [dbo].[ProductGroupOrder] (
[ProductGroupOrderCode]
),
CONSTRAINT [FK_Products_ProductGroups] FOREIGN KEY
(
[ProductGroupCode]
) REFERENCES [dbo].[ProductGroups] (
[ProductGroupCode]
)
GO

Paul Ibison

unread,
Jan 20, 2005, 4:17:57 AM1/20/05
to
Nick,
what about all the other tables in this publication (or
do you know that it is this table that causes the
problem). Also, can you enable logging to see it we can
get more error info: http://support.microsoft.com/?
id=312292
Rgds,
Paul Ibison (SQL Server MVP)

Nick

unread,
Jan 20, 2005, 3:11:06 PM1/20/05
to
Hi Paul:

the siuation is i already done 30 tables replication , and product
table is last table i gonna to add in. i can add in and replicated to PDA
without any problems but only if do not add row filter in product table?

so i am wondering is that becasue productCode is a identity fields?


Thanks

Nick

Nick

unread,
Jan 21, 2005, 6:46:35 PM1/21/05
to
HI Paul:

I have two tables Customers and BannerGroups,
when i do replication with those two tables without set join filter. it
works fine. but when i set join filter for those two table :
Customers.BannerGroupCode = BannerGroups.BannerGroupCode. it will fail to
generate snapshot with error "invalid column name 'bannerGroupCode'"

Below are scripts of customer and bannerGroupCode:


CREATE TABLE [dbo].[BannerGroups] (
[BannerGroupCode] [int] NOT NULL ,
[BannerGroupName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[BannerGroupContactName] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BannerGroupContactEmail] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,


[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Customers] (
[CustomerCode] [int] NOT NULL ,
[CustomerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[CustomerAlias] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerLiquorLicence] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CustomerAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CustomerCity] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerPostCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CustomerPhone] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerMobile] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerPhoneOther] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CustomerEmail] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerAccount] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[CustomerInactive] [bit] NOT NULL ,
[StateCode] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ReportGroupCode] [int] NULL ,
[BannerGroupCode] [int] NOT NULL ,
[RepCode] [int] NOT NULL ,
[LastVerified] [datetime] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL ,
[CustomerFax] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[BannerGroups] ADD
CONSTRAINT [DF__BannerGro__rowgu__703483B9] DEFAULT (newid()) FOR
[rowguid],
CONSTRAINT [PK_BannerGroups] PRIMARY KEY CLUSTERED
(
[BannerGroupCode]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [IX_BannerGroups] UNIQUE NONCLUSTERED
(
[BannerGroupName]


) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Customers] ADD
CONSTRAINT [DF__Customers__Inact__5BAD9CC8] DEFAULT (0) FOR
[CustomerInactive],
CONSTRAINT [DF__Customers__rowgu__17635F73] DEFAULT (newid()) FOR
[rowguid],
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[CustomerCode]


) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Customers] ADD
CONSTRAINT [FK_Customers_BannerGroups] FOREIGN KEY
(
[BannerGroupCode]
) REFERENCES [dbo].[BannerGroups] (
[BannerGroupCode]
),
CONSTRAINT [FK_Customers_ReportGroups] FOREIGN KEY
(
[ReportGroupCode]
) REFERENCES [dbo].[ReportGroups] (
[ReportGroupCode]
),
CONSTRAINT [FK_Customers_Reps] FOREIGN KEY
(
[RepCode]
) REFERENCES [dbo].[Reps] (
[RepCode]
),
CONSTRAINT [FK_Customers_States] FOREIGN KEY
(
[StateCode]
) REFERENCES [dbo].[States] (
[StateCode]
)
GO


Paul Ibison

unread,
Jan 21, 2005, 4:03:50 AM1/21/05
to
Nick,
the column filter info now point at this error:
http://support.microsoft.com/kb/821535
0 new messages