Solved

sp_addsubscriber ??

Posted on 2004-08-19
10
1,150 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now