New subscription wizard never completes or even errors (SQL 2005 Trans repl with Queued Up

I am testing replication (Transactional with Queued Updating) between SQL
Server 2005 Developer Edition (which is on a domain) and SQL Express (which
is on a machine on same LAN but in a workgroup). I have configured the
publisher as its own distributor and created a publication consisting of 2
views (1 indexed, 1 regular). My problem is that when I run the New
Subscription Wizard, after clicking Finish the process hangs forever on the
first step ("Creating Subscription For ....."). No errors appear, nothing.
Clicking Stop has no effect.
Both views are tiny, only returning half a dozen rows. I have also tried
with a publication containing only one small table, and the same thing
occurs.
The publisher is clearly able to connect to the subscriber because the
linked server is created on the subscriber successfully. There is no sign of
a subscription however on the subscriber.
Here is the configuration for the subscription (following steps in New
Subscription Wizard):
Push subscription
Subscription database is new blank database
Distribution Agent Security: Run under SQL Agent service account, Connects
to distributor by impersonation, Connects to subscriber using a SysAdmin SQL
login
Schedule: runs continuously
Queued updating
Create new linked server using same SysAdmin SQL login as above
Initialize subscription immediately
What could be wrong?
Can you script out your publication creation steps and then run it manually
through Query Analyzer or the SSMS and see where it hangs. This should give
you some indication of where the problem is.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:eyE7utcAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I am testing replication (Transactional with Queued Updating) between SQL
>Server 2005 Developer Edition (which is on a domain) and SQL Express (which
>is on a machine on same LAN but in a workgroup). I have configured the
>publisher as its own distributor and created a publication consisting of 2
>views (1 indexed, 1 regular). My problem is that when I run the New
>Subscription Wizard, after clicking Finish the process hangs forever on the
>first step ("Creating Subscription For ....."). No errors appear, nothing.
>Clicking Stop has no effect.
> Both views are tiny, only returning half a dozen rows. I have also tried
> with a publication containing only one small table, and the same thing
> occurs.
> The publisher is clearly able to connect to the subscriber because the
> linked server is created on the subscriber successfully. There is no sign
> of a subscription however on the subscriber.
> Here is the configuration for the subscription (following steps in New
> Subscription Wizard):
> Push subscription
> Subscription database is new blank database
> Distribution Agent Security: Run under SQL Agent service account, Connects
> to distributor by impersonation, Connects to subscriber using a SysAdmin
> SQL login
> Schedule: runs continuously
> Queued updating
> Create new linked server using same SysAdmin SQL login as above
> Initialize subscription immediately
> What could be wrong?
>
|||The step that hangs is the last step - the execution of sp_link_publication
on the subscriber:
exec sp_link_publication @.publisher = N'LAURENCEN\SQL2005', @.publisher_db =
N'RMIS_QA', @.publication = N'test views', @.distributor =
N'LAURENCEN\SQL2005', @.security_mode = 0, @.login = N'xxxxx', @.password =
N'xxxx'
In the Application Event log the following error appears:
MS DTC is unable to communicate with MS DTC on a remote system. No common
RPC protocol is supported between the two systems. Please ensure that one
or more of the following RPC protocols are common to both systems: TCP/IP,
SPX, or NetBEUI. Error Specifics: .\iomgrclt.cpp:204, Pid: 904, CmdLine:
C:\WINNT\System32\msdtc.exe
I have checked that the MSDTC service is running on publisher and
subscriber. The publisher server is Windows XP SP2 and from googling it
looks like DTC is not given network access by default, however I have
enabled the required settings and the error persists:
In Admin tools.Component Services or via Component Services MSC Snap-in
- right-click the appropriate computer node and select properties
- on the MSDTC tab click Security Configuration button
- in addition to the Network DTC Access being ticked, tick Allow Remote
Clients, Allow Inbound, Allow Outbound
- restart MSDTC
The subscriber server is Windows 2000 Workstation, and it doesn't look like
there are any DTC settings to configure.
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:eyE7utcAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I am testing replication (Transactional with Queued Updating) between SQL
>Server 2005 Developer Edition (which is on a domain) and SQL Express (which
>is on a machine on same LAN but in a workgroup). I have configured the
>publisher as its own distributor and created a publication consisting of 2
>views (1 indexed, 1 regular). My problem is that when I run the New
>Subscription Wizard, after clicking Finish the process hangs forever on the
>first step ("Creating Subscription For ....."). No errors appear, nothing.
>Clicking Stop has no effect.
> Both views are tiny, only returning half a dozen rows. I have also tried
> with a publication containing only one small table, and the same thing
> occurs.
> The publisher is clearly able to connect to the subscriber because the
> linked server is created on the subscriber successfully. There is no sign
> of a subscription however on the subscriber.
> Here is the configuration for the subscription (following steps in New
> Subscription Wizard):
> Push subscription
> Subscription database is new blank database
> Distribution Agent Security: Run under SQL Agent service account, Connects
> to distributor by impersonation, Connects to subscriber using a SysAdmin
> SQL login
> Schedule: runs continuously
> Queued updating
> Create new linked server using same SysAdmin SQL login as above
> Initialize subscription immediately
> What could be wrong?
>
|||Is this an upgrade from SQL 2000? There are some issues with a remote
distributor in an upgrade. Basically the repl_distributor remote server is
modified to a linked server and there have been cases where you have to
update the passwords to get this to work. AFAIK - MSDTC is not involved in
queued replication.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:O%237HO7oAHHA.5068@.TK2MSFTNGP02.phx.gbl...
> The step that hangs is the last step - the execution of
> sp_link_publication on the subscriber:
> exec sp_link_publication @.publisher = N'LAURENCEN\SQL2005', @.publisher_db
> = N'RMIS_QA', @.publication = N'test views', @.distributor =
> N'LAURENCEN\SQL2005', @.security_mode = 0, @.login = N'xxxxx', @.password =
> N'xxxx'
> In the Application Event log the following error appears:
> MS DTC is unable to communicate with MS DTC on a remote system. No common
> RPC protocol is supported between the two systems. Please ensure that one
> or more of the following RPC protocols are common to both systems:
> TCP/IP, SPX, or NetBEUI. Error Specifics: .\iomgrclt.cpp:204, Pid: 904,
> CmdLine: C:\WINNT\System32\msdtc.exe
> I have checked that the MSDTC service is running on publisher and
> subscriber. The publisher server is Windows XP SP2 and from googling it
> looks like DTC is not given network access by default, however I have
> enabled the required settings and the error persists:
> In Admin tools.Component Services or via Component Services MSC Snap-in
> - right-click the appropriate computer node and select properties
> - on the MSDTC tab click Security Configuration button
> - in addition to the Network DTC Access being ticked, tick Allow Remote
> Clients, Allow Inbound, Allow Outbound
> - restart MSDTC
> The subscriber server is Windows 2000 Workstation, and it doesn't look
> like there are any DTC settings to configure.
>
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:eyE7utcAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>
|||I figured it out after a long search.
MSDTC is involved, at least to set up the subscription. MSDTC in Windows XP
(and Win 2003 also I think) has some network access settings that are set to
prevent any access from outside by default. I mentioned these before.
However, in addition to setting these settings to allow network access,
there is a security setting for RPC, which I missed. This is the registry
key:
HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Win dows
NT\RPC\RestrictRemoteClients
This needs to exist and have a value of 0 (DWORD) if the subscriber is
Windows 2000.
For more info read:
http://blogs.msdn.com/florinlazar/archive/2004/06/18/159127.aspx
http://www.microsoft.com/technet/prodtechnol/winxppro/maintain/sp2netwk.mspx#XSLTsection128121120120
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23tmOUdqAHHA.3536@.TK2MSFTNGP03.phx.gbl...
> Is this an upgrade from SQL 2000? There are some issues with a remote
> distributor in an upgrade. Basically the repl_distributor remote server is
> modified to a linked server and there have been cases where you have to
> update the passwords to get this to work. AFAIK - MSDTC is not involved in
> queued replication.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:O%237HO7oAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>

New kind of merge repl error floating point exception?

Got a weird one. Hope someone has seen it before. Need some smarts.
Rolled out 150 new laptops, all imaged the same for users with a local msde
sp3a. All installed with subscriptionDB.
Previously, all were synching well on the same Sql 2000 pub/distr, on the
same publications for 18 months.
50% now work just fine, the other half get the message below. I tried
reinits, reinstall and sp4 upgrade (per several bug doc, found 3 close).
Varied results, some success but not consistant. Temporary success with
failures occationally but never clean.
Dies on sp_MSenumchanges....
The process could not enumerate changes at the 'Subscriber'.
(Source: Merge Replication Provider (Agent); Error number: -2147200999)
------
A floating point exception occurred in the user process. Current transaction
is canceled.
(Source: Servername_removed (Data source); Error number: 3628)
------
The process was successfully stopped.
Any experiences with this, ideas of causes are appreciated.
Mike
If data falls in the woods and nobody is there to see it ...... ?
You could try running service pack 3a again on the subscribers although
it doesn't make logical sense why anything should be different. I know
of a similiar error if the clients are use Windows 95/98/ME. Try
running dbcc checkdb just in case there is some corruption in any of
the tables.
|||Thanks for the suggestions. Good ideas.
I tried reinstalling sp3 and a new subscriptionDB too.
Dumped the subscription and recreated it with the wizard too.
dbcc checkdb ran clean but I haven't caught one with a failure in action
yet. Will keep at it.
The randomness makes me wonder if the subscription proc timesout somehow
before the server can return info. The subs history shows it processes
uploads to some random article and then fails.
Mike
If data falls in the woods and nobody is there to see it ...... ?
"seanbell68@.gmail.com" wrote:

> You could try running service pack 3a again on the subscribers although
> it doesn't make logical sense why anything should be different. I know
> of a similiar error if the clients are use Windows 95/98/ME. Try
> running dbcc checkdb just in case there is some corruption in any of
> the tables.
>
|||any commonalities on the machines that do not work? also take in
consideration isp/routers/vpn/encryption etc?
"Tigermikefl" <Tigermikefl@.discussions.microsoft.com> wrote in message
news:0900566A-3A1B-44F4-8118-4427B4FBAD3B@.microsoft.com...[vbcol=seagreen]
> Thanks for the suggestions. Good ideas.
> I tried reinstalling sp3 and a new subscriptionDB too.
> Dumped the subscription and recreated it with the wizard too.
> dbcc checkdb ran clean but I haven't caught one with a failure in action
> yet. Will keep at it.
> The randomness makes me wonder if the subscription proc timesout somehow
> before the server can return info. The subs history shows it processes
> uploads to some random article and then fails.
> --
> Mike
> If data falls in the woods and nobody is there to see it ...... ?
>
> "seanbell68@.gmail.com" wrote:
|||Unfortunately no commonalities any found yet. All laptops in the same
shipment, imaged with the same WinXP. All received a new vpn client but half
are synching fine. Soho offices mostly, isp's vary.
I will poll the challenged as to ISP/Wireless/Routers etc to see if
something jumps out.
We are leaning towards a connection problem but can't figure how they all
get the same error and doesn't go into the retry mode via sql agent.
Mike
If data falls in the woods and nobody is there to see it ...... ?
"S c o t t K r a m e r" wrote:

> any commonalities on the machines that do not work? also take in
> consideration isp/routers/vpn/encryption etc?
>
> "Tigermikefl" <Tigermikefl@.discussions.microsoft.com> wrote in message
> news:0900566A-3A1B-44F4-8118-4427B4FBAD3B@.microsoft.com...
>
>
|||Turned out to be a problem with the newer cisco vpn client 4.6. Runs
errorless with earier client. Checking networking but no longer a repl issue.
Thanks for the input anywho.
Mike
If data falls in the woods and nobody is there to see it ...... ?
"Tigermikefl" wrote:
[vbcol=seagreen]
> Unfortunately no commonalities any found yet. All laptops in the same
> shipment, imaged with the same WinXP. All received a new vpn client but half
> are synching fine. Soho offices mostly, isp's vary.
> I will poll the challenged as to ISP/Wireless/Routers etc to see if
> something jumps out.
> We are leaning towards a connection problem but can't figure how they all
> get the same error and doesn't go into the retry mode via sql agent.
>
> --
> Mike
> If data falls in the woods and nobody is there to see it ...... ?
>
> "S c o t t K r a m e r" wrote: