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

Pulling Data from more than one UNLINKED table

I am new to this so hopefully you'll bare with me if this is a stupid
question.
I receive data in a dbf format from the state office on a yearly basis. I
have figured out how to use SSIS to pull these into the SQL system as
different tables (e. Birth2002, Birth2003, Birth2004) within a single DB.
The state has a habit of changing the fields names from year to year, and
what I would like to do is build standard queries that compares one year to
the next. Since I cannot link this data (it is prenatal birth data) on any
field because there is no like field from one year to the next, what would a
query look like, say, to compare birth_hospital in 2002, 2003, and 2004.
I have three separate Select statements that pulls and groups the data, but
they are feeding into separate panes (one for each year). How do I get them
in one pane side-by-side?
TIA
Any help is appreciated.
SELECT birth_date, birth_hospital
FROM Birth2002
UNION
SELECT BirthDate, BirthHospital
FROM Birth2003
UNION
SELECT Bdte, BHosp
FROM Birth2004
"JC HARRIS" wrote:

> I am new to this so hopefully you'll bare with me if this is a stupid
> question.
> I receive data in a dbf format from the state office on a yearly basis. I
> have figured out how to use SSIS to pull these into the SQL system as
> different tables (e. Birth2002, Birth2003, Birth2004) within a single DB.
> The state has a habit of changing the fields names from year to year, and
> what I would like to do is build standard queries that compares one year to
> the next. Since I cannot link this data (it is prenatal birth data) on any
> field because there is no like field from one year to the next, what would a
> query look like, say, to compare birth_hospital in 2002, 2003, and 2004.
> I have three separate Select statements that pulls and groups the data, but
> they are feeding into separate panes (one for each year). How do I get them
> in one pane side-by-side?
> TIA
> Any help is appreciated.
>
>

Pulling Data from more than one UNLINKED table

I am new to this so hopefully you'll bare with me if this is a stupid
question.
I receive data in a dbf format from the state office on a yearly basis. I
have figured out how to use SSIS to pull these into the SQL system as
different tables (e. Birth2002, Birth2003, Birth2004) within a single DB.
The state has a habit of changing the fields names from year to year, and
what I would like to do is build standard queries that compares one year to
the next. Since I cannot link this data (it is prenatal birth data) on any
field because there is no like field from one year to the next, what would a
query look like, say, to compare birth_hospital in 2002, 2003, and 2004.
I have three separate Select statements that pulls and groups the data, but
they are feeding into separate panes (one for each year). How do I get them
in one pane side-by-side?
TIA
Any help is appreciated.SELECT birth_date, birth_hospital
FROM Birth2002
UNION
SELECT BirthDate, BirthHospital
FROM Birth2003
UNION
SELECT Bdte, BHosp
FROM Birth2004
"JC HARRIS" wrote:

> I am new to this so hopefully you'll bare with me if this is a stupid
> question.
> I receive data in a dbf format from the state office on a yearly basis. I
> have figured out how to use SSIS to pull these into the SQL system as
> different tables (e. Birth2002, Birth2003, Birth2004) within a single DB.
> The state has a habit of changing the fields names from year to year, and
> what I would like to do is build standard queries that compares one year t
o
> the next. Since I cannot link this data (it is prenatal birth data) on any
> field because there is no like field from one year to the next, what would
a
> query look like, say, to compare birth_hospital in 2002, 2003, and 2004.
> I have three separate Select statements that pulls and groups the data, bu
t
> they are feeding into separate panes (one for each year). How do I get the
m
> in one pane side-by-side?
> TIA
> Any help is appreciated.
>
>

Pulling Data from more than one UNLINKED table

I am new to this so hopefully you'll bare with me if this is a stupid
question.
I receive data in a dbf format from the state office on a yearly basis. I
have figured out how to use SSIS to pull these into the SQL system as
different tables (e. Birth2002, Birth2003, Birth2004) within a single DB.
The state has a habit of changing the fields names from year to year, and
what I would like to do is build standard queries that compares one year to
the next. Since I cannot link this data (it is prenatal birth data) on any
field because there is no like field from one year to the next, what would a
query look like, say, to compare birth_hospital in 2002, 2003, and 2004.
I have three separate Select statements that pulls and groups the data, but
they are feeding into separate panes (one for each year). How do I get them
in one pane side-by-side?
TIA
Any help is appreciated.SELECT birth_date, birth_hospital
FROM Birth2002
UNION
SELECT BirthDate, BirthHospital
FROM Birth2003
UNION
SELECT Bdte, BHosp
FROM Birth2004
"JC HARRIS" wrote:
> I am new to this so hopefully you'll bare with me if this is a stupid
> question.
> I receive data in a dbf format from the state office on a yearly basis. I
> have figured out how to use SSIS to pull these into the SQL system as
> different tables (e. Birth2002, Birth2003, Birth2004) within a single DB.
> The state has a habit of changing the fields names from year to year, and
> what I would like to do is build standard queries that compares one year to
> the next. Since I cannot link this data (it is prenatal birth data) on any
> field because there is no like field from one year to the next, what would a
> query look like, say, to compare birth_hospital in 2002, 2003, and 2004.
> I have three separate Select statements that pulls and groups the data, but
> they are feeding into separate panes (one for each year). How do I get them
> in one pane side-by-side?
> TIA
> Any help is appreciated.
>
>

Publishing OLAP based Pivotables from Excel

Hi

I want to publish an OLAP based pivottables from Excel to a HTML file using Office web componets.

I have a server with SQL Analysis Services 2005 cubes and I can browse the cube from my desktop in Excel 2003 Pivottables.

When I publish the Pivottable with Pivottable interactivity I get an error in the browser:

The query could not be processed

- An error was encountered in the transport layer

- The peer prematurely closed the connection

In the Pivottable I have set 'Save password' in properties.

Any suggestions ?

Thomas Black

I'm having the same error msg and problem to access SSAS 2005 cube over http at client machine.

This doesn't happen if you work in server (with Sql Server 2005 and web server installed).

|||Hello

did you manage to get around this error? I get this when I try to display a pivottable for data on a remote (but in LAN) server, connect goes through ...

Thanks for any help

Regards
Klaus Wiesel
|||

I used this link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=38be67a5-2056-46a1-84b1-337ffb549c5c&DisplayLang=en

That enables Pivottables and charts in Sharepoint. Very easy to use. Useful for designing dashboards.

Regard

Thomas

|||

I also encountered this problem ,need any solutions!

thanks a lot!

|||

Hello

I solved this for me.

The following is done by my app:

1) Connect to the server to access the existing AS databases and their cubes

2) make a connectionstring that is later on passed to the pivottable control (named opData here):

.opData.ConnectionString = sConnString
.opData.DataMember = .coCUBE_NAME
When building the sConnString you have to use a named user (in the format domain\username) with his password to be able to display the data of the remote machine

Regards

Klaus

Publishing OLAP based Pivotables from Excel

Hi

I want to publish an OLAP based pivottables from Excel to a HTML file using Office web componets.

I have a server with SQL Analysis Services 2005 cubes and I can browse the cube from my desktop in Excel 2003 Pivottables.

When I publish the Pivottable with Pivottable interactivity I get an error in the browser:

The query could not be processed

- An error was encountered in the transport layer

- The peer prematurely closed the connection

In the Pivottable I have set 'Save password' in properties.

Any suggestions ?

Thomas Black

I'm having the same error msg and problem to access SSAS 2005 cube over http at client machine.

This doesn't happen if you work in server (with Sql Server 2005 and web server installed).

|||Hello

did you manage to get around this error? I get this when I try to display a pivottable for data on a remote (but in LAN) server, connect goes through ...

Thanks for any help

Regards
Klaus Wiesel
|||

I used this link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=38be67a5-2056-46a1-84b1-337ffb549c5c&DisplayLang=en

That enables Pivottables and charts in Sharepoint. Very easy to use. Useful for designing dashboards.

Regard

Thomas

|||

I also encountered this problem ,need any solutions!

thanks a lot!

|||

Hello

I solved this for me.

The following is done by my app:

1) Connect to the server to access the existing AS databases and their cubes

2) make a connectionstring that is later on passed to the pivottable control (named opData here):

.opData.ConnectionString = sConnString
.opData.DataMember = .coCUBE_NAME
When building the sConnString you have to use a named user (in the format domain\username) with his password to be able to display the data of the remote machine

Regards

Klaus

Publisher/Distributor

Hi,
I would like to hear opinion from experts.
Assume I have 3 warehouses and 1 Head office. I want to build a
centralized Subscriber. should i use
"3 Publishers/3 Distributors and 1 Subscriber" -- each sever will have 1 P
and 1 D
or
"3 Publishers and 1 Distributor and 1 Subscriber"
or
both way will work well...
Thanks
Ed
I would use 3 publishers and 3 local distributors (on the same box as the
three publishers).
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
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:3A2A37F3-7254-4985-9B5F-A5A5C000F980@.microsoft.com...
> Hi,
> I would like to hear opinion from experts.
> Assume I have 3 warehouses and 1 Head office. I want to build a
> centralized Subscriber. should i use
> "3 Publishers/3 Distributors and 1 Subscriber" -- each sever will have 1
P
> and 1 D
> or
> "3 Publishers and 1 Distributor and 1 Subscriber"
> or
> both way will work well...
> Thanks
> Ed

Publish scenario - how can this work?

Our current setup has a hand held device, a laptop and head office. The
field reps have the hand held and laptop and need to get and send data back
to head office. Merge Replication seems to be the answer and I was thinking
that the Head Office would publish to the Laptop and the Laptop would then
publish to the Hand Held. But SQL Server 2005 Express can only be a
Subscriber and not a Publisher.
The reason to do this is that only certain rows will be published to the
laptop (say 1 weeks work) and then only certain rows (say 1 or 2 days work)
will be published to the hand held.
Is there a way to accomplish this?
Here's how it breaks down:
Head Office = SQL Server 2005 Standard Edition
Laptop = SQL Server 2005 Express Edition
Hand Held = SQL Server 2005 Compact Edition
Thanks,
Richard.
You might want to look at RDA as the transit mechanism between Express and
the HandHelds.
Otherwise I would replicate from the Standard Edition publisher to the
handhelds.
http://www.zetainteractive.com - Shift Happens!
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
"Richard Wodabek" <rwodabek@.cogeco.ca> wrote in message
news:uocMysrNIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Our current setup has a hand held device, a laptop and head office. The
> field reps have the hand held and laptop and need to get and send data
> back
> to head office. Merge Replication seems to be the answer and I was
> thinking
> that the Head Office would publish to the Laptop and the Laptop would then
> publish to the Hand Held. But SQL Server 2005 Express can only be a
> Subscriber and not a Publisher.
> The reason to do this is that only certain rows will be published to the
> laptop (say 1 weeks work) and then only certain rows (say 1 or 2 days
> work)
> will be published to the hand held.
> Is there a way to accomplish this?
> Here's how it breaks down:
> Head Office = SQL Server 2005 Standard Edition
> Laptop = SQL Server 2005 Express Edition
> Hand Held = SQL Server 2005 Compact Edition
> Thanks,
> Richard.
>
|||Thanks Hilary, RDA might work for us.
Any chance 2008 Express Edition will allow Publishing?
Richard.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uNmcUmzNIHA.292@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
> You might want to look at RDA as the transit mechanism between Express and
> the HandHelds.
> Otherwise I would replicate from the Standard Edition publisher to the
> handhelds.
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "Richard Wodabek" <rwodabek@.cogeco.ca> wrote in message
> news:uocMysrNIHA.2268@.TK2MSFTNGP02.phx.gbl...
then
>

PTS sp4 causes Distinct Count error

I have Analyisis Sevices service pack 3a. Office xp has been installed on the client machines and the server, which has installed PTS service pack 4. This has resulted in one of the calculated members showing as #1NF in Proclarity. It works perfectly well from client machines still running with PTS service pack 3. The calculated member in question uses a distinct count which is now always returning 0.

Does anyone have any suggestions? Thanks.

If the cube where it happens is a virtual cube - then it is a known regression in SP4 w.r.t. DistinctCount MDX function. There is a hotfix avaialable - please contact product support in order to obtain it.|||

Hello,

Yes it is a virtual cube. Is the hotfix for Analysis services sp4? The server is still on Analysis services sp3a but PTS got upgraded when office xp was installed.

Thanks for your help.

|||Yes, the hotfix is on top of SP4 and should be applied to PTS.

PTS sp4 causes Distinct Count error

I have Analyisis Sevices service pack 3a. Office xp has been installed on the client machines and the server, which has installed PTS service pack 4. This has resulted in one of the calculated members showing as #1NF in Proclarity. It works perfectly well from client machines still running with PTS service pack 3. The calculated member in question uses a distinct count which is now always returning 0.

Does anyone have any suggestions? Thanks.

If the cube where it happens is a virtual cube - then it is a known regression in SP4 w.r.t. DistinctCount MDX function. There is a hotfix avaialable - please contact product support in order to obtain it.|||

Hello,

Yes it is a virtual cube. Is the hotfix for Analysis services sp4? The server is still on Analysis services sp3a but PTS got upgraded when office xp was installed.

Thanks for your help.

|||Yes, the hotfix is on top of SP4 and should be applied to PTS.