I configure a brand new distribution configuration for a replication today. At the last step, I get an error on complaining the setup of SQL Server Agent automatic startup.
I think this is due to the fact that I have disabled SQL Server agent to start as the default. So I start the SQL Server Agent. But, now, I cannot re-configure the distribution anymore. The distributor properties do not help. So I decide to delete the distribution configuration and start a new one.
How to do it?
Exec sp_dropdistributor @no_checks = 1
or
Exec sp_removedbreplication 'PublicationName','both'
If you already have publishers and subscribers associated with this distributor, see Hilary Cotter's post on
http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/7c12242d-cc1f-4f23-92e1-b8cffd55a59e/
Well, I still get the same error message on Access is denied.
Solution:
Use SQL Server Configuration Manager to configure the SQL Server Agent start automatically. Then in the distribution configuration process, the option to set up an auto/manual startup of the SQL Server does not appear anymore.
Note: Open SQL Server explicitly as an admin does not work. Re-starting SQL Server and SQL Server Agent does not help either.
