May 16, 2012

sp_MSgetsupportabilitysettings (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_MSgetsupportabilitysettings(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @server_name
, nvarchar @db_name
, nvarchar @web_server
, int @compatlevel)

MetaData:

   
create procedure sys.sp_MSgetsupportabilitysettings
(@publisher sysname,
@publisher_db sysname,
@publication sysname,
@server_name sysname = NULL, -- Replica or subscriber Server Name --
@db_name sysname = NULL, -- Replica or subscriber Database Name --
@web_server sysname = NULL, -- name of the IIS server --
@compatlevel int = 90) -- backward compatibility level, default=Sphinx

as
declare @retcode int
declare @subid uniqueidentifier
declare @pubid uniqueidentifier
declare @last_log_upload_time datetime
declare @minutes_since_last_log_upload int
declare @upload_interval_in_minutes int
declare @upload_requested int

select @publisher_db = RTRIM(@publisher_db)
select @db_name = RTRIM(@db_name)

--
-- Security Check and publication validation
--
exec @retcode = sys.sp_MSmerge_validate_publication_presence @publication, @publisher_db, @publisher, @pubid output
if @retcode <> 0 or @@error <> 0
return 1

if (@server_name is NULL)
SET @server_name = publishingservername()

if (@db_name is NULL)
set @db_name = db_name()

SELECT @subid = subid
FROM dbo.sysmergesubscriptions
WHERE UPPER(subscriber_server) collate database_default = UPPER(@server_name) collate database_default
and db_name = @db_name and pubid = @pubid

if @subid is NULL
begin
RAISERROR(20021, 16, -1)
return (1)
end

set @upload_requested = 0

SELECT @upload_interval_in_minutes = upload_interval,
@last_log_upload_time = last_log_upload_time
from dbo.MSmerge_supportability_settings
WHERE pubid = @pubid and subid = @subid and
((@web_server IS NULL and web_server IS NULL) or (@web_server IS NOT NULL and
UPPER(web_server) collate database_default = UPPER(@web_server) collate database_default ))

if @upload_interval_in_minutes = 0
set @upload_requested = 1
else if @last_log_upload_time is not null
begin
select @minutes_since_last_log_upload = datediff(mi, @last_log_upload_time, getdate())
if @minutes_since_last_log_upload > @upload_interval_in_minutes
set @upload_requested = 1
end

select top 1 support_options, log_severity, log_modules, log_file_path, log_file_name, log_file_size, no_of_log_files, @upload_requested, delete_after_upload, custom_script, message_pattern,
agent_xe, agent_xe_ring_buffer, sql_xe
from dbo.MSmerge_supportability_settings
WHERE pubid = @pubid and subid = @subid and
((@web_server IS NULL and web_server IS NULL) or (@web_server IS NOT NULL and
UPPER(web_server) collate database_default = UPPER(@web_server) collate database_default ))
return (0)

No comments:

Post a Comment

Total Pageviews