I cannot get internet connection to work for push subscription to transactional publication.
Publisher/distributor: SQL Server 2000
Subscriber: SQL Server 2000
Connection: TCP/IP, SQL Authentication. Works fine from SQLEM or Query Analyzer.
Subscription: push, transactional, schema/data already initialized.
When I run distributor agent fails with "The process could not connect to subscriber 'TEST'", error "Server does not exist or access denied".
Agent output does not give anything more specific. It fails after...
Connecting to Subscriber 'TEST'
Connecting to Subscriber 'TEST.REPL'
Profiler shows nothing happening at all.
If change subscriber setup on distributor to use trusted connection instead of SQL Auth it works fine.
Any ideas?OK, so all the replication experts are on vacation this week but I eventually managed to figure it out myself.
When defining subscribers (through 'distributor properties') use the subscriber TCP/IP address directly and not a server alias like I was doing. Silly Billy!
Showing posts with label fails. Show all posts
Showing posts with label fails. Show all posts
Monday, March 26, 2012
Push replication to multihomed client fails
Hi
We're having problems with push replication to multihomed clients. if the DNS contains multiple IP's and one of those are not reachable from the sql server. the replication fails. no matter order of IP adresses. i wrote a DNS proxy which re-ordered the IP's so that the "working" IP would come first in the list but no success until i filtered out so that the DNS proxy only replied with one, the working, IP.
the replication is part of a commercial application which we have no control over. are there any settings on the server side that can help here? I really want to fix the source of the problem and not patch it with a proxy.
any ideas are welcome. the server is sql 2005 standard and the clients are running 2005 express
Rgds
just an update, if anyone has the same problem
i found out how it works by reading this thread.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1526698&SiteID=1
this doesnt fix the problem. but it explains it very well. so we'll stick with the proxy.
Push replication to multihomed client fails
Hi
We're having problems with push replication to multihomed clients. if the DNS contains multiple IP's and one of those are not reachable from the sql server. the replication fails. no matter order of IP adresses. i wrote a DNS proxy which re-ordered the IP's so that the "working" IP would come first in the list but no success until i filtered out so that the DNS proxy only replied with one, the working, IP.
the replication is part of a commercial application which we have no control over. are there any settings on the server side that can help here? I really want to fix the source of the problem and not patch it with a proxy.
any ideas are welcome. the server is sql 2005 standard and the clients are running 2005 express
Rgds
just an update, if anyone has the same problem
i found out how it works by reading this thread.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1526698&SiteID=1
this doesnt fix the problem. but it explains it very well. so we'll stick with the proxy.
sqlPush Merge snapshot fails with Data is Null message.
Hi,
I'm converting a replication script from SQL 2000 to SQL 2005.
I am getting an error with push merge with no way to figure out what is wrong.
I've configured replication on a single XP server in SQL 2005 RTM version.
I have a push merge set up between A and B and between B and C. All 3 databases are 9.0 compatibility.
The snapshot and merge jobs for the A to B run fine with no errors, and merge replicates ok.
The snapshot for B to C fails with this message:
Message
2006-03-09 17:30:35.94
2006-03-09 17:30:35.94 -BcpBatchSize 100000
2006-03-09 17:30:35.94 -HistoryVerboseLevel 2
2006-03-09 17:30:35.94 -LoginTimeout 15
2006-03-09 17:30:35.94 -QueryTimeout 1800
2006-03-09 17:30:35.94
2006-03-09 17:30:35.95 Connecting to Publisher 'MyInstance'
2006-03-09 17:30:35.97 Publisher database compatibility level is set to 90.
2006-03-09 17:30:35.97 Retrieving publication and article information from the publisher database 'MyInstance.MyDB'
2006-03-09 17:30:36.22 [0%] The replication agent had encountered an exception.
2006-03-09 17:30:36.22 Source: Replication
2006-03-09 17:30:36.22 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2006-03-09 17:30:36.22 Exception Message: Data is Null. This method or property cannot be called on Null values.
2006-03-09 17:30:36.22 Message Code: 52006
2006-03-09 17:30:36.22
Love that exception message: "Data is Null" - very helpful to someone who is clairvoyant perhaps.
I checked the snapshot bcp files. The tables being merged all have data.
A sample add article command is:
exec sp_addmergearticle @.publication = N'MyMerge', @.article = N'Phone', @.processing_order = 4, @.source_owner = N'dbo', @.source_object = N'Phone', @.type = N'table', @.description = null, @.column_tracking = N'true', @.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option = 0x000000004C42CDDF, @.article_resolver = null, @.subset_filterclause = null, @.vertical_partition = N'false', @.destination_owner = N'dbo', @.verify_resolver_signature = 0, @.allow_interactive_resolver = N'false', @.fast_multicol_updateproc = N'true', @.check_permissions = 0, @.identityrangemanagementoption = N'none' ,@.force_invalidate_snapshot = 1,@.force_reinit_subscription = 1
If you have any ideas on how to fix this, I'd be most grateful. As it is after 6pm I probably won't read this again until morning. Thanks for any suggestions.
Can you get the full stack trace from MSrepl_errors in the distribution database and post it here please? Thanks.
-Raymond
|||Here you go:
Message: Data is Null. This method or property cannot be called on Null values.
Command Text: sp_MSestimatemergesnapshotworkload
Parameters: @.publication = StandardMerge Stack:
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate)
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.InitializeProgressTracker()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run()
Source: System.Data Target Site: Int32 get_Int32()
Message: Data is Null. This method or property cannot be called on Null values. Stack:
at System.Data.SqlClient.SqlBuffer.get_Int32()
at System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i)
at Microsoft.SqlServer.Replication.Snapshot.ProgressTracker..ctor(SqlDataReader dataReader, SnapshotGenerationAgent snapshotGenerationAgent)
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.CreateProgressTracker(SqlDataReader dataReader)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior)
This looks like a bug, can you execute sp_MSestimatemergesnapshotworkload @.publication = 'StandardMerge' at the publisher database and post any rows with a null 'taskid' or 'taskload' value here. Thanks.
-Raymond
|||Here they are. I've renamed the tables from their real names.
By the way, for the same task id (4), for the A to B merge, these are the values (in case this is useful for comparison).
Can you try the following query at the publisher database and see if it returns any rows?
select rowcnt from sysindexes where id = object_id(N'Table1') and indid in (0,1)
If the above query doesn't return any rows then this is basically the cause of the problem and it would be great if you can retry the query without the "and indid in (0,1)" part and report back whether there are any rows returned in that case. And if the publisher connection of the snapshot agent is not db_owner\sysadmin, you may want to temporarily change that and see if the problem goes away.
-Raymond
|||The result from the original query is rowcnt = 195.
|||Also, this query and the prior queries were run using the login of the original script.|||
More info - in case it is useful
The parms passed for the merge publications:
For the AtoB merge (which works)
exec sp_addmergepublication @.publication = N'AtoB', @.description = N'Corporate Merge Replication', @.retention = 14, @.sync_mode = N'native', @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'false', @.publication_compatibility_level = '90RTM', @.enabled_for_internet = N'false', @.conflict_logging = N'publisher', @.snapshot_in_defaultfolder = N'true', @.pre_snapshot_script = @.PreScript, @.post_snapshot_script = @.PostScript, @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.conflict_retention = 14, @.keep_partition_changes = N'false', @.allow_subscription_copy = N'false', @.allow_synctoalternate = N'false', @.add_to_active_directory = N'false', @.max_concurrent_merge = 0, @.max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @.publication = N'AtoB',@.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 1, @.frequency_subday_interval = 5, @.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 500, @.active_end_time_of_day = 235959, @.snapshot_job_name = @.MyName
exec sp_addmergesubscription @.publication = N'AtoB', @.subscriber = @.MyInstance, @.subscriber_db = N'SiteMaster', @.subscription_type = N'push', @.subscriber_type = N'global', @.subscription_priority = 75.0, @.sync_type = N'automatic', @.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.enabled_for_syncmgr = N'false', @.offloadagent = 0, @.use_interactive_resolver = N'false', @.merge_job_name = @.Myname
For the BtoC merge (which doesn't)
exec sp_addmergepublication @.publication = N'BtoC', @.description = N'Facility Master Merge Replication', @.retention = 14, @.sync_mode = N'native', @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'false',@.publication_compatibility_level = '90RTM', @.enabled_for_internet = N'false', @.conflict_logging = N'publisher', @.snapshot_in_defaultfolder = N'true', @.pre_snapshot_script = @.PreScript, @.post_snapshot_script = @.PostScript, @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.conflict_retention = 14, @.keep_partition_changes = N'false', @.allow_subscription_copy = N'false', @.allow_synctoalternate = N'false', @.add_to_active_directory = N'false', @.max_concurrent_merge = 0, @.max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @.publication = N'BtoC',@.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 1, @.frequency_subday_interval = 5, @.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 500, @.active_end_time_of_day = 235959, @.snapshot_job_name = @.Myname
exec sp_addmergesubscription @.publication = N'BtoC', @.subscriber = @.MyInstance, @.subscriber_db = @.MyFacility, @.subscription_type = N'push', @.subscriber_type = N'local', @.subscription_priority = 0.0, @.sync_type = N'automatic', @.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.enabled_for_syncmgr = N'false', @.offloadagent = 0, @.use_interactive_resolver = N'false', @.merge_job_name = @.MyName
|||This looks really weird, can you run sp_helpmergearticle @.publication =N'BtoC' at B and report back whether the source_object and source_owner columns are NULLs? Thanks much.
-Raymond
|||Raymond,
I get nothing back from that at all. When I run it on the AtoB pub,run at A, the result looks normal. But nothing at all comes back for BtoC publication, run at B.
|||This is interesting:
When I run EXEC dbo.sp_helpmergepublication at A, I get priority = 100.
When I run the same thing at B, I also get priority = 100.
You can see in my sp_addmergesubscription for A, I had set priority to 75.0 and that for B I'd set it to 0.0 so SQL is ignoring those parms I guess.
|||Ok, looks like your publication\article metadata is very messed up at B. To confirm my suspicion, can you try the following query at B:
select name, pubid, objid, object_name(objid) from dbo.sysmergeextendedarticlesview
I am interested to know if any values in the last column are null or not. If there are any null values, then your article meta-data is seriously corrupted. You can, of course, recover from this particular problem by recreating the publication at B but given the kind of problems you have experienced in the past, I strongly suspect some other background operations at B is what is causing the problems in the first place. Here are a couple of possibilities that I can think of:
1) Someone tried to renamed the published table at B
2) Someone tried to drop and recreate the published table at B and use sp_MSunmarkreplino to allow the operation to occur.
3) The merge agent has a problem updating the object ids of published tables at B when a snapshot is delivered from A to B.
4) You have included sysmergearticles and\or sysmergepublications as part of the publication from A to B.
HTH
-Raymond
|||I ran it (ordered by 2,1) and all the articles for B publication have NULL in the last column.
As for the possibilities, nobody but me has done anything to this data.
1) haven't done
2) I ran that once a week ago when I was first putting this stuff together and trying to clean stuff up with another problem I have with transactional replication getting a 3724 error ("Cannot drop the table 'dbo.Mytablename' because it is being used for replication.) Would that still be messing me up after many builds since then?
3) At the moment, I believe everything is running under the SQL Agent Svc login.
4) Those are not in my 13 explicitly named sp_addmergearticle articles.
Also, per priority difference mentioned earlier. I ran sp_helpmergesubscription and the priorities were as I expected. So why are the sp_helpmergepublication priorities 100?
Push merge replication fails - problem connecting to subscriber
I am trying to replicate a SQL 2000 database from one branch office to
another. The replication monitor seems to stall at Connecting to
'Subscriber', opening SQL profiler shows repeated sp_MSgetonerow calls. I
have left it running for a day and a half to see if it would sort itself out
and it hasn't.
Replication seemed to work fine while I was making changes at the publisher
and replicating them down to subscriber. As soon as subscriber made changes,
this happened. How do I start to troubleshoot?
Thanks
Brandon
Pasting my output debugging file
Microsoft SQL Server Merge Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: SERVER-ildm-ildm-CALGSERV-34
Percent Complete: 0
Connecting to Distributor 'SERVER'
Connecting to Distributor 'SERVER.'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:00 AM]SERVER.: {call sp_MSgetversion }
[2/7/2005 11:08:00 AM]SERVER.: {call sp_helpdistpublisher (N'SERVER') }
[2/7/2005 11:08:00 AM]SERVER.distribution: select datasource, srvid from
master..sysservers where upper(srvname) = upper(N'SERVER')
[2/7/2005 11:08:00 AM]SERVER.distribution: select datasource, srvid from
master..sysservers where upper(srvname) = upper(N'CALGSERV')
[2/7/2005 11:08:00 AM]SERVER.distribution: {call sp_MShelp_merge_agentid (0,
N'ildm', N'ildm', 2, N'ILDM')}
[2/7/2005 11:08:00 AM]SERVER.distribution: {call sp_MShelp_profile (34, 4,
N'')}
Percent Complete: 0
Connecting to Publisher 'SERVER.ildm'
Initializing
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:00 AM]SERVER.ildm: 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'
Percent Complete: 1
Connecting to Publisher 'SERVER.ildm'
Connecting to Publisher 'SERVER'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:01 AM]SERVER.ildm: {call sp_MSgetversion }
[2/7/2005 11:08:01 AM]SERVER.distribution: {call sp_MShelp_subscriber_info
(N'SERVER', N'CALGSERV')}
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:01 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Percent Complete: 2
Connecting to Subscriber 'CALGSERV'
Percent Complete: 3
Retrieving publication information
Percent Complete: 4
Retrieving subscription information
Percent Complete: 4
The merge process is cleaning up meta data in database 'ildm'.
Percent Complete: 4
The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in
MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
Percent Complete: 4
Uploading data changes to the Publisher
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:03 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Connecting to Publisher 'SERVER.ildm'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:03 AM]SERVER.ildm: {call sp_MSgetversion }
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:03 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Connecting to Publisher 'SERVER.ildm'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:04 AM]SERVER.ildm: {call sp_MSgetversion }
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:04 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Connecting to Publisher 'SERVER.ildm'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:04 AM]SERVER.ildm: {call sp_MSgetversion }
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:05 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Connecting to Publisher 'SERVER.ildm'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:05 AM]SERVER.ildm: {call sp_MSgetversion }
|||What profile are you running?
Can you right click on your merge agent, select agent properties, Steps, and
then Run Agent. Click on the Edit button. Click in the Commands box, and
press CTRL A. Then Press CTRL C
Then paste the commands here and post everything here.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"pUmkInhEd" <pUmkInhEd@.discussions.microsoft.com> wrote in message
news:388D911E-2BB3-4A02-AC22-F1129C02C2BC@.microsoft.com...
> I am trying to replicate a SQL 2000 database from one branch office to
> another. The replication monitor seems to stall at Connecting to
> 'Subscriber', opening SQL profiler shows repeated sp_MSgetonerow calls. I
> have left it running for a day and a half to see if it would sort itself
out
> and it hasn't.
> Replication seemed to work fine while I was making changes at the
publisher
> and replicating them down to subscriber. As soon as subscriber made
changes,
> this happened. How do I start to troubleshoot?
> Thanks
> Brandon
another. The replication monitor seems to stall at Connecting to
'Subscriber', opening SQL profiler shows repeated sp_MSgetonerow calls. I
have left it running for a day and a half to see if it would sort itself out
and it hasn't.
Replication seemed to work fine while I was making changes at the publisher
and replicating them down to subscriber. As soon as subscriber made changes,
this happened. How do I start to troubleshoot?
Thanks
Brandon
Pasting my output debugging file
Microsoft SQL Server Merge Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: SERVER-ildm-ildm-CALGSERV-34
Percent Complete: 0
Connecting to Distributor 'SERVER'
Connecting to Distributor 'SERVER.'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:00 AM]SERVER.: {call sp_MSgetversion }
[2/7/2005 11:08:00 AM]SERVER.: {call sp_helpdistpublisher (N'SERVER') }
[2/7/2005 11:08:00 AM]SERVER.distribution: select datasource, srvid from
master..sysservers where upper(srvname) = upper(N'SERVER')
[2/7/2005 11:08:00 AM]SERVER.distribution: select datasource, srvid from
master..sysservers where upper(srvname) = upper(N'CALGSERV')
[2/7/2005 11:08:00 AM]SERVER.distribution: {call sp_MShelp_merge_agentid (0,
N'ildm', N'ildm', 2, N'ILDM')}
[2/7/2005 11:08:00 AM]SERVER.distribution: {call sp_MShelp_profile (34, 4,
N'')}
Percent Complete: 0
Connecting to Publisher 'SERVER.ildm'
Initializing
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:00 AM]SERVER.ildm: 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'
Percent Complete: 1
Connecting to Publisher 'SERVER.ildm'
Connecting to Publisher 'SERVER'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:01 AM]SERVER.ildm: {call sp_MSgetversion }
[2/7/2005 11:08:01 AM]SERVER.distribution: {call sp_MShelp_subscriber_info
(N'SERVER', N'CALGSERV')}
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:01 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Percent Complete: 2
Connecting to Subscriber 'CALGSERV'
Percent Complete: 3
Retrieving publication information
Percent Complete: 4
Retrieving subscription information
Percent Complete: 4
The merge process is cleaning up meta data in database 'ildm'.
Percent Complete: 4
The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in
MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
Percent Complete: 4
Uploading data changes to the Publisher
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:03 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Connecting to Publisher 'SERVER.ildm'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:03 AM]SERVER.ildm: {call sp_MSgetversion }
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:03 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Connecting to Publisher 'SERVER.ildm'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:04 AM]SERVER.ildm: {call sp_MSgetversion }
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:04 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Connecting to Publisher 'SERVER.ildm'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:04 AM]SERVER.ildm: {call sp_MSgetversion }
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:05 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Connecting to Publisher 'SERVER.ildm'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:05 AM]SERVER.ildm: {call sp_MSgetversion }
|||What profile are you running?
Can you right click on your merge agent, select agent properties, Steps, and
then Run Agent. Click on the Edit button. Click in the Commands box, and
press CTRL A. Then Press CTRL C
Then paste the commands here and post everything here.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"pUmkInhEd" <pUmkInhEd@.discussions.microsoft.com> wrote in message
news:388D911E-2BB3-4A02-AC22-F1129C02C2BC@.microsoft.com...
> I am trying to replicate a SQL 2000 database from one branch office to
> another. The replication monitor seems to stall at Connecting to
> 'Subscriber', opening SQL profiler shows repeated sp_MSgetonerow calls. I
> have left it running for a day and a half to see if it would sort itself
out
> and it hasn't.
> Replication seemed to work fine while I was making changes at the
publisher
> and replicating them down to subscriber. As soon as subscriber made
changes,
> this happened. How do I start to troubleshoot?
> Thanks
> Brandon
Monday, March 12, 2012
Pull Subscription
I'm trying to pull new subscription from another server, but for some reason
my attemp fails. The error I get is Error 15004: Name cannot be NULL. Can
someone tell me how can I resolve this issue.
Thanks
what do you get when you issue a select @.@.servername on the publisher and
subscriber?
If you get (NULL) follow the instructions here for more information.
http://support.microsoft.com/defaul..kb;en-us;302223
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"BigRome" <BigRome@.discussions.microsoft.com> wrote in message
news:401552C3-6C3D-482F-94A8-CD57E8B164C3@.microsoft.com...
> I'm trying to pull new subscription from another server, but for some
reason
> my attemp fails. The error I get is Error 15004: Name cannot be NULL. Can
> someone tell me how can I resolve this issue.
> Thanks
my attemp fails. The error I get is Error 15004: Name cannot be NULL. Can
someone tell me how can I resolve this issue.
Thanks
what do you get when you issue a select @.@.servername on the publisher and
subscriber?
If you get (NULL) follow the instructions here for more information.
http://support.microsoft.com/defaul..kb;en-us;302223
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"BigRome" <BigRome@.discussions.microsoft.com> wrote in message
news:401552C3-6C3D-482F-94A8-CD57E8B164C3@.microsoft.com...
> I'm trying to pull new subscription from another server, but for some
reason
> my attemp fails. The error I get is Error 15004: Name cannot be NULL. Can
> someone tell me how can I resolve this issue.
> Thanks
Pull subscription
Hi,
I'm trying to create a pull subscription but it fails. I
would like to synchronize data between my laptop (remote
or in network) and our SQL Server.
I managed to create a push subscription replicating every
hour but it would be better to have a pull subscription.
I always get an error saying that the schema script could
not be propagated to the subscriber. I already spent days
to find a solution.
I tried to set the snapshot folder to default or a
(shared) networkfolder. I can connect to the
publisher/distributor (same machine).
Any help?
are you pulling using ftp?
Also can you check how your configured your subscriber? On your publisher go
to Tools, Replication, Configure Publisher, distributor, and subscribers.
Then click on your subscriber, and make sure it is impersonating the SQL
Server agent on the publisher.
Then connect with your subscriber, expand the subscription database, expand
the pull subscription folder, right click on your pull subscription, and
select security and make sure you are using the impersonate options there as
well.
now make sure your sql server agent on the subscribre runs under the same
account as the sql server agent on the publisher.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Bruno" <anonymous@.discussions.microsoft.com> wrote in message
news:aa3b01c4d6cb$09dfbee0$a601280a@.phx.gbl...
> Hi,
> I'm trying to create a pull subscription but it fails. I
> would like to synchronize data between my laptop (remote
> or in network) and our SQL Server.
> I managed to create a push subscription replicating every
> hour but it would be better to have a pull subscription.
> I always get an error saying that the schema script could
> not be propagated to the subscriber. I already spent days
> to find a solution.
> I tried to set the snapshot folder to default or a
> (shared) networkfolder. I can connect to the
> publisher/distributor (same machine).
> Any help?
>
I'm trying to create a pull subscription but it fails. I
would like to synchronize data between my laptop (remote
or in network) and our SQL Server.
I managed to create a push subscription replicating every
hour but it would be better to have a pull subscription.
I always get an error saying that the schema script could
not be propagated to the subscriber. I already spent days
to find a solution.
I tried to set the snapshot folder to default or a
(shared) networkfolder. I can connect to the
publisher/distributor (same machine).
Any help?
are you pulling using ftp?
Also can you check how your configured your subscriber? On your publisher go
to Tools, Replication, Configure Publisher, distributor, and subscribers.
Then click on your subscriber, and make sure it is impersonating the SQL
Server agent on the publisher.
Then connect with your subscriber, expand the subscription database, expand
the pull subscription folder, right click on your pull subscription, and
select security and make sure you are using the impersonate options there as
well.
now make sure your sql server agent on the subscribre runs under the same
account as the sql server agent on the publisher.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Bruno" <anonymous@.discussions.microsoft.com> wrote in message
news:aa3b01c4d6cb$09dfbee0$a601280a@.phx.gbl...
> Hi,
> I'm trying to create a pull subscription but it fails. I
> would like to synchronize data between my laptop (remote
> or in network) and our SQL Server.
> I managed to create a push subscription replicating every
> hour but it would be better to have a pull subscription.
> I always get an error saying that the schema script could
> not be propagated to the subscriber. I already spent days
> to find a solution.
> I tried to set the snapshot folder to default or a
> (shared) networkfolder. I can connect to the
> publisher/distributor (same machine).
> Any help?
>
Subscribe to:
Posts (Atom)