June 13, 2012

sp_validate_replica_hosts_as_publishers (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_validate_replica_hosts_as_publishers(nvarchar @original_publisher
, nvarchar @publisher_db)

MetaData:

 --   
-- Name: sp_validate_replica_hosts_as_publishers
--
-- Descriptions: Retrieve the redirected publisher for the specified
-- publisher database pair. If the publisher is not
-- currently redirected, return NULL.
--
-- For redirected publishers, a connection is made to the
-- redirected publisher to determine whether the target of
-- redirection is an availability group VNN Name. If it is
-- not, a message is returned indicating that the redirected
-- publisher is not associated with an availability group.
--
-- The availability group primary is then queried for the
-- names of the hosts of the member replicas. Each host in
-- turn is validated as a publisher for the database.
--
-- In general, the stored procedure will attempt to continue
-- after encountering errors, and will try to validate all
-- of the known replica hosts associated with the availability
-- group.
--
-- Parameters: as defined in create statement
--
-- Returns: 0 on success, 1 on failure; on failure errors are raised
--
-- Security: Public procedure invoked via RPC. check caller for db_owner
-- or in the PAL of a publication of the named pubisher check
--
create procedure sys.sp_validate_replica_hosts_as_publishers
(
@original_publisher sysname,
@publisher_db sysname,
@redirected_publisher sysname output
)
as
begin

set nocount on

declare
@target_server sysname,
@publisher_linked_server sysname,
@dbname sysname,
@retcode int,
@use_caller_credentials bit

set @redirected_publisher = null
set @target_server = null
set @publisher_linked_server = null
set @dbname = db_name()
set @use_caller_credentials = 1

-- Has to be executed from a distribution database
--
if (sys.fn_MSrepl_isdistdb (@dbname) <> 1)
begin
raiserror(21874, 16, -1, 'sys.sp_validate_replica_hosts_as_publishers', @dbname)
return 1
end

-- Verify input parameters are not NULL
--
if @original_publisher is null or
@publisher_db is null
begin
raiserror (21875, 16, -1, 'sys.sp_validate_replica_hosts_as_publishers')
return 1
end

-- Security check
--
if is_member(N'db_owner') <> 1
begin
exec @retcode = sys.sp_MSrepl_DistDBPALAccess @original_publisher

if (@retcode <> 0) or (@@error <> 0)
begin
raiserror (21873, 16, -1, 'sys.sp_validate_replica_hosts_as_publishers')
return 1
end
end

-- If the publisher of the database has not been redirected
-- return without performing any validation.
--
select @redirected_publisher = redirected_publisher
from MSredirected_publishers
where upper(original_publisher) = upper(rtrim(@original_publisher))
and publisher_db = rtrim(@publisher_db)

if @redirected_publisher is null
begin
raiserror(21871, 10, 0, @original_publisher, @publisher_db);
return 0
end

-- Verify that the original publisher is a SQL Server publisher of this
-- distributor.
--
if not exists (
select name from msdb.dbo.MSdistpublishers
where upper(rtrim(@original_publisher)) = upper(name) collate database_default
and N'MSSQLSERVER' = upper(publisher_type))
begin
raiserror (21876, 16, -1, @@servername, @original_publisher, @publisher_db)
return 1
end

-- Verify that there is a sysservers entry for the original publisher
-- at the distributor.
--
if not exists (
select srvid
from master..sysservers
where upper(srvname) collate database_default = upper(rtrim(@original_publisher)))
begin
raiserror (21877, 16, -1, @original_publisher, @publisher_db)
return 1
end

-- Create a linked server to connect to the target of redirection.
--
exec @retcode = sys.sp_hadr_create_linked_server @original_publisher, @publisher_db,
@redirected_publisher, @publisher_linked_server output, @use_caller_credentials
if @retcode <> 0
begin
return 1
end

-- Query the availability group primary for the names of the member replicas associated
-- with the publisher database and validate the host server for each replica as a
-- replication publisher.
--
exec @retcode = sys.sp_hadr_validate_replica_hosts_as_publishers @original_publisher,
@redirected_publisher, @publisher_db, @publisher_linked_server

return @retcode
end

sp_vupgrade_mergeobjects (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_vupgrade_mergeobjects(nvarchar @login
, nvarchar @password
, bit @security_mode)

MetaData:

   
create procedure sys.sp_vupgrade_mergeobjects( @login sysname = NULL, @password sysname = N'', @security_mode bit = 1)
as
begin
declare @qual_source_object nvarchar(540),
@artnick int,
@objid int,
@pubid uniqueidentifier,
@artid uniqueidentifier,
@retcode int,
@source_object sysname,
@source_owner sysname

declare @publication_number smallint
declare @partition_id_eval_proc sysname
declare @pubidstr sysname



-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-- verify input parameters (1,2.3)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-- 1. don't upgrade system databases and distribution databases
if db_name() in (N'master' COLLATE DATABASE_DEFAULT,
N'tempdb' COLLATE DATABASE_DEFAULT,
N'msdb' COLLATE DATABASE_DEFAULT,
N'model' COLLATE DATABASE_DEFAULT)
or sys.fn_MSrepl_isdistdb (db_name()) = 1
or databasepropertyex(db_name(), 'Updateability') <> 'READ_WRITE'
return 1

-- 2. Security Check: require sysadmin
IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)
BEGIN
RAISERROR(21089,16,-1)
RETURN (1)
END

-- 3. Check to ensure a login is provided if security mode is SQL Server authentication.
select @login = rtrim(ltrim(isnull(@login, '')))
if @security_mode = 0 and @login = ''
begin
-- '@login cannot be null or empty when @security_mode is set to 0 (SQL Server authentication).'
raiserror(21694, 16, -1, '@login', '@security_mode')
return 1
end

-- 4. Only upgrade merge databases
if( object_id('dbo.sysmergearticles') is NULL)
return 1

begin tran
save tran vupgrade_mergeobjects

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- Loop through each article in the database
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

-- regenerate procs that is publication-specific ( not on the article leve)
declare @pubs table ( pubid uniqueidentifier) -- a list of publications that has been processed
declare @snapshot_ready tinyint

select @artnick = min(nickname) from dbo.sysmergearticles
while @artnick is not null
begin

select @objid = NULL
select @source_object = NULL
select top 1 @objid = objid, @artid = artid, @pubid = pubid from dbo.sysmergearticles where nickname = @artnick
select @source_owner = schema_name(schema_id), @source_object = name from sys.objects where object_id = @objid
if @objid is NULL or @source_object is NULL
goto error

-- don't regenerate objects if snapshot has not been run
select @snapshot_ready = snapshot_ready from dbo.sysmergepublications where pubid = @pubid
if @snapshot_ready=0
goto nextarticle

-- should we drop trigger before disable triggers?
exec @retcode = sys.sp_MSdroparticletriggers @source_object, @source_owner
if @retcode<>0 or @@error<>0
goto error

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- Step 1: disable DML for all articles in this database
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
exec sys.sp_MScreatedisabledmltrigger @source_object, @source_owner
if @retcode<>0 or @@error<>0
goto error

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- Step 2: regenerate triggers and procs
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

select @qual_source_object = QUOTENAME(@source_owner) + N'.' + QUOTENAME(@source_object)
exec sys.sp_MSResetTriggerProcs @qual_source_object, @pubid, 1, 1 -- article level reset, regenerate sub procs


-- if this publication has not been processed for regenerating the publication-level objects
if not exists (select pubid from @pubs where pubid = @pubid)
begin
declare @use_partition_groups smallint
select @use_partition_groups = use_partition_groups from dbo.sysmergepublications where pubid = @pubid
if (@use_partition_groups > 0) -- only do this if we use partition groups
begin
exec @retcode = sys.sp_MSsetup_publication_for_partition_groups @pubid

-- regenerate the partitionid_eval proc, but don't change the table, by setting @upgrade = 1
exec @retcode = sys.sp_MSsetup_partition_groups_table @pubid, 1
end

-- now insert this pubid into the tracking table
insert into @pubs(pubid) values( @pubid )
end -- if not exists (select pubid from @pubs where pubid = @pubid)

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- Step 3: enable DML for all articles in this database
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

exec sys.sp_MSdropdisabledmltrigger @source_object, @source_owner
if @retcode<>0 or @@error<>0
goto error

nextarticle:
-- find next article
select @artnick = min(nickname) from dbo.sysmergearticles where nickname > @artnick
end -- end article while

commit tran

return (0)

error:
rollback tran vupgrade_mergeobjects
commit tran
return (1)
end

sp_views_rowset2 (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_views_rowset2(nvarchar @view_schema)

MetaData:

   
create procedure sys.sp_views_rowset2
(
@view_schema sysname = null
)
as
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-- copy & pasted from version 1 of the SProc and removed checks for 1st parameter !
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
select
TABLE_CATALOG = db_name(),
TABLE_SCHEMA = schema_name(a_v.schema_id),
TABLE_NAME = a_v.name,
VIEW_DEFINITION = convert(nvarchar(1),null),
CHECK_OPTION = convert(bit, NULL),
IS_UPDATABLE = convert(bit, NULL),
DESCRIPTION = convert(nvarchar(1),null),
DATE_CREATED = a_v.create_date,
DATE_MODIFIED = convert(datetime,null)
from
sys.all_views a_v
where
(@view_schema is null or schema_id(@view_schema) = a_v.schema_id) and
has_perms_by_name(quotename(schema_name(a_v.schema_id)) + '.' + quotename(a_v.name),
'object',
'select') = 1
order by 1, 2, 3

sp_views_rowset (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_views_rowset(nvarchar @view_name
, nvarchar @view_schema)

MetaData:

   
create procedure sys.sp_views_rowset
(
@view_name sysname,
@view_schema sysname = null
)
as
select
TABLE_CATALOG = db_name(),
TABLE_SCHEMA = schema_name(a_v.schema_id),
TABLE_NAME = a_v.name,
VIEW_DEFINITION = convert(nvarchar(1),null),
CHECK_OPTION = convert(bit, NULL),
IS_UPDATABLE = convert(bit, NULL),
DESCRIPTION = convert(nvarchar(1),null),
DATE_CREATED = a_v.create_date,
DATE_MODIFIED = convert(datetime,null)
from
sys.all_views a_v
where
(
(@view_schema is null and a_v.name = @view_name) or
object_id(quotename(@view_schema) + '.' + quotename(@view_name)) = a_v.object_id
) and
has_perms_by_name(quotename(schema_name(a_v.schema_id)) + '.' + quotename(@view_name),
'object',
'select') = 1
order by 1, 2, 3

sp_verifypublisher (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_verifypublisher(nvarchar @publisher)

MetaData:

   
--
-- Name:
-- sp_verifypublisher
--
-- Description:
-- Verify that publisher state is valid for HREPL
--
-- Returns:
-- 0 == Valid
-- 1 == Error in publisher state
--
-- Security:
-- Internal
--
-- Notes:
-- Used by any routine that wants to validate
-- the state of the HREPL publisher before
-- performing any actions against it.
-- Errors are issued at warning level to allow
-- the calling proc to decide how to handle it.
-- For example, drop procedures would typically
-- choose to ignore the errors since everything
-- is being dropped. Add procedures would tend
-- to error out immediately.
--

CREATE PROCEDURE sys.sp_verifypublisher
(
@publisher sysname
)
AS
BEGIN
DECLARE @retcode int
DECLARE @publisher_type sysname
DECLARE @cmd nvarchar(4000)

-- -- -- -- security check, db_owner
exec @retcode = dbo.sp_MSreplcheck_publish
if @@ERROR <> 0 or @retcode <> 0
return(1)

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

IF @retcode <> 0
RETURN (@retcode)

-- Execute provider-specific verification
IF UPPER(@publisher_type) IN ('ORACLE', 'ORACLE GATEWAY')
BEGIN
SET @publisher = UPPER(@publisher) COLLATE DATABASE_DEFAULT
SELECT @cmd = @cmd + 'sys.sp_ORAverifypublisher'
EXEC @retcode = @cmd @publisher
END
ELSE
BEGIN
RAISERROR(21645, 16, -1, @publisher_type)
SET @retcode = 1
END

return @retcode
END

sp_validname (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_validname(bit @raise_error
, nvarchar @name)

MetaData:

   
-- -- -- -- -- -- -- -- -- -- -- -- -- -- sp_validname -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
CREATE PROCEDURE sys.sp_validname
@name sysname,
@raise_error bit = 1
AS
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-- Check for valid SQL-Server identifiers: All non-binary-zero
-- characters are valid. String must not be null or empty.
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-- QuoteName() will be non-null only if no null-characters. Also check length.
IF (quotename(@name) is not null AND datalength(@name) > 0)
RETURN (0)

-- Bad Name
if @raise_error = 1
raiserror (15004,-1,-1)
return (1) -- sp_validname

sp_validlang (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_validlang(nvarchar @name)

MetaData:

   
-- -- -- -- -- -- -- -- -- -- -- -- -- -- sp_validlang -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
CREATE PROCEDURE sys.sp_validlang
@name sysname
AS

-- Check to see if this language is in Syslanguages.
if exists (select * from sys.syslanguages where name = @name or alias = @name)
return(0)

raiserror(15033,-1,-1,@name)
return (1) -- sp_validlang

sp_validatemergesubscription (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_validatemergesubscription(nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db
, tinyint @level)

MetaData:

   
create procedure sys.sp_validatemergesubscription
(@publication sysname,
@subscriber sysname,
@subscriber_db sysname,
@level tinyint
) AS

set nocount on
declare @retcode int
declare @pubid uniqueidentifier
declare @subid uniqueidentifier
--
-- Security Check
--
EXEC @retcode = sys.sp_MSreplcheck_publish
IF @@ERROR <> 0 or @retcode <> 0
return (1)

-- make sure current database is enabled for merge replication --
exec @retcode=sys.sp_MSCheckmergereplication
if @@ERROR<>0 or @retcode<>0
return (1)

select @pubid = pubid from dbo.sysmergepublications where name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name()
if @pubid is NULL
begin
raiserror (20026, 16, -1, @publication)
return (1)
end
if @level <1 or @level > 3
begin
raiserror(21184, 16, -1, '@level', '1', '2','3')
return (1)
end

select @subid = NULL
select @subid = subid from dbo.sysmergesubscriptions where pubid=@pubid and db_name=@subscriber_db and
UPPER(subscriber_server) collate database_default = UPPER(@subscriber) collate database_default
if @subid is NULL
begin
raiserror(14055, 16, -1)
return (1)
end


update dbo.MSmerge_replinfo
set validation_level=@level
where repid = @subid
if @@ERROR<>0
return (1)
return (0)

sp_validatemergepullsubscription (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_validatemergepullsubscription(nvarchar @publication
, nvarchar @publisher
, nvarchar @publisher_db
, tinyint @level)

MetaData:

 create procedure sys.sp_validatemergepullsubscription  
(@publication sysname,
@publisher sysname,
@publisher_db sysname,
@level tinyint
) AS

set nocount on
declare @retcode int
declare @pubid uniqueidentifier
declare @subscriber sysname
declare @subscriber_db sysname
declare @subid uniqueidentifier
--
-- Security Check
--
EXEC @retcode = sys.sp_MSreplcheck_publish
IF @@ERROR <> 0 or @retcode <> 0
return (1)

select @subscriber = @@SERVERNAME, @subscriber_db=db_name()
select @pubid = NULL
select @subid = NULL

select @pubid=pubid from dbo.sysmergepublications where LOWER(publisher)=LOWER(@publisher) and publisher_db=@publisher_db and name=@publication
if @pubid is NULL
begin
raiserror (20026, 16, -1, @publication)
return (1)
end

if @level <0 or @level > 3
begin
raiserror(21184, 16, -1, '@level', '1', '2','3')
return (1)
end

select @subid = subid from dbo.sysmergesubscriptions where pubid=@pubid and db_name=@subscriber_db and LOWER(subscriber_server) = LOWER(@subscriber)
if @subid is NULL
begin
raiserror(14055, 16, -1)
return (1)
end

update dbo.MSmerge_replinfo
set validation_level=@level
where repid = @subid
if @@ERROR<>0
return (1)
return (0)

sp_validatemergepublication (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_validatemergepublication(nvarchar @publication
, tinyint @level)

MetaData:

   
create procedure sys.sp_validatemergepublication
(@publication sysname,
@level tinyint
) AS

set nocount on
declare @artid uniqueidentifier
declare @schematype int
declare @schemaversion int
declare @schemaguid uniqueidentifier
declare @schematext nvarchar
declare @retcode int
declare @pubid uniqueidentifier
declare @compatlevel int

--
-- Security Check
--
EXEC @retcode = sys.sp_MSreplcheck_publish
IF @@ERROR <> 0 or @retcode <> 0
return (1)

-- make sure current database is enabled for merge replication --
exec @retcode=sys.sp_MSCheckmergereplication
if @@ERROR<>0 or @retcode<>0
return (1)

select @pubid = pubid, @compatlevel = backward_comp_level from dbo.sysmergepublications where name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name()
if @pubid is NULL
begin
raiserror (20026, 16, -1, @publication)
return (1)
end
if @level <1 or @level > 3
begin
raiserror(21184, 16, -1, '@level', '1', '2','3')
return (1)
end

if @compatlevel < 30
begin
raiserror(21356, 10, -1, @publication)
exec @retcode=sys.sp_MSBumpupCompLevel @pubid, 30
if @@ERROR<>0 or @retcode<>0
return (1)
end

select @schemaversion = schemaversion from dbo.sysmergeschemachange
if (@schemaversion is NULL)
set @schemaversion = 1
else
select @schemaversion = 1 + max(schemaversion) from dbo.sysmergeschemachange
set @schemaguid = newid()
set @artid = newid()
set @schematype = 66 -- publication wide validation --
select @schematext = convert(nvarchar, @level)
exec @retcode=sys.sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext
if @@ERROR<>0 or @retcode<>0
return (1)
return (0)

Total Pageviews