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 sql login using active directory

I have a sql admin who is not a domain admin and when he selects a new login for sql 2000 using the domain lookup it tells him he has no permission and can't see any user in active directory. He does have active directory users and computers on his desktop and can see them all there. Any suggestions' He is a local admin on the sql server box and can see local users.I would think you can't enumerate users and groups in
Active Directory unless you are given specific rights. I
know this is true for Open LDAP and IBM SecureWay bases
directory services. Ask him to enter NET USERS /DOMAIN
command on DOS command prompt and see if it lists all
users in the Domain. I would suggest you to post this
question to windows 2000 group, or ask your network admin.
This isn't a sql issue, if he is local admin then more
likely he has DBA privileges in server.
>--Original Message--
>I have a sql admin who is not a domain admin and when he
selects a new login for sql 2000 using the domain lookup
it tells him he has no permission and can't see any user
in active directory. He does have active directory users
and computers on his desktop and can see them all there.
Any suggestions' He is a local admin on the sql
server box and can see local users.
>.
>

New server name

I am tasked with bringing my Windows 2000 server which
runs SQL 7.0 into another domain as a member server. I
will have to change the name of my server also. Does this
affect SQL? I know the Services Manager refers to the
file server name and was wondering, after I change the
name, will SQL see this, or will I have to manually change
some things in SQL?Hi,
SQL 7.0 will not start after changing the Server name. You need to insert
the SQL Server CD and run setup.
This just updates the registry with the new server name and will not change
any databases.
After this start SQL server and login using Query Analyzer and execute below
commands,
sp_dropserver <old server name>
go
sp_addserver <newserver name>,local
Thanks
Hari
MCDBA
"Rick" <anonymous@.discussions.microsoft.com> wrote in message
news:0e6101c3a222$ddbface0$a501280a@.phx.gbl...
> I am tasked with bringing my Windows 2000 server which
> runs SQL 7.0 into another domain as a member server. I
> will have to change the name of my server also. Does this
> affect SQL? I know the Services Manager refers to the
> file server name and was wondering, after I change the
> name, will SQL see this, or will I have to manually change
> some things in SQL?|||SQL should recognize the new server name when you reboot or start and
stop sql services. I believe you should correct the sysservers system
table by manually running this...
sp_dropserver old_name
GO
sp_addserver new_name, local
GO
Billy|||For 7.0, you need to run setup as well.
--
Tibor Karaszi
"Billy" <bwood@.gibson.com> wrote in message
news:182d2236.0311031226.2ddcda98@.posting.google.com...
> SQL should recognize the new server name when you reboot or start and
> stop sql services. I believe you should correct the sysservers system
> table by manually running this...
> sp_dropserver old_name
> GO
> sp_addserver new_name, local
> GO
> Billy|||Thanks for the solid info Hari. Appreciate it.
>--Original Message--
>Hi,
>SQL 7.0 will not start after changing the Server name.
You need to insert
>the SQL Server CD and run setup.
>This just updates the registry with the new server name
and will not change
>any databases.
>After this start SQL server and login using Query
Analyzer and execute below
>commands,
>sp_dropserver <old server name>
>go
>sp_addserver <newserver name>,local
>Thanks
>Hari
>MCDBA
>
>
>"Rick" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0e6101c3a222$ddbface0$a501280a@.phx.gbl...
>> I am tasked with bringing my Windows 2000 server which
>> runs SQL 7.0 into another domain as a member server. I
>> will have to change the name of my server also. Does
this
>> affect SQL? I know the Services Manager refers to the
>> file server name and was wondering, after I change the
>> name, will SQL see this, or will I have to manually
change
>> some things in SQL?
>
>.
>

New Install Error

Hi,
I'm installing Reporting Services on our productions
server which is a domain controller, Windows 2003.
The database is on a different server, so it would be a
remote install of the databases. I keep getting error
messages that say 'can not create database'. I'm using sa
as a sql login, which has administrative rights.
Please let me know if you need anymore information.
Thanks in Advance,
MarciaBy default, setup uses the credentials of the user running setup to create
the report server database. The screen where you configure the report
server database only allows you to specify credentials the report server
uses to access the database (after setup is over).
To use a SQL login to create the report server database, you need to specify
RSSETUPACCOUNT and RSSETUPPASSWORD on the command line when running setup.
More info here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsinstall/htm/gs_installingrs_v1_6h6h.asp
-Lukasz
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Marcia" <anonymous@.discussions.microsoft.com> wrote in message
news:461701c47332$7e57f870$a501280a@.phx.gbl...
> Hi,
> I'm installing Reporting Services on our productions
> server which is a domain controller, Windows 2003.
> The database is on a different server, so it would be a
> remote install of the databases. I keep getting error
> messages that say 'can not create database'. I'm using sa
> as a sql login, which has administrative rights.
> Please let me know if you need anymore information.
> Thanks in Advance,
> Marcia

New Install - [WildPacket]

I am new with SQL 2000. Last night I installed SQL 2000 and attached a
database which went well. Whiel doing all this I was logged in as the domain
administrator.
If I log in as the Administrator adn open the SQL Enterprise Manager I see
all the DBs but when I logon with my account which also has admin rigths and
I open the SQL Enterprise Manager I see no DBs no nothing ... '
How can I see all with my person ADM account?
Advise Please.
Thank you in advance.Hi,
I am afraid, that you must register your local server with Enterprise Manager.
Next when installing SQL Server 2000, you must have chosen Domain Account\
User.
This will prevent access to other users from accessing SQL Server.
Try to login back as an Administrator and under Security Add your ADM
username under the list of users.
And make sure that under Database Access, you choose the database that the
user needs access.
Things should work fine ;)
Hope this helps :)
--
Best Regards
Anil Mahadev
http://anilm001.myfreewebs.net/index2.php
http://www.db2india.org
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200609/1

New domain name - problems connecting

I installed SQL Server 2005 yesterday on a freshly installed Windows 2003 R2
standard server. I installed with mixed-mode authentication and was able to
connect to the database.
The server was then changed to a PDC with a new forest and domain. I am now
unable to connect. What changes do I need to make to Sql Server to get back
up and running.
tia,
Bob M..Refer:
http://support.microsoft.com/kb/910228/en-us#E0WR0ABAAA
Do not change the role of the server after you install SQL Server 2005. For
example, if you install SQL Server 2005 on a member server, do not use the
Dcpromo tool to promote the server to a domain controller. Or, if you
install SQL Server 2005 on a domain controller, do not use Dcpromo to
demote the server to a member server. Changing the role of a server after
you install SQL Server 2005 can result in loss of functionality and is not
supported.
=======
--
>From: "Bob Murdoch" <ram_re_move_5@.erols.com>
>Subject: New domain name - problems connecting
>Date: Thu, 4 May 2006 12:44:16 -0400

>I installed SQL Server 2005 yesterday on a freshly installed Windows 2003
R2
>standard server. I installed with mixed-mode authentication and was able
to
>connect to the database.
>The server was then changed to a PDC with a new forest and domain. I am
now
>unable to connect. What changes do I need to make to Sql Server to get
back
>up and running.
>tia,
>Bob M..
>
>

New domain

I have a client that has been running 2 separate domains (same subnet). An
NT4 domain and a W2K3 domain. All clients have been added to the W2k3
domain. The NT domain now consists of a PDC, a BDC and 2 SQL 2000 servers
running W2k. My plan is to simply downlevel (remove from NT domain add to
workgroup) 2 SQL servers and then add them to to W2K3 domain. This would
render PDC and BDC with out use and we could do whatever with them. I am
wondering if this will cause any issues with SQL servers that I am not
forseeing. Insights very much appreciated.I cannot see any problems switching domain belonging as long as you dont
rename the SQL servers hostnames. You might also want to keep an eye on
which account is running the SQL services.
----
---
Johan Engdahl
CCSA, CCSE, CCA, MCP | johan AT firewall1 DOT nu | http://www.firewall1.nu
"Laurie" <Laurie@.discussions.microsoft.com> wrote in message
news:5E47BEEF-9BBD-43F6-B99F-BEAAEA4643A3@.microsoft.com...
>I have a client that has been running 2 separate domains (same subnet). An
> NT4 domain and a W2K3 domain. All clients have been added to the W2k3
> domain. The NT domain now consists of a PDC, a BDC and 2 SQL 2000 servers
> running W2k. My plan is to simply downlevel (remove from NT domain add to
> workgroup) 2 SQL servers and then add them to to W2K3 domain. This would
> render PDC and BDC with out use and we could do whatever with them. I am
> wondering if this will cause any issues with SQL servers that I am not
> forseeing. Insights very much appreciated.
>