after exec sp_addmergearticle sp_addmergefilter snapshot gives invalid column

[Pedro Felix]

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

ALTER TABLE [dbo].[Client] ADD
CONSTRAINT [DF__Client__rowguid__4D94879B] DEFAULT (newid()) FOR
[rowguid]
GO

CREATE INDEX [FK_Client_SalesRep] ON [dbo].[Client]([SalesRepID]) ON
[PRIMARY]
GO

CREATE UNIQUE INDEX [index_421576540] ON [dbo].[Client]([rowguid])
ON [PRIMARY]
GO

--------------------------------------------------------------------------------

CREATE TABLE [dbo].[Contact] (
[ContactID] [varchar] (15) COLLATE Latin1_General_CI_AS NOT NULL ,
[ContactFirstName] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[ContactLastName] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[ContactTelephone] [varchar] (13) COLLATE Latin1_General_CI_AS NULL ,
[ContactFax] [varchar] (13) COLLATE Latin1_General_CI_AS NULL ,
[ContactMobile] [varchar] (13) COLLATE Latin1_General_CI_AS NULL ,
[ContactEmail] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[ClientID] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Contact] WITH NOCHECK ADD
CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED
(
[ContactID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Contact] ADD
CONSTRAINT [DF__Contact__rowguid__4E53A1AA] DEFAULT (newid()) FOR
[rowguid]
GO

CREATE INDEX [FK_Contact_Client] ON [dbo].[Contact]([ClientID]) ON
[PRIMARY]
GO

CREATE UNIQUE INDEX [index_1218103380] ON
[dbo].[Contact]([rowguid]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Contact] ADD
CONSTRAINT [FK_Contact_Client] FOREIGN KEY ([ClientID] REFERENCES
[dbo].[Client] ([ClientID])
GO

I'm using dynamic filters in table [Client] and I want to join the
[Contact] with it and get all the contacts associated with the my
clients subsetting.

Can anyone help me to overcome this! Help will be appreciated.
Thanks in advance
Pedro Felix
+

[Greg Yvkof [MSFT]]

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?

"Pedro Felix" <pedro...@mail.com> wrote in message
news:1efd55b0.03091...@posting.google.com...

[debbie]

Hello Pedro,

I've run your scripts on my test server with out experiencing the problem
you indicated in your message. So what I would like you to do is look at
the publisher and subscriber and see if you have two sets of stored
procedures for inserts, updates and deletes. They should begin with
sp_insxxxxxxxxxxx, sp_selxxxxxxxxxxxxx, sp_updxxxxxxxxx. I'm thinking the
stored procedure might not have been created or not created correctly. I
want to assure you that what you are doing is possible and can be done
without problems.

thanks
debbie
--------------------
| From: pedro...@mail.com (Pedro Felix)
| Newsgroups: microsoft.public.sqlserver.replication
| Subject: after exec sp_addmergearticle sp_addmergefilter snapshot gives
invalid column
| Date: 18 Sep 2003 11:00:19 -0700
| Organization: http://groups.google.com/
| Lines: 292
| Message-ID: <1efd55b0.03091...@posting.google.com>
| NNTP-Posting-Host: 194.30.112.3
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1063908020 16455 127.0.0.1 (18 Sep 2003
18:00:20 GMT)
| X-Complaints-To: groups...@google.com
| NNTP-Posting-Date: 18 Sep 2003 18:00:20 GMT
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!cyclone.bc.net!sjc70.we
busenet.com!news.webusenet.com!sn-xit-02!sn-xit-06!sn-xit-05!sn-xit-09!super
news.com!postnews1.google.com!not-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.replication:43486
| X-Tomcat-NG: microsoft.public.sqlserver.replication

[Pedro Felix]

Hi Greg,

thank you for your reply!

The Snapshot log I posted was made with "-OutputVerboseLevel 2" and it
was this scripted out
via EM!

Anyway, the odd think is if I execute/add the two tables without running
the snapshot betwen them everythink works well! But I was trying to
simulate a add of tables to a snapshot with subscriptions (as in
production the sales representative are in the street)

Conclusion:
-If I run all the script once - works fine
-If I add a table with subsetting, run the snapshot, add the other table
and a merge filter, snapshot fails!

Best regards,
Pedro Felix

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Pedro Felix]

Hi Debbie,

I'm not sure that you receive my previous post/reply so I will repeat
it!

It's strange that you manage to make it work with my scripts! I can see
the
triggers you mentioned but only for "Client" table! I profiled the run
of the
snapshot an I manage to see where the error occurs:

exec sp_MSprepare_mergearticle N'dbo', N'Client', N'SFA',
N'[dbo].[Client]'

after seeing the sp properties, the line that gives error is:

exec sp_MSaddmergetriggers N'[dbo].[Client]' with:

"Invalid column name 'ClientID'."

So my question is:
Did you run the snapshot before adding the "Contact" article?
To get the error you have to:
-Creating the Publication
-Add the "Client" article
-Run the SNAPSHOT (this is important!)
-Then add "Contact" article with sp_addmergearticle
and sp_addmergefilter
-Run the SNAPSHOT and get the error:
"Invalid column name 'ClientID'."

Hope this helps, thanks

regards

[debbie]

Hello Pedro,

I did indeed follow your instructions with create snapshot for the client
table then add contact table so I know the process works. I've also done
this type of simular procedure in production so I do know this is possible.
I've done this with transactional as well as merge replication.

So in your database SFA there are no stored procedure and only some
triggers for the client table? Can you we go with this plan of action?
1. Uninstall the merge replication. Make sure no remnants of replication
remain
2. Start with you first set of scripts up to the first snapshot and make
sure it is run in the publication database. Also make sure your
publication name is a little different than your database name. It really
shouldn't matter but I've always done it as a precaution.
3. Check in the publication database for the stored procedures and
triggers. You can see them by running "select * from sysobjects where
type='P' and select * from sysobjects where type='TR'". If you really want
to confirm the triggers and stored procedure belong to article check the
sysmergearticles in you publication database.
4. Add the last table and snapshot. Make sure you are in the publication
database when you run the script.
5. Check the publication database for the stored procedures and triggers
that belong to the new article.

If all doesn't go well from there then we'll tackle what might have
happened during the creation of the triggers and stored procedures.

thanks
debbie
--------------------
| From: Pedro Felix <pedro...@mail.com>
| References: <QEBiy$tfDHA...@cpmsftngxa06.phx.gbl>
| X-Newsreader: AspNNTP 1.50 (ActionJackson.com)
| Subject: RE: after exec sp_addmergearticle sp_addmergefilter snapshot
gives invalid column
| Mime-Version: 1.0
| Content-Type: text/plain; charset="us-ascii"
| Content-Transfer-Encoding: 7bit
| Message-ID: <OEWJ$5QgDH...@TK2MSFTNGP11.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.replication
| Date: Mon, 22 Sep 2003 06:36:14 -0700
| NNTP-Posting-Host: actionjackson133.dsl.frii.net 216.17.147.133
| Lines: 1
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.replication:43544
| X-Tomcat-NG: microsoft.public.sqlserver.replication

[Pedro Felix]

Hi Debbie,

sorry for the delay but I've been out of country!
Back to your issue, I'm afraid I mislead you on
>I posted:

>It's strange that you manage to make it work with my scripts! I can
see the triggers you
>mentioned but only for "Client" table!

>You reply:

>So in your database SFA there are no stored procedure and only some
triggers for the client >table?

I can see the triggers and stored procedures for table "Client" but
not for table "Contact". My apologies on that.

Nevertheless I installed SQL2000 SP3a in a new desktop machine and
still didn't work!
Neither through Enterprise Manager or by script in SQL Analyzer! If
you are able to make it work I must be doing something wrong or
working with wrong versions!

I've tested SQL2000 SP3a on WinXP SP1 professional and Win2000
professional SP3!

regards,
Pedro Felix

[debbie]

Hello Pedro,

Can you go ahead and give me the schema for you CO table? I want to make
sure I have all the peices of this problem.

debbie