Microsoft KB Archive/153780

{|
 * width="100%"|

-

The information in this article applies to:


 * Microsoft SQL Server version 6.5

-

BUG# NT: 15184 (6.5)

SYMPTOMS
The sp_droppublisher stored procedure, used to drop a publication server, does not properly clear the 'pub' server option from sysservers. This will prevent users from properly disabling a publication server on the screen "Enable Subscribing from these Servers."

WORKAROUND
In the master database of any server participating in replication, please replace the sp_droppublisher procedure by running the following script:

use master go if exists (select * from sysobjects where sysstat & 0xf = 4 and name = 'sp_droppublisher') drop procedure sp_droppublisher go create procedure sp_droppublisher (  @publisher varchar (30),        /* publisher server name */    @type varchar (5) = null     /* null or 'dist' */         ) as    declare @distaccount varchar(127) declare @proc varchar (255) declare @retcode int declare @privilege varchar (30)

/*   ** parameter check:  @publisher. ** check to make sure that the publisher exists, that the name isn't   ** null, and that the name conforms to the rules for identifiers. */

if @publisher is null begin raiserror (14043, 16, -1, 'the publisher') return (1) end

execute @retcode = sp_validname @publisher

if @retcode <> 0 return (1)

/*   ** perform special logic if dropping a publisher for a distribution ** server. */    if lower(@type) = 'dist' begin if not exists (select *     from master..sysservers                where srvname = @publisher                and srvstatus & 16 <> 0) begin raiserror (14080, 11, -1) return (1) end

execute @retcode = sp_serveroption @publisher, 'dpub', false if @@error <> 0 or @retcode <> 0 return (1)

if exists (select * from master..sysremotelogins         where remoteserverid = (select srvid from master..sysservers where srvname = @publisher)         and remoteusername = 'sa'          and suid = 1)    /* 'sa' */ begin execute @retcode = sp_dropremotelogin @publisher, sa, sa         if @@error <> 0 or @retcode <> 0 return (1) end

if exists (select * from master..sysremotelogins         where remoteserverid = (select srvid from master..sysservers where srvname = @publisher)         and remoteusername = 'probe'          and suid = 10)   /* 'probe' */ begin execute @retcode = sp_dropremotelogin @publisher, probe, probe if @@error <> 0 or @retcode <> 0 return (1) end

return (0) end

/*   ** make sure the server is defined as a 'publisher'. */    if not exists (select *                     from master..sysservers                    where srvname = @publisher                      and srvstatus & 2 <> 0) begin raiserror (14080, 11, -1) return (1) end

/*   ** turn off the server option to indicate that this is a publisher. */    execute @retcode = sp_serveroption @publisher, 'pub', false if @@error <> 0 or @retcode <> 0 return (1)

/*   ** fetch the publisher's distributor account. */

select @proc = rtrim(@publisher) + '.master..sp_helpdistributor ' exec @retcode = @proc @account = @distaccount output if @@error <> 0 or @retcode <> 0 begin raiserror (14071, 16, -1) return (1) end /*   ** if @distaccount = 'localsystem' assume 'admin' privilege */    if @distaccount = 'localsystem' return (0)

/*   ** if @distaccount has 'admin' privilege, do not revoke */    execute @retcode = master.dbo.xp_logininfo @distaccount, 'all', @privilege = @privilege output if @@error <> 0 or @retcode <> 0 return (1)

if @privilege = 'admin' return (0)

/*   ** revoke replication privilege to the distributor nt account. */    exec @retcode = master.dbo.xp_revokelogin @distaccount if @@error <> 0 or @retcode <> 0 return (1) go

To clear the problem before applying the procedure, you can manually disable the 'pub' server option by executing the following statement on the subscription server:

use master go sp_serveroption, 'pub', false go

Example:

use master go sp_serveroption AIKMAN, 'pub', false go

STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.

MORE INFORMATION
When using SQL Enterprise Manager to disable a publication server (via the screen "Enable Subscribing from these Servers"), the operation will appear to succeed and the checkbox indicating the publication server is disabled will be cleared. If you return to the same screen without exiting SQL Enterprise Manager, the checkbox will still be cleared. However, any attempt to re-enable the publication server will result in the following SQL-DMO error:

Error 14704: [SQL Server] The server ' ' is already listed as a publisher

If you exit SQL Enterprise Manager and return to the same screen, the checkbox will appear selected again indicating the 'pub' server option was not cleared. This problem essentially will never allow the user to properly disable the publication server using SQL Enterprise Manager or the procedure sp_droppublisher. Additional query words: sysservers.status

Keywords : kbbug6.50 kbfix6.50.sp1

Version : 6.5

Platform : WINDOWS

Issue type :
 * }