sql server replication: Hi,

I'm a system integrator and I'm using oracle lite 9i with 200 users
with PPC in our solution for 2 years!

We are going worldwide in our costumer with this solution and we are
evaluating SQLSERVER2K & SQLCE as an alternative!

I'm having some problems with Replication tests in SQLSERVER SP3a!

Our problem is: In oracle we can manage to add and change publications
without the user even suspect that something has changed. So we are
able to add, change and drop articles without loosing anything or even
initialize the replication!
The costumer don't want to loose this so I'm trying to test it and I'm
stuck in this:

After Creating a publication for my Database with one table (Clients)
and sync with a PPC 2000/2002. I receive the desired table and data!
In server after adding another table (Contacts) and try to run the
snapshot I receive "Invalid column name 'ClientID'".

My steps to get there were:

use [SFA]
GO

-- Dropping the merge publication
exec sp_dropmergepublication
@publication = N'SFA'
GO

-- Enabling the replication database
use master
GO

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

use [SFA]
GO

-- Adding the merge publication
exec sp_addmergepublication
@publication = N'SFA',
@description = N'Merge publication of SFA database from Publisher
PFELIX.',
@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'SFA',
@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'PFELIX-SFA-SFA-8'
GO

exec sp_grant_publication_access
@publication = N'SFA',
@login = N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access
@publication = N'SFA',
@login = N'distributor_admin'
GO
exec sp_grant_publication_access
@publication = N'SFA',
@login = N'sa'
GO

exec sp_addmergearticle
@publication = N'SFA',
@article = N'Client',
@source_owner = N'dbo',
@source_object = N'Client',
@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'SalesRepID = 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

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
OK - Run the Snapshot on the SQL Server Enterprise Manager
OK - Sync with handheld (Got the Client Table and Data)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

-- Adding the merge articles
exec sp_addmergearticle
@publication = N'SFA',
@article = N'Contact',
@source_owner = N'dbo',
@source_object = N'Contact',
@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 = 3,
@force_invalidate_snapshot = 1
GO

-- Adding the article subset filter
exec sp_addmergefilter
@publication = N'SFA',
@article = N'Contact',
@filtername = N'Contact_Client',
@join_articlename = N'Client',
@join_filterclause = N'[Contact].[ClientID]=[Client].[ClientID]',
@join_unique_key = 0,
@force_invalidate_snapshot = 1,
@force_reinit_subscription = 1
GO


--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
FAILED + - Fail to run the Snapshot on the SQL Server Enterprise
Manager
- Got the error "Invalid column name 'ClientID'"


Microsoft SQL Server Snapshot Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation

Connecting to Distributor 'PFELIX'
Connecting to Publisher 'PFELIX.SFA'

Server:
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288

[9/18/2003 4:12:22 PM]PFELIX.SFA: sp_MSgetversion
Initializing the publication 'SFA'
Warning: Updates at a Subscriber may violate constraints at the
Publisher because foreign key constraints were not scripted.
Warning: Unpublished table 'CO' references published table 'Client'.

*** [Publication:'SFA'] Publication view generation time: 301 (ms) ***

Preparing table '[dbo].[Client]' for merge replication
SourceTypeId = 5
SourceName = PFELIX
ErrorCode = 207
ErrorText = Invalid column name 'ClientID'.
Invalid column name 'ClientID'.
Disconnecting from Publisher 'PFELIX'
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

CREATE TABLE [dbo].[Client] (
[ClientID] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[ClientName] [varchar] (40) COLLATE Latin1_General_CI_AS NULL ,
[SalesRepID] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Client] WITH NOCHECK ADD
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientID]
) ON [PRIMARY]
GO

One of two things can help. Run snapshot agent with -OutputVerboseLevel 2,
this gives more detail in the output and error message.

Second thing is, your very last alter table statement doesn't look correct,
the one with the FOREIGN KEY REFERENCES statement. Was this scripted out
via EM?




[quoted text, click to view]