sp_addsubscriber ??

sp_helpsubscriberinfo @@SERVERNAME

Or, just

sp_helpsubscriberinfo

to get a list of all subscribers.

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

ok i'll increse the points

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.

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

Award the points to AustinSeven.