May 8, 2012

sp_MSchecksnapshotstatus (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_MSchecksnapshotstatus(nvarchar @publication)

MetaData:

 create procedure sys.sp_MSchecksnapshotstatus  
@publication sysname
AS
declare @db_name sysname
declare @retention int
declare @retention_period_unit tinyint
declare @snapshot_ready int
declare @pubid uniqueidentifier
declare @last_snapshot datetime

select @snapshot_ready = NULL
select @db_name = db_name()
select @snapshot_ready=snapshot_ready, @retention=retention, @retention_period_unit = retention_period_unit, @pubid=pubid
from dbo.sysmergepublications where name=@publication and publisher=publishingservername() and publisher_db=@db_name
if @snapshot_ready is NULL
select @snapshot_ready=snapshot_ready, @retention=retention, @retention_period_unit = retention_period_unit, @pubid=pubid
from dbo.sysmergepublications where name=@publication
if @snapshot_ready is NULL
begin
raiserror (21423, 11, -1, @publication)
return (1)
end

--
-- Check to see if current publication has permission
--
if ({ fn ISPALUSER(@pubid) } <> 1)
begin
RAISERROR (21423, 11, -1, @publication)
return (1)
end

if @snapshot_ready=1 and @retention>0
begin
select @last_snapshot=last_validated from dbo.sysmergesubscriptions where subid=@pubid
if sys.fn_add_units_to_date(@retention, @retention_period_unit, @last_snapshot) < getdate()
select @snapshot_ready=3 -- snapshot is obsolete --
end
select @snapshot_ready

No comments:

Post a Comment

Total Pageviews