January 11, 2012 at 6:57 am
I have a SQL server 2005 server with a merge publication of 6 articles all of which are tables which is also the distributor. I have two existing subscriptions one from a SQL 2005 Server and one from a SQL Server 2008 R2 Server both of which are working fine on this publication. I am trying to setup a third subscription to another SQL Server 2008 R2 server, the only difference with this server is that it is a named instance. I have forced the port to 1433, and created an alias on the publisher connectivity is not an issue or at least i don't think so.
when I try to initialise the subscription from a new snapshot I get the below error;
Error: 14151, Severity: 18, State: 1.
Replication-Replication Merge Subsystem: agent failed. No subscription is on this publication or article.
I've changed the agent profile and turned on verbose logging and output the results to a file the snapshot delivery is failing on the below for the first table;
call sys.sp_MSsetup_identity_range (?,?,?,?,?,?,?,?)
I've checked the subscriber and the table does indeed get dropped and created, permissions / routes don't seem to be an issue. I've included some of the verbose logging output below.
2012-01-11 12:05:40.684 Microsoft SQL Server Merge Agent 9.00.3042.00
2012-01-11 12:05:40.700 Copyright (c) 2005 Microsoft Corporation
2012-01-11 12:05:40.716 Microsoft SQL Server Replication Agent: replmerg
2012-01-11 12:05:40.716
2012-01-11 12:05:40.731 The timestamps prepended to the output lines are expressed in terms of UTC time.
2012-01-11 12:05:40.747 User-specified agent parameter values:
-Publisher PubServerName
-PublisherDB UserDBName
-Publication MtoC_M
-Subscriber SubServerName
-SubscriberDB UserDBName
-Distributor PubServerName
-DistributorSecurityMode 1
-Continuous
-OutputVerboseLevel 4
-Output G:\CMcReplicationLogging\OUTPUT.TXT
-XJOBID 0x5FFBC73C3A4A494A9E10C369696B1C72
-XJOBNAME PubServerName-UserDBName-MtoC_M-SubServerName-29
-XSTEPID 2
-XSUBSYSTEM Merge
-XSERVER PubServerName
-XCMDLINE 0
-XCancelEventHandle 000008EC
-XParentProcessHandle 00000E9C
2012-01-11 12:05:40.841 Percent Complete: 0
2012-01-11 12:05:40.841 Connecting to Distributor 'PubServerName'
2012-01-11 12:05:40.856 Repl Agent Status: 3
2012-01-11 12:05:40.856 Connecting to OLE DB Distributor at datasource: 'PubServerName', location: '', catalog: '', providerstring: '' using provider 'SQLNCLI'
2012-01-11 12:05:42.137 OLE DB Distributor: PubServerName
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name:
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2012-01-11 12:05:42.137 OLE DB Distributor 'PubServerName': {call sp_MSgetversion }
2012-01-11 12:05:42.153 OLE DB Distributor 'PubServerName': {call sp_helpdistpublisher (N'PubServerName') }
2012-01-11 12:05:42.169 OLE DB Distributor 'PubServerName': {call sp_MShelp_repl_agent (N'PubServerName', N'UserDBName', N'MtoC_M', N'SubServerName', N'UserDBName', 1)}
2012-01-11 12:05:42.169 OLE DB Distributor 'PubServerName': select datasource, srvid from master..sysservers where upper(srvname) = upper(N'PubServerName')
2012-01-11 12:05:42.184 OLE DB Distributor 'PubServerName': {call sp_MShelp_merge_agentid (0,N'UserDBName',N'MtoC_M',null,N'UserDBName',90,N'SubServerName')}
2012-01-11 12:05:42.184 OLE DB Distributor 'PubServerName': {call sp_MShelp_profile (29, 4, N'')}
2012-01-11 12:05:42.184 Percent Complete: 0
2012-01-11 12:05:42.184 Connecting to OLE DB Publisher at datasource: 'PubServerName', location: '', catalog: 'UserDBName', providerstring: '' using provider 'SQLNCLI'
2012-01-11 12:05:42.200 Initializing
2012-01-11 12:05:42.200 Repl Agent Status: 1
2012-01-11 12:05:42.216 OLE DB Distributor 'PubServerName': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:42.216 Percent Complete: 0
2012-01-11 12:05:42.231 Connecting to Publisher 'PubServerName'
2012-01-11 12:05:42.231 Repl Agent Status: 3
2012-01-11 12:05:42.231 OLE DB Distributor 'PubServerName': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:43.153 OLE DB Publisher: PubServerName
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: UserDBName
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2012-01-11 12:05:43.169 OLE DB Publisher 'PubServerName': set nocount on declare @dbname sysname select @dbname = db_name() declare @collation nvarchar(255) select @collation = convert(nvarchar(255), databasepropertyex(@dbname, N'COLLATION')) select collationproperty(@collation, N'CODEPAGE') as 'CodePage', collationproperty(@collation, N'LCID') as 'LCID', collationproperty(@collation, N'COMPARISONSTYLE') as 'ComparisonStyle',cast(case when convert (int,databasepropertyex (@dbname,'comparisonstyle')) & 0x1 = 0x1 then 0 else 1 end as bit) as DB_CaseSensitive,cast(case when convert (int,serverproperty ('comparisonstyle')) & 0x1 = 0x1 then 0 else 1 end as bit) as Server_CaseSensitive set nocount off
2012-01-11 12:05:43.169 OLE DB Publisher 'PubServerName': {call sp_MSgetversion }
2012-01-11 12:05:43.200 Connecting to OLE DB Publisher at datasource: 'PubServerName', location: '', catalog: 'UserDBName', providerstring: '' using provider 'SQLNCLI'
2012-01-11 12:05:44.137 OLE DB Publisher: PubServerName
DBMS: Microsoft SQL Server
Version: 09.00.3042
catalog name: UserDBName
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2012-01-11 12:05:44.137 OLE DB Publisher 'PubServerName': {call sp_MSchecksnapshotstatus (N'MtoC_M')}
2012-01-11 12:05:44.153 OLE DB Publisher 'PubServerName': {call sp_helpmergepublication (N'MtoC_M')}
2012-01-11 12:05:44.153 OLE DB Publisher 'PubServerName': {call sys.sp_MSgetreplicainfo(?,?,?,?,?,?,?,90)}
2012-01-11 12:05:44.169 OLE DB Distributor 'PubServerName': {call sp_MShelp_repl_agent (N'PubServerName', N'UserDBName', N'MtoC_M', N'SubServerName', N'UserDBName', 1)}
2012-01-11 12:05:44.169 Connecting to OLE DB Subscriber at datasource: 'SubServerName', location: '', catalog: 'UserDBName', providerstring: '' using provider 'SQLNCLI'
2012-01-11 12:05:44.747 OLE DB Subscriber: SubServerName
DBMS: Microsoft SQL Server
Version: 10.50.1600
catalog name: UserDBName
user name: distributor_MtoC
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2012-01-11 12:05:45.122 OLE DB Subscriber 'SubServerName': {call sp_MSgetversion }
2012-01-11 12:05:45.216 OLE DB Subscriber 'SubServerName': {call sp_MSreplcheck_subscribe}
2012-01-11 12:05:45.309 OLE DB Subscriber 'SubServerName': set nocount on declare @dbname sysname select @dbname = db_name() declare @collation nvarchar(255) select @collation = convert(nvarchar(255), databasepropertyex(@dbname, N'COLLATION')) select collationproperty(@collation, N'CODEPAGE') as 'CodePage', collationproperty(@collation, N'LCID') as 'LCID', collationproperty(@collation, N'COMPARISONSTYLE') as 'ComparisonStyle',cast(case when convert (int,databasepropertyex (@dbname,'comparisonstyle')) & 0x1 = 0x1 then 0 else 1 end as bit) as DB_CaseSensitive,cast(case when convert (int,serverproperty ('comparisonstyle')) & 0x1 = 0x1 then 0 else 1 end as bit) as Server_CaseSensitive set nocount off
2012-01-11 12:05:45.419 Percent Complete: 0
2012-01-11 12:05:45.419 OLE DB Subscriber 'SubServerName': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:45.419 Connecting to Subscriber 'SubServerName'
2012-01-11 12:05:45.434 Repl Agent Status: 3
2012-01-11 12:05:45.434 OLE DB Distributor 'PubServerName': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:45.559 Percent Complete: 0
2012-01-11 12:05:45.559 OLE DB Subscriber 'SubServerName': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:45.559 Retrieving publication information
2012-01-11 12:05:45.575 Repl Agent Status: 3
2012-01-11 12:05:45.575 OLE DB Distributor 'PubServerName': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:45.653 OLE DB Subscriber 'SubServerName': {call sys.sp_MSmerge_upgrade_subscriber(1,?)}
2012-01-11 12:05:45.653 Percent Complete: 0
2012-01-11 12:05:45.669 Retrieving subscription information.
2012-01-11 12:05:45.669 Repl Agent Status: 3
2012-01-11 12:05:45.684 OLE DB Distributor 'PubServerName': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:45.763 OLE DB Publisher 'PubServerName': {call sys.sp_MSgetreplicainfo(?,?,?,?,?,?,?,90)}
2012-01-11 12:05:45.763 OLE DB Publisher 'PubServerName': {call sys.sp_MShelpmergearticles (?,9000000,?) }
2012-01-11 12:05:45.778 OLE DB Publisher 'PubServerName': {call sp_MSenumschemachange (?,?,9000000,?,0,1) }
2012-01-11 12:05:45.794 OLE DB Subscriber 'SubServerName': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:45.888 OLE DB Publisher 'PubServerName': {call sys.sp_MSallocate_new_identity_range (?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:45.888 Percent Complete: 0
2012-01-11 12:05:45.903 Applying the snapshot to the Subscriber
2012-01-11 12:05:45.903 OLE DB Publisher 'PubServerName': {call sys.sp_MSallocate_new_identity_range (?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:45.903 Repl Agent Status: 3
2012-01-11 12:05:45.919 OLE DB Publisher 'PubServerName': {call sys.sp_MSallocate_new_identity_range (?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:45.919 OLE DB Distributor 'PubServerName': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:45.919 OLE DB Publisher 'PubServerName': {call sys.sp_MSallocate_new_identity_range (?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:45.934 OLE DB Publisher 'PubServerName': {call sys.sp_MSallocate_new_identity_range (?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:45.950 OLE DB Publisher 'PubServerName': {call sys.sp_MSallocate_new_identity_range (?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:45.950 OLE DB Publisher 'PubServerName': {call sys.sp_MSallocate_new_identity_range (?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:45.966 OLE DB Publisher 'PubServerName': {call sys.sp_MSallocate_new_identity_range (?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:45.981 OLE DB Publisher 'PubServerName': {call sys.sp_MSallocate_new_identity_range (?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:45.997 OLE DB Publisher 'PubServerName': {call sys.sp_MSallocate_new_identity_range (?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:45.997 OLE DB Subscriber 'SubServerName': {call master..sp_helpreplicationoption ('merge')}
2012-01-11 12:05:46.091 OLE DB Subscriber 'SubServerName': {call sys.sp_MSmergesubscribedb ('true', 0) }
2012-01-11 12:05:47.263 OLE DB Subscriber 'SubServerName': {call sp_MSpublicationcleanup (?,?,?)}
2012-01-11 12:05:47.372 OLE DB Subscriber 'SubServerName': {call sp_MSCleanupForPullReinit (?,?,?)}
2012-01-11 12:05:47.481 OLE DB Subscriber 'SubServerName': {call sp_MSdropconstraints (N'tblEmailMessages',N'dbo')}
2012-01-11 12:05:47.606 OLE DB Subscriber 'SubServerName': {call sp_MSdropconstraints (N'tblUser',N'dbo')}
2012-01-11 12:05:47.716 OLE DB Subscriber 'SubServerName': {call sp_MSdropconstraints (N'tblDPVLocked',N'dbo')}
2012-01-11 12:05:47.809 OLE DB Subscriber 'SubServerName': {call sp_MSdropconstraints (N'tblCaptureAppToken',N'dbo')}
2012-01-11 12:05:47.903 OLE DB Subscriber 'SubServerName': {call sp_MSdropconstraints (N'tblCreditThresholdNotification',N'dbo')}
2012-01-11 12:05:48.013 OLE DB Subscriber 'SubServerName': {call sp_MSdropconstraints (N'tblCreditPot',N'dbo')}
2012-01-11 12:05:48.106 OLE DB Subscriber 'SubServerName': {call sys.sp_MSaddinitialpublication (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:48.247 OLE DB Subscriber 'SubServerName': {call sp_MSunmarkifneeded (N'tblEmailMessages',?, 1, ?, ?)}
2012-01-11 12:05:48.403 OLE DB Subscriber 'SubServerName': {call sp_MSunmarkifneeded (N'tblUser',?, 1, ?, ?)}
2012-01-11 12:05:48.513 OLE DB Subscriber 'SubServerName': {call sp_MSunmarkifneeded (N'tblDPVLocked',?, 1, ?, ?)}
2012-01-11 12:05:48.606 OLE DB Subscriber 'SubServerName': {call sp_MSunmarkifneeded (N'tblCaptureAppToken',?, 1, ?, ?)}
2012-01-11 12:05:48.700 OLE DB Subscriber 'SubServerName': {call sp_MSunmarkifneeded (N'tblCreditThresholdNotification',?, 1, ?, ?)}
2012-01-11 12:05:48.809 OLE DB Subscriber 'SubServerName': {call sp_MSunmarkifneeded (N'tblCreditPot',?, 1, ?, ?)}
2012-01-11 12:05:48.903 OLE DB Publisher 'PubServerName': {call sp_MShelpmergeschemaarticles(?)}
2012-01-11 12:05:48.919 OLE DB Subscriber 'SubServerName': {call sp_MSaddinitialsubscription (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}
2012-01-11 12:05:49.059 OLE DB Distributor 'PubServerName': select datasource, srvid from master..sysservers where upper(srvname) = upper(N'PubServerName')
2012-01-11 12:05:49.059 OLE DB Distributor 'PubServerName': {call sys.sp_MSadd_mergesubentry_indistdb (0,N'PubServerName',N'UserDBName',N'MtoC_M',N'SubServerName',N'UserDBName',0,1,0,N'',?,90)}
2012-01-11 12:05:49.075 OLE DB Subscriber 'SubServerName': {call sp_MScreateglobalreplica (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,90)}
2012-01-11 12:05:49.341 Connecting to OLE DB Subscriber at datasource: 'SubServerName', location: '', catalog: 'UserDBName', providerstring: '' using provider 'SQLNCLI'
2012-01-11 12:05:49.825 OLE DB Subscriber: SubServerName
DBMS: Microsoft SQL Server
Version: 10.50.1600
catalog name: UserDBName
user name: distributor_MtoC
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2012-01-11 12:05:50.185 OLE DB Subscriber: SubServerName
DBMS: Microsoft SQL Server
Version: 10.50.1600
catalog name: UserDBName
user name: distributor_MtoC
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2012-01-11 12:05:50.325 OLE DB Subscriber 'SubServerName': {call sys.sp_MSregistermergesnappubid(?, ?)}
2012-01-11 12:05:50.450 OLE DB Subscriber 'SubServerName': sp_MSacquiresnapshotdeliverysessionlock
2012-01-11 12:05:50.544 OLE DB Subscriber 'SubServerName': sp_MStrypurgingoldsnapshotdeliveryprogress
2012-01-11 12:05:50.810 OLE DB Subscriber 'SubServerName': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:50.966 [2%] OLE DB Subscriber 'SubServerName': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
ꜻä㹒 ꜐äB 11 12:05ä谼砗꜐ä镴ĀPercent Complete: 4
2012-01-11 12:05:50.997 Propagated 1 schema changes: 1 total
2012-01-11 12:05:51.013 Repl Agent Status: 3
2012-01-11 12:05:51.013 OLE DB Distributor 'PubServerName': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:51.091 [6%] OLE DB Subscriber 'SubServerName': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
ꜻä㹒 ꜐äB 11 12:05ä谼砗꜐ä镴ĀPercent Complete: 6
2012-01-11 12:05:51.122 Propagated 1 schema changes: 2 total
2012-01-11 12:05:51.153 Repl Agent Status: 3
2012-01-11 12:05:51.153 OLE DB Distributor 'PubServerName': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:51.200 OLE DB Subscriber 'SubServerName': {call sp_MSunmarkreplinfo (N'tblEmailMessages')}
2012-01-11 12:05:51.200 [6%] Percent Complete: 6
2012-01-11 12:05:51.231 Propagated 1 schema changes: 3 total
2012-01-11 12:05:51.247 Repl Agent Status: 3
2012-01-11 12:05:51.263 OLE DB Distributor 'PubServerName': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:51.325 OLE DB Subscriber 'SubServerName': sp_MSissnapshotitemapplied @snapshot_session_token = N'E:\Microsoft SQL Server\MSSQL.1\MSSQL\repldata\unc\PubServerName_UserDBName_MTOC_M\20120111115787\', @snapshot_progress_token = N'E:\Microsoft SQL Server\MSSQL.1\MSSQL\repldata\unc\PubServerName_UserDBName_MTOC_M\20120111115787\tblEmailMessages_2.sch'
2012-01-11 12:05:51.435 drop Table [dbo].[tblEmailMessages]
go
SET ANSI_PADDING ON
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblEmailMessages](
[EmailMessageId] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Guid] [nvarchar](32) NOT NULL,
[Recipients] [varchar](8000) NOT NULL,
[CcRecipients] [varchar](8000) NULL,
[BccRecipients] [varchar](8000) NULL,
[Sender] [varchar](8000) NOT NULL,
[Subject] [nvarchar](4000) NOT NULL,
[BodyText] [nvarchar](max) NULL,
[BodyHTML] [nvarchar](max) NULL,
[IsBodyEncrypted] [bit] NOT NULL CONSTRAINT [DF_tblEmailMessages_IsBodyEncrypted] DEFAULT ((0)),
[Status] [tinyint] NOT NULL CONSTRAINT [DF_tblEmailMessages_Status] DEFAULT ((1)),
[DateAdded] [datetime] NOT NULL CONSTRAINT [DR_tblEmailMessages_DateAdded] DEFAULT (getdate()),
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [MSmerge_df_rowguid_207F75F229994684BC3FAAE5BE4CD6EB] DEFAULT (newsequentialid())
)
GO
GRANT DELETE ON [dbo].[tblEmailMessages] TO [odintermediary]
GO
GRANT INSERT ON [dbo].[tblEmailMessages] TO [odintermediary]
GO
GRANT SELECT ON [dbo].[tblEmailMessages] TO [odintermediary]
GO
GRANT UPDATE ON [dbo].[tblEmailMessages] TO [odintermediary]
GO
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
ALTER TABLE [dbo].[tblEmailMessages] ADD CONSTRAINT [PK_tblEmailMessage] PRIMARY KEY CLUSTERED
(
[EmailMessageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
2012-01-11 12:05:51.450 OLE DB Subscriber 'SubServerName': drop Table [dbo].[tblEmailMessages]
2012-01-11 12:05:51.560 OLE DB Subscriber 'SubServerName': SET ANSI_PADDING ON
2012-01-11 12:05:51.669 OLE DB Subscriber 'SubServerName': SET ANSI_NULLS ON
2012-01-11 12:05:51.778 OLE DB Subscriber 'SubServerName': SET QUOTED_IDENTIFIER ON
2012-01-11 12:05:51.872 OLE DB Subscriber 'SubServerName': CREATE TABLE [dbo].[tblEmailMessages](
[EmailMessageId] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Guid] [nvarchar](32) NOT NULL,
[Recipients] [varchar](8000) NOT NULL,
[CcRecipients] [varchar](8000) NULL,
[BccRecipients] [varchar](8000) NULL,
[Sender] [varchar](8000) NOT NULL,
[Subject] [nvarchar](4000) NOT NULL,
[BodyText] [nvarchar](max) NULL,
[BodyHTML] [nvarchar](max) NULL,
[IsBodyEncrypted] [bit] NOT NULL CONSTRAINT [DF_tblEmailMessages_IsBodyEncrypted] DEFAULT ((0)),
[Status] [tinyint] NOT NULL CONSTRAINT [DF_tblEmailMessages_Status] DEFAULT ((1)),
[DateAdded] [datetime] NOT NULL CONSTRAINT [DR_tblEmailMessages_DateAdded] DEFAULT (getdate()),
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [MSmerge_df_rowguid_207F75F229994684BC3FAAE5BE4CD6EB] DEFAULT (newsequentialid())
)
2012-01-11 12:05:51.981 OLE DB Subscriber 'SubServerName': GRANT DELETE ON [dbo].[tblEmailMessages] TO [odintermediary]
2012-01-11 12:05:52.091 OLE DB Subscriber 'SubServerName': GRANT INSERT ON [dbo].[tblEmailMessages] TO [odintermediary]
2012-01-11 12:05:52.200 OLE DB Subscriber 'SubServerName': GRANT SELECT ON [dbo].[tblEmailMessages] TO [odintermediary]
2012-01-11 12:05:52.294 OLE DB Subscriber 'SubServerName': GRANT UPDATE ON [dbo].[tblEmailMessages] TO [odintermediary]
2012-01-11 12:05:52.403 OLE DB Subscriber 'SubServerName': SET ANSI_NULLS ON
2012-01-11 12:05:52.497 OLE DB Subscriber 'SubServerName': SET QUOTED_IDENTIFIER ON
2012-01-11 12:05:52.606 OLE DB Subscriber 'SubServerName': ALTER TABLE [dbo].[tblEmailMessages] ADD CONSTRAINT [PK_tblEmailMessage] PRIMARY KEY CLUSTERED
(
[EmailMessageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
2012-01-11 12:05:52.716 OLE DB Subscriber 'SubServerName': sp_MSrecordsnapshotdeliveryprogress @snapshot_session_token = N'E:\Microsoft SQL Server\MSSQL.1\MSSQL\repldata\unc\PubServerName_UserDBName_MTOC_M\20120111115787\', @snapshot_progress_token = N'E:\Microsoft SQL Server\MSSQL.1\MSSQL\repldata\unc\PubServerName_UserDBName_MTOC_M\20120111115787\tblEmailMessages_2.sch'
2012-01-11 12:05:52.841 OLE DB Subscriber 'SubServerName': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:52.966 [6%] Percent Complete: 6
2012-01-11 12:05:52.981 Applied script 'tblEmailMessages_2.sch'
2012-01-11 12:05:52.981 Repl Agent Status: 3
2012-01-11 12:05:52.981 OLE DB Distributor 'PubServerName': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:53.153 OLE DB Subscriber 'SubServerName': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:53.263 [6%] OLE DB Subscriber 'SubServerName': {call sys.sp_MSaddinitialarticle (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
ꜻä㹒 ꜐äB 11 12:05ä谼砗꜐ä镴ĀPercent Complete: 6
2012-01-11 12:05:53.294 Preparing table 'tblEmailMessages' for merge replication
2012-01-11 12:05:53.294 Repl Agent Status: 3
2012-01-11 12:05:53.310 OLE DB Distributor 'PubServerName': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:53.450 OLE DB Subscriber 'SubServerName': {call sp_MSupdatesysmergearticles (?,?,?,?,0)}
2012-01-11 12:05:53.575 OLE DB Subscriber 'SubServerName': {call sys.sp_MSsetup_identity_range (?,?,?,?,?,?,?,?)}
2012-01-11 12:05:53.794 The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
2012-01-11 12:05:53.825 OLE DB Subscriber 'SubServerName': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:53.966 Percent Complete: 6
2012-01-11 12:05:53.966 No subscription is on this publication or article.
2012-01-11 12:05:53.997 Repl Agent Status: 6
2012-01-11 12:05:54.013 OLE DB Distributor 'PubServerName': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-01-11 12:05:54.216 Percent Complete: 0
2012-01-11 12:05:54.231 Category:SQLSERVER
Source: SubServerName
Number: 14050
Message: No subscription is on this publication or article.
2012-01-11 12:05:54.247 Repl Agent Status: 3
2012-01-11 12:05:54.372 Percent Complete: 0
2012-01-11 12:05:54.372 Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
2012-01-11 12:05:54.388 Repl Agent Status: 3
2012-01-11 12:05:54.388 Disconnecting from OLE DB Subscriber 'SubServerName'
2012-01-11 12:05:54.388 Disconnecting from OLE DB Subscriber 'SubServerName'
2012-01-11 12:05:54.403 Disconnecting from OLE DB Subscriber 'SubServerName'
2012-01-11 12:05:54.403 Disconnecting from OLE DB Subscriber 'SubServerName'
2012-01-11 12:05:54.403 Disconnecting from OLE DB Publisher 'PubServerName'
2012-01-11 12:05:54.419 Disconnecting from OLE DB Publisher 'PubServerName'
2012-01-11 12:05:54.419 Disconnecting from OLE DB Publisher 'PubServerName'
2012-01-11 12:05:54.419 Disconnecting from OLE DB Publisher 'PubServerName'
2012-01-11 12:05:54.435 Disconnecting from OLE DB Distributor 'PubServerName'
2012-01-11 12:05:54.435 Disconnecting from OLE DB Distributor 'PubServerName'
2012-01-11 12:05:54.450 The merge process will restart after waiting 30 second(s)...
Percent Complete: 0
Any help would be much appreciated.
Thanks
Chris
January 12, 2012 at 2:03 am
Some additional info;
I also noticed that the subscriber server had a collation of SQL_LATIN1_GENERAL_CP1_CI_AS whereas the publisher is LATIN1_GENERAL_CI_AS I changed the subscriber database to LATIN1_GENERAL_CI_AS but still get the same error. I then rebuilt the system databases to use LATIN1_GENERAL_CI_AS and still get the same error. Last night I uninstalled and reinstalled SQL Server (initial install was done by a different team) just to be sure of the installation settings, and you know what I STILL get the same error 🙁
The subscriber server looks to have been created from an image as I had to rename SQL Server the other day using sp_dopserver and sp_addserver see my blog;
http://sqlserver365.blogspot.com/2012/01/server-name-and-sql-server-name.html
Thanks
Chris
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply