April 25, 2012

sp_help_agent_default (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_help_agent_default(int @agent_type)

MetaData:

 create procedure sys.sp_help_agent_default (  
@profile_id int OUTPUT,
@agent_type int
)
as
begin
set nocount on

-- Security Check: Must be sysadmin or the replmonitor
if isnull(is_member(N'replmonitor'),0) = 0 and isnull(is_srvrolemember(N'sysadmin'),0) = 0
begin
RAISERROR(14260,16,-1)
return 1
end

if @agent_type not in (1, 2, 3, 4, 9)
BEGIN
RAISERROR(20058, 16, -1)
return (1)
END

select @profile_id = profile_id
from msdb.dbo.MSagent_profiles
where agent_type = @agent_type
and def_profile = 1
end

sp_help_spatial_geography_histogram (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_help_spatial_geography_histogram(nvarchar @colname
, int @resolution
, float @sample
, nvarchar @tabname)

MetaData:

 CREATE PROC sys.sp_help_spatial_geography_histogram  
(
@tabname SYSNAME,
@colname SYSNAME,
@resolution INT,
@sample FLOAT = 100
)
AS
BEGIN
-- Check to see that the object names are local to the current database.
DECLARE @dbname SYSNAME = parsename(@tabname,3)
IF @dbname is null
SELECT @dbname = db_name()
ELSE IF @dbname <> db_name()
BEGIN
raiserror(15250,-1,-1)
return (1)
END

-- Check to see if the TABLE exists
DECLARE @objid int = object_id(@tabname);
IF @objid is NULL
BEGIN
raiserror(15009,-1,-1,@tabname,@dbname)
return (1)
END

declare @quoted_tabname nvarchar(max) = QUOTENAME(@dbname, N']') + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(@objid), N']') + N'.' + QUOTENAME(OBJECT_NAME(@objid), N']');

-- Check to see if the geography COLUMN exists
DECLARE @columns INT = (select COUNT(*) from sys.columns where object_id = @objid and name = @colname and system_type_id = 240 and user_type_id = 130);
IF @columns <> 1
BEGIN
raiserror(15148,-1,-1,@colname)
return (1)
END

-- Check to see if the RESOLUTION is valid
IF @resolution < 10
BEGIN
SET @resolution = 10;
END
IF @resolution > 5000
BEGIN
SET @resolution = 5000;
END

DECLARE @tablesample varchar(max) = N'';
IF @sample <> 100
BEGIN
SET @tablesample = N'TABLESAMPLE (' + cast(@sample as nvarchar) + N' PERCENT)';
END

-- Run the query
DECLARE @query nvarchar(max) = N'SELECT a.id AS CellId, geography::STGeomFromWKB(a.WKB, 4326) AS Cell, COUNT(*) AS IntersectionCount FROM ' + @quoted_tabname + N' ' + @tablesample +
N' CROSS APPLY sys.GeodeticGridCoverage(' + @colname + N',' + cast(@resolution as nvarchar) + N',' + cast(@resolution as nvarchar) + N') a GROUP BY a.id, a.WKB';
exec(@query);
END

sp_get_mergepublishedarticleproperties (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_get_mergepublishedarticleproperties(nvarchar @source_object
, nvarchar @source_owner)

MetaData:

 create procedure sys.sp_get_mergepublishedarticleproperties (  
@source_object sysname = NULL, -- The name of the object --
@source_owner sysname = NULL -- The name of the owner of the object --
) AS

SET NOCOUNT ON

declare @retcode int
--
-- Check for dbo permission
--
exec @retcode=sys.sp_MSreplcheck_subscribe
if @retcode<>0 or @@ERROR<>0
return (1)


--
-- Declarations.
--

declare @objid int
declare @partition_options tinyint

IF object_id('sysmergearticles') is NULL
RETURN (0)

if @source_object IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@source_object', 'sp_get_mergepublishedarticleproperties')
RETURN (1)
END

if @source_owner IS NULL
select @objid = object_id(quotename(@source_object))
else
select @objid = object_id(quotename(@source_owner) + '.' + quotename(@source_object))

if @objid is NULL
RETURN(0)

if object_id('sysmergepartitioninfo') is not NULL
begin
select top 1 @partition_options = smpi.partition_options
from dbo.sysmergepartitioninfo as smpi join sysmergearticles as sma
on sma.artid = smpi.artid
and sma.pubid = smpi.pubid
where @objid = sma.objid
order by sma.pubid
end

select top 1
column_tracking as column_tracking,
schema_option as schema_option,
vertical_partition as vertical_partition,
identity_support as identity_support,
upload_options as subscriber_upload_options,
well_partitioned_lightweight as well_partitioned_lightweight,
delete_tracking as delete_tracking,
compensate_for_errors as compensate_for_errors,
pub_range as pub_range,
range as range,
threshold as threshold,
@partition_options as partition_options,
stream_blob_columns as stream_blob_columns
from dbo.sysmergearticles
where @objid = objid
order by pubid

RETURN (0)

sp_grant_publication_access (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_grant_publication_access(nvarchar @publication
, nvarchar @login
, nvarchar @reserved
, nvarchar @publisher)

MetaData:

 create procedure sys.sp_grant_publication_access  
(
@publication sysname,
@login sysname,
@reserved nvarchar(10) = NULL, -- 'init'
@publisher sysname = NULL
)
AS
BEGIN
set nocount on

-- This stored procedure can be called repeatedly.
DECLARE @distribdb sysname,
@distproc nvarchar (300),
@retcode int,
@dist_rpcname sysname,
@database sysname,
@publisher_type sysname,
@loc_publisher sysname,
@role sysname,
@pubid uniqueidentifier,
@pubidtran int,
@pubidstr nvarchar(40),
@user sysname,
@fmergepub bit,
@ftranpub bit,
@skip bit,
@login2 sysname,
@login_id int,
@sid varbinary(85),
@db_name sysname,
@isloginsysadmin int,
@issqlpublisher bit,
@version int,
@versionyukon int,
@initinfo nvarchar(max)

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

-- Set publisher name if not supplied
select @loc_publisher = case when (@publisher is null) then publishingservername()
else @publisher end

-- Get publisher info
EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @publisher = @loc_publisher,
@rpcsrvname = @dist_rpcname OUTPUT,
@distribdb = @distribdb OUTPUT,
@publisher_type = @publisher_type OUTPUT,
@version = @version OUTPUT
IF @@error <> 0
BEGIN
RAISERROR (14071, 16, -1)
return (1)
END

IF @retcode <> 0 OR @distribdb IS NULL OR @dist_rpcname IS NULL
BEGIN
RAISERROR (14071, 16, -1)
return(1)
END

-- Restrict specification of publisher to HREPL only
select @issqlpublisher = case when (@publisher_type = N'MSSQLSERVER') then 1 else 0 end
IF @issqlpublisher = 1 AND @publisher IS NOT NULL
BEGIN
RAISERROR(21606, 16, -1, '@publisher', 'NULL')
RETURN (1)
END
--
-- Use current context if @login is null
-- NOTE: if the login is provisioned (no explicitly created
-- using CREATE LOGIN since it may be a member of GROUP) then
-- 1)suser_id(@login) will return NULL - the login does not
-- have any explicit entry in master.dbo.syslogins
-- 2)suser_id() will map to login token information
--
if @login is null
begin
-- if the current user is a member of a NT group and
-- NT group has access then is_srvrolemember()
-- and suser_id() will not work properly unless they
-- are invoked for current user
select @login = suser_name()
,@isloginsysadmin = is_srvrolemember(N'sysadmin')
,@login_id = suser_id()
end
else
begin
select @isloginsysadmin = is_srvrolemember(N'sysadmin',@login)
,@login_id = suser_id(@login)
end
-- Initialize
-- Set db context to distributor if HREPL
select @database = case when (@issqlpublisher = 1) then db_name() else @distribdb end
,@sid = suser_sid(@login,0)
,@fmergepub = 0
,@ftranpub = 0
,@skip = case when (@reserved = N'init') then 1 else 0 end
,@versionyukon = 0x900048B
--
-- We should always have a valid SID for any kind of login (provisioned or not)
-- If there is not valid SID then it is a bad login
--
if (@sid is null)
begin
raiserror(15007, 16, 1, @login)
return (1)
end
-- Existance check of the publication will be done in sp_MSpublication_access
-- Note, even if the login exists, it may or may not has access
-- to the server (granted or denied).
if @skip = 0
begin
-- Check if this database is published
IF sys.fn_MSrepl_ispublished(@database) != 1
BEGIN
-- The database is not published.
RAISERROR (18757, 16, -1)
RETURN 1
END
--
-- Permission check for CONNECT SQL
-- Skip this check when login_id is null
--
if @login_id is not null and
(exists (select * from sys.server_permissions where
class = 100 and major_id = 0 and minor_id = 0 and type = 'COSQ' and
grantee_principal_id = @login_id and state = 'D') or
not exists (select * from sys.server_permissions where
class = 100 and major_id = 0 and minor_id = 0 and type = 'COSQ' and
grantee_principal_id = @login_id and state in ('G','W')))
begin
raiserror(15007, 16, 2, @login)
return (1)
end

-- Don't do the user check if sysadmin since sysadmin can enter
-- the database as dbo.
if @isloginsysadmin = 0 and
not exists (select * from sys.database_principals u join sys.database_permissions p on u.principal_id = p.grantee_principal_id
where (u.sid = @sid or u.name = N'guest') and
p.class = 0 and p.major_id = 0 and p.minor_id = 0 and p.type = 'CO' and p.state in ('G','W') and
not exists (select * from sys.database_permissions d where d.class = 0 and d.major_id = 0 and d.minor_id = 0 and
d.grantee_principal_id = p.grantee_principal_id and d.type = 'CO' and d.state = 'D'))
begin
raiserror(20619, 16, -1, @login, @database)
return (1)
end
end
--
-- Get the publication id and see if what kind of publication it is
--
if object_id(N'dbo.sysmergepublications') is not NULL
begin
select @pubid = pubid from dbo.sysmergepublications where name = @publication and UPPER(publisher) = UPPER(@loc_publisher) and publisher_db = @database
if (@pubid is not null)
select @fmergepub = 1
end
if @fmergepub = 0 and object_id(N'dbo.syspublications') is not NULL
begin
if (@issqlpublisher = 1)
select @pubidtran = pubid from dbo.syspublications where name = @publication
else
select @pubidtran = pubid from sys.fn_IHgetpubid(@publication, @loc_publisher, @publisher_type)
if (@pubidtran is not null)
select @ftranpub = 1
end
--
-- publication should exist
--
if (@fmergepub = 0 and @ftranpub = 0)
begin
raiserror (20026, 16, -1, @publication)
return (1)
end
--
-- map the login to a user in the current db
--
select @user = name from sys.database_principals u where u.sid = @sid and
exists (select * from sys.database_permissions p where
p.class = 0 and p.major_id = 0 and p.minor_id = 0 and p.grantee_principal_id = u.principal_id and
p.type = 'CO' and p.state in ('G','W') and
not exists (select * from sys.database_permissions d where d.class = 0 and d.major_id = 0 and d.minor_id = 0 and
d.grantee_principal_id = p.grantee_principal_id and d.type = 'CO' and d.state = 'D'))
if @user is NULL
begin
--
-- The login is NON provisioned or
-- no explicit user mapping was found.
-- Find ways to get the user mapping
--
if @isloginsysadmin = 1
begin
--
-- the login is a NON provisioned sysadmin account
-- the user mapping is known
--
select @user = N'dbo'
end
else if @login_id is null
begin
--
-- NON provisioned non sysadmin account
-- impersonate the login and get the current user
-- there is no better way (server should have a better way)
--
select @distproc = N'EXECUTE AS LOGIN = N''' + REPLACE(@login, N'''', N'''''') + N''' '
+ N'select @p1 = user_name() '
+ N'REVERT '
exec @retcode = sys.sp_executesql @stmt = @distproc
,@params = N'@p1 sysname OUTPUT'
,@p1 = @user OUTPUT
if @@error <> 0
begin
--
-- could impersonate the non provisioned login
--
raiserror(20624, 16, 2, @login, @database)
return(1)
end
--
-- Now check if we have valid user
--
if @user is NULL
begin
--
-- the non provisioned login has no user access in the current db
--
raiserror(20624, 16, 3, @login, @database)
return(1)
end
end -- if @login_id is null
else
begin
--
-- the provisioned login has no user access in the current db
--
raiserror(20624, 16, 1, @login, @database)
return (1)
end
end
-- at this point we know that the login exists and has access to the publishing database
-- Add role if this SP is not being called from sp_addpublication/sp_addmergepublication
-- don't add role if 'init' because role cannot be added inside a transaction
if (@skip = 0)
begin
if (@ftranpub = 1)
begin
-- Tran specific processing
-- we will create just the role if needed
-- and skip fixing the the role
-- Role fixing should be explicitly done by calling sp_createtranpalrole
exec @retcode = sys.sp_MSrepl_FixTranPALRole @pubidtran, @publication, @publisher_type, @loc_publisher, @role output
end
else
begin
-- Merge specific processing
-- we will create just the role if needed
-- Fix the database role which represents the users who have access to this publication
exec @retcode = sys.sp_MSrepl_FixPALRole @pubid, @role output
end
if (@retcode<>0 or @@error <> 0)
return 1
--
-- add the user for the given login to the PAL role
-- we have to skip this in the 'init' stage because we may not have a role
-- explicitly call sp_grant_publication_access for any PAL user
-- no need to add if system dbo user
--
if @user != N'dbo'
begin
exec @retcode = sys.sp_addrolemember @role, @user
if (@retcode <> 0 or @@error <> 0)
return 1
exec @retcode = sys.sp_MSgrantconnectreplication @user
if (@retcode <> 0 or @@error <> 0)
return 1
end
end -- if (@skip = 0)
--
-- add login(s) to distributor
--
if (@skip = 1)
begin
--
-- publication init processing
-- choose all sysadmin logins + given login and prepare a info string for init
-- Skip logins that are not at the distributor without raising error during init
--
SELECT @distproc = QUOTENAME(RTRIM(@dist_rpcname)) + N'.' + QUOTENAME(RTRIM(@distribdb))
+ case when (isnull(@version,0) < @versionyukon) then N'.sys.sp_MSpublication_access'
else N'.sys.sp_MSinit_publication_access' end
,@initinfo = N''

declare #hC CURSOR LOCAL FAST_FORWARD for
select name from master.sys.syslogins
where hasaccess = 1
and (sid = @sid or sysadmin = 1)
for read only

open #hC
fetch #hC into @login2
while (@@fetch_status <> -1)
begin
if (isnull(@version,0) < @versionyukon)
begin
-- Pre-Yukon distributor
-- Call RPC to add login
EXEC @retcode = @distproc
@publisher = @loc_publisher,
@publisher_db = @database,
@publication = @publication,
@login = @login2,
@operation = N'add',
@skip = @skip
IF @@error <> 0 OR @retcode <> 0
return (1)
end
else
begin
-- Yukon or later version of distributor
-- build initinfo
select @initinfo = @initinfo + N'<item\>' + @login2 + N'</item\>'
end
fetch #hC into @login2
end
close #hC
deallocate #hC
--
-- One RPC call to Yukon or recent distributor with login initinfo
--
if (@initinfo != N'')
begin
EXEC @retcode = @distproc
@publisher = @loc_publisher,
@publisher_db = @database,
@publication = @publication,
@initinfo = @initinfo,
@skip = @skip
IF @@error <> 0 OR @retcode <> 0
return (1)
end
end
else
begin
--
-- choose given login for regular processing
--
SELECT @distproc = QUOTENAME(@dist_rpcname) + N'.' + QUOTENAME(@distribdb)
+ N'.sys.sp_MSpublication_access'
EXEC @retcode = @distproc
@publisher = @loc_publisher,
@publisher_db = @database,
@publication = @publication,
@login = @login,
@operation = N'add',
@skip = @skip
IF @@error <> 0 OR @retcode <> 0
return (1)
end
--
-- all done
--
return 0
END

sp_get_Oracle_publisher_metadata (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_get_Oracle_publisher_metadata(nvarchar @database_name)

MetaData:

   
--
-- Name:
-- sp_get_Oracle_publisher_metadata
--
-- Description:
-- Specialized stored procedure for repldiag tool to gather
-- Oracle publishing meta data from all Oracle publishers
-- hosted at this distribution database
--
-- Arguments:
-- @database_name -- Name of database where meta data for publishers is to be deposited.
--
-- Security:
-- 'sysadmin'
-- Requires Certificate signature for catalog access
--
-- Owner:
-- sward

create procedure sys.sp_get_Oracle_publisher_metadata
(
@database_name sysname
)
AS
BEGIN
set nocount on

DECLARE @cmd nvarchar(4000)
DECLARE @retcode int
DECLARE @distbit int
DECLARE @publisher_type sysname
DECLARE @publisher sysname
DECLARE @quoteddb sysname
DECLARE @database sysname
DECLARE @nologgingtable int

CREATE TABLE #MetadataCaptureLog
(
ID int IDENTITY,
MsgType nvarchar(100),
MsgTypeID int,
Time DateTime,
Message nvarchar(max)
)

-- Error if not sysadmin
IF NOT (is_srvrolemember('sysadmin') = 1)
BEGIN
RAISERROR('The stored procedure sp_get_Oracle_publisher_metadata may only be run by members of the fixed server role ''sysadmin''.',10,-1)
RETURN(1)
END

SELECT @nologgingtable = 0
SELECT @distbit = 16
SELECT @database = sys.fn_replquotename(@database_name, '''') collate database_default
SELECT @quoteddb = sys.fn_replquotename(@database_name, default) collate database_default

-- Verify that database to be used as repository exists
IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE name = @database_name)
BEGIN
RAISERROR('The database %s to be used as a repository for Oracle publishing meta data does not exist.',10,-1, @database)
RETURN(1)
END

-- Verify that the database to be used as a repository is not a distribution database.
-- This is required to prevent the possibility of overwriting meta data on the running
-- system.
IF EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE name = @database_name AND (category & @distbit) = @distbit )
BEGIN
RAISERROR('The database %s to be used as a repository for Oracle publishing meta data is a distribution database. This is not allowed. Choose another database as the repository.',10,-1, @database)
RETURN(1)
END

SELECT @cmd = N'SELECT * FROM ' + @quoteddb + N'.dbo.MetadataCapture_Log WHERE 0 = 1'

BEGIN TRY
EXEC @retcode = sys.sp_executesql @cmd
END TRY
BEGIN CATCH
-- If table doesn't exist, set nologgingtable flag
SELECT @nologgingtable = 1
END CATCH

IF (@nologgingtable = 1)
BEGIN
SELECT @cmd = N'SELECT * INTO ' + @quoteddb + N'.dbo.MetadataCapture_Log FROM #MetadataCaptureLog'

EXEC @retcode = sys.sp_executesql @cmd

IF @retcode != 0 OR @@error != 0
BEGIN
RETURN(1)
END

EXEC sys.sp_log_repository_message N'Success', @cmd, @database_name
END

DROP TABLE #MetadataCaptureLog

DECLARE hC CURSOR LOCAL FAST_FORWARD FOR
SELECT srvname from master.dbo.sysservers sys, msdb.dbo.MSdistpublishers pub
WHERE (sys.srvproduct = 'Oracle Gateway Replication' OR srvproduct = 'Oracle Replication')
AND pub.publisher_type LIKE 'ORACLE%'
AND UPPER(sys.srvname collate database_default) = UPPER(pub.name collate database_default)

OPEN hC
FETCH hC INTO @publisher
WHILE (@@fetch_status <> -1)
BEGIN

SET @retcode = 0

EXEC @retcode = sys.sp_MSrepl_getpublisherinfo
@publisher = @publisher,
@publisher_type = @publisher_type OUTPUT,
@rpcheader = @cmd OUTPUT,
@skipSecurityCheck = 1

IF @retcode = 0
BEGIN

SET @publisher = UPPER(@publisher) COLLATE DATABASE_DEFAULT
SET @cmd = @cmd + N'sys.sp_MSrepl_get_Oracle_publisher_metadata'
EXEC @retcode = @cmd
@publisher,
@database_name
END

FETCH hC INTO @publisher
END
CLOSE hC
DEALLOCATE hC

RETURN
END

sp_help_spatial_geometry_histogram (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_help_spatial_geometry_histogram(nvarchar @tabname
, nvarchar @colname
, int @resolution
, float @xmin
, float @ymin
, float @xmax
, float @ymax
, float @sample)

MetaData:

 CREATE PROC sys.sp_help_spatial_geometry_histogram  
(
@tabname SYSNAME,
@colname SYSNAME,
@resolution INT,
@xmin FLOAT(53),
@ymin FLOAT(53),
@xmax FLOAT(53),
@ymax FLOAT(53),
@sample FLOAT = 100
)
AS
BEGIN
-- Check to see that the object names are local to the current database.
DECLARE @dbname SYSNAME = parsename(@tabname,3)
IF @dbname is null
SELECT @dbname = db_name()
ELSE IF @dbname <> db_name()
BEGIN
raiserror(15250,-1,-1)
return (1)
END

-- Check to see if the TABLE exists
DECLARE @objid int = object_id(@tabname);
IF @objid is NULL
BEGIN
raiserror(15009,-1,-1,@tabname,@dbname)
return (1)
END

declare @quoted_tabname nvarchar(max) = QUOTENAME(@dbname, N']') + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(@objid), N']') + N'.' + QUOTENAME(OBJECT_NAME(@objid), N']');

-- Check to see if the COLUMN exists
DECLARE @columns INT = (select COUNT(*) from sys.columns where object_id = @objid and name = @colname and system_type_id = 240 and user_type_id = 129);
IF @columns <> 1
BEGIN
raiserror(15148,-1,-1,@colname)
return (1)
END

-- Check to see if the RESOLUTION is valid
IF @resolution < 10
BEGIN
SET @resolution = 10;
END
IF @resolution > 5000
BEGIN
SET @resolution = 5000;
END

-- Check to see if the BOUNDING BOX is valid
DECLARE @bb_query nvarchar(max) = N'DECLARE @a int; SELECT @a = id FROM sys.PlanarGridCoverage(NULL, ' + convert(nvarchar, @xmin, 2) + N',' + convert(nvarchar, @ymin, 2) + N','
+ convert(nvarchar, @xmax, 2) + N',' + convert(nvarchar, @ymax, 2) + N',' + cast(@resolution as nvarchar) + N',' + cast(@resolution as nvarchar) + N')';
exec(@bb_query);

DECLARE @tablesample nvarchar(max) = '';
IF @sample <> 100
BEGIN
SET @tablesample = N'TABLESAMPLE (' + cast(@sample as nvarchar) + N' PERCENT)';
END

-- Run the query
DECLARE @query nvarchar(max) = N'SELECT a.id AS CellId, geometry::STGeomFromWKB(a.WKB, 0) AS Cell, COUNT(*) AS IntersectionCount FROM ' + @quoted_tabname + N' ' + @tablesample +
N' CROSS APPLY sys.PlanarGridCoverage(' + @colname + N',' + convert(nvarchar, @xmin, 2) + N',' + convert(nvarchar, @ymin, 2) + N',' + convert(nvarchar, @xmax, 2) + N',' + convert(nvarchar, @ymax, 2) +
N',' + cast(@resolution as nvarchar) + N',' + cast(@resolution as nvarchar) + N') a GROUP BY a.id, a.WKB';
exec(@query);
END

sp_help_spatial_geography_index_xml (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_help_spatial_geography_index_xml(nvarchar @tabname
, nvarchar @indexname
, tinyint @verboseoutput
, hierarchyid @query_sample)

MetaData:

 CREATE PROC sys.sp_help_spatial_geography_index_xml  
(
@tabname NVARCHAR(776), -- the TABLE to check for indexes
@indexname SYSNAME, -- the INDEX name
@verboseoutput TINYINT, -- OUTPUT all properties
@query_sample GEOGRAPHY, -- query window object
@xml_output XML OUTPUT -- XML variable to OUTPUT
)
AS
BEGIN
EXEC sys.sp_help_spatial_geography_index_helper @tabname, @indexname, 1, @xml_output OUTPUT, @verboseoutput, @query_sample
END

sp_help_spatial_geography_index (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_help_spatial_geography_index(nvarchar @tabname
, nvarchar @indexname
, tinyint @verboseoutput
, hierarchyid @query_sample)

MetaData:

 CREATE PROC sys.sp_help_spatial_geography_index  
(
@tabname NVARCHAR(776), -- the TABLE to check for indexes
@indexname SYSNAME, -- the INDEX name
@verboseoutput TINYINT, -- OUTPUT all properties
@query_sample GEOGRAPHY -- query window object
)
AS
BEGIN
EXEC sys.sp_help_spatial_geography_index_helper @tabname, @indexname, 0, null, @verboseoutput, @query_sample
END

sp_help_publication_access (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_help_publication_access(nvarchar @publication
, bit @return_granted
, nvarchar @login
, bit @initial_list
, nvarchar @publisher)

MetaData:

 create procedure sys.sp_help_publication_access  
(
@publication sysname,
@return_granted bit = 1,
@login sysname = N'%',
@initial_list bit = 0,
@publisher sysname = NULL
)
AS
BEGIN
set nocount on
-- This stored procedure can be called repeatedly.
DECLARE @distribdb sysname
,@distproc nvarchar (300)
,@retcode int
,@dist_rpcname sysname
,@database sysname
,@publisher_type sysname
,@loc_publisher sysname

exec @retcode = sys.sp_MSreplcheck_publish
if @@ERROR <> 0 or @retcode <> 0
return(1)

-- Set publisher name if not supplied
SELECT @loc_publisher = case when (@publisher IS NULL) then publishingservername() else @publisher end

-- Get publisher info
EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @publisher = @loc_publisher,
@rpcsrvname = @dist_rpcname OUTPUT,
@distribdb = @distribdb OUTPUT,
@publisher_type = @publisher_type OUTPUT
IF @@error <> 0
BEGIN
RAISERROR (14071, 16, -1)
return (1)
END

IF @retcode <> 0 OR @distribdb IS NULL OR @dist_rpcname IS NULL
BEGIN
RAISERROR (14071, 16, -1)
return(1)
END

-- Restrict specification of publisher to HREPL only
IF @publisher_type = N'MSSQLSERVER' AND @publisher IS NOT NULL
BEGIN
RAISERROR(21606, 16, -1, '@publisher', 'NULL')
RETURN (1)
END

-- Set db context to distributor if HREPL
SELECT @database = case when (@publisher_type = N'MSSQLSERVER') then db_name() else @distribdb end
,@distproc = QUOTENAME(RTRIM(@dist_rpcname)) + N'.' + QUOTENAME(RTRIM(@distribdb)) + N'.sys.sp_MSpublication_access'

-- Do check existense when dropping since the login might be dropped
-- outside replication already.
-- Get logins in the PAL if needed
if @initial_list = 0
begin
create table #granted (login sysname null)

insert into #granted
EXEC @retcode = @distproc
@publisher = @loc_publisher,
@publisher_db = @database,
@publication = @publication,
@operation = N'help',
@login = @login

IF @@error <> 0 OR @retcode <> 0
return (1)
end

-- Get distributor valid logins if needed
if @return_granted = 0 or @initial_list = 1
begin
create table #dist_logins(login sysname null)

insert into #dist_logins
EXEC @retcode = @distproc
@publisher = @loc_publisher,
@operation = N'get_logins'
end

if @initial_list = 1
begin
-- Get the initial list for the publication to be created
-- by the current user
-- It contains all the logins sysadmin group and the current user
-- that have valid login at the distributor.
select l.loginname, l.isntname, l.isntgroup
from master.dbo.syslogins l, #dist_logins d where
l.sid = suser_sid(d.login, 0) and
l.hasaccess = 1 and
(is_srvrolemember('sysadmin',d.login) = 1 or (l.sid = suser_sid()))
end
else if @return_granted = 0
begin
-- resultset will have list of logins
-- that are there on both distributor and publisher
-- and that currently do not have access to publication
-- and have publisher db access
select l.loginname, l.isntname, l.isntgroup
from master.dbo.syslogins l
join #dist_logins d
on l.sid = suser_sid(d.login, 0)
and l.hasaccess = 1
--
-- The login should not exist in PAL
--
and not exists (select *
from #granted g
where suser_sid(g.login, 0) = l.sid )
--
-- The login should have a user mapping in current db
-- For sysadmins - there may not be a direct user mapping
--
and (l.sysadmin = 1
or exists (select * from dbo.sysusers u
where u.sid = l.sid
and u.hasdbaccess = 1))
end
else if @return_granted = 1
begin
select l.loginname, l.isntname, l.isntgroup from master.dbo.syslogins l,
#granted g where
l.sid = suser_sid(g.login, 0)
end
END

sp_help_peerconflictdetection (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_help_peerconflictdetection(nvarchar @publication
, int @timeout)

MetaData:

 create procedure sys.sp_help_peerconflictdetection  
(
@publication sysname,
@timeout int = 60 -- seconds
)
as
begin
set NOCOUNT on

declare @retcode int
,@pubid int
,@options int
,@OPT_ENABLED_FOR_P2P int
,@OPT_ENABLED_FOR_P2P_CONFLICTDETECTION int
,@OPT_ENABLED_FOR_P2P_CONTINUE_ONCONFLICT int
,@originator_node sysname
,@originator_version int
,@originator_db sysname
,@originator_db_version int
,@originator_id int
,@conflict_retention int
,@continue_onconflict bit
,@subscriptions xml
,@sub_srvname sysname
,@sub_db sysname
,@request_id int
,@conflictdetection_enabled bit
,@elapsetime int -- in seconds
,@phase_timed_out bit
,@cursor_allocated bit
,@cursor_opened bit


select @OPT_ENABLED_FOR_P2P = 0x1
,@OPT_ENABLED_FOR_P2P_CONFLICTDETECTION = 0x8
,@OPT_ENABLED_FOR_P2P_CONTINUE_ONCONFLICT = 0x10
,@originator_version = @@microsoftversion
,@originator_db = db_name()
,@cursor_allocated = 0
,@cursor_opened = 0


-- Security Check

exec @retcode = sys.sp_MSreplcheck_publish
if @@error <> 0 or @retcode <> 0
return(1)

-- Check to see if the database has been activated for publication.

if sys.fn_MSrepl_istranpublished(@originator_db, 1) <> 1
begin
raiserror (14013, 16, -1)
return (1)
end

-- Parameter Check: @publication.
-- The @publication name cannot be NULL and must conform to the rules
-- for identifiers.

if @publication is NULL
begin
raiserror (14043, 16, -1, N'@publication', N'sp_help_peerconflictdetection')
return (1)
end

execute @retcode = sys.sp_validname @publication
if @retcode <> 0
return (1)

select @pubid = pubid, @options = options, @originator_id = originator_id, @conflict_retention = conflict_retention
from syspublications
where name = @publication

if @pubid is NULL
begin
raiserror (20026, 11, -1, @publication)
return (1)
end

select @originator_node = publishingservername()

if (@options & @OPT_ENABLED_FOR_P2P) <> @OPT_ENABLED_FOR_P2P
begin
raiserror (22808, 16, -1, @publication, N'sp_help_peerconflictdetection')
return (1)
end

-- parameter check: @timeout
if @timeout <= 0
begin
raiserror (22811, 16, -1)
return (1)
end

-- this procedure can not be executed in a user
-- transaction because it needs to be in its own tran
-- space (it can not be mixed with replicated cmds).
--
-- we use this tmp table insert to force any implicit
-- transaction (user may set IMPLICIT_TRANSACTIONS on)
declare @check_tran table(col1 int)
insert into @check_tran(col1) values (1)

if @@trancount > 0
begin
-- The procedure 'sp_help_peerconflictdetection' cannot be executed within a transaction.
raiserror(15002, 16, -1, 'sp_help_peerconflictdetection')
return (1)
end

if (@options & @OPT_ENABLED_FOR_P2P_CONFLICTDETECTION) = @OPT_ENABLED_FOR_P2P_CONFLICTDETECTION
begin
select @conflictdetection_enabled = 1

if (@options & @OPT_ENABLED_FOR_P2P_CONTINUE_ONCONFLICT) = @OPT_ENABLED_FOR_P2P_CONTINUE_ONCONFLICT
select @continue_onconflict = 1
else
select @continue_onconflict = 0
end
else
begin
select @conflictdetection_enabled = 0
select @originator_id = NULL
select @conflict_retention = NULL
select @continue_onconflict = NULL
end

exec @retcode = sys.sp_MSgetdbversion @current_version = @originator_db_version output
if @@error <> 0 or @retcode <> 0
begin
raiserror(21542, 16, 1, @@error, 'sp_MSgetdbversion')
return (1)
end

-- begin transaction
begin tran
save tran tr_sp_help_peerconflictdetection

-- obtain subscriptions
declare @peersubtab table (sub_node sysname, sub_db sysname)
insert @peersubtab
select distinct A.srvname, A.dest_db
from syssubscriptions A join sysarticles B on A.artid = B.artid
where B.pubid = @pubid and A.srvid <> -1
if @@error <> 0
goto UNDO

select @subscriptions = (select * from @peersubtab as sub for XML auto)
if @@error <> 0
goto UNDO

select @subscriptions = convert(xml, N'<peer_subs>' + convert(nvarchar(max), @subscriptions) + N'</peer_subs>')

-- in order to obtain the result in relational form, run the following statements
-- declare @DocHandle int
-- exec sp_xml_preparedocument @DocHandle OUTPUT, convert(nvarchar(max), @subscriptions)
-- select *
-- from OPENXML (@DocHandle, N'/peer_subs/sub', 1)
-- with (sub_node sysname, sub_db sysname)
-- exec sp_xml_removedocument @DocHandle

-- delete existing requests and responses
delete from MSpeer_conflictdetectionconfigrequest
if @@error <> 0
goto UNDO

delete from MSpeer_conflictdetectionconfigresponse
if @@error <> 0
goto UNDO

-- new a request
insert MSpeer_conflictdetectionconfigrequest (publication, timeout, progress_phase, phase_timed_out)
values(@publication, @timeout, N'started', 0)
if @@error <> 0
goto UNDO

select @request_id =MAX(id)
from MSpeer_conflictdetectionconfigrequest

-- insert the response from local peer
insert MSpeer_conflictdetectionconfigresponse (request_id, peer_node, peer_db, peer_version, peer_db_version,
is_peer, conflictdetection_enabled, originator_id, peer_conflict_retention, peer_continue_onconflict, peer_subscriptions, progress_phase)
values (@request_id, @originator_node, @originator_db, @originator_version, @originator_db_version, 1 -- is_peer -- ,
@conflictdetection_enabled, @originator_id, @conflict_retention, @continue_onconflict, @subscriptions, N'status collected')
if @@error <> 0
goto UNDO

-- insert sub entries of this peer
insert MSpeer_conflictdetectionconfigresponse (request_id, peer_node, peer_db, progress_phase)
select @request_id, sub_node, sub_db, N'started'
from @peersubtab
if @@error <> 0
goto UNDO

commit tran

raiserror (22817, 10, -1, N'Scanning topology', N'started') with nowait

-- starting first round: topology exploring
update MSpeer_conflictdetectionconfigrequest
set progress_phase = N'exploring topology',
modified_date = GETDATE()
where id = @request_id

exec sp_MSpeerconflictdetection_topology_sendrequest @request_id, @publication
if @@error <> 0
return (1)

select @elapsetime = 0
while (@elapsetime < @timeout)
begin
waitfor delay '00:00:01' -- 1 second
select @elapsetime = @elapsetime + 1

if exists(select * from MSpeer_conflictdetectionconfigresponse
where request_id = @request_id and is_peer is null)
continue
else
break
end


if @elapsetime >= @timeout
begin
raiserror(22812, 10, -1, N'exploring topology', @timeout) with nowait -- raise a warning

update MSpeer_conflictdetectionconfigrequest
set phase_timed_out = 1,
modified_date = GETDATE()
where id = @request_id
end

raiserror (22817, 10, -1, N'Scanning topology', N'ended') with nowait
raiserror (22817, 10, -1, N'Status collection', N'started') with nowait

-- starting second round: status collecting
update MSpeer_conflictdetectionconfigrequest
set progress_phase = N'collecting status',
modified_date = GETDATE()
where id = @request_id

exec sp_MSpeerconflictdetection_statuscollection_sendrequest @request_id, @publication
if @@error <> 0
return (1)

select @elapsetime = 0
while (@elapsetime < @timeout)
begin
waitfor delay '00:00:01' -- 1 second
select @elapsetime = @elapsetime + 1

if exists(select * from MSpeer_conflictdetectionconfigresponse
where request_id = @request_id
and (is_peer is NULL or progress_phase <> N'status collected'))
continue
else
break
end

if @elapsetime >= @timeout
begin
raiserror(22812, 10, -1, N'collecting status', @timeout) with nowait -- raise a warning
select @phase_timed_out = 1
end
else
select @phase_timed_out = 0

update MSpeer_conflictdetectionconfigrequest
set progress_phase = N'status collected',
phase_timed_out = @phase_timed_out,
modified_date = GETDATE()
where id = @request_id
if @@error <> 0
return (1)

raiserror (22817, 10, -1, N'Status collection', N'ended') with nowait

-- status report
if exists(select * from MSpeer_conflictdetectionconfigresponse
where peer_version < 0x0A000000)
begin
-- peers earlier than Katmail exist
raiserror(22813, 10, -1) with nowait
end

if (select count (distinct originator_id)
from MSpeer_originatorid_history
where originator_publication = @publication)
< (select count (originator_id)
from MSpeer_originatorid_history
where originator_publication = @publication)
begin
-- duplicate peer IDs are found
raiserror(22814, 10, -1) with nowait
end

select * from MSpeer_conflictdetectionconfigrequest
select * from MSpeer_conflictdetectionconfigresponse
select * from MSpeer_originatorid_history

return (0)

UNDO:
rollback tran tr_sp_help_peerconflictdetection
commit tran
return (1)
end

Total Pageviews