Solved

sp_addsubscriber ??

Posted on 2004-08-19
10
1,182 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:Dishan Fernando
  • 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:Dishan Fernando
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:Dishan Fernando
ID: 11849600
ok i'll increse the points
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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:Dishan Fernando
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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

680 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