April 18, 2012

DirectoryInfo GetFiles Method returns to many files

Working with the DirectoryInfo.GetFiles method in the .Net Framework, I notised that the method, returned to many files in some setups.
Looking at the MSDN I found the following statement:

NOTE:
When using the asterisk wildcard character in a searchPattern (for example, "*.txt"), the matching behavior varies depending on the length of the specified file extension.
A searchPattern with a file extension of exactly three characters returns files with an extension of three or more characters, where the first three characters match the file extension specified in the searchPattern.
A searchPattern with a file extension of one, two, or more than three characters returns only files with extensions of exactly that length that match the file extension specified in the searchPattern.
When using the question mark wildcard character, this method returns only files that match the specified file extension.
For example, given two files in a directory, "file1.txt" and "file1.txtother", a search pattern of "file?.txt" returns only the first file, while a search pattern of "file*.txt" returns both files.

and

NOTE:
Because this method checks against file names with both the 8.3 file name format and the long file name format, a search pattern similar to "*1*.txt" may return unexpected file names. For example, using a search pattern of "*1*.txt" will return "longfilename.txt" because the equivalent 8.3 file name format would be "longf~1.txt".

Microsoft is saying that the following searchpattern is in effect:

The following list shows the behavior of different lengths for the searchPattern parameter:
"*.abc" returns files having an extension of.abc,.abcd,.abcde,.abcdef, and so on.
"*.abcd" returns only files having an extension of.abcd.
"*.abcde" returns only files having an extension of.abcde.
"*.abcdef" returns only files having an extension of.abcdef.


So I came up with these three extensions for the DirectoryInfo to work around the issue.

            /// <summary>
            /// Returns a file list from the current directory matching the given searchPattern and using a value to determine whether to search subdirectories.
            /// This is as fix for 8.3 filename standard. more information here: http://msdn.microsoft.com/en-us/library/ms143327.aspx
            /// </summary>
            /// <param name="directoryinfo">object to use.</param>
            /// <param name="searchPattern">The search string, such as "System*", used to search for all directories beginning with the word "System".</param>
            /// <param name="searchOption">One of the values of the System.IO.SearchOption enumeration that specifies whether the search operation should include only the current directory or should include all subdirectories.</param>
            /// <returns>An array of type System.IO.FileInfo.</returns>
            /// <exception cref="System.ArgumentNullException">searchPattern is null.</exception>
            /// <exception cref="System.IO.DirectoryNotFoundException">The path is invalid, such as being on an unmapped drive.</exception>
            /// <exception cref="System.Security.SecurityException">The caller does not have the required permission.</exception>
            /// <remarks>http://msdn.microsoft.com/en-us/library/ms143327.aspx</remarks>
            public static FileInfo[] GetFilesFixed(this DirectoryInfo directoryinfo, string searchPattern, SearchOption searchOption)
            {
                if (((searchPattern.Length - (searchPattern.LastIndexOf('.') + 1)) == 3) && !searchPattern.Substring(searchPattern.LastIndexOf('.')).Contains('*'))
                    return directoryinfo.GetFiles(searchPattern, searchOption).ToList().FindAll(F => F.Extension.Length == 4).ToArray();
                return directoryinfo.GetFiles(searchPattern, searchOption);
            }

            /// <summary>
            /// Returns a file list from the current directory matching the given searchPattern.
            /// This is as fix for 8.3 filename standard. more information here: http://msdn.microsoft.com/en-us/library/ms143327.aspx
            /// </summary>
            /// <param name="directoryinfo">object to use.</param>
            /// <param name="searchPattern">The search string, such as "*.txt".</param>
            /// <returns>An array of type System.IO.FileInfo.</returns>
            /// <exception cref="System.ArgumentNullException">searchPattern is null.</exception>
            /// <exception cref="System.IO.DirectoryNotFoundException">The path is invalid, such as being on an unmapped drive.</exception>
            /// <exception cref="System.Security.SecurityException">The caller does not have the required permission.</exception>
            /// <remarks>http://msdn.microsoft.com/en-us/library/ms143327.aspx</remarks>
            public static FileInfo[] GetFilesFixed(this DirectoryInfo directoryinfo, string searchPattern)
            {
                if (((searchPattern.Length - (searchPattern.LastIndexOf('.') + 1)) == 3) && !searchPattern.Substring(searchPattern.LastIndexOf('.')).Contains('*'))
                    return directoryinfo.GetFiles(searchPattern).ToList().FindAll(F => F.Extension.Length == 4).ToArray();
                return directoryinfo.GetFiles(searchPattern);
            }

            /// <summary>
            /// Retrieves an array of strongly typed System.IO.FileSystemInfo objects representing the files and subdirectories matching the specified search criteria.
            /// This is as fix for 8.3 filename standard. more information here: http://msdn.microsoft.com/en-us/library/ms143327.aspx
            /// </summary>
            /// <param name="directoryinfo">object to use.</param>
            /// <param name="searchPattern">The search string, such as "System*", used to search for all directories beginning with the word "System".</param>
            /// <returns>An array of strongly typed FileSystemInfo objects matching the search criteria.</returns>
            /// <exception cref="System.ArgumentNullException">searchPattern is null.</exception>
            /// <exception cref="System.IO.DirectoryNotFoundException">The path is invalid, such as being on an unmapped drive.</exception>
            /// <exception cref="System.Security.SecurityException">The caller does not have the required permission.</exception>
            public static FileSystemInfo[] GetFileSystemInfosFixed(this DirectoryInfo directoryinfo, string searchPattern)
            {
                if (((searchPattern.Length - (searchPattern.LastIndexOf('.') + 1)) == 3) && !searchPattern.Substring(searchPattern.LastIndexOf('.')).Contains('*'))
                    return directoryinfo.GetFileSystemInfos(searchPattern).ToList().FindAll(F => F.Extension.Length == 4).ToArray();
                return directoryinfo.GetFiles(searchPattern);
            }

sp_dropmergelogsettings (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_dropmergelogsettings(nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db
, nvarchar @web_server)

MetaData:

 create procedure sys.sp_dropmergelogsettings (  
@publication sysname = NULL, -- Publication name --
@subscriber sysname = NULL, -- Subscriber server --
@subscriber_db sysname = NULL, -- Subscription database --
@web_server sysname = NULL
) AS

declare @retcode int
declare @pubid uniqueidentifier
declare @subid uniqueidentifier
--
-- Security Check.
--
exec @retcode= sys.sp_MSreplcheck_subscribe
if @@error <> 0 or @retcode <> 0
begin
raiserror(15247,-1,-1)
return (1)
end


--
-- Check to see if current database is doing publishing/subscribing
--
IF object_id('sysmergesubscriptions') is NULL
BEGIN
RAISERROR (14055, 16, -1)
RETURN (1)
END

--
-- Parameter Check: @publication.
-- Make sure that the publication exists.
--

IF @publication IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@publication', 'sp_dropmergelogsettings')
RETURN (1)
END

select @pubid = pubid
FROM dbo.sysmergepublications
WHERE name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name()
IF @pubid IS NULL
BEGIN
RAISERROR (20026, 11, -1, @publication)
RETURN (1)
END


--
-- Parameter Check: @subscriber.
-- Check to make sure we have a valid subscriber.
--
IF @subscriber IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@subscriber', 'sp_dropmergelogsettings')
RETURN (1)
END

--
-- Check to see if you have a subscription on this publication
--
set @subid = NULL
select @subid = subid, @pubid = pubid -- identified from publication name --
from dbo.sysmergesubscriptions
where UPPER(subscriber_server) collate database_default = UPPER(@subscriber) collate database_default
and db_name = @subscriber_db

if @subid IS NULL
begin
RAISERROR (14050, 11, -1)
RETURN(1)
end

if not exists (select * 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 )))
begin
RAISERROR (20720, 16, -1, @subscriber, @subscriber_db,@web_server)
RETURN(1)
end

begin tran
save TRAN dropmergelogsettings

delete 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 @@ERROR <> 0
BEGIN
GOTO FAILURE
END

-- Get the subscriber out of supportability mode if there are no
-- entries for the subscriber with support_options turned on.
if not exists ( select * from dbo.MSmerge_supportability_settings
WHERE pubid = @pubid and subid = @subid and
support_options <> 0
)
begin
update dbo.sysmergesubscriptions
set supportability_mode = 0
where subid = @subid and pubid = @pubid

if @@ERROR <> 0
BEGIN
GOTO FAILURE
END
end

COMMIT TRAN
return (0)

FAILURE:
RAISERROR (20721, 16, -1)
-- UNDONE : This code is specific to 6.X nested transaction semantics --
if @@TRANCOUNT > 0
begin
ROLLBACK TRANSACTION dropmergelogsettings
COMMIT TRANSACTION
end
RETURN (1)

sp_dropmergefilter (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_dropmergefilter(nvarchar @publication
, nvarchar @article
, nvarchar @filtername
, bit @force_invalidate_snapshot
, bit @force_reinit_subscription)

MetaData:

 create procedure sys.sp_dropmergefilter  
@publication sysname, -- publication name --
@article sysname, -- article name --
@filtername sysname, -- Name of the table being joined to the base table --
@force_invalidate_snapshot bit = 0,
@force_reinit_subscription bit = 0
AS

set nocount on

declare @pubid uniqueidentifier
declare @artid uniqueidentifier
declare @join_objid int
declare @retcode int
declare @join_filterid int
declare @db_name sysname
declare @allow_anonymous int
declare @snapshot_ready tinyint
declare @filter_type tinyint
declare @procname nvarchar(260)
declare @quoted_procname nvarchar(260)
declare @compatlevel int
declare @automatic_reinitialization_policy bit
declare @regenerate_triggers bit
declare @got_merge_admin_applock bit

select @got_merge_admin_applock = 0

-- make sure current database is enabled for merge replication --
exec @retcode=sys.sp_MSCheckmergereplication
if @@ERROR<>0 or @retcode<>0
return (1)

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

select @regenerate_triggers = 0
--
-- Parameter Check: @publication.
-- The @publication id cannot be NULL and must conform to the rules
-- for identifiers.
--

if @publication is NULL
begin
raiserror (14003, 16, -1)
return (1)
end

--
-- Get the pubid, and check if this publication exists.
--
select @pubid = pubid,
@snapshot_ready = snapshot_ready,
@allow_anonymous = allow_anonymous,
@compatlevel = backward_comp_level,
@automatic_reinitialization_policy = automatic_reinitialization_policy
from dbo.sysmergepublications
where name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name()
if @pubid is NULL
begin
raiserror (20026, 16, -1, @publication )
return (1)
end

select @db_name = db_name from dbo.sysmergesubscriptions
where (pubid=@pubid) and (subid=@pubid)
IF @db_name <> db_name()
BEGIN
RAISERROR (20047, 16, -1)
RETURN (1)
END

--
-- Parameter Check: @article.
-- Check to see that the @article is valid, and if it exists
--
if @article is NULL
begin
raiserror (20045, 16, -1)
return (1)
end

select @artid = artid from dbo.sysmergearticles where name = @article and pubid = @pubid
if @artid is NULL
begin
raiserror (20027, 16, -1, @article)
return (1)
end

select @join_filterid = join_filterid, @filter_type = filter_type from dbo.sysmergesubsetfilters
where pubid = @pubid AND artid= @artid AND filtername=@filtername
if @join_filterid is NULL
begin
raiserror (20685, 16, -1, @filtername)
return (1)
end

begin tran
save tran dropmergefilter

exec @retcode = sys.sp_MSgetmergeadminapplock @timeout = 0, -- no wait
@lockowner = N'Transaction'
if @retcode<>0 or @@error<>0
begin
raiserror(20713, 16, -1, 'sp_dropmergefilter', @publication)
goto FAILURE
end

select @got_merge_admin_applock = 1

if @snapshot_ready>0
begin
if @force_invalidate_snapshot = 0 and @snapshot_ready = 1
begin
raiserror(21382, 16, -1, @filtername)
goto FAILURE
end
select @regenerate_triggers = 1
update dbo.sysmergepublications set snapshot_ready=2,
use_partition_groups = case when use_partition_groups = 1 then 2 else use_partition_groups end
where pubid=@pubid
if @@ERROR<>0
goto FAILURE

--
if @filter_type & 2 = 2
begin
exec @retcode = sys.sp_MSreinitmergepublication
@publication = @publication,
@upload_first = @automatic_reinitialization_policy
if @retcode<>0 or @@ERROR<>0 goto FAILURE
end
--
end

if @snapshot_ready>0 and
((@allow_anonymous = 1 and @compatlevel < 90) or
exists (select * from dbo.sysmergesubscriptions where pubid=@pubid and subid<>pubid and status=1))
BEGIN
if @force_reinit_subscription = 0
begin
RAISERROR (21372, 16, -1, @filtername, @publication)
goto FAILURE
end
else
begin
exec @retcode = sys.sp_MSreinitmergepublication
@publication = @publication,
@upload_first = @automatic_reinitialization_policy
if @retcode<>0 or @@ERROR<>0 goto FAILURE
end
END

-- remove the expand proc for this filter before deleting from sysmergesubsetfilters
select @procname = quotename(expand_proc) from dbo.sysmergesubsetfilters where join_filterid = @join_filterid
if @procname is not NULL
begin
exec ('drop proc ' + @procname)
end

-- drop expand proc and nullify the expand_proc column in sysmergepartitioninfo
select @procname = expand_proc
from dbo.sysmergepartitioninfo
where pubid=@pubid
and artid = ( select art.artid from dbo.sysmergearticles art, dbo.sysmergesubsetfilters filter
where art.name = filter.join_articlename and filter.pubid=@pubid and filter.artid=@artid)
if @procname is not NULL
begin
select @quoted_procname = quotename(@procname)
exec ('drop proc ' + @quoted_procname)
update dbo.sysmergepartitioninfo
set expand_proc = null
where expand_proc = @procname
end

--
-- Remove the join filter from dbo.sysmergesubsetfilters
--
delete from dbo.sysmergesubsetfilters
where join_filterid = @join_filterid
if @@error <> 0
begin
goto FAILURE
end

--
-- set the pub type to subset or full as appropriate
--
exec @retcode=sys.sp_MSsubsetpublication @publication
if @@ERROR <> 0 or @retcode<>0
begin
goto FAILURE
end

if (@regenerate_triggers = 1)
begin
exec @retcode = sys.sp_MSpublicationview @publication = @publication, @force_flag = 1
if @@ERROR<>0 or @retcode <>0
goto FAILURE

exec @retcode = sp_MSregenerate_mergetriggers @publication = @publication
if @@ERROR<>0 or @retcode<>0
goto FAILURE
end

exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction'
commit tran
return(0)

FAILURE:
if @got_merge_admin_applock=1
exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction'
rollback tran dropmergefilter
commit tran
RAISERROR (20039, 16, -1, @article, @publication)
return (1)

sp_dropmergearticle (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_dropmergearticle(nvarchar @publication
, nvarchar @article
, bit @ignore_distributor
, bit @reserved
, bit @force_invalidate_snapshot
, bit @force_reinit_subscription
, bit @ignore_merge_metadata)

MetaData:

   
create procedure sys.sp_dropmergearticle(
@publication sysname, -- The publication name --
@article sysname, -- The article name --
@ignore_distributor bit = 0,
@reserved bit = 0,
@force_invalidate_snapshot bit = 0,
@force_reinit_subscription bit = 0,
@ignore_merge_metadata bit = 0
) AS

set nocount on

declare @artid uniqueidentifier
declare @snapshot_ready int
declare @objid int
declare @pubid uniqueidentifier
declare @pubidstr nvarchar(38)
declare @retcode int
declare @qualified_name nvarchar(270)
declare @filterid int
declare @proc_name sysname
declare @implicit_transaction int
declare @close_cursor_at_commit int
declare @sync_objid int
declare @view_type int
declare @type tinyint
declare @compatlevel int
declare @SCHEMA_TYPE_DROPARTICLE int
declare @automatic_reinitialization_policy bit
declare @got_merge_admin_applock bit

select @got_merge_admin_applock = 0

select @close_cursor_at_commit = 0
select @implicit_transaction = 0
set @SCHEMA_TYPE_DROPARTICLE= 28
--
-- Save setting values first before changing them
--
IF (@reserved = 0)
BEGIN
SELECT @implicit_transaction = @@options & 2
SELECT @close_cursor_at_commit = @@options & 4
SET IMPLICIT_TRANSACTIONS OFF
SET CURSOR_CLOSE_ON_COMMIT OFF
END

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

-- make sure current database is enabled for merge replication --
exec @retcode=sys.sp_MSCheckmergereplication
if @@ERROR<>0 or @retcode<>0
return (1)

select @pubid = pubid,
@snapshot_ready=snapshot_ready,
@compatlevel = backward_comp_level,
@automatic_reinitialization_policy = automatic_reinitialization_policy
FROM dbo.sysmergepublications
WHERE name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name()
if @pubid is NULL
BEGIN
RAISERROR (20026, 16, -1, @publication)
RETURN (1)
END

if @snapshot_ready>0
begin
if @force_invalidate_snapshot = 0 and @snapshot_ready = 1
begin
raiserror(21379, 16, -1, @article, @publication)
return (1)
end
update dbo.sysmergepublications set snapshot_ready=2 where pubid=@pubid
if @@ERROR<>0
return (1)
end

set @pubidstr = '''' + convert(nchar(36), @pubid) + ''''

--
-- Parameter Check: @article.
-- If the @article is 'all', drop all articles for the specified
-- publication (@publication).
--
if LOWER(@article) = 'all'
BEGIN
declare hC CURSOR LOCAL FAST_FORWARD FOR select DISTINCT name FROM dbo.sysmergeextendedarticlesview WHERE pubid=@pubid FOR READ ONLY

OPEN hC
FETCH hC INTO @article
WHILE (@@fetch_status <> -1)
BEGIN
EXECUTE sys.sp_dropmergearticle @publication, @article,
@ignore_distributor = @ignore_distributor,
@reserved = 1, @ignore_merge_metadata = @ignore_merge_metadata
FETCH hC INTO @article
END
CLOSE hC
DEALLOCATE hC
RETURN (0)
END

--
-- Parameter Check: @article.
-- The @article name must conform to the rules for identifiers.
--

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

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

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

--
-- Ascertain the existence of the article.
--
select @type = NULL
select @type = type ,
@artid = artid,
@objid = objid
from dbo.sysmergeextendedarticlesview
where name = @article
and pubid = @pubid
if @type is NULL
begin
raiserror (20027, 16, -1, @article)
return (1)
end

-- if all articles are to be dropped, ignore this checking.
if @reserved=0 and exists (select * from dbo.sysmergesubsetfilters where pubid=@pubid and join_articlename=@article)
begin
raiserror(21421, 16, -1, @article)
return (1)
end

-- if it is a table article and the publication could have down level subscribers do not allow the drop
if (@compatlevel < 90) and @reserved = 0 and
exists (select * from dbo.sysmergesubscriptions where pubid=@pubid and subid<>pubid and status=1) and
not exists (select * from dbo.sysmergeschemaarticles where pubid = @pubid and name = @article)
begin
RAISERROR (21338, 16, -1, @article, @publication)
RETURN (1)
end

--
-- Delete article from dbo.sysmergearticles and clear publish bit in
-- sys.objects.
--

begin tran
save TRAN dropmergearticle

exec @retcode = sys.sp_MSgetmergeadminapplock @timeout = 0, -- no wait
@lockowner = N'Transaction'
if @retcode<>0 or @@error<>0
begin
raiserror(20713, 16, -1, 'sp_dropmergearticle', @publication)
goto FAILURE
end

select @got_merge_admin_applock = 1


exec @retcode = sys.sp_MSdropmergearticle @pubid, @artid, @ignore_merge_metadata
if @@ERROR <> 0 or @retcode <> 0
goto FAILURE

if @compatlevel >= 90
begin
-- post a schema change to drop the article on the subscriber
declare @schemaversion int
declare @schemaguid uniqueidentifier
declare @schematype int
declare @schematext nvarchar(2000)
declare @artidstr nvarchar(38)

set @artidstr = '''' + convert(nchar(36), @artid) + ''''

set @schemaguid = newid()
-- in this schemachange perform article cleanup and drop the article as well if this is not a republisher
-- put in SCHEMA_TYPE_DROPARTICLE
set @schemaguid = newid()
set @schematype = @SCHEMA_TYPE_DROPARTICLE
select @schematext = 'exec sp_MSdropmergearticle ' + @pubidstr + ', ' + @artidstr
select @schemaversion = 1 + max(schemaversion) from dbo.sysmergeschemachange
if @schemaversion is NULL
select @schemaversion = 1
exec @retcode=sys.sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext
if @@ERROR<>0 or @retcode<>0 goto FAILURE
end

--
-- set the pub type to subset or full as appropriate
--
execute @retcode = sys.sp_MSsubsetpublication @publication
if @@ERROR <> 0 or @retcode <> 0
goto FAILURE

if @reserved=0
begin
-- now check if after this article has been removed the publication is still dynamically
-- filtered if it was dynamically filtered before
-- this is a lot of computation but I don't see a better way to do this
declare @can_use_partition_groups bit
declare @has_dynamic_filters bit
declare @dynamic_filters_function_list nvarchar(500)
declare @validate_subscriber_info nvarchar(500)
declare @uses_host_name bit
declare @uses_suser_sname bit
declare @dont_raise_error bit
declare @dynamic_filters bit
declare @use_partition_groups smallint
declare @partid_eval_proc nvarchar(258)

select @dont_raise_error = 1
select @dynamic_filters = dynamic_filters,
@use_partition_groups = use_partition_groups,
@partid_eval_proc = quotename(partition_id_eval_proc)
from dbo.sysmergepublications where pubid = @pubid

exec @retcode = sys.sp_MScheck_dynamic_filters @publication,
@can_use_partition_groups output,
@has_dynamic_filters output,
@dynamic_filters_function_list output,
@validate_subscriber_info output,
@uses_host_name output,
@uses_suser_sname output,
@dont_raise_error
if @@error<>0 or @retcode<>0
goto FAILURE

if @dynamic_filters = 1 and @has_dynamic_filters = 0
begin
if exists (select * from dbo.sysmergesubscriptions where pubid=@pubid and subid<>pubid and (status=1 or status=6))
begin
if @force_reinit_subscription = 0
begin
raiserror(20697, 16, -1, @article, @publication)
goto FAILURE
end
else
begin
-- reinit all subscriptions
exec @retcode = sys.sp_MSreinitmergepublication
@publication = @publication,
@upload_first = @automatic_reinitialization_policy

if @retcode<>0 or @@ERROR<>0
goto FAILURE
end
end

update dbo.sysmergepublications
set dynamic_filters = 0, dynamic_filters_function_list = NULL
where pubid = @pubid
if @@error<>0
goto FAILURE

-- delete all schemachanges except for the ones just added.
delete from dbo.sysmergeschemachange where pubid = @pubid and schematype not in (@SCHEMA_TYPE_DROPARTICLE)
if @@error<>0
goto FAILURE
end

if @use_partition_groups in (1,2) and @can_use_partition_groups = 0
begin
if (object_id(@partid_eval_proc) is not NULL)
begin
exec ('drop procedure ' + @partid_eval_proc)
end
update dbo.sysmergepublications
set use_partition_groups = NULL,
partition_id_eval_proc = NULL
where pubid = @pubid
end
end

exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction'
COMMIT TRAN

--
-- Set back original settings
--
IF @reserved = 0
BEGIN
IF @implicit_transaction <>0
SET IMPLICIT_TRANSACTIONS ON
IF @close_cursor_at_commit <>0
SET CURSOR_CLOSE_ON_COMMIT ON
END

RETURN (0)
FAILURE:
RAISERROR (14047, 16, -1, @article)
if @@TRANCOUNT > 0
begin
if @got_merge_admin_applock=1
exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction'
ROLLBACK TRANSACTION dropmergearticle
COMMIT TRANSACTION
end
--
-- Set back original settings
--
IF @reserved = 0
BEGIN
IF @implicit_transaction <>0
SET IMPLICIT_TRANSACTIONS ON
IF @close_cursor_at_commit <>0
SET CURSOR_CLOSE_ON_COMMIT ON
END
RETURN (1)

sp_dropmergealternatepublisher (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_dropmergealternatepublisher(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @alternate_publisher
, nvarchar @alternate_publisher_db
, nvarchar @alternate_publication)

MetaData:

 create procedure sys.sp_dropmergealternatepublisher (  
@publisher sysname, -- Publisher server --
@publisher_db sysname, -- Publisher database --
@publication sysname, -- Publication name --
@alternate_publisher sysname, -- Alternate publisher --
@alternate_publisher_db sysname, -- Alternate publisher_db --
@alternate_publication sysname -- Alternate publication --
) AS

SET NOCOUNT ON
declare @retcode int
declare @subid uniqueidentifier
declare @alternate_subid uniqueidentifier
declare @pubid uniqueidentifier

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

-- Check is there is an invalid attempt to drop the default publiser/publisher_db/publication --
if (LOWER(@publisher) = LOWER(@alternate_publisher)) AND
(@publisher_db = @alternate_publisher_db) AND
(@publication = @alternate_publication)
begin
RAISERROR(21252, 11, -1, @alternate_publisher, @alternate_publisher_db, @alternate_publication)
return (1)
end

--
-- Select the main publication's pubid and subid
--
select @pubid = pubid from dbo.sysmergepublications
where name = @publication and
LOWER(publisher collate database_default) = LOWER(@publisher collate database_default) and
publisher_db = @publisher_db
if @pubid is NULL
begin
raiserror (20026, 11, -1, @publication)
return (1)
end

-- this subid should really be equal to @pubid
select @subid = subid from dbo.sysmergesubscriptions
where LOWER(subscriber_server) collate database_default = LOWER(@publisher) collate database_default and
db_name = @publisher_db and
pubid = @pubid and
status <> 7 -- REPLICA_STATUS_BeforeRestore

-- check if we know about the publication
-- we know of the publication if there is an entry in sysmergepublications corresponding to it
-- the values of @alternate_subid should be same as 'alternate_pubid' in sysmergepublications
select @alternate_subid = s.subid
from dbo.sysmergesubscriptions s, dbo.sysmergepublications p
where p.name = @alternate_publication and
LOWER(p.publisher) collate database_default = LOWER(@alternate_publisher) collate database_default and
p.publisher_db = @alternate_publisher_db and
p.pubid = s.pubid and
s.subid = s.pubid and
s.status <> 7 -- REPLICA_STATUS_BeforeRestore
IF @subid is NULL or @alternate_subid IS NULL
BEGIN
RAISERROR(21216, 11, -1, @alternate_publisher, @alternate_publisher_db, @alternate_publication)
RETURN (1)
END

if exists (select * from MSmerge_altsyncpartners where subid = @subid and alternate_subid = @alternate_subid)
begin
delete from MSmerge_altsyncpartners where @subid = subid and alternate_subid = @alternate_subid
if @@ERROR <> 0 or @@rowcount <> 1
BEGIN
GOTO FAILURE
END
end
return (0)

FAILURE:
RAISERROR(21251, 11, -1, @alternate_publisher, @alternate_publisher_db, @alternate_publication)
RETURN (1)

sp_droplogin (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_droplogin(nvarchar @loginame)

MetaData:

 create procedure sys.sp_droplogin  
@loginame sysname
as
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @exec_stmt nvarchar(4000)
declare @ret int

-- DISALLOW USER TRANSACTION --
set implicit_transactions off
IF (@@trancount > 0)
begin
raiserror(15002,-1,-1,'sys.sp_droplogin')
return (1)
end

-- VALIDATE LOGIN NAME:
execute @ret = sys.sp_validname @loginame
if (@ret <> 0)
return (1)

-- CHECK IT'S A SQL LOGIN --
if (not exists (select * from master.dbo.syslogins where
loginname = @loginame and isntname = 0))
begin
raiserror(15007,-1,-1,@loginame)
return(1)
end

set @exec_stmt = 'drop login ' + quotename(@loginame)

exec (@exec_stmt)

if @@error <> 0
return (1)

-- SUCCESS MESSAGE --
return (0) -- sp_droplogin

sp_droplinkedsrvlogin (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_droplinkedsrvlogin(nvarchar @rmtsrvname
, nvarchar @locallogin)

MetaData:

 create procedure sys.sp_droplinkedsrvlogin  
@rmtsrvname sysname,
@locallogin sysname
as
declare @localid int

-- DISALLOW USER TRANSACTION
set implicit_transactions off
if @@trancount > 0
begin
raiserror(15002,-1,-1,'sys.sp_droplinkedsrvlogin')
return (1)
end

-- CHECK PERMISSIONS
if not (has_perms_by_name(null, null, 'alter any login') = 1)
begin
EXEC %%System().AuditEvent(ID = 1380207692, Success = 0, TargetLoginName = @locallogin, TargetUserName = NULL, Role = NULL, Object = NULL, Provider = NULL, Server = @rmtsrvname)
raiserror(15247,-1,-1)
return (1)
end
else
EXEC %%System().AuditEvent(ID = 1380207692, Success = 1, TargetLoginName = @locallogin, TargetUserName = NULL, Role = NULL, Object = NULL, Provider = NULL, Server = @rmtsrvname)

BEGIN TRAN

-- VALIDATE/LOCK SERVER NAME --
EXEC %%LinkedServer ( Name = @rmtsrvname ) . Lock ( Exclusive = 1 )
IF @@ERROR <> 0
begin
ROLLBACK TRAN
raiserror(15015,-1,-1,@rmtsrvname)
return (1)
end

-- CHECK LOCAL USER NAME IF GIVEN
select @localid = 0
if @locallogin is not null
begin
-- share-lock the local login
EXEC %%LocalLogin ( Name = @locallogin ) . Lock ( Exclusive = 0 )
IF @@ERROR = 0
select @localid = principal_id from sys.server_principals
where name = @locallogin and type in ('S', 'U')
if @localid = 0
begin
-- THERE WILL BE NO LinkedLogins, SO ONLY AN ERROR IF NO NT-NAME OUT THERE --
IF suser_sid(@locallogin) IS NULL
BEGIN
ROLLBACK TRAN
raiserror(15007,-1,-1,@locallogin)
return (1)
END

-- "SUCCESS" --
COMMIT TRAN
return(0)
end
end

-- IF MAPPING EXISTS FOR THIS SID, REMOVE IT --
IF EXISTS (SELECT * FROM sys.linked_logins WHERE
server_id IN (SELECT server_id FROM sys.servers where name = @rmtsrvname)
AND local_principal_id = @localid)
BEGIN
EXEC %%LinkedServer(Name=@rmtsrvname).RemoveLinkedLogin(LocalID=@localid)
END

-- EMDEventType(x_eet_Drop_Linked_Server_Login), EMDUniversalClass(x_eunc_Linked_Server), src major id, src minor id, src name
-- -1 means ignore target stuff, target major id, target minor id, target name,
-- # of parameters, 5 parameters
EXEC %%System().FireTrigger(ID = 236, ID = 102, ID = 0, ID = 0, Value = @rmtsrvname,
ID = 101, ID = @localid, ID = 0, Value = NULL,
ID = 2, Value = @rmtsrvname, Value = @locallogin, Value = NULL, Value = NULL, Value = NULL, Value = NULL, Value = NULL)

-- SUCCESS --
COMMIT TRAN
return(0) -- sp_droplinkedsrvlogin

sp_dropextendedproperty (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_dropextendedproperty(nvarchar @name
, varchar @level0type
, nvarchar @level0name
, varchar @level1type
, nvarchar @level1name
, varchar @level2type
, nvarchar @level2name)

MetaData:

 create procedure sys.sp_dropextendedproperty  
@name sysname,
@level0type varchar(128) = NULL,
@level0name sysname = NULL,
@level1type varchar(128) = NULL,
@level1name sysname = NULL,
@level2type varchar(128) = NULL,
@level2name sysname = NULL
as
declare @ret int

if @name is null
begin
raiserror(15600,-1,-1,'sp_dropeextendedproperty')
return (1)
end

execute @ret = sys.sp_validname @name
if (@ret <> 0)
begin
raiserror(15600,-1,-1,'sp_dropeextendedproperty')
return (1)
end

BEGIN TRANSACTION

begin
EXEC %%ExtendedPropertySet().RemoveValue(Name = @name, Level0type = @level0type, Level0name = @level0name, Level1type = @level1type, Level1name = @level1name, Level2type = @level2type, Level2name = @level2name)
IF @@ERROR <> 0
begin
COMMIT TRANSACTION
return (1)
end
end

COMMIT TRANSACTION
return (0)

sp_dropextendedproc (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_dropextendedproc(nvarchar @functname)

MetaData:

   
-- -- -- -- -- -- -- -- -- -- -- -- -- -- sp_dropextendedproc -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
create procedure sys.sp_dropextendedproc
@functname nvarchar(517) -- name of function
as
-- If we're in a transaction, disallow the dropping of the
-- extended stored procedure.
set implicit_transactions off
if @@trancount > 0
begin
raiserror(15002,-1,-1,'sys.sp_dropextendedproc')
return (1)
end

BEGIN TRANSACTION

-- Drop the extended procedure mapping.
EXEC %%System().DropExtendedProc(Name = @functname)

COMMIT

return (0) -- sp_dropextendedproc

sp_dropdynamicsnapshot_job (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_dropdynamicsnapshot_job(nvarchar @publication
, nvarchar @dynamic_snapshot_jobname
, uniqueidentifier @dynamic_snapshot_jobid
, bit @ignore_distributor)

MetaData:

 create procedure sys.sp_dropdynamicsnapshot_job (  
@publication sysname,
@dynamic_snapshot_jobname sysname = '%',
@dynamic_snapshot_jobid uniqueidentifier = null,
@ignore_distributor bit = 0
)
AS
declare @retcode int
exec @retcode = sys.sp_MSreplcheck_publish
if @@error <> 0 or @retcode <> 0
return (1)
exec @retcode = sys.sp_MSdropmergedynamicsnapshotjob
@publication,
@dynamic_snapshot_jobname,
@dynamic_snapshot_jobid,
@ignore_distributor
return @retcode

Total Pageviews