April 27, 2012

sp_helpmergelogfileswithdata (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_helpmergelogfileswithdata(nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db
, nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @web_server
, int @id)

MetaData:

 create procedure sys.sp_helpmergelogfileswithdata(  
@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 --
@id int = -1 -- id of the log file to get data for --
)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_helpmergelogfileswithdata')
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_helpmergelogfileswithdata')
RETURN (1)
END

create table #helplogfileswithdata
(
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,
log_file varbinary(max) NULL
)


insert into #helplogfileswithdata 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, mlf.log_file

FROM dbo.MSmerge_log_files mlf,
dbo.sysmergesubscriptions subs,
dbo.sysmergepublications pubs
where ( @id = -1 or @id = mlf.id)
and 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 #helplogfileswithdata

drop table #helplogfileswithdata
return @retcode

No comments:

Post a Comment

Total Pageviews