April 27, 2012

sp_helpmergelogfiles (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_helpmergelogfiles(nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db
, nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @web_server)

MetaData:

 create procedure sys.sp_helpmergelogfiles(  
@publication sysname = '%', -- Publication name --
@subscriber sysname = '%', -- Subscriber server --
@subscriber_db sysname = '%', -- Subscription database --
@publisher sysname = '%', -- Publisher server --
@publisher_db sysname = '%', -- Publisher database --
@web_server sysname = '%' -- logs from IIS server --
)AS

SET NOCOUNT ON

--
-- Declarations.
--

declare @retcode int
declare @pubid uniqueidentifier
declare @subid uniqueidentifier

IF object_id('sysmergesubscriptions') is NULL
RETURN (0)

-- Security check --
EXEC @retcode = sys.sp_MSreplcheck_pull @publication = @publication,
@raise_fatal_error = 0
if @@ERROR <> 0 or @retcode <> 0
return(1)

--
-- Parameter Check: @publisher
-- Check to make sure that the publisher is defined
--
IF @publisher <> '%'
BEGIN
EXECUTE @retcode = sys.sp_validname @publisher
IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)
END

--
-- Parameter Check: @subscriber.
-- If remote server, limit the view to the remote server's subscriptions.
-- Make sure that the name isn't NULL.
--
if @subscriber IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@subscriber', 'sp_helpmergelogfiles')
RETURN (1)
END

--
-- Parameter Check: @subscriber.
-- Check if remote server is defined as a subscription server, and
-- that the name conforms to the rules for identifiers.
--

if @subscriber <> '%'
BEGIN
EXECUTE @retcode = sys.sp_validname @subscriber

if @retcode <> 0 OR @@ERROR <> 0
RETURN (1)

END

--
-- Parameter Check: @publication.
-- If the publication name is specified, check to make sure that it
-- conforms to the rules for identifiers and that the publication
-- actually exists. Disallow NULL.
--
if @publication IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@publication', 'sp_helpmergelogfiles')
RETURN (1)
END

create table #helplogfiles
(
id int NULL,
pubname sysname NULL,
publisher sysname NULL,
publisher_db sysname NULL,
subscriber_server sysname NULL,
db_name sysname NULL,
web_server sysname NULL,
file_name sysname NULL,
upload_time datetime NULL,
log_file_type int NULL
)


insert into #helplogfiles select distinct mlf.id, pubs.name, pubs.publisher, pubs.publisher_db, subs.subscriber_server, subs.db_name,
mlf.web_server, mlf.file_name, mlf.upload_time, mlf.log_file_type

FROM dbo.MSmerge_log_files mlf,
dbo.sysmergesubscriptions subs,
dbo.sysmergepublications pubs
where pubs.pubid = subs.pubid
and subs.pubid <> subs.subid
and pubs.name = @publication
and mlf.subid = subs.subid
and mlf.pubid = subs.pubid
and ((@web_server = N'%') or (mlf.web_server = @web_server collate database_default))
and ((@subscriber_db = N'%') or (subs.db_name = @subscriber_db collate database_default))
and ((@publisher_db = N'%') or (pubs.publisher_db = @publisher_db collate database_default))
and ((@subscriber = N'%') or (UPPER(subs.subscriber_server) = UPPER(@subscriber) collate database_default))
and ((@publisher = N'%') or (UPPER(pubs.publisher) = UPPER(@publisher) collate database_default))

select * from #helplogfiles

drop table #helplogfiles
return @retcode

sp_helpreplfailovermode (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_helpreplfailovermode(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication)

MetaData:

 create procedure sys.sp_helpreplfailovermode (  
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@failover_mode_id tinyint = 0 output,
@failover_mode nvarchar(10) = NULL output)
as
BEGIN
declare @subfound bit
,@retcode int
,@update_mode int

--
-- security check
--
exec @retcode = sys.sp_MSreplcheck_subscribe
if @@error <> 0 or @retcode <> 0
begin
return (1)
end
--
-- Check if the table MSsubscription_agents exists
--
if object_id('MSsubscription_agents') is not NULL
begin
--
-- Only valid to get failover_mode, if failover_mode is failover (3,5)
--
if exists (select * from dbo.MSsubscription_agents
where UPPER(publisher) = UPPER(@publisher) and
publisher_db = @publisher_db and
publication = @publication and
update_mode in (3,5) )
select @subfound = 1
else
select @subfound = 0
end
else
select @subfound = 0

--
-- Did we find an entry for initialized failover subscription
--
if (@subfound = 0)
begin
--
-- Three possibilities : uninitialized subscription, non existent subscription
-- or a non-mixed mode. Check If we have a PULL uninitialized subscription
--
if object_id('MSreplication_subscriptions') is not NULL
begin
select @update_mode = update_mode
from dbo.MSreplication_subscriptions
where publisher = @publisher
and publisher_db = @publisher_db
and publication = @publication
if (@update_mode is null)
begin
select @subfound = 0
end
begin
--
-- unitialized PULL subscription : return the values
--
select @failover_mode_id = case when (@update_mode = 6) then 1 else 0 end
select @subfound = 1
end
end

if (@subfound = 0)
begin
raiserror(20588, 16, -1)
return 1
end
end
else
begin
--
-- we found our subscription
--
select @failover_mode_id = cast(failover_mode as tinyint)
from dbo.MSsubscription_agents
where UPPER(publisher) = UPPER(@publisher) and
publisher_db = @publisher_db and
publication = @publication and
update_mode in (3,5)
--
-- initialize to 'immediate' if necessary
--
if @failover_mode_id not in (0, 1)
begin
raiserror (22576, 11, 1, @failover_mode_id, @publisher, @publisher_db, @publication)
update dbo.MSsubscription_agents
set failover_mode = 0
where UPPER(publisher) = UPPER(@publisher) and
publisher_db = @publisher_db and
publication = @publication and
update_mode in (3,5)

select @failover_mode_id = 0
end
end
--
-- prepare the output
--
select @failover_mode = case
when @failover_mode_id = 0 then N'immediate'
when @failover_mode_id = 1 then N'queued'
end

select N'failover_mode value' = @failover_mode_id,
N'failover_mode' = @failover_mode
--
-- all done
--
return 0
END

sp_helpreplicationdboption (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_helpreplicationdboption(nvarchar @dbname
, nvarchar @type
, bit @reserved)

MetaData:

   
--
-- Name:
-- sp_helpreplicationdboption
--
-- Description:
-- This stored procedure shows databases that have the replication
-- option enabled. It is executed at the publisher on any database.
-- It is used in snapshot, transactional, and merge replication.
-- It is not valid for heterogeneous publishers.
--
-- When the @dbname is speciified, a result set is returned if the
-- database meets the criteria specified in @type.
--
-- When the @dbname parameter is NULL, the result set includes entries
-- for all databases at the publisher that meet the criteria specified
-- in @type.
--
-- The user must be a member of the db_owner role of a publishing database
-- or in the PAL for a publication associated with the publishing database
-- to retrieve information for that database.
--
-- Security:
-- 'sysadmin', db_owner of publishing database, PAL for publication
-- associated with publishing databaseDB
-- Requires Certificate signature for catalog access
--
-- Returns:
-- Result set of database name, database id, transactional publisher,
-- merge publisher, current user is dbowner, database is readonly.
--
-- Owner:
-- <current owner>
--
create procedure sys.sp_helpreplicationdboption
(
@dbname sysname = N'%'
,@type sysname = N'replication allowed'
,@reserved bit = 0 -- 1 = get publication and pull subscription info
)
AS
BEGIN
SET NOCOUNT ON

--
-- Declarations.
--

DECLARE @retcode int
,@typebit int
,@distbit int -- bit to distinguish distribution databases
,@dbowner bit
,@issysadmin bit
,@replication_db sysname
,@db_category int
,@inPAL int
,@inPALrole bit
,@transpublish bit
,@mergepublish bit
,@cmd nvarchar(500)

SELECT @distbit = 16
-- optimize security check for sysadmin and dbo
,@issysadmin = case when (IS_SRVROLEMEMBER(N'sysadmin') = 1) then 1 else 0 end
,@inPAL = 0
,@inPALrole = 0
,@typebit = case
when (lower(@type) like N'publish%') then 1
when (lower(@type) like N'subscribe%') then 2
when (lower(@type) like N'merge publish%') then 4
when (lower(@type) like N'merge subscribe%') then 8
when (lower(@type) like N'replication allowed%') then 0
else null end

if @typebit is null
begin
raiserror(14091,-1,-1)
return 1
end
--
-- Parameter Check: @dbname.
-- Check to make sure that the database name conforms to the rules
-- for identifiers.
--
IF @dbname <> N'%'
BEGIN
EXECUTE @retcode = sys.sp_validname @dbname
IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)
END

--
-- create temp table for resultset
--
CREATE TABLE #replicationdbs
(
name sysname primary key,
id int identity NOT NULL,
transpublish bit not null,
mergepublish bit not null,
dbowner bit not null,
dbreadonly bit not null,
haspublications bit NULL,
haspullsubscriptions bit NULL
)
--
-- populate the table
--
INSERT INTO #replicationdbs (name, transpublish, mergepublish, dbowner, dbreadonly)
SELECT name
,is_published
,is_merge_published
,case when (@issysadmin = 1) then 1 else 0 end
,is_read_only
FROM master.sys.databases
WHERE ((@dbname = N'%') OR (name = @dbname))
AND ((@typebit = 0) or (is_published = 1) or (is_merge_published = 1) or (is_subscribed = 1))
AND (HAS_DBACCESS ( name ) = 1)
AND (@typebit != 0 OR is_distributor = 0) -- Filter out distribution databases
AND (@typebit != 0 OR name not in (N'master',N'model',N'tempdb',N'msdb',N'MSSQLWeb')) -- filter our system dbs
AND source_database_id is NULL -- exclude snapshot files

--
-- enumerate database collection
--
DECLARE #hCdboinfo CURSOR LOCAL FAST_FORWARD FOR
SELECT name, transpublish, mergepublish, dbowner
FROM #replicationdbs

OPEN #hCdboinfo
FETCH #hCdboinfo INTO @replication_db, @transpublish, @mergepublish, @dbowner
WHILE (@@fetch_status <> -1)
BEGIN
--
-- initialize
--
select @inPAL = 0
,@inPALrole = 0
--
-- skip dbowner check when sysadmin
--
if (@dbowner = 0)
begin
exec @dbowner = sys.sp_MSrepl_isdbowner @dbname = @replication_db
if (@dbowner = 1)
begin
update #replicationdbs
set dbowner = 1
where name = @replication_db
end
end
--
-- skip PAL check when sysadmin or dbo
--
if (@dbowner = 0)
begin
--
-- Check to see if the user has PAL access to a database publication
--
if (@transpublish = 1)
begin
-- Check PAL for transactional publication
select @cmd = quotename(@replication_db) + N'.sys.sp_MScheck_palroleinpubdb '
EXEC @retcode = @cmd @login = NULL
,@hasaccess = @inPAL output
end

if (@mergepublish = 1)
begin
-- Check PAL role for merge publication
select @cmd = quotename(@replication_db) + N'.sys.sp_MSrepl_IsUserInAnyPAL '
EXEC @retcode = @cmd
@raise_error = 0
if @@error = 0 and @retcode = 0
select @inPALrole = 1
end
end
--
-- Valid entry if user has access
--
if (@dbowner = 0 and @inPAL = 0 and @inPALrole = 0)
begin
DELETE #replicationdbs where name = @replication_db
end
else if (@reserved = 1)
begin
--
-- get publication and pull subscription information
--
select @cmd = quotename(@replication_db) + N'.sys.sp_MSrepl_helpreplicationdboptionex '
EXEC @retcode = @cmd
@name = @replication_db
,@transpublish = @transpublish
,@mergepublish = @mergepublish
end
--
-- get next db entry
--
FETCH #hCdboinfo INTO @replication_db, @transpublish, @mergepublish, @dbowner
END
CLOSE #hCdboinfo
DEALLOCATE #hCdboinfo
--
-- return resultset
--
if (@reserved = 0)
begin
SELECT
name
,id
,transpublish
,mergepublish
,dbowner
,dbreadonly
FROM #replicationdbs order by name
end
else
begin
SELECT
name
,id
,transpublish
,mergepublish
,dbowner
,dbreadonly
,haspublications
,haspullsubscriptions
FROM #replicationdbs order by name
end
-- all done
return 0
END

sp_helpreplicationdb (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_helpreplicationdb(nvarchar @dbname
, nvarchar @type)

MetaData:

 create procedure sys.sp_helpreplicationdb  
@dbname sysname = '%', @type sysname = 'pub'
AS

SET NOCOUNT ON

--
-- Declarations.
--

DECLARE @retcode int, @typebit int

if (lower(@type collate SQL_Latin1_General_CP1_CS_AS) like 'pub%')
select @typebit = 1
else if (lower(@type collate SQL_Latin1_General_CP1_CS_AS) like 'sub%')
select @typebit = 2
else
begin
raiserror(14091,-1,-1)
return 1
end

--
-- Parameter Check: @dbname.
-- Check to make sure that the database name conforms to the rules
-- for identifiers.
--

IF @dbname <> '%'
BEGIN
EXECUTE @retcode = sys.sp_validname @dbname

IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)
END

--
-- Show databases with this option enabled.
--

SELECT name
FROM master.dbo.sysdatabases
WHERE ((@dbname = N'%') or (name = @dbname collate database_default))
AND (category & @typebit) <> 0

sp_helpremotelogin (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_helpremotelogin(nvarchar @remoteserver
, nvarchar @remotename)

MetaData:

 create procedure sys.sp_helpremotelogin  
@remoteserver sysname = NULL, -- remote server name --
@remotename sysname = NULL -- remote login name --
as
set nocount on

-- If no server given, get 'em all.
if not exists (select * from master.dbo.sysservers s, master.dbo.sysremotelogins r
where s.srvid = r.remoteserverid
and (@remoteserver is null or s.srvname = @remoteserver ))
begin
if @remoteserver is null
begin
raiserror(15200,-1,-1)
return (0)
end

raiserror(15201,-1,-1,@remoteserver)
return (1)
end

-- If no remotename given, get 'em all.
if not exists (select * from master.dbo.sysremotelogins
where (@remotename is null or isnull(remoteusername, ' ') = @remotename))
begin
if @remotename is null
begin
raiserror(15202,-1,-1)
return (1)
end

raiserror(15203,-1,-1,@remotename)
return (1)

end

-- Check for empty results.
if not exists (select *
from master.dbo.sysremotelogins r, master.dbo.sysservers s
where ( @remotename is null or isnull(r.remoteusername, ' ') = @remotename)
and s.srvid = r.remoteserverid
and (@remoteserver is null or s.srvname = @remoteserver))
begin
raiserror(15204,-1,-1,@remotename,@remoteserver)
return (1)
end

-- select the information.
select server = substring(s.srvname, 1, 22),
local_user_name =
substring(isnull(suser_sname(r.sid), '-- use local name -- '), 1, 22),
remote_user_name =
substring(isnull(r.remoteusername, '-- mapped locally -- '), 1, 22),
options = case
when datalength(v.name) is null then ''
when datalength(v.name) = 0 then ''
else substring(v.name, 1, 9)
end
from master.dbo.sysservers s, master.dbo.sysremotelogins r,
master.dbo.spt_values v
where s.srvid = r.remoteserverid
and (@remoteserver is null or s.srvname = @remoteserver)
and (@remotename is null or isnull(r.remoteusername, ' ') = @remotename)
and v.type = 'F'
and v.number = r.status
order by server, remote_user_name

return (0) -- sp_helpremotelogin

sp_helpqreader_agent (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_helpqreader_agent(bit @frompublisher)

MetaData:

 --   
-- Name:
-- sp_helpqreader_agent
--
-- Description:
-- Displays the following information on qreader agent:
-- QA ID
-- NAME
-- Job ID
-- Job Login
-- Job Password (dummy value of 10 *'s)
--
-- Security:
-- DBO of distributiondb
-- Requires Certificate signature for catalog access
--
-- Returns:
-- 0 : success
-- 1 : failure
--
CREATE PROCEDURE sys.sp_helpqreader_agent
(
@frompublisher bit = 0
)
AS
BEGIN
DECLARE @retcode int

IF @frompublisher = 0
BEGIN
-- Security Check: require sysadmin/dbo of dist
IF IS_SRVROLEMEMBER('sysadmin') != 1
BEGIN
RAISERROR(21089,16,-1)
RETURN 1
END

-- database must be distribution db or we exit with no resultset
IF sys.fn_MSrepl_isdistdb(DB_NAME()) <> 1
BEGIN
RETURN 0
END
END
ELSE
BEGIN
-- Security Check: require sysadmin/dbo of publisher
IF IS_SRVROLEMEMBER('sysadmin') != 1
AND IS_MEMBER ('db_owner') != 1
BEGIN
RAISERROR(21089,16,-1)
RETURN 1
END

-- only verify this for the DBO case, sysadmin gets a pass
IF IS_SRVROLEMEMBER('sysadmin') != 1
BEGIN
-- must be tran published or we exit with no resultset
IF sys.fn_MSrepl_istranpublished(DB_NAME(),0) <> 1
BEGIN
RETURN 0
END
END
END

--
-- if executed from publisher
--
IF @frompublisher = 1
BEGIN
DECLARE @loc_publisher sysname
,@loc_distribdb sysname
,@rpcsrvname sysname
,@rpc nvarchar(1000)
--
-- get the distributor rpc info
--
SELECT @loc_publisher = CONVERT(sysname, SERVERPROPERTY('ServerName'))
EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @publisher = @loc_publisher,
@rpcsrvname = @rpcsrvname OUTPUT,
@distribdb = @loc_distribdb OUTPUT

IF @@error <> 0 OR @retcode <> 0 or (@rpcsrvname IS NULL) or (@loc_distribdb IS NULL)
BEGIN
RAISERROR(20036, 16, -1)
RETURN 1
END
--
-- execute the RPC
--
select @rpc = quotename(@rpcsrvname) + N'.' + quotename(@loc_distribdb) + N'.dbo.sp_helpqreader_agent'
exec @retcode = @rpc @frompublisher = 0
IF @@error != 0
select @retcode = 1

RETURN @retcode
END

SELECT msqa.id,
msqa.name,
sjb.job_id,
sc.credential_identity as [job_login],
N'-- -- -- -- -- ' as [job_password]
FROM MSqreader_agents msqa
JOIN msdb..sysjobs sjb
ON convert(uniqueidentifier, msqa.job_id) = sjb.job_id
LEFT JOIN msdb..sysjobsteps sj
ON msqa.job_step_uid = sj.step_uid
LEFT JOIN msdb..sysproxies sp
ON sj.proxy_id = sp.proxy_id
LEFT JOIN sys.credentials sc
ON sp.credential_id = sc.credential_id

RETURN 0
END

sp_helppullsubscription (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_helppullsubscription(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @show_push)

MetaData:

   
create procedure sys.sp_helppullsubscription
(
@publisher sysname = N'%',
@publisher_db sysname = N'%',
@publication sysname = N'%',
@show_push nvarchar(5) = N'false'
)
AS
begin
SET NOCOUNT ON

-- Declarations.
DECLARE @command nvarchar(255)
,@name nvarchar(255)
,@retcode int
,@show_push_bit bit
,@push int
,@subscriber sysname
,@subscriber_db sysname
,@publisher_local sysname
,@publisher_db_local sysname
,@publication_local sysname
,@subscription_name nvarchar(1000)
,@regkey nvarchar(1000)
,@syncmgr_keyexist int
,@helpsubscriptioncursor_open int

--
-- security check
--
exec @retcode = sys.sp_MSreplcheck_subscribe
if @retcode <> 0 or @@error <> 0
return 1

-- For attach
if object_id(N'dbo.MSrepl_restore_stage') is not null
-- The database is attached from a subscription copy file without using
-- sp_attachsubscription. Return nothing
return 0
--
-- Initializations.
--
select @subscriber = @@SERVERNAME
,@subscriber_db = DB_NAME()
,@push = 0
--
-- Parameter Check: @publisher
-- Check to make sure that the publisher is define
--
IF @publisher IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@publisher', 'sp_helppullsubscription')
RETURN (1)
END

IF @publisher <> N'%'
BEGIN
EXECUTE @retcode = sys.sp_validname @publisher
IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)
END

IF @publication <> N'%'
BEGIN
EXECUTE @retcode = sys.sp_validname @publication
IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)
END

IF @show_push IS NOT NULL AND
LOWER(@show_push) NOT IN (N'true', N'false')
BEGIN
RAISERROR (14148, 16, -1, '@show_push')
RETURN (1)
END
SELECT @show_push_bit = case when (LOWER(@show_push) = N'false') then 0 else 1 end

IF object_id('MSreplication_subscriptions','U') is NULL
OR object_id('MSsubscription_properties','U') is NULL
OR object_id('MSsubscription_agents','U') is NULL
RETURN (0)
--
-- Get the result
-- Note: have to return meta data
--
SELECT 'publisher' = rs.publisher,
'publisher database' = rs.publisher_db,
'publication' = rs.publication,
'independent_agent' = rs.independent_agent,
'subscription type' = rs.subscription_type,
'distribution agent' = distribution_agent,
'publication description' = rs.description,
'last updating time' = sys.fn_replformatdatetime(time),
'subscription_name' = rs.publisher + N':' + rs.publisher_db + N':' + rs.publication,
'last transaction timestamp' = transaction_timestamp,
-- SyncTran
-- For Queued case, Until the subscription is initialized
-- the entry in MSreplication_subscriptions will be used to
-- decide the update mode. Once the distribution agent has
-- initialized the entry in MSsubscription_agents, then we
-- will use failover_mode instead (the update mode entry in
-- MSreplication_subscriptions will be changed by distribution
-- agent)
--
-- When update mode = 6 - Queued Failover (4)
-- When update mode = 4 - Queued only (2)
-- When update mode = 5 and Failover flag = 0 - Immediate Failover (3)
-- When update mode = 5 and Failover flag = 1 - Queued Failover (4)
--
'update_mode' = case
when rs.update_mode = 4 then 2
when rs.update_mode = 6 then 4
when rs.update_mode = 5 and isnull(a.failover_mode,0) = 0 then 3
when rs.update_mode = 5 and isnull(a.failover_mode,0) = 1 then 4
else rs.update_mode
end,
'distribution agent job_id' = agent_id,
'enabled for syncmgr' = enabled_for_syncmgr,
'subscription guid' = subscription_guid,
'subid ' = subid,
'immediate_sync' = immediate_sync,
'publisher_login' = publisher_login,
'publisher_password' = publisher_password,
'publisher_security_mode' = publisher_security_mode,
'distributor' = distributor,
'distributor_login' = distributor_login,
'distributor_password' = distributor_password,
'distributor_security_mode' = distributor_security_mode,
'ftp_address' = ftp_address,
'ftp_port' = ftp_port,
'ftp_login' = ftp_login,
'ftp_password' = ftp_password,
'alt_snapshot_folder' = alt_snapshot_folder,
'working_directory' = working_directory,
'use_ftp' = use_ftp,
'publication_type' = publication_type,
'dts_package_name' = dts_package_name,
'dts_package_location' = dts_package_location,
'offload_agent' = offload_agent,
'offload_server' = offload_server,
a.last_sync_status,
a.last_sync_summary,
'last_sync_time' = sys.fn_replformatdatetime(a.last_sync_time),
'job_login' = sc.credential_identity,
'job_password' = N'-- -- -- -- -- '
from MSreplication_subscriptions rs
left outer join MSsubscription_properties sp
on (UPPER(rs.publisher) = UPPER(sp.publisher) and rs.publisher_db = sp.publisher_db and rs.publication = sp.publication)
left outer join MSsubscription_agents a
on (UPPER(rs.publisher) = UPPER(a.publisher) and
rs.publisher_db = a.publisher_db and
((rs.publication = a.publication and
rs.independent_agent = 1 and
a.publication <> N'ALL') or
(a.publication = N'ALL' and rs.independent_agent = 0)) and
rs.subscription_type = a.subscription_type)
left outer join msdb..sysjobsteps sj
on sp.job_step_uid = sj.step_uid
left outer join msdb..sysproxies p
on sj.proxy_id = p.proxy_id
left join sys.credentials sc
on p.credential_id = sc.credential_id
where ((@publisher = N'%') OR (UPPER(rs.publisher) = UPPER(@publisher))) AND
((@publisher_db = N'%') OR (rs.publisher_db = @publisher_db)) AND
((@publication = N'ALL' AND rs.independent_agent = 0) OR rs.publication LIKE @publication) AND
(rs.subscription_type <> @push OR
@show_push_bit = 1)
ORDER BY rs.publisher, rs.publisher_db, rs.publication
--
-- all done
--
return 0
end

sp_helppublicationsync (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_helppublicationsync(nvarchar @publication)

MetaData:

   
create procedure sys.sp_helppublicationsync (
@publication sysname -- The publication name --
) AS
SET NOCOUNT ON
RAISERROR (21023, 16, -1,'sp_helppublicationsync')
RETURN(1)

sp_helppublication_snapshot (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_helppublication_snapshot(nvarchar @publication
, nvarchar @publisher)

MetaData:

 CREATE PROCEDURE sys.sp_helppublication_snapshot   
(
@publication sysname,
@publisher sysname = NULL
)
AS
BEGIN
DECLARE @cmd nvarchar(4000)
DECLARE @retcode int
DECLARE @publisher_type sysname

SET @retcode = 0

EXEC @retcode = sys.sp_MSrepl_getpublisherinfo @publisher = @publisher,
@publisher_type = @publisher_type OUTPUT,
@rpcheader = @cmd OUTPUT

IF @retcode <> 0
RETURN (@retcode)

-- Add sp
SET @publisher = UPPER(@publisher) COLLATE DATABASE_DEFAULT
set @cmd = @cmd + N'sys.sp_MSrepl_helppublication_snapshot'

EXEC @retcode = @cmd @publication = @publication,
@publisher = @publisher,
@publisher_type = @publisher_type

RETURN (@retcode)
END

sp_helppublication (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_helppublication(nvarchar @publication
, nvarchar @publisher)

MetaData:

   
--
-- Name:
-- sp_helppublication
--
-- Description:
-- Returns information about a publication. If the article
-- name is not specified, it returns information for all publications
-- associated with the publishing database.
--
-- For a SQL Server publication, this stored procedure is executed
-- at the Publisher on the publishing database. For a heterogeneous
-- publication, this stored procedure may be executed in any database
-- at the distributor for the associated publisher.
--
-- When the @publication parameter is NULL, results are only returned
-- for those publications that the current user has PAL access to.
--
-- Security:
-- SQL Server publication: 'sysadmin', db_owner of publishing database, PAL
-- Heterogeneous publication: 'sysadmin', db_owner of distribution database, PAL
--
-- Returns:
-- Result set of publication properties
--
-- Owner:
-- <current owner>
--

create procedure sys.sp_helppublication
(
@publication sysname = N'%',
@found int = 23456 OUTPUT, -- flag indicate returning row
@publisher sysname = NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @cmd nvarchar(4000)
,@retcode int
,@publisher_type sysname

EXEC @retcode = sys.sp_MSrepl_getpublisherinfo
@publisher = @publisher,
@publisher_type = @publisher_type OUTPUT,
@rpcheader = @cmd OUTPUT,
@skipSecurityCheck = 1
IF @retcode <> 0
RETURN (@retcode)

SELECT @publisher = UPPER(@publisher)
,@cmd = case when (@publisher_type = N'MSSQLSERVER')
then @cmd + N'sys.sp_MSrepl_helppublication'
else @cmd + N'sys.sp_IHhelppublication' end
EXEC @retcode = @cmd
@publication,
@found OUTPUT,
@publisher,
@publisher_type
RETURN (@retcode)
END

sp_helppeerresponses (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_helppeerresponses(int @request_id)

MetaData:

 create procedure sys.sp_helppeerresponses  
(
@request_id int
)
as
begin
declare @retcode int

-- Security Check
exec @retcode = sys.sp_MSreplcheck_publish
if @@error <> 0 or @retcode <> 0
begin
return 1
end

-- Check to see if database is activated for publication
if sys.fn_MSrepl_istranpublished(db_name(),0) <> 1
begin
RAISERROR (14013, 16, -1)
return 1
end

-- return results
select *
from MSpeer_response
where request_id = @request_id

return 0
end

sp_helppeerrequests (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_helppeerrequests(nvarchar @publication
, nvarchar @description)

MetaData:

 create procedure sys.sp_helppeerrequests  
(
@publication sysname,
@description nvarchar(4000) = '%'
)
as
begin
declare @retcode int

-- Security Check
exec @retcode = sys.sp_MSreplcheck_publish
if @@error <> 0 or @retcode <> 0
begin
return 1
end

-- Check to see if database is activated for publication
if sys.fn_MSrepl_istranpublished(db_name(),0) <> 1
begin
RAISERROR (14013, 16, -1)
return 1
end

-- return results
select *
from MSpeer_request
where publication = @publication
and isnull(description, N'') like @description
order by sent_date desc

return 0
end

sp_helpntgroup (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_helpntgroup(nvarchar @ntname)

MetaData:

 create procedure sys.sp_helpntgroup  
@ntname sysname = NULL
AS
if @ntname is not null
begin
-- VALIDATE GIVEN NAME
if not exists (select * from sysusers where name = @ntname and isntgroup = 1)
begin
raiserror(15420, -1, -1, @ntname)
return (1)
end

-- RESULT SET FOR SINGLE GROUP
select 'NTGroupName' = u.name, 'NtGroupId' = u.principal_id, 'SID' = u.sid, 'HasDbAccess' = case when p.state in ('G','W') then 1 else 0 end
from sys.database_principals u
left join sys.database_permissions p on p.class = 0 and p.major_id = 0 and p.minor_id = 0
and p.grantee_principal_id = u.principal_id AND p.grantor_principal_id = 1 AND p.type = 'CO'
where u.name = @ntname and u.type = 'G'
end
else
begin
-- RESULT SET FOR ALL GROUPS
select 'NTGroupName' = u.name, 'NtGroupId' = u.principal_id, 'SID' = u.sid, 'HasDbAccess' = case when p.state in ('G','W') then 1 else 0 end
from sys.database_principals u
left join sys.database_permissions p on p.class = 0 and p.major_id = 0 and p.minor_id = 0
and p.grantee_principal_id = u.principal_id AND p.grantor_principal_id = 1 AND p.type = 'CO'
where u.type = 'G'
end

return (0) -- sp_helpntgroup

sp_helpmergesubscription (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_helpmergesubscription(nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db
, nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @subscription_type)

MetaData:

   
create procedure sys.sp_helpmergesubscription(
@publication sysname = '%', -- Publication name --
@subscriber sysname = '%', -- Subscriber server --
@subscriber_db sysname = '%', -- Subscription database --
@publisher sysname = '%', -- Publisher server --
@publisher_db sysname = '%', -- Publisher database --
@subscription_type nvarchar(15) = 'both', -- Subscription type - push or pull --
@found int = NULL OUTPUT
)AS

SET NOCOUNT ON

--
-- Declarations.
--

declare @db sysname
declare @retcode int
declare @subscriber_bit smallint
declare @srvid int
declare @pubid uniqueidentifier
declare @subid uniqueidentifier
declare @cursor_open int
declare @no_row bit
declare @subscription_type_id int

declare @distributor sysname
declare @distributiondb sysname
declare @distproc nvarchar(300)
declare @dbname sysname
,@publishingservername sysname

select @distributor = null
select @distributiondb = null
select @distproc = null
select @dbname = null
,@publishingservername = publishingservername()

--
-- Initializations.
--
set @subscriber_bit = 4
set @cursor_open = 0

--
-- Initializations of @now_row.
--
IF @found is NULL
BEGIN
SELECT @no_row=0
END
ELSE
BEGIN
SELECT @no_row=1
END

select @db=db_name() -- so that it can appear in dynamic query

--
-- Calling sp_help* is all right whether current database is enabled for pub/sub or not
--

IF object_id('sysmergesubscriptions') is NULL
RETURN (0)

-- Security check --
EXEC @retcode = sys.sp_MSreplcheck_pull @publication = @publication,
@raise_fatal_error = 0
if @@ERROR <> 0 or @retcode <> 0
return(1)

--
-- Parameter Check: @subscription_type.
-- Set subscription_typeid based on the @subscription_type specified.
--
-- subscription_type subscription_type
-- ================= ===============
-- 0 push
-- 1 pull
-- 2 both
--
if LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('push', 'pull', 'both')
BEGIN
RAISERROR (20079, 16, -1)
RETURN (1)
END
IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'both'
set @subscription_type_id = 2
else IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'push'
set @subscription_type_id = 0
else
set @subscription_type_id = 1

--
-- Parameter Check: @publisher
-- Check to make sure that the publisher is defined
--
IF @publisher <> '%'
BEGIN
EXECUTE @retcode = sys.sp_validname @publisher
IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)
END

--
-- Parameter Check: @subscriber.
-- If remote server, limit the view to the remote server's subscriptions.
-- Make sure that the name isn't NULL.
--
if @subscriber IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@subscriber', 'sp_helpmergesubscription')
RETURN (1)
END

--
-- Parameter Check: @subscriber.
-- Check if remote server is defined as a subscription server, and
-- that the name conforms to the rules for identifiers.
--

if @subscriber <> '%'
BEGIN
EXECUTE @retcode = sys.sp_validname @subscriber

if @retcode <> 0 OR @@ERROR <> 0
RETURN (1)

END

--
-- Parameter Check: @publication.
-- If the publication name is specified, check to make sure that it
-- conforms to the rules for identifiers and that the publication
-- actually exists. Disallow NULL.
--
if @publication IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@publication', 'sp_helpmergesubscription')
RETURN (1)
END

create table #helpsubscription
(
publication sysname collate database_default not null,
publisher sysname collate database_default not null,
publisher_db sysname collate database_default not null,
subscriber sysname collate database_default not null,
subscriber_db sysname collate database_default not null,
status int NOT NULL,
subscriber_type int NOT NULL,
subscription_type int NOT NULL,
priority float(8) NOT NULL,
sync_type tinyint NOT NULL,
description nvarchar(255) collate database_default null,
merge_jobid binary(16) NULL,
full_publication tinyint NULL,
use_interactive_resolver int NULL,
hostname sysname NULL
)


--
-- Performance Optimization: Eliminate the 'LIKE' clause for publication name.
-- Empirical evidence shows almost 50% speed improvement when
-- opening the cursor if publication name is provided.
--
IF (@publication <> '%')
insert into #helpsubscription select distinct pubs.name, pubs.publisher, pubs.publisher_db, subs.subscriber_server, subs.db_name,
subs.status, subs.subscriber_type, subs.subscription_type, subs.priority,
subs.sync_type, subs.description, replinfo.merge_jobid, pubs.publication_type,
replinfo.use_interactive_resolver, replinfo.hostname

FROM dbo.sysmergesubscriptions subs,
dbo.MSmerge_replinfo replinfo,
dbo.sysmergepublications pubs
where subs.status <> 2
and pubs.pubid = subs.pubid
and subs.pubid <> subs.subid
and pubs.name = @publication
and replinfo.repid = subs.subid
and (suser_sname(suser_sid()) = replinfo.login_name OR is_member('db_owner')=1 OR is_srvrolemember('sysadmin') = 1)
and ((@subscriber_db = N'%') or (subs.db_name = @subscriber_db collate database_default))
and ((@publisher_db = N'%') or (pubs.publisher_db = @publisher_db collate database_default))
and ((@subscriber = N'%') or (UPPER(subs.subscriber_server) = UPPER(@subscriber) collate database_default))
and ((@publisher = N'%') or (UPPER(pubs.publisher) = UPPER(@publisher) collate database_default))
and (subs.subscription_type = @subscription_type_id or @subscription_type_id = 2)
and (subs.subscriber_type <> 3)
ELSE
insert into #helpsubscription select distinct pubs.name, pubs.publisher, pubs.publisher_db, subs.subscriber_server, subs.db_name,
subs.status, subs.subscriber_type, subs.subscription_type, subs.priority,
subs.sync_type, subs.description, replinfo.merge_jobid, pubs.publication_type,
replinfo.use_interactive_resolver, replinfo.hostname

FROM dbo.sysmergesubscriptions subs,
dbo.MSmerge_replinfo replinfo,
dbo.sysmergepublications pubs
where subs.status <> 2
and pubs.pubid = subs.pubid
and subs.pubid <> subs.subid
and replinfo.repid = subs.subid
and (suser_sname(suser_sid()) = replinfo.login_name OR is_member('db_owner')=1 OR is_srvrolemember('sysadmin') = 1)
and ((@subscriber_db = N'%') or (subs.db_name = @subscriber_db collate database_default))
and ((@publisher_db = N'%') or (pubs.publisher_db = @publisher_db collate database_default))
and ((@subscriber = N'%') or (UPPER(subs.subscriber_server) = UPPER(@subscriber) collate database_default))
and ((@publisher = N'%') or (UPPER(pubs.publisher) = UPPER(@publisher) collate database_default))
and (subs.subscription_type = @subscription_type_id or @subscription_type_id = 2)
and (subs.subscriber_type <> 3)


if exists (select * from #helpsubscription)
select @found = 1
else
select @found = 0

if @no_row = 1
goto DONE

CREATE TABLE #merge_agent_properties
(
job_id VARBINARY(16) NULL,
offload_enabled bit NULL,
offload_server sysname collate database_default null,
subscriber_security_mode smallint NULL,
subscriber_login sysname NULL,
job_login sysname NULL,
publisher_security_mode smallint NULL,
publisher_login sysname NULL,
merge_agent_name nvarchar(100) NULL
)

EXEC @retcode = sys.sp_helpdistributor @rpcsrvname = @distributor OUTPUT,
@distribdb = @distributiondb OUTPUT
IF @retcode <> 0
GOTO DONE

SELECT @distributor = RTRIM(@distributor)

-- Get distribution agent properties
IF LOWER(@@SERVERNAME) <> LOWER(@distributor)
BEGIN
SELECT @distproc = QUOTENAME(@distributor) + '.' + QUOTENAME(@distributiondb) +
'.dbo.sp_MSenum_merge_agent_properties'
END
ELSE
BEGIN
SELECT @distproc = QUOTENAME(@distributiondb) +
'.dbo.sp_MSenum_merge_agent_properties'
END

SELECT @dbname = db_name()

INSERT INTO #merge_agent_properties
EXEC @retcode = @distproc @publisher = @publishingservername,
@publisher_db = @dbname,
@publication = @publication,
@show_security = 1

IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'push' or LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'both'
begin
select 'subscription_name' = subscriber + ':' + subscriber_db,
hs.publication, hs.publisher, hs.publisher_db,
hs.subscriber, hs.subscriber_db, hs.status, hs.subscriber_type,
hs.subscription_type, hs.priority, hs.sync_type, hs.description,
ap.job_id, hs.full_publication,
ap.offload_enabled, ap.offload_server,
hs.use_interactive_resolver, hs.hostname,
ap.subscriber_security_mode,
ap.subscriber_login,
'subscriber_password' = '-- -- -- -- -- ',
ap.job_login,
'job_password' = '-- -- -- -- -- ',
ap.publisher_security_mode,
ap.publisher_login,
'publisher_password' = '-- -- -- -- -- ',
ap.merge_agent_name
from #helpsubscription hs
left outer join #merge_agent_properties ap
on hs.merge_jobid = ap.job_id
order by hs.publisher, hs.publisher_db, hs.publication, hs.subscriber, hs.subscriber_db
end
else
begin
select 'subscription_name' = hs.publisher + ':' + hs.publisher_db + ':' + hs.publication,
hs.publication, hs.publisher, hs.publisher_db,
hs.subscriber, hs.subscriber_db, hs.status, hs.subscriber_type,
hs.subscription_type, hs.priority, hs.sync_type, hs.description,
ap.job_id, hs.full_publication,
ap.offload_enabled, ap.offload_server,
hs.use_interactive_resolver, hs.hostname,
ap.subscriber_security_mode,
ap.subscriber_login,
'subscriber_password' = '-- -- -- -- -- ',
ap.job_login,
'job_password' = '-- -- -- -- -- ',
ap.publisher_security_mode,
ap.publisher_login,
'publisher_password' = '-- -- -- -- -- ',
ap.merge_agent_name
from #helpsubscription hs
left outer join #merge_agent_properties ap
on hs.merge_jobid = ap.job_id
order by hs.publisher, hs.publisher_db, hs.publication, hs.subscriber, hs.subscriber_db
end

drop table #merge_agent_properties
select @retcode = 0
DONE:
if (@cursor_open = 1)
begin
close #cursor
deallocate #cursor
end
drop table #helpsubscription
return @retcode

Total Pageviews