Solved

sp_addsubscriber ??

Posted on 2004-08-19
10
1,168 Views
Last Modified: 2008-01-09
Hi
when I execute these statement..
sp_addsubscriber  @subscriber =  @@SERVERNAME

It will gives

Server: Msg 14040, Level 16, State 1, Procedure sp_addsubscriber, Line 95
The server '<ServerName>' is already a Subscriber.

how can I check if the subscriber already exists or not

thanks,
Dishan

0
Comment
Question by:dishanf
  • 3
  • 3
10 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11849561
sp_helpsubscriberinfo @@SERVERNAME

Or, just

sp_helpsubscriberinfo

to get a list of all subscribers.
0
 
LVL 8

Author Comment

by:dishanf
ID: 11849576
Hi
Yes.. I tried
exec sp_helpsubscriberinfo @@SERVERNAME
It will return 1 record. but I want to check it and do something in script.

I tried to get the result set ...

CREATE TABLE #helpsubscriberinfo(
      publisher sysname,
      subscriber sysname,
      type tinyint,
      login sysname,
      password sysname,
      commit_batch_size int,
      status_batch_size int,
      flush_frequency int,
      frequency_type int,
      frequency_interval int,
      frequency_relative_interval int,
      frequency_recurrence_factor int,
      frequency_subday int,
      frequency_subday_interval int,
      active_start_time_of_day int,
      active_end_time_of_day int,
      active_start_date int,
      active_end_date int,
      retryattempt int,
      retrydelay int,
      description nvarchar(255),
      security_mode int
)

insert into #helpsubscriberinfo
exec sp_helpsubscriberinfo @@SERVERNAME

but it gives ERROR:
Server: Msg 8164, Level 16, State 1, Procedure sp_helpsubscriberinfo, Line 124
An INSERT EXEC statement cannot be nested.
Server: Msg 14071, Level 16, State 1, Procedure sp_helpsubscriberinfo, Line 131
Could not find the Distributor or the distribution database for the local server. The Distributor may not be installed, or the local server may not be configured as a Publisher at the Distributor.

But I do same thing in this.............
CREATE TABLE #helpdistributor(
      Distributor sysname,-- Name of the Distributor.
      distribution_database sysname,-- Name of the distribution database.
      Directory nvarchar(255),-- Name of the working directory.
      Account nvarchar(255),-- Name of the Windows user account.
      min_distrib_retention int,-- Minimum distribution retention period.
      max_distrib_retention int,-- Maximum distribution retention period.
      history_retention int,-- History retention period.
      history_cleanup_agent nvarchar(100),-- Name of the History Cleanup Agent.
      distribution_cleanup_agent nvarchar(100),-- Name of the Distribution Cleanup Agent.
      rpc_server_name sysname,-- Name of the remote or local Distributor.
      rpc_login_name sysname
)

insert into #helpdistributor
exec sp_helpdistributor

and it works properlly

Thanks
DishanF
0
 
LVL 8

Author Comment

by:dishanf
ID: 11849600
ok i'll increse the points
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 10

Accepted Solution

by:
AustinSeven earned 500 total points
ID: 11849885
Yeh, that's caught me out a few times too.   The reason it failed is that  sp_helpsubscriberinfo has got an:-

insert into #temptable
execute sproc

of its own.

So, in your script, you are doing:-

insert into #helpsubscriberinfo
exec sp_helpsubscriberinfo @@SERVERNAME

as well and that is not allowed.   As the error message says, you've nested your 'insert exec' statments.

Here's a likely workaround...

In EM, go to the Master database, Stored Procedures and find sp_helpsubscriberinfo.  Do Properties on it and <Ctrl C> the T-SQL out and paste the code into Query Analzyer.   Alertatively, you can script the sproc if you want and do it that way.

In QA, comment out the create procedure header stuff:-

--CREATE PROCEDURE sp_helpsubscriberinfo
--    @subscriber sysname = '%'
--    AS

Then enter the following just below the commented out section:-

declare @subscriber sysname
select @subscriber = @@servername -- or  =  '%'

Then just run the script interactively.   The results, as well as being displayed, should be still held in the sprocs temporary tabled called  #subscriber_info.   I had a quick look and I couldn't see a 'drop table  #subscriber_info' command anywhere.  Therefore, you should be able to simply do:-

select * from  #subscriber_info


AustinSeven
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11851532
Hey, Dishan, I see your note about increasing the points and that's it now 500. I don't know what it was before, but I hope you didn't think I abandoned you because it wasn't enough points. I'm in the U.S. and happened to be awake with a sore back. After I posted my first response, I tried to go back to sleep...

Anyway, I couldn't have answered any better than AustinSeven. His answer is your best solution if you're going to automate the query handing.

Cheers.
0
 
LVL 8

Author Comment

by:dishanf
ID: 11929683
CREATE TABLE #helpserver(
      name sysname null,-- Server name.
      network_name sysname null,-- Server's network name.
      status varchar(70) null,-- Server status.
      id char(4) null,-- Server's identification number.
      collation_name sysname null,-- Server's collation.
      connect_timeout int null,-- Time-out value for connecting to linked server.
      query_timeout int null
)

insert into #helpserver
exec sp_helpserver  @server = @@SERVERNAME ,@optname = 'sub'

if not exists(SELECT 1 FROM #helpserver)
      exec sp_addsubscriber  @subscriber =  @@SERVERNAME
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12150867
Award the points to AustinSeven.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question