Invisible rows after failed syncronization

Publisher: SQL Server 2000 SP4
Subscriber: SQL Server 2005 Mobile Edition

Sometimes, after a failed merge replication(due to communication error) some rows on the subscriber became invisible for the publisher.

I've reproduced a case:

[START Short Version]
All rows inserted between a comm failed sync and a fine sync became invisible.
This seems to be caused because the subscriber keeps generating rows with the same generation number and the publisher doesn't look for this generation anymore.
[END Short Version]

[START Detailed case]
STEP 0: INITIAL STATE
Publisher
Orders Table
EMPTY

Subscriber
NO DATABASE

STEP 1: After FIRST SYNC and INSERTING 2 Orders

Publisher

Orders Table

EMPTY

Subscriber

Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 81
000002 4 4 81

STEP 2: After SECOND SYNC

Publisher

Orders Table

OrderId

000001

000002

Subscriber

Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80

STEP 3: INSERT ANOTHER ORDER

Subscriber

Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80

000003 6 6 81

STEP 4: After THIRD SYNC (with comm error)
Error: [NativeError:28037][HRESULT:-2147012889]->[A request to send data to the computer running IIS has failed. For more information, see HRESULT.]
When: Error happens provoked after sending from Subscriber to Publisher but before ending syn process.
Publisher
Orders Table

OrderId

000001

000002

000003
Subscriber
Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80
000003 6 6 81
Note: Publisher has the row but subscriber keeps the state in 81.

STEP 5: INSERT ANOTHER ORDER (BEFORE A GOOD SYNC)
Subscriber
Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80
000003 6 6 81

000004 6 6 81
Note: Orders 000003 and 000004 have the same system info

STEP 6: After FOURTH SYNC
Publisher
Orders Table

OrderId
000001

000002

000003
Subscriber
Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80
000003 6 6 81

000004 6 6 81

Note: Even with a fine merge process Publisher didn't get the 000004 row and the Subscriber didn't update the __sysMC

STEP 7: INSERT ANOTHER ORDER

Subscriber

Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80

000003 6 6 81

000004 6 6 81

000005 8 8 81

STEP 8: After FIFTH SYNC

Publisher
Orders Table
OrderId
000001
000002
000003
000005
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80
000003 6 6 81

000004 6 6 81

000002 8 8 80

Note: Data from generation 8 merge correctly, but data from generation 6 keeps invisible to publisher.
STEP 9: DUMMY UPDATE ON GEN 6 ROWS
Action: I made a non relevant update to force the Subscriber update the generation.
Subscriber
Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80
000003 6 10 81
000004 6 10 81
000005 8 8 80

Note: sysCG get its value updated correctly.

STEP 10: After SIXTH SYNC

Publisher
Orders Table
OrderId
000001
000002
000003
000004

000005
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80

000003 6 6 80

000004 6 6 80

000002 8 8 80

Note: Data merges correctly.
[END Detailed case]

I could code a system that checks all the publication tables and updates all the last generation rows in case of communication error but i would really like to avoid doing it.

I don't know if I'm missing something or if this is a bug or a known issue.

This is a big problem for me because communication errors on cellular phone based connection are quite common and users keep working even if there's an error.

Any comment will be appreciated.Finally, I've modified my replication process solve this manually.

On a normal case (no previous error):
1. I get the current generation (__sysMergeSubscriptions.LastUploadedGen+1)
2. Launch a normal sync.
3. If it fails I save the failed generation for using it on next sync.

On the previous error case:
1. Do a normal sync (to get a valid __sysMergeSubscriptions.LastUploadedGen)
2. Do a dummy update on all rows (from all published tables (from __sysMergeArticles)) that __sysIG or __sysCG equals the wrong generation number.
3. Do a normal sync to send all the invisible rows to the publisher.

I don't like my own solution, but that's the only way I have to keep the system working as I haven't find any reference to this problem anywhere.|||

Hey MeZKaL,

I just have few questions to narrow downt he problem point in the whole path of sync components.

In the same above scenario, can you please also let me know the contents of __sysMergeSubscription and
__sysMergeSubscriptionProperties table for that subscription in each of the above STEPs (esp. Steps 4 to 6)?

Basically, I just want to know which of the following is true (after a failed sync)

1) Client did not enumerate the changes and hence did not send the changes

2) Client has enumerated the changes, sent the changes but metadata does not say its new change

3) Client has enumerated, sent the changes, metadata also says its a new change, but publisher ignores it for some internal
reasons like publisher metadata says its a false change ...etc

Thanks,

Laxmi

|||I repeated the case saving a copy of the mobile db for each step.

There are no changes on __sysMergeSubscriptionProperties so I will put it just one time.

__sysMergeSubscriptionProperties
SubscriptionId: 87a0e646-d908-b0b9-e2b1-d6eb58165c8f
Distributor
DistributorAddress
DistributorNetwork: 0
DistributorSecurityMode: 1
DistributorLogin
DistributorPassword
ExchangeType: 3
InternetLogin: domain\user
InternetURL: URL
InternetPassword: *******
LoginTimeout: 15
ProfileName: DEFAULT
Publisher: SERVER\INSTANCE
PublisherAddress
PublisherNetwork: 0
PublisherDatabase: DBName
PublisherSecurityMode: 1
PublisherLogin: domain\user
PublisherPassword: *******
Publication: PubName
QueryTimeout: 300
Validate: 0
HostName
Subscriber: domain\user
InternetProxyServer
InternetProxyLogin
InternetProxyPassword
ConnectionRetryTimeout: 120
CompressionLevel: 1
ConnectionManager: 0
SnapshotTransferType: 99

Many of the field value from __sysMergeSubscriptions didn't change during the process.

__sysMergeSubscriptions
Publisher: server\instance
PublisherDatabase: DBName
Publication: PubName
PublicationId: 03e497f6-83b6-4de3-87e3-e97835880c94
SubscriptionId: 87a0e646-d908-b0b9-e2b1-d6eb58165c8f
SyncPartnerId: 751a4dfe-2eba-7a4a-b11a-35beec9a8bf3
SyncAnchorId: 00000000-0000-0000-0000-000000000000
SchemaVersion: 481
SchemaVersionId: 00000000-0000-0000-0000-000000000000
ReplNick (casted to int): -312916250
Retention: 0
MachineId: 374677b5-b002-d9f5-d4e4-4628538f2ccd
PathName: Path\DB.sdf
ReinitRequested: 0
ReplProp: NULL
HostName:
ValidationHostName: 0
ReplicaState: 00000000-0000-0000-0000-000000000000
LastCleanupDate: 15/11/2006 16:30
ReconcilerVersion: 8
Subscriber: domain\user

[START Detailed case]

STEP 1: After FIRST SYNC
Publisher
Orders Table
EMPTY

Subscriber
Orders Table
EMPTY

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
4977 0 15/11/2006 16:30 70
SentGen80Id: 61327c39-f34b-15d1-3924-8e2577f94d6f

STEP 2: After INSERTING 2 Orders
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 4 81
57001 4 4 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
4977 0 15/11/2006 16:30 70
SentGen80Id: 61327c39-f34b-15d1-3924-8e2577f94d6f

STEP 3: After SECOND SYNC (with comm error)
Error: [NativeError:28037][HRESULT:-2147012889]->[A request to send data to the computer running IIS has failed. For more information, see HRESULT.]
When: Error happens provoked after sending from Subscriber to Publisher but before ending syn process.
Publisher
Orders Table
OrderId
57000
57001

Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 4 81
57001 4 4 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
4977 0 15/11/2006 16:30 70
SentGen80Id: 61327c39-f34b-15d1-3924-8e2577f94d6f

STEP 4: INSERT ANOTHER ORDER
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 4 81
57001 4 4 81
57002 4 4 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
4977 0 15/11/2006 16:30 70
SentGen80Id: 61327c39-f34b-15d1-3924-8e2577f94d6f

STEP 5: After THIRD SYNC
Publisher
Orders Table
OrderId
57000
57001

Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 4 81
57001 4 4 81
57002 4 4 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
5095 5 15/11/2006 17:22 71
SentGen80Id: e5ff3df6-195c-3145-273e-86784ace7e16

STEP 6: DUMMY UPDATE
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 6 81
57001 4 6 81
57002 4 6 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
5095 5 15/11/2006 17:22 71
SentGen80Id: e5ff3df6-195c-3145-273e-86784ace7e16

STEP 7: After FOURTH SYNC
Publisher
Orders Table
OrderId
57000
57001
57002

Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 6 80
57001 4 6 80
57002 4 6 80

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
5213 7 15/11/2006 17:35 72
SentGen80Id
SentGen80Id: 4171a971-b963-a541-300a-0b1602902e39

[__sysMergeSubscriptions DETAIL]
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
step1 4977 0 15/11/2006 16:30 70
step2 4977 0 15/11/2006 16:30 70
step3 4977 0 15/11/2006 16:30 70
step4 4977 0 15/11/2006 16:30 70
step5 5095 5 15/11/2006 17:22 71
step6 5095 5 15/11/2006 17:22 71
step7 5213 7 15/11/2006 17:35 72

SentGen80Id
61327c39-f34b-15d1-3924-8e2577f94d6f
61327c39-f34b-15d1-3924-8e2577f94d6f
61327c39-f34b-15d1-3924-8e2577f94d6f
61327c39-f34b-15d1-3924-8e2577f94d6f
e5ff3df6-195c-3145-273e-86784ace7e16
e5ff3df6-195c-3145-273e-86784ace7e16
4171a971-b963-a541-300a-0b1602902e39|||Can you open a case with CSS please for this? That way we can get a better handle on this in a timely manner.|||My workaround runs pretty well and I don't have time now for spending hours and hours crossing mails and test results with a support service.

If anyone can help with the previous data, it'll be welcomed but I won't spent more time testing and logging for this issue. I've lots of work and not many time.

Invalid Object Name

I have merge replication setup on MY SLQ server 2000 service pack 2 server.

I was having no issue with replication for the past three month however now I am getting this error when I try to update a record in the database.

In\valid Object Nmae 'ctsv_.....'

I am not sure what is wong with it. Any help or suggestion.

Thanks

Tanweer

you have to let us know what changes were made to your publication. Do you know if the object does in fact exist?|||

No structure changes were made however our system regenerate sanpshot weekly should I turned it off.

Thanks

Tanweer

|||If this worked previously, but not now, something must have changed. Either that or you should describe your scenario in more detail. Does the object exist in question exist?

Invalid Object Name

I have merge replication setup on MY SLQ server 2000 service pack 2 server.

I was having no issue with replication for the past three month however now I am getting this error when I try to update a record in the database.

In\valid Object Nmae 'ctsv_.....'

I am not sure what is wong with it. Any help or suggestion.

Thanks

Tanweer

you have to let us know what changes were made to your publication. Do you know if the object does in fact exist?|||

No structure changes were made however our system regenerate sanpshot weekly should I turned it off.

Thanks

Tanweer

|||If this worked previously, but not now, something must have changed. Either that or you should describe your scenario in more detail. Does the object exist in question exist?sql

Invalid Descriptor Index

I have a Sql Server 2000 SP3 Publisher that performs merge replication with a group of MSDE 2000 SP3 subscribers.
Everything was working fine. I had to make changes to the publication and reinit the subscribers. Some subscribers are working fine. Others are getting an error. The error is
The process could not enumerate changes at the 'Subscriber'.
The process could not enumerate changes at the 'Subscriber'.
(Source: Merge Replication Provider (Agent); Error number: -2147200999)
------
Invalid Descriptor Index
(Source: XXXXXXXX(Data source); Error number: 0)
------
Anyone know what might cause this? And the solution for it?
90 % of the failure to enumerat changes errors can be cleared by restarting the agent.
In your case the invalid descriptor index is unusual and points to an ODBC error. SQL Server replication uses ODBC to communicate between servers.
There are some reports that you can clear this error by rearranging your columns returned in your results set.
It sounds like this might be a bug.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||"There are some reports that you can clear this error by rearranging your columns returned in your results set."
I can't rearrange anything since its setup from replication itself. Its wierd because some subscribers to go through fine, others are failing with the error.
|||Is this error a transient error for these subscribers? Are these subscribers at the same MDAC, sp, hot fix level for that the successful subscribers are at?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Invalid column name 'rowguid'

I'm trying to set up a merge publication in MSSQL 2005 SP1.

I have a database which I have successfully made into a merge publication. When I run the snapshot agent, several articles are processed successfully, then I encounter an error with the following: Invalid column name 'rowguid'

Note that none of the tables contain 'rowguid' columns before this process - I let MSSQL automatically create these as needed.

The replication monitor details the following error:

Error messages:

· Message: Invalid column name 'rowguid'.
Command Text: sp_MSpublicationview
Parameters: @.publication = RelMS Publication
Stack: at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command)
at Microsoft.SqlServer.Replication.AgentCore.AgentExecuteNonQuery(SqlCommand command, Int32 queryTimeout)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate, Int32 queryTimeout)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate)
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.MakePublicationViews()
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoRegularMergeSnapshotPreparations()
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoPreArticleFilesGenerationProcessing()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 4615)
Get help: http://help/4615

· Server [XXXX\XXXXXX], Level 16, State 1, Procedure , Line 1
Invalid column name 'rowguid'. (Source: MSSQLServer, Error number: 4615)
Get help: http://help/4615

Can anyone shed further light on this?

Thanks.

Further note: I downloaded and applied MSSQL 2005 SP2 just now and started process from scratch (i.e. restored clean database with no replication, then created publication successfully, then ran snapshot agent). Exactly same error was encountered.

Further details that might be relevant:

Publisher and Distributor are set to same. Error persists when compatibility is 2005 or 2000.

Unfortunately the error report doesn't indicate which article was being processed when the error occurred, and the list of successfully processed articles in the log doesn't seem to follow any discernible order.

Any suggestions?

|||

Hi Ramaj;

What are the values for @.keep_partition_changes and @.use_partition_groups parameters in your replication creation script.

|||

Hi Hakan,

@.keep_partition_changes = N'true'

@.use_partition_groups not specified

Thanks

|||

Hi Ramaj;

Could you please try to set @.keep_partition_changes = N'false' then retry to run snapshot.

If the same error occours please try again by adding @.use_partition_groups= N'true' and @.keep_partition_changes = N'false' .

|||

Thanks Hakan, the first step by itself (i.e. @.keep_partition_changes = N'false') did the trick and the snapshot completed successfully. What a strange and misleading error message though - completely had me barking up the wrong tree.

Your help was much appreciated!

|||

I would still be ineterested in finding out what is causing the problem.

Can you run snapshot with higher outputverboseLevel 2. That should tell you which article you have issues with. Then isolating that article in a sepearate publication would help. Also the article schema, publication and article script would really help us troubleshoot the problem.

|||

It's very easy to repro this issue.

Create a one table db with two cols one for filtering.

Below script creates db, table and replication. But the key is to setting keep_partiton_changes = true.

-- CREATE DB
USE [master]
GO
/****** Object: Database [REPLTEST] Script Date: 02/26/2007 10:15:31 ******/
CREATE DATABASE [REPLTEST] ON PRIMARY
( NAME = N'REPLTEST', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\REPLTEST.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'REPLTEST_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\REPLTEST_log.ldf' , SIZE = 3840KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE Turkish_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @.dbname=N'REPLTEST', @.new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [REPLTEST].[dbo].[sp_fulltext_database] @.action = 'disable'
end
GO
ALTER DATABASE [REPLTEST] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [REPLTEST] SET ANSI_NULLS OFF
GO
ALTER DATABASE [REPLTEST] SET ANSI_PADDING OFF
GO
ALTER DATABASE [REPLTEST] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [REPLTEST] SET ARITHABORT OFF
GO
ALTER DATABASE [REPLTEST] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [REPLTEST] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [REPLTEST] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [REPLTEST] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [REPLTEST] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [REPLTEST] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [REPLTEST] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [REPLTEST] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [REPLTEST] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [REPLTEST] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [REPLTEST] SET ENABLE_BROKER
GO
ALTER DATABASE [REPLTEST] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [REPLTEST] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [REPLTEST] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [REPLTEST] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [REPLTEST] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [REPLTEST] SET READ_WRITE
GO
ALTER DATABASE [REPLTEST] SET RECOVERY SIMPLE
GO
ALTER DATABASE [REPLTEST] SET MULTI_USER
GO
ALTER DATABASE [REPLTEST] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [REPLTEST] SET DB_CHAINING OFF
GO

-CREATE TABLE
USE REPLTEST
CREATE TABLE [dbo].[TBLTEST](
[nvarchar](50) COLLATE Turkish_CI_AS NULL,
[UID] [nvarchar](10) COLLATE Turkish_CI_AS NULL,
) ON [PRIMARY]

CREATE REPL
-- Enabling the replication database
use master
exec sp_replicationdboption @.dbname = N'REPLTEST', @.optname = N'merge publish', @.value = N'true'
GO

-- Adding the merge publication
use [REPLTEST]
exec sp_addmergepublication @.publication = N'REPLTEST', @.description = N'Merge publication of REPLTEST database from Publisher CCIMOBILE.', @.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'true', @.allow_synctoalternate = N'false', @.validate_subscriber_info = N'HOST_NAME()', @.max_concurrent_merge = 10, @.max_concurrent_dynamic_snapshots = 0, @.use_partition_groups = N'false', @.publication_compatibility_level = N'90RTM', @.replicate_ddl = 1, @.allow_subscriber_initiated_snapshot = N'true', @.allow_web_synchronization = N'false', @.allow_partition_realignment = N'true', @.retention_period_unit = N'days', @.conflict_logging = N'publisher', @.automatic_reinitialization_policy = 0
GO

exec sp_addpublication_snapshot @.publication = N'REPLTEST', @.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 = null, @.job_password = null, @.publisher_security_mode = 1
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'sa'
GO
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'NT AUTHORITY\SYSTEM'
GO
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'HQMRKT\SQLServer2005SQLAgentUser$HQMRKT$SQL2005'
GO
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'HQMRKT\SQLServer2005MSSQLUser$HQMRKT$SQL2005'
GO
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'distributor_admin'
GO

-- Adding the merge articles
use [REPLTEST]
exec sp_addmergearticle @.publication = N'REPLTEST', @.article = N'TBLTEST', @.source_owner = N'dbo', @.source_object = N'TBLTEST', @.type = N'table', @.description = N'', @.creation_script = N'', @.pre_creation_cmd = N'drop', @.schema_option = 0x000000003000CFF1, @.identityrangemanagementoption = N'none', @.destination_owner = N'dbo', @.force_reinit_subscription = 1, @.column_tracking = N'true', @.subset_filterclause = N'UID=HOST_NAME()', @.vertical_partition = N'false', @.verify_resolver_signature = 0, @.allow_interactive_resolver = N'false', @.fast_multicol_updateproc = N'true', @.check_permissions = 0, @.subscriber_upload_options = 2, @.delete_tracking = N'true', @.compensate_for_errors = N'false', @.stream_blob_columns = N'false', @.partition_options = 0
GO

use [REPLTEST]
exec sp_changemergepublication N'REPLTEST', N'status', N'active'
GO

|||

Here is the verbose log of the snapshot agent:

2007-02-26 08:38:58.98
2007-02-26 08:38:58.98 Microsoft (R) SQL Server Snapshot Agent
2007-02-26 08:38:58.98 [Assembly Version = 9.0.242.0, File Version = 9.00.2047.00]
2007-02-26 08:38:58.98 Copyright (C) 1988-2005 Microsoft Corporation. All rights reserved.
2007-02-26 08:38:58.98 The timestamps prepended to the output lines are expressed in terms of UTC time.
2007-02-26 08:38:58.98 User-specified agent parameter values:
2007-02-26 08:38:58.98 --
2007-02-26 08:38:58.98 -Publisher HQMRKT\SQL2005
2007-02-26 08:38:58.98 -PublisherDB REPLTEST
2007-02-26 08:38:58.98 -Publication REPLTEST
2007-02-26 08:38:58.98 -ReplicationType 2
2007-02-26 08:38:58.98 -Distributor HQMRKT\SQL2005
2007-02-26 08:38:58.98 -DistributorSecurityMode 1
2007-02-26 08:38:58.98 -Output C:\ReplOutput.txt
2007-02-26 08:38:58.98 -OutputVerboseLevel 2
2007-02-26 08:38:58.98 -XJOBID 0xEC583CE49E702944B367AF1E70053178
2007-02-26 08:38:58.98 --
2007-02-26 08:38:58.98 Connecting to Distributor 'HQMRKT\SQL2005'
2007-02-26 08:38:59.20
2007-02-26 08:38:59.20 SQL Command dump
2007-02-26 08:38:59.20 ================
2007-02-26 08:38:59.20 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.20 Database: master
2007-02-26 08:38:59.20 Command Text: sp_helpdistpublisher
2007-02-26 08:38:59.20 Parameters:
2007-02-26 08:38:59.20 @.publisher = HQMRKT\SQL2005
2007-02-26 08:38:59.20
2007-02-26 08:38:59.20 SQL Command dump
2007-02-26 08:38:59.20 ================
2007-02-26 08:38:59.20 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.20 Database: distribution
2007-02-26 08:38:59.20 Command Text: select 'id' = convert(int, srvid) from master..sysservers where upper(srvname) = upper(N'HQMRKT\SQL2005')
2007-02-26 08:38:59.20 Parameters:
2007-02-26 08:38:59.21
2007-02-26 08:38:59.21 SQL Command dump
2007-02-26 08:38:59.21 ================
2007-02-26 08:38:59.21 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.21 Database: distribution
2007-02-26 08:38:59.21 Command Text: sp_MShelp_snapshot_agentid
2007-02-26 08:38:59.21 Parameters:
2007-02-26 08:38:59.21 @.publisher_id = 0
2007-02-26 08:38:59.21 @.publisher_db = REPLTEST
2007-02-26 08:38:59.21 @.publication = REPLTEST
2007-02-26 08:38:59.21 @.job_id = System.Byte[]
2007-02-26 08:38:59.21
2007-02-26 08:38:59.21 SQL Command dump
2007-02-26 08:38:59.21 ================
2007-02-26 08:38:59.21 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.21 Database: distribution
2007-02-26 08:38:59.21 Command Text: sp_MShelp_snapshot_agent
2007-02-26 08:38:59.21 Parameters:
2007-02-26 08:38:59.21 @.agent_id = 1244
2007-02-26 08:38:59.23
2007-02-26 08:38:59.23 SQL Command dump
2007-02-26 08:38:59.23 ================
2007-02-26 08:38:59.23 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.23 Database: distribution
2007-02-26 08:38:59.23 Command Text: sp_MShelp_profile
2007-02-26 08:38:59.23 Parameters:
2007-02-26 08:38:59.23 @.agent_id = 1244
2007-02-26 08:38:59.23 @.agent_type = 1
2007-02-26 08:38:59.23 @.profile_name =
2007-02-26 08:38:59.23 Parameter values obtained from agent profile:
2007-02-26 08:38:59.23
2007-02-26 08:38:59.23 -BcpBatchSize 100000
2007-02-26 08:38:59.23 -HistoryVerboseLevel 2
2007-02-26 08:38:59.23 -LoginTimeout 15
2007-02-26 08:38:59.23 -QueryTimeout 1800
2007-02-26 08:38:59.23
2007-02-26 08:38:59.26 Connecting to Publisher 'HQMRKT\SQL2005'
2007-02-26 08:38:59.29
2007-02-26 08:38:59.29 SQL Command dump
2007-02-26 08:38:59.29 ================
2007-02-26 08:38:59.29 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.29 Database: REPLTEST
2007-02-26 08:38:59.29 Command Text: use master
2007-02-26 08:38:59.29 select isnull(convert(int, value_in_use), 0) from master.sys.configurations where lower(name) = 'smo and dmo xps'
2007-02-26 08:38:59.29 use [REPLTEST]
2007-02-26 08:38:59.29 Parameters:
2007-02-26 08:38:59.29
2007-02-26 08:38:59.29 SQL Command dump
2007-02-26 08:38:59.29 ================
2007-02-26 08:38:59.29 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.29 Database: REPLTEST
2007-02-26 08:38:59.29 Command Text: use [REPLTEST]
2007-02-26 08:38:59.29 select publishingservername(), convert(int, compatibility_level) from sys.databases where name = @.database_name
2007-02-26 08:38:59.29 Parameters:
2007-02-26 08:38:59.29 @.database_name = REPLTEST
2007-02-26 08:38:59.31 Publisher database compatibility level is set to 90.
2007-02-26 08:38:59.31
2007-02-26 08:38:59.31 SQL Command dump
2007-02-26 08:38:59.31 ================
2007-02-26 08:38:59.31 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.31 Database: REPLTEST
2007-02-26 08:38:59.31 Command Text: select convert(int, isnull(is_member('db_owner'),0))
2007-02-26 08:38:59.31 Parameters:
2007-02-26 08:38:59.31
2007-02-26 08:38:59.31 SQL Command dump
2007-02-26 08:38:59.31 ================
2007-02-26 08:38:59.31 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.31 Database: REPLTEST
2007-02-26 08:38:59.31 Command Text:
2007-02-26 08:38:59.31 declare @.retcode int
2007-02-26 08:38:59.31 exec @.retcode = sys.sp_getapplock @.Resource = N'HQMRKT\SQL2005-REPLTEST-REPLTEST-1244',
2007-02-26 08:38:59.31 @.LockMode = N'Exclusive',
2007-02-26 08:38:59.31 @.LockOwner = N'Session',
2007-02-26 08:38:59.31 @.LockTimeout = 0,
2007-02-26 08:38:59.31 @.DbPrincipal = N'db_owner'
2007-02-26 08:38:59.31 if @.retcode < 0 raiserror(21036, 16, -1, 'snapshot')
2007-02-26 08:38:59.31 Parameters:
2007-02-26 08:38:59.31 Retrieving publication and article information from the publisher database 'HQMRKT\SQL2005.REPLTEST'
2007-02-26 08:38:59.31
2007-02-26 08:38:59.31 SQL Command dump
2007-02-26 08:38:59.31 ================
2007-02-26 08:38:59.31 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.31 Database: REPLTEST
2007-02-26 08:38:59.31 Command Text: sp_helpmergepublication
2007-02-26 08:38:59.31 Parameters:
2007-02-26 08:38:59.31 @.publication = REPLTEST
2007-02-26 08:38:59.31 @.reserved = fromSnapshot
2007-02-26 08:38:59.32
2007-02-26 08:38:59.32 SQL Command dump
2007-02-26 08:38:59.32 ================
2007-02-26 08:38:59.32 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.32 Database: REPLTEST
2007-02-26 08:38:59.32 Command Text: sys.sp_MSset_dynamic_filter_options
2007-02-26 08:38:59.32 Parameters:
2007-02-26 08:38:59.32 @.publication = REPLTEST
2007-02-26 08:38:59.32 @.dynamic_filters = True
2007-02-26 08:38:59.39
2007-02-26 08:38:59.39 SQL Command dump
2007-02-26 08:38:59.39 ================
2007-02-26 08:38:59.39 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.39 Database: REPLTEST
2007-02-26 08:38:59.39 Command Text: declare @.ftp_password nvarchar(524) select @.ftp_password = ftp_password from dbo.sysmergepublications where name = N'REPLTEST' and publisher_db = N'REPLTEST' and upper(publisher) = upper(N'HQMRKT\SQL2005') if object_id('sys.fn_repldecryptver4', 'FN') is not null begin select sys.fn_repldecryptver4(@.ftp_password) end else begin select @.ftp_password end
2007-02-26 08:38:59.39 Parameters:
2007-02-26 08:38:59.39
2007-02-26 08:38:59.39 SQL Command dump
2007-02-26 08:38:59.39 ================
2007-02-26 08:38:59.39 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.39 Database: REPLTEST
2007-02-26 08:38:59.39 Command Text: sp_MSestimatemergesnapshotworkload
2007-02-26 08:38:59.39 Parameters:
2007-02-26 08:38:59.39 @.publication = REPLTEST
2007-02-26 08:38:59.40
2007-02-26 08:38:59.40 SQL Command dump
2007-02-26 08:38:59.40 ================
2007-02-26 08:38:59.40 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.40 Database: REPLTEST
2007-02-26 08:38:59.40 Command Text: sp_helpmergearticle
2007-02-26 08:38:59.40 Parameters:
2007-02-26 08:38:59.40 @.publication = REPLTEST
2007-02-26 08:38:59.42
2007-02-26 08:38:59.42 SQL Command dump
2007-02-26 08:38:59.42 ================
2007-02-26 08:38:59.42 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.42 Database: REPLTEST
2007-02-26 08:38:59.42 Command Text: sys.sp_MSget_load_hint
2007-02-26 08:38:59.42 Parameters:
2007-02-26 08:38:59.42 @.qualified_source_object_name = [dbo].[TBLTEST]
2007-02-26 08:38:59.42 @.qualified_sync_object_name = [dbo].[MSmerge_REPLTEST_TBLTEST_VIEW]
2007-02-26 08:38:59.42 @.primary_key_only = False
2007-02-26 08:38:59.42 @.is_vertically_partitioned = False
2007-02-26 08:38:59.42
2007-02-26 08:38:59.42 SQL Command dump
2007-02-26 08:38:59.42 ================
2007-02-26 08:38:59.42 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.42 Database: REPLTEST
2007-02-26 08:38:59.42 Command Text: sp_MSvalidate_wellpartitioned_articles
2007-02-26 08:38:59.42 Parameters:
2007-02-26 08:38:59.42 @.publication = REPLTEST
2007-02-26 08:38:59.42
2007-02-26 08:38:59.42 SQL Command dump
2007-02-26 08:38:59.42 ================
2007-02-26 08:38:59.42 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.42 Database: REPLTEST
2007-02-26 08:38:59.42 Command Text: sp_browsemergesnapshotfolder
2007-02-26 08:38:59.42 Parameters:
2007-02-26 08:38:59.42 @.publication = REPLTEST
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 SQL Command dump
2007-02-26 08:38:59.43 ================
2007-02-26 08:38:59.43 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.43 Database: REPLTEST
2007-02-26 08:38:59.43 Command Text: sys.sp_MSgetmergeadminapplock
2007-02-26 08:38:59.43 Parameters:
2007-02-26 08:38:59.43 @.timeout = -1
2007-02-26 08:38:59.43 @.lockmode = Exclusive
2007-02-26 08:38:59.43 @.lock_acquired = -1
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 SQL Command dump
2007-02-26 08:38:59.43 ================
2007-02-26 08:38:59.43 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.43 Database: REPLTEST
2007-02-26 08:38:59.43 Command Text: sp_MSenumpubreferences
2007-02-26 08:38:59.43 Parameters:
2007-02-26 08:38:59.43 @.publication = REPLTEST
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 SQL Command dump
2007-02-26 08:38:59.43 ================
2007-02-26 08:38:59.43 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.43 Database: REPLTEST
2007-02-26 08:38:59.43 Command Text:
2007-02-26 08:38:59.43 declare @.retcode int,
2007-02-26 08:38:59.43 @.resource sysname,
2007-02-26 08:38:59.43 @.DbPrincipal sysname,
2007-02-26 08:38:59.43 @.pubid uniqueidentifier
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 select @.resource = N'dynpub_' + convert(nvarchar(36), pubid), @.pubid = pubid
2007-02-26 08:38:59.43 from dbo.sysmergepublications
2007-02-26 08:38:59.43 where name = N'REPLTEST'
2007-02-26 08:38:59.43 and upper(publisher) = upper(publishingservername())
2007-02-26 08:38:59.43 and publisher_db = db_name()
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 execute sys.sp_MSrepl_FixPALRole @.pubid = @.pubid, @.role = @.DbPrincipal output
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 execute @.retcode = sys.sp_getapplock @.Resource = @.resource,
2007-02-26 08:38:59.43 @.LockOwner = N'Session',
2007-02-26 08:38:59.43 @.LockMode = N'Exclusive',
2007-02-26 08:38:59.43 @.LockTimeout = 0,
2007-02-26 08:38:59.43 @.DbPrincipal = @.DbPrincipal
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 select 'LockOutcome' = @.retcode, 'LockName' = @.resource, 'DbPrincipal' = @.DbPrincipal
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 Parameters:
2007-02-26 08:38:59.45 [0%] Preparing table '[dbo].[TBLTEST]' for merge replication
2007-02-26 08:38:59.45
2007-02-26 08:38:59.45 SQL Command dump
2007-02-26 08:38:59.45 ================
2007-02-26 08:38:59.45 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.45 Database: REPLTEST
2007-02-26 08:38:59.45 Command Text: SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION
2007-02-26 08:38:59.45 Parameters:
2007-02-26 08:38:59.45
2007-02-26 08:38:59.45 SQL Command dump
2007-02-26 08:38:59.45 ================
2007-02-26 08:38:59.45 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.45 Database: REPLTEST
2007-02-26 08:38:59.45 Command Text: SELECT * FROM [dbo].[TBLTEST] WITH (TABLOCK HOLDLOCK) WHERE 1 = 2
2007-02-26 08:38:59.45 Parameters:
2007-02-26 08:38:59.45
2007-02-26 08:38:59.45 SQL Command dump
2007-02-26 08:38:59.45 ================
2007-02-26 08:38:59.45 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.45 Database: REPLTEST
2007-02-26 08:38:59.45 Command Text: sp_MSprepare_mergearticle
2007-02-26 08:38:59.45 Parameters:
2007-02-26 08:38:59.45 @.source_owner = dbo
2007-02-26 08:38:59.45 @.source_table = TBLTEST
2007-02-26 08:38:59.45 @.publication = REPLTEST
2007-02-26 08:38:59.45 @.qualified_tablename = [dbo].[TBLTEST]
2007-02-26 08:38:59.46
2007-02-26 08:38:59.46 SQL Command dump
2007-02-26 08:38:59.46 ================
2007-02-26 08:38:59.46 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.46 Database: REPLTEST
2007-02-26 08:38:59.46 Command Text: COMMIT TRANSACTION
2007-02-26 08:38:59.46 Parameters:
2007-02-26 08:38:59.53
2007-02-26 08:38:59.53 SQL Command dump
2007-02-26 08:38:59.53 ================
2007-02-26 08:38:59.53 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.53 Database: REPLTEST
2007-02-26 08:38:59.53 Command Text: sp_MSpublicationview
2007-02-26 08:38:59.53 Parameters:
2007-02-26 08:38:59.53 @.publication = REPLTEST
2007-02-26 08:38:59.70
2007-02-26 08:38:59.70 SQL Command dump
2007-02-26 08:38:59.70 ================
2007-02-26 08:38:59.70 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.70 Database: REPLTEST
2007-02-26 08:38:59.70 Command Text: sys.sp_releaseapplock
2007-02-26 08:38:59.70 Parameters:
2007-02-26 08:38:59.70 @.Resource = HQMRKT\SQL2005-REPLTEST-REPLTEST-1244
2007-02-26 08:38:59.70 @.LockOwner = Session
2007-02-26 08:38:59.70 @.DbPrincipal = db_owner
2007-02-26 08:38:59.71 [3%] The replication agent had encountered an exception.
2007-02-26 08:38:59.71 Source: Replication
2007-02-26 08:38:59.71 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-02-26 08:38:59.71 Exception Message: Invalid column name 'rowguid'.
2007-02-26 08:38:59.71 Message Code: 4615
2007-02-26 08:38:59.71
2007-02-26 08:38:59.71 Call Stack:
2007-02-26 08:38:59.71 Microsoft.SqlServer.Replication.ReplicationAgentSqlException: Invalid column name 'rowguid'. > System.Data.SqlClient.SqlException: Invalid column name 'rowguid'.
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
2007-02-26 08:38:59.71 at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
2007-02-26 08:38:59.71 at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.AgentExecuteNonQuery(SqlCommand command, Int32 queryTimeout)
2007-02-26 08:38:59.71 End of inner exception stack trace
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command)
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.AgentExecuteNonQuery(SqlCommand command, Int32 queryTimeout)
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate, Int32 queryTimeout)
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate)
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.MakePublicationViews()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoRegularMergeSnapshotPreparations()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoPreArticleFilesGenerationProcessing()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.Run()

'Invalid Column Name' on initial snapshot

Building a second merge publication from a db, I'm getting an error 'Invalid
Column Name XXXXX' on gernerating the initial snapshot. The column
definitely exists. Anyone seen this behaviour before and maybe offer some
guidance?
Looks like this issue may be fixed in SP4
http://support.microsoft.com/kb/821535
"Tony Toker" <xyzzy@.identic.co.uk> wrote in message
news:crdj1r$rn3$1$8302bc10@.news.demon.co.uk...
> Building a second merge publication from a db, I'm getting an error
'Invalid
> Column Name XXXXX' on gernerating the initial snapshot. The column
> definitely exists. Anyone seen this behaviour before and maybe offer some
> guidance?
>
>
|||Thanks again for the help Paul, looks like we were posting simultaneously.
"Tony Toker" <xyzzy@.identic.co.uk> wrote in message
news:crduu6$f5i$1$830fa795@.news.demon.co.uk...[vbcol=seagreen]
> Looks like this issue may be fixed in SP4
> http://support.microsoft.com/kb/821535
>
>
> "Tony Toker" <xyzzy@.identic.co.uk> wrote in message
> news:crdj1r$rn3$1$8302bc10@.news.demon.co.uk...
> 'Invalid
some
>

Invalid column name 'rowguid'

I'm trying to set up a merge publication in MSSQL 2005 SP1.

I have a database which I have successfully made into a merge publication. When I run the snapshot agent, several articles are processed successfully, then I encounter an error with the following: Invalid column name 'rowguid'

Note that none of the tables contain 'rowguid' columns before this process - I let MSSQL automatically create these as needed.

The replication monitor details the following error:

Error messages:

· Message: Invalid column name 'rowguid'.
Command Text: sp_MSpublicationview
Parameters: @.publication = RelMS Publication
Stack: at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command)
at Microsoft.SqlServer.Replication.AgentCore.AgentExecuteNonQuery(SqlCommand command, Int32 queryTimeout)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate, Int32 queryTimeout)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate)
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.MakePublicationViews()
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoRegularMergeSnapshotPreparations()
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoPreArticleFilesGenerationProcessing()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 4615)
Get help: http://help/4615

· Server [XXXX\XXXXXX], Level 16, State 1, Procedure , Line 1
Invalid column name 'rowguid'. (Source: MSSQLServer, Error number: 4615)
Get help: http://help/4615

Can anyone shed further light on this?

Thanks.

Further note: I downloaded and applied MSSQL 2005 SP2 just now and started process from scratch (i.e. restored clean database with no replication, then created publication successfully, then ran snapshot agent). Exactly same error was encountered.

Further details that might be relevant:

Publisher and Distributor are set to same. Error persists when compatibility is 2005 or 2000.

Unfortunately the error report doesn't indicate which article was being processed when the error occurred, and the list of successfully processed articles in the log doesn't seem to follow any discernible order.

Any suggestions?

|||

Hi Ramaj;

What are the values for @.keep_partition_changes and @.use_partition_groups parameters in your replication creation script.

|||

Hi Hakan,

@.keep_partition_changes = N'true'

@.use_partition_groups not specified

Thanks

|||

Hi Ramaj;

Could you please try to set @.keep_partition_changes = N'false' then retry to run snapshot.

If the same error occours please try again by adding @.use_partition_groups= N'true' and @.keep_partition_changes = N'false' .

|||

Thanks Hakan, the first step by itself (i.e. @.keep_partition_changes = N'false') did the trick and the snapshot completed successfully. What a strange and misleading error message though - completely had me barking up the wrong tree.

Your help was much appreciated!

|||

I would still be ineterested in finding out what is causing the problem.

Can you run snapshot with higher outputverboseLevel 2. That should tell you which article you have issues with. Then isolating that article in a sepearate publication would help. Also the article schema, publication and article script would really help us troubleshoot the problem.

|||

It's very easy to repro this issue.

Create a one table db with two cols one for filtering.

Below script creates db, table and replication. But the key is to setting keep_partiton_changes = true.

-- CREATE DB
USE [master]
GO
/****** Object: Database [REPLTEST] Script Date: 02/26/2007 10:15:31 ******/
CREATE DATABASE [REPLTEST] ON PRIMARY
( NAME = N'REPLTEST', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\REPLTEST.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'REPLTEST_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\REPLTEST_log.ldf' , SIZE = 3840KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE Turkish_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @.dbname=N'REPLTEST', @.new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [REPLTEST].[dbo].[sp_fulltext_database] @.action = 'disable'
end
GO
ALTER DATABASE [REPLTEST] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [REPLTEST] SET ANSI_NULLS OFF
GO
ALTER DATABASE [REPLTEST] SET ANSI_PADDING OFF
GO
ALTER DATABASE [REPLTEST] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [REPLTEST] SET ARITHABORT OFF
GO
ALTER DATABASE [REPLTEST] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [REPLTEST] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [REPLTEST] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [REPLTEST] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [REPLTEST] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [REPLTEST] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [REPLTEST] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [REPLTEST] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [REPLTEST] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [REPLTEST] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [REPLTEST] SET ENABLE_BROKER
GO
ALTER DATABASE [REPLTEST] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [REPLTEST] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [REPLTEST] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [REPLTEST] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [REPLTEST] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [REPLTEST] SET READ_WRITE
GO
ALTER DATABASE [REPLTEST] SET RECOVERY SIMPLE
GO
ALTER DATABASE [REPLTEST] SET MULTI_USER
GO
ALTER DATABASE [REPLTEST] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [REPLTEST] SET DB_CHAINING OFF
GO

-CREATE TABLE
USE REPLTEST
CREATE TABLE [dbo].[TBLTEST](
[nvarchar](50) COLLATE Turkish_CI_AS NULL,
[UID] [nvarchar](10) COLLATE Turkish_CI_AS NULL,
) ON [PRIMARY]

CREATE REPL
-- Enabling the replication database
use master
exec sp_replicationdboption @.dbname = N'REPLTEST', @.optname = N'merge publish', @.value = N'true'
GO

-- Adding the merge publication
use [REPLTEST]
exec sp_addmergepublication @.publication = N'REPLTEST', @.description = N'Merge publication of REPLTEST database from Publisher CCIMOBILE.', @.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'true', @.allow_synctoalternate = N'false', @.validate_subscriber_info = N'HOST_NAME()', @.max_concurrent_merge = 10, @.max_concurrent_dynamic_snapshots = 0, @.use_partition_groups = N'false', @.publication_compatibility_level = N'90RTM', @.replicate_ddl = 1, @.allow_subscriber_initiated_snapshot = N'true', @.allow_web_synchronization = N'false', @.allow_partition_realignment = N'true', @.retention_period_unit = N'days', @.conflict_logging = N'publisher', @.automatic_reinitialization_policy = 0
GO

exec sp_addpublication_snapshot @.publication = N'REPLTEST', @.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 = null, @.job_password = null, @.publisher_security_mode = 1
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'sa'
GO
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'NT AUTHORITY\SYSTEM'
GO
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'HQMRKT\SQLServer2005SQLAgentUser$HQMRKT$SQL2005'
GO
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'HQMRKT\SQLServer2005MSSQLUser$HQMRKT$SQL2005'
GO
exec sp_grant_publication_access @.publication = N'REPLTEST', @.login = N'distributor_admin'
GO

-- Adding the merge articles
use [REPLTEST]
exec sp_addmergearticle @.publication = N'REPLTEST', @.article = N'TBLTEST', @.source_owner = N'dbo', @.source_object = N'TBLTEST', @.type = N'table', @.description = N'', @.creation_script = N'', @.pre_creation_cmd = N'drop', @.schema_option = 0x000000003000CFF1, @.identityrangemanagementoption = N'none', @.destination_owner = N'dbo', @.force_reinit_subscription = 1, @.column_tracking = N'true', @.subset_filterclause = N'UID=HOST_NAME()', @.vertical_partition = N'false', @.verify_resolver_signature = 0, @.allow_interactive_resolver = N'false', @.fast_multicol_updateproc = N'true', @.check_permissions = 0, @.subscriber_upload_options = 2, @.delete_tracking = N'true', @.compensate_for_errors = N'false', @.stream_blob_columns = N'false', @.partition_options = 0
GO

use [REPLTEST]
exec sp_changemergepublication N'REPLTEST', N'status', N'active'
GO

|||

Here is the verbose log of the snapshot agent:

2007-02-26 08:38:58.98
2007-02-26 08:38:58.98 Microsoft (R) SQL Server Snapshot Agent
2007-02-26 08:38:58.98 [Assembly Version = 9.0.242.0, File Version = 9.00.2047.00]
2007-02-26 08:38:58.98 Copyright (C) 1988-2005 Microsoft Corporation. All rights reserved.
2007-02-26 08:38:58.98 The timestamps prepended to the output lines are expressed in terms of UTC time.
2007-02-26 08:38:58.98 User-specified agent parameter values:
2007-02-26 08:38:58.98 --
2007-02-26 08:38:58.98 -Publisher HQMRKT\SQL2005
2007-02-26 08:38:58.98 -PublisherDB REPLTEST
2007-02-26 08:38:58.98 -Publication REPLTEST
2007-02-26 08:38:58.98 -ReplicationType 2
2007-02-26 08:38:58.98 -Distributor HQMRKT\SQL2005
2007-02-26 08:38:58.98 -DistributorSecurityMode 1
2007-02-26 08:38:58.98 -Output C:\ReplOutput.txt
2007-02-26 08:38:58.98 -OutputVerboseLevel 2
2007-02-26 08:38:58.98 -XJOBID 0xEC583CE49E702944B367AF1E70053178
2007-02-26 08:38:58.98 --
2007-02-26 08:38:58.98 Connecting to Distributor 'HQMRKT\SQL2005'
2007-02-26 08:38:59.20
2007-02-26 08:38:59.20 SQL Command dump
2007-02-26 08:38:59.20 ================
2007-02-26 08:38:59.20 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.20 Database: master
2007-02-26 08:38:59.20 Command Text: sp_helpdistpublisher
2007-02-26 08:38:59.20 Parameters:
2007-02-26 08:38:59.20 @.publisher = HQMRKT\SQL2005
2007-02-26 08:38:59.20
2007-02-26 08:38:59.20 SQL Command dump
2007-02-26 08:38:59.20 ================
2007-02-26 08:38:59.20 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.20 Database: distribution
2007-02-26 08:38:59.20 Command Text: select 'id' = convert(int, srvid) from master..sysservers where upper(srvname) = upper(N'HQMRKT\SQL2005')
2007-02-26 08:38:59.20 Parameters:
2007-02-26 08:38:59.21
2007-02-26 08:38:59.21 SQL Command dump
2007-02-26 08:38:59.21 ================
2007-02-26 08:38:59.21 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.21 Database: distribution
2007-02-26 08:38:59.21 Command Text: sp_MShelp_snapshot_agentid
2007-02-26 08:38:59.21 Parameters:
2007-02-26 08:38:59.21 @.publisher_id = 0
2007-02-26 08:38:59.21 @.publisher_db = REPLTEST
2007-02-26 08:38:59.21 @.publication = REPLTEST
2007-02-26 08:38:59.21 @.job_id = System.Byte[]
2007-02-26 08:38:59.21
2007-02-26 08:38:59.21 SQL Command dump
2007-02-26 08:38:59.21 ================
2007-02-26 08:38:59.21 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.21 Database: distribution
2007-02-26 08:38:59.21 Command Text: sp_MShelp_snapshot_agent
2007-02-26 08:38:59.21 Parameters:
2007-02-26 08:38:59.21 @.agent_id = 1244
2007-02-26 08:38:59.23
2007-02-26 08:38:59.23 SQL Command dump
2007-02-26 08:38:59.23 ================
2007-02-26 08:38:59.23 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.23 Database: distribution
2007-02-26 08:38:59.23 Command Text: sp_MShelp_profile
2007-02-26 08:38:59.23 Parameters:
2007-02-26 08:38:59.23 @.agent_id = 1244
2007-02-26 08:38:59.23 @.agent_type = 1
2007-02-26 08:38:59.23 @.profile_name =
2007-02-26 08:38:59.23 Parameter values obtained from agent profile:
2007-02-26 08:38:59.23
2007-02-26 08:38:59.23 -BcpBatchSize 100000
2007-02-26 08:38:59.23 -HistoryVerboseLevel 2
2007-02-26 08:38:59.23 -LoginTimeout 15
2007-02-26 08:38:59.23 -QueryTimeout 1800
2007-02-26 08:38:59.23
2007-02-26 08:38:59.26 Connecting to Publisher 'HQMRKT\SQL2005'
2007-02-26 08:38:59.29
2007-02-26 08:38:59.29 SQL Command dump
2007-02-26 08:38:59.29 ================
2007-02-26 08:38:59.29 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.29 Database: REPLTEST
2007-02-26 08:38:59.29 Command Text: use master
2007-02-26 08:38:59.29 select isnull(convert(int, value_in_use), 0) from master.sys.configurations where lower(name) = 'smo and dmo xps'
2007-02-26 08:38:59.29 use [REPLTEST]
2007-02-26 08:38:59.29 Parameters:
2007-02-26 08:38:59.29
2007-02-26 08:38:59.29 SQL Command dump
2007-02-26 08:38:59.29 ================
2007-02-26 08:38:59.29 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.29 Database: REPLTEST
2007-02-26 08:38:59.29 Command Text: use [REPLTEST]
2007-02-26 08:38:59.29 select publishingservername(), convert(int, compatibility_level) from sys.databases where name = @.database_name
2007-02-26 08:38:59.29 Parameters:
2007-02-26 08:38:59.29 @.database_name = REPLTEST
2007-02-26 08:38:59.31 Publisher database compatibility level is set to 90.
2007-02-26 08:38:59.31
2007-02-26 08:38:59.31 SQL Command dump
2007-02-26 08:38:59.31 ================
2007-02-26 08:38:59.31 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.31 Database: REPLTEST
2007-02-26 08:38:59.31 Command Text: select convert(int, isnull(is_member('db_owner'),0))
2007-02-26 08:38:59.31 Parameters:
2007-02-26 08:38:59.31
2007-02-26 08:38:59.31 SQL Command dump
2007-02-26 08:38:59.31 ================
2007-02-26 08:38:59.31 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.31 Database: REPLTEST
2007-02-26 08:38:59.31 Command Text:
2007-02-26 08:38:59.31 declare @.retcode int
2007-02-26 08:38:59.31 exec @.retcode = sys.sp_getapplock @.Resource = N'HQMRKT\SQL2005-REPLTEST-REPLTEST-1244',
2007-02-26 08:38:59.31 @.LockMode = N'Exclusive',
2007-02-26 08:38:59.31 @.LockOwner = N'Session',
2007-02-26 08:38:59.31 @.LockTimeout = 0,
2007-02-26 08:38:59.31 @.DbPrincipal = N'db_owner'
2007-02-26 08:38:59.31 if @.retcode < 0 raiserror(21036, 16, -1, 'snapshot')
2007-02-26 08:38:59.31 Parameters:
2007-02-26 08:38:59.31 Retrieving publication and article information from the publisher database 'HQMRKT\SQL2005.REPLTEST'
2007-02-26 08:38:59.31
2007-02-26 08:38:59.31 SQL Command dump
2007-02-26 08:38:59.31 ================
2007-02-26 08:38:59.31 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.31 Database: REPLTEST
2007-02-26 08:38:59.31 Command Text: sp_helpmergepublication
2007-02-26 08:38:59.31 Parameters:
2007-02-26 08:38:59.31 @.publication = REPLTEST
2007-02-26 08:38:59.31 @.reserved = fromSnapshot
2007-02-26 08:38:59.32
2007-02-26 08:38:59.32 SQL Command dump
2007-02-26 08:38:59.32 ================
2007-02-26 08:38:59.32 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.32 Database: REPLTEST
2007-02-26 08:38:59.32 Command Text: sys.sp_MSset_dynamic_filter_options
2007-02-26 08:38:59.32 Parameters:
2007-02-26 08:38:59.32 @.publication = REPLTEST
2007-02-26 08:38:59.32 @.dynamic_filters = True
2007-02-26 08:38:59.39
2007-02-26 08:38:59.39 SQL Command dump
2007-02-26 08:38:59.39 ================
2007-02-26 08:38:59.39 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.39 Database: REPLTEST
2007-02-26 08:38:59.39 Command Text: declare @.ftp_password nvarchar(524) select @.ftp_password = ftp_password from dbo.sysmergepublications where name = N'REPLTEST' and publisher_db = N'REPLTEST' and upper(publisher) = upper(N'HQMRKT\SQL2005') if object_id('sys.fn_repldecryptver4', 'FN') is not null begin select sys.fn_repldecryptver4(@.ftp_password) end else begin select @.ftp_password end
2007-02-26 08:38:59.39 Parameters:
2007-02-26 08:38:59.39
2007-02-26 08:38:59.39 SQL Command dump
2007-02-26 08:38:59.39 ================
2007-02-26 08:38:59.39 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.39 Database: REPLTEST
2007-02-26 08:38:59.39 Command Text: sp_MSestimatemergesnapshotworkload
2007-02-26 08:38:59.39 Parameters:
2007-02-26 08:38:59.39 @.publication = REPLTEST
2007-02-26 08:38:59.40
2007-02-26 08:38:59.40 SQL Command dump
2007-02-26 08:38:59.40 ================
2007-02-26 08:38:59.40 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.40 Database: REPLTEST
2007-02-26 08:38:59.40 Command Text: sp_helpmergearticle
2007-02-26 08:38:59.40 Parameters:
2007-02-26 08:38:59.40 @.publication = REPLTEST
2007-02-26 08:38:59.42
2007-02-26 08:38:59.42 SQL Command dump
2007-02-26 08:38:59.42 ================
2007-02-26 08:38:59.42 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.42 Database: REPLTEST
2007-02-26 08:38:59.42 Command Text: sys.sp_MSget_load_hint
2007-02-26 08:38:59.42 Parameters:
2007-02-26 08:38:59.42 @.qualified_source_object_name = [dbo].[TBLTEST]
2007-02-26 08:38:59.42 @.qualified_sync_object_name = [dbo].[MSmerge_REPLTEST_TBLTEST_VIEW]
2007-02-26 08:38:59.42 @.primary_key_only = False
2007-02-26 08:38:59.42 @.is_vertically_partitioned = False
2007-02-26 08:38:59.42
2007-02-26 08:38:59.42 SQL Command dump
2007-02-26 08:38:59.42 ================
2007-02-26 08:38:59.42 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.42 Database: REPLTEST
2007-02-26 08:38:59.42 Command Text: sp_MSvalidate_wellpartitioned_articles
2007-02-26 08:38:59.42 Parameters:
2007-02-26 08:38:59.42 @.publication = REPLTEST
2007-02-26 08:38:59.42
2007-02-26 08:38:59.42 SQL Command dump
2007-02-26 08:38:59.42 ================
2007-02-26 08:38:59.42 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.42 Database: REPLTEST
2007-02-26 08:38:59.42 Command Text: sp_browsemergesnapshotfolder
2007-02-26 08:38:59.42 Parameters:
2007-02-26 08:38:59.42 @.publication = REPLTEST
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 SQL Command dump
2007-02-26 08:38:59.43 ================
2007-02-26 08:38:59.43 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.43 Database: REPLTEST
2007-02-26 08:38:59.43 Command Text: sys.sp_MSgetmergeadminapplock
2007-02-26 08:38:59.43 Parameters:
2007-02-26 08:38:59.43 @.timeout = -1
2007-02-26 08:38:59.43 @.lockmode = Exclusive
2007-02-26 08:38:59.43 @.lock_acquired = -1
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 SQL Command dump
2007-02-26 08:38:59.43 ================
2007-02-26 08:38:59.43 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.43 Database: REPLTEST
2007-02-26 08:38:59.43 Command Text: sp_MSenumpubreferences
2007-02-26 08:38:59.43 Parameters:
2007-02-26 08:38:59.43 @.publication = REPLTEST
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 SQL Command dump
2007-02-26 08:38:59.43 ================
2007-02-26 08:38:59.43 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.43 Database: REPLTEST
2007-02-26 08:38:59.43 Command Text:
2007-02-26 08:38:59.43 declare @.retcode int,
2007-02-26 08:38:59.43 @.resource sysname,
2007-02-26 08:38:59.43 @.DbPrincipal sysname,
2007-02-26 08:38:59.43 @.pubid uniqueidentifier
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 select @.resource = N'dynpub_' + convert(nvarchar(36), pubid), @.pubid = pubid
2007-02-26 08:38:59.43 from dbo.sysmergepublications
2007-02-26 08:38:59.43 where name = N'REPLTEST'
2007-02-26 08:38:59.43 and upper(publisher) = upper(publishingservername())
2007-02-26 08:38:59.43 and publisher_db = db_name()
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 execute sys.sp_MSrepl_FixPALRole @.pubid = @.pubid, @.role = @.DbPrincipal output
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 execute @.retcode = sys.sp_getapplock @.Resource = @.resource,
2007-02-26 08:38:59.43 @.LockOwner = N'Session',
2007-02-26 08:38:59.43 @.LockMode = N'Exclusive',
2007-02-26 08:38:59.43 @.LockTimeout = 0,
2007-02-26 08:38:59.43 @.DbPrincipal = @.DbPrincipal
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 select 'LockOutcome' = @.retcode, 'LockName' = @.resource, 'DbPrincipal' = @.DbPrincipal
2007-02-26 08:38:59.43
2007-02-26 08:38:59.43 Parameters:
2007-02-26 08:38:59.45 [0%] Preparing table '[dbo].[TBLTEST]' for merge replication
2007-02-26 08:38:59.45
2007-02-26 08:38:59.45 SQL Command dump
2007-02-26 08:38:59.45 ================
2007-02-26 08:38:59.45 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.45 Database: REPLTEST
2007-02-26 08:38:59.45 Command Text: SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION
2007-02-26 08:38:59.45 Parameters:
2007-02-26 08:38:59.45
2007-02-26 08:38:59.45 SQL Command dump
2007-02-26 08:38:59.45 ================
2007-02-26 08:38:59.45 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.45 Database: REPLTEST
2007-02-26 08:38:59.45 Command Text: SELECT * FROM [dbo].[TBLTEST] WITH (TABLOCK HOLDLOCK) WHERE 1 = 2
2007-02-26 08:38:59.45 Parameters:
2007-02-26 08:38:59.45
2007-02-26 08:38:59.45 SQL Command dump
2007-02-26 08:38:59.45 ================
2007-02-26 08:38:59.45 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.45 Database: REPLTEST
2007-02-26 08:38:59.45 Command Text: sp_MSprepare_mergearticle
2007-02-26 08:38:59.45 Parameters:
2007-02-26 08:38:59.45 @.source_owner = dbo
2007-02-26 08:38:59.45 @.source_table = TBLTEST
2007-02-26 08:38:59.45 @.publication = REPLTEST
2007-02-26 08:38:59.45 @.qualified_tablename = [dbo].[TBLTEST]
2007-02-26 08:38:59.46
2007-02-26 08:38:59.46 SQL Command dump
2007-02-26 08:38:59.46 ================
2007-02-26 08:38:59.46 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.46 Database: REPLTEST
2007-02-26 08:38:59.46 Command Text: COMMIT TRANSACTION
2007-02-26 08:38:59.46 Parameters:
2007-02-26 08:38:59.53
2007-02-26 08:38:59.53 SQL Command dump
2007-02-26 08:38:59.53 ================
2007-02-26 08:38:59.53 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.53 Database: REPLTEST
2007-02-26 08:38:59.53 Command Text: sp_MSpublicationview
2007-02-26 08:38:59.53 Parameters:
2007-02-26 08:38:59.53 @.publication = REPLTEST
2007-02-26 08:38:59.70
2007-02-26 08:38:59.70 SQL Command dump
2007-02-26 08:38:59.70 ================
2007-02-26 08:38:59.70 Server: HQMRKT\SQL2005
2007-02-26 08:38:59.70 Database: REPLTEST
2007-02-26 08:38:59.70 Command Text: sys.sp_releaseapplock
2007-02-26 08:38:59.70 Parameters:
2007-02-26 08:38:59.70 @.Resource = HQMRKT\SQL2005-REPLTEST-REPLTEST-1244
2007-02-26 08:38:59.70 @.LockOwner = Session
2007-02-26 08:38:59.70 @.DbPrincipal = db_owner
2007-02-26 08:38:59.71 [3%] The replication agent had encountered an exception.
2007-02-26 08:38:59.71 Source: Replication
2007-02-26 08:38:59.71 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-02-26 08:38:59.71 Exception Message: Invalid column name 'rowguid'.
2007-02-26 08:38:59.71 Message Code: 4615
2007-02-26 08:38:59.71
2007-02-26 08:38:59.71 Call Stack:
2007-02-26 08:38:59.71 Microsoft.SqlServer.Replication.ReplicationAgentSqlException: Invalid column name 'rowguid'. > System.Data.SqlClient.SqlException: Invalid column name 'rowguid'.
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
2007-02-26 08:38:59.71 at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
2007-02-26 08:38:59.71 at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
2007-02-26 08:38:59.71 at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.AgentExecuteNonQuery(SqlCommand command, Int32 queryTimeout)
2007-02-26 08:38:59.71 End of inner exception stack trace
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command)
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.AgentExecuteNonQuery(SqlCommand command, Int32 queryTimeout)
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate, Int32 queryTimeout)
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate)
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.MakePublicationViews()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoRegularMergeSnapshotPreparations()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoPreArticleFilesGenerationProcessing()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
2007-02-26 08:38:59.71 at Microsoft.SqlServer.Replication.AgentCore.Run()

Internet Merge Replication IIS

Maybe I am missing something here, but it seems like the point of doing Merge Replication via IIS is so that you do not have to expose your DB server directly to the internet (i.e. all your clients), rather you expose a webserver to the internet and the webserver handles the replication keeping your DB server closed to direct internet access and thus keeping it more secure.

However, to set up Internet Merge Replication using IIS a client has to have direct internet access to the DB server as a stored procedure needs to be run on the publisher.

So Internet Merge Replciation using IIS makes it so your DB does not have to be directly exposed to the internet except your db needs to be exposed directly to the internet so every client can execute a stored procedure against it. How does this make sense? Yeah, I can write a webservice or something that gets the SP from the client and executes it against my db server but why should I have to? Why isn't the dll that the "Configure Web Synchronization" wizard puts into place capable of doing this?

Using web sync not only "hide" your sql server from the client, but also extends the merge replication to devices as well as PCs in anywhere that has the internet access. the later is actually the main reason to have this new sync type.

Sure, one can write their own web services to do the sync ( in fact, I believe there are some companies doing that already ), but this will not fully utilize the merge replication's functionalities to the web client subscriptions as it was to the fully connected subscriptions.

hence, the "Configure web synchronization" wizard means to provide some walk throguh steps to help users to set up the IIS server for replication and not to intend to setup the webservices.

thanks

Yunwen

|||

Thank you for your reply, but your reply...parts don't make sense and other parts don't answer what I asked.

>> but also extends the merge replication to devices as well as PCs in anywhere that has the internet access. the later is actually the main reason to have this new sync type.

That is NOT the "main reason" of doing web sync via IIS. Since for a client to do merge synching offsite, they have to have internet access to the sql server location, synching could be accomplished quite nicely without IIS by merely poking the appropriate holes in your firewall to allow the needed direct sql connections to be made. Thus IIS is not what facilitates merge synching over the internet. It still seems to me that the ONLY point of doing merge replication via IIS is to hide your SQL server from direct internet access.

>>Sure, one can write their own web services to do the sync

I never said this. What I said was that to set up Merge replication a client needed direct access to a sql server because a SP (Stored Procedure) has to be run against the Publisher that registeres the subscriber with the publisher. If you are using IIS to accomplish your merge replication, then you do not have your sql server directly exposed to the internet which makes it rather hard for your subscriber to connect directly to the publisher to execute a SP. What I said was that I could write a Webservice that could facilitate the subscriber sending the SP to the publisher but that I shouldn't have to as the replisapi.dll should be doing that. I said nothing about writing a webservice to do the sync, just that as things stand now I would have to write a webservice to facilitate setting up the sync.

So again, it seems like the point of using IIS to do merge replication is to hide your SQL Server publisher from the internet. However, part of setting up a subscriber involves running a SP (Stored Procedure) against the SQL Server Publisher. If your SQL Server publisher is hidden from the internet, it makes it rather impossible for your subscriber to connect to it to run a SP against it. It seems that currently the only work around is to make your own webservice that can access the SP arguments from the subscriber and run the proper SP with the proper arguments against your publisher for you. It seems sort of silly that you would have to set that up yourself, that MS hasn't built that in to something that already exists.

The situation is analguous to the Exchange RPC over HTTPS problem that occured when Office 2003 first came out. RPC over HTTPS allows Outlook to run against an Exchange server without having to have direct port 135-139 access. However in order to set up RPC over HTTPS your computer FIRST had to be able to connect directly to Exchange on ports 135-139 (which was a problem for me since I use Comcast and Comcast blocks these ports). The only work around was to lug my computer into the office, put it on a lan with the exchange server, setup RPC over HTTPS, then take it back home.) MS has since fixed this problem. However, this seems like the exact same problem. Doing merge replication via IIS makes it so that clients do not have to connect directly to the SQL Server, however to initially set up each client, that client has to connect directly to the SQL Server. Is there a way around this?

Thank you

|||

Many people may not have ability to connect to publisher directly through port 1433. That is one of the main motivation to support https (port 443) replication. IIS server is needed to understand the https protocol.

|||

Yes, that is right along the lines of what I have been saying.

Why is this so hard to understand? I will try to make it simple.

I have a sql server that is acting as a publisher. This publisher is not directly accessable via the internet. I have set up Merge replication using IIS and https on a server running IIS. The IIS server is accessable to the internet on ports 80 and 443. Ok? Simple enough.

I want to connect a subscriber to this publisher. The subscriber is somewhere on the internet. In order for the subscriber to successfully set up its subscription, it needs to execute the Stored Procedure named "sp_addmergesubscription" ON THE PUBLISHER!

As you so aptly pointed out "Many people may not have ability to connect to publisher directly through port 1433" that being the case, how then is a subscriber supposed to run the stored procedure "sp_addmergesubscription" on the publisher?

|||

You have two choices:

1. Add the subscription at the publisher before subscriber connects. Meaning an administrator at the publisher will need to add this information, not the subscriber since the subscriber does not have access.

2. Connect as an anonymous subscriber. Meaning if subscriber cannot have their subscription added to the publication, then connect as an anonymous subscriber, which will add the info for you on your first connect.

Internet merge pull subscription server name problems

I recently did a test-run of continuous merge pull replication on my
intranet and it worked wonderfully. Now I am trying to make it work
over the net, and I just can't get server names to ever work
correctly! The server is listening on a non-standard port and this is
open from the firewall.. a test connection and telnet connect just
fine through the firewall.
If someone could please give me a list of everything that must be in
place for the remote server to be able to reference the
publisher/distributor by host NAME not IP, I would greatly appreciate
it.
I assume I need a server alias on the subscriber? Does the servername
here include the instance name? How do i reference this alias in my
sp_addmergepullsubscription and sp_addmergepullsubscription_agent
calls? What about the non-standard port?
The publisher/distributor has a localname, say SERVER, so in EM it
shows up as "SERVER\INSTANCENAME"... shouldn't it show up as
"SERVER.DOMAIN.COM\INSTANCENAME" ? Do I need another alias on the
server?
The setup is: SQL Server 2000 publisher/distributor at a fixed domain,
we can't be sure of the IP, but we know it's always at
server.domain.com
MSDE is semi-disconnected, so this will be a PULL anonymous
subscription. I will be creating it through the above referenced
stored procedures.
Thanks in advance!
you must use SQL client network utilty to talk to the instance. Open up Client Network Utility, add a name for the alias. Then for servername add the name of the server and its instance, ie ServerName\InstanceName.
Then edit your hosts file for the ip address of the server, ie
ServerName xxx.xxx.xxx.xxx
The server name does not have to be a FQDN, ie SERVER.DOMAIN.COM\INSTANCENAME, just a name that has an ip address. For logistical purposes you should call it after your Publisher or Subscriber name, but you don't have to.
Ping this server from your publisher/subscriber to make sure it can resolve correctly and you have connectivity. you may need to use tracert to find out where your connectivity breaks.
Then its a matter of enabling your subscriber. register this server in Enterprise Manager, and then go to tools, replication, configure distributor, publishers, and subscribers, locate your subscriber, click on the check box to the left of the servername,
click on the browse button and enter the sa account (or an account in the sysadmin role), and its password. To replicate over the internet you must use sql authentication. Your publication should be set up for anonymous pull using FTP.
In your procs sp_addmergepullsubscription and sp_addmergepullsubscription_agent you will be referencing the publisher by whatever name you have assigned to this subscriber as its alias in Client Network Utiltiy.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Josh" wrote:

> I recently did a test-run of continuous merge pull replication on my
> intranet and it worked wonderfully. Now I am trying to make it work
> over the net, and I just can't get server names to ever work
> correctly! The server is listening on a non-standard port and this is
> open from the firewall.. a test connection and telnet connect just
> fine through the firewall.
> If someone could please give me a list of everything that must be in
> place for the remote server to be able to reference the
> publisher/distributor by host NAME not IP, I would greatly appreciate
> it.
> I assume I need a server alias on the subscriber? Does the servername
> here include the instance name? How do i reference this alias in my
> sp_addmergepullsubscription and sp_addmergepullsubscription_agent
> calls? What about the non-standard port?
> The publisher/distributor has a localname, say SERVER, so in EM it
> shows up as "SERVER\INSTANCENAME"... shouldn't it show up as
> "SERVER.DOMAIN.COM\INSTANCENAME" ? Do I need another alias on the
> server?
> The setup is: SQL Server 2000 publisher/distributor at a fixed domain,
> we can't be sure of the IP, but we know it's always at
> server.domain.com
> MSDE is semi-disconnected, so this will be a PULL anonymous
> subscription. I will be creating it through the above referenced
> stored procedures.
> Thanks in advance!
>