April 25, 2012

sp_get_redirected_publisher (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


sys.sp_get_redirected_publisher(nvarchar @original_publisher
, nvarchar @publisher_db
, bit @bypass_publisher_validation)


-- Name: sp_get_redirected_publisher
-- Descriptions: Retrieve the redirected publisher for the specified
-- publisher database pair. If the publisher is not
-- currently redirected, return NULL.
-- For redirected publishers, if the @bypass_publisher_validation
-- parameter is 0 (default), validation checks are performed
-- to verify that the target of the redirection is a suitable
-- host for the published database. Both informational and
-- error messages can be raised by the validation stored
-- procedure. Raised errors are caught in a TRY/CATCH
-- block and the severity, error number and error message
-- are returned as output parameters.
-- Parameters: as defined in create statement
-- Returns: 0 - On both success and error; errors caught in TRY/CATCH
-- Result set:
-- redirected_publisher sysname
-- error_number int
-- error_severity int
-- error_message nvarchar(4000)
-- Security: Public procedure invoked via RPC. Check caller for db_owner
-- or in the PAL of a publication of the named pubisher.
create procedure sys.sp_get_redirected_publisher
@original_publisher sysname,
@publisher_db sysname,
@bypass_publisher_validation bit = 0
set nocount on

declare @redirected_publisher sysname,
@error_severity int,
@error_number int,
@error_message nvarchar(4000),
@dbname sysname,
@retcode int

select @redirected_publisher = null,
@error_severity = 0,
@error_number = 0,
@error_message = null,
@dbname = db_name(),
@retcode = 0

begin try

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

-- Verify input parameters are not null
if @original_publisher is null or
@publisher_db is null
raiserror (21875, 16, -1, 'sys.sp_get_redirected_publisher')

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

if (@@error <> 0) or(@retcode <> 0)
raiserror (21873, 16, -1, 'sys.sp_get_redirected_publisher')

-- If the publisher of the database has not been redirected,
-- or the @bypass_publisher_validation parameter was set to 1
-- 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 not null and
@bypass_publisher_validation = 0
-- Call the validation stored procedure to verify
-- that the target of redirection is a suitable
-- publisher for the database
exec sys.sp_validate_redirected_publisher
@redirected_publisher output

end try

begin catch

-- Set output parameters for raised error
select @error_severity = ERROR_SEVERITY(),
@error_number = ERROR_NUMBER(),
@error_message = ERROR_MESSAGE()

end catch

select @redirected_publisher as redirected_publisher,
@error_number as error_number,
@error_severity as error_severity,
@error_message as error_message

return 0

No comments:

Post a Comment

Total Pageviews