Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSremove_userscript

  No additional text.


Syntax

-- Name: sp_MSremove_userscript

-- Descriptions:

-- Parameters: as defined in create statement

-- Returns: 0 - success
--          1 - Otherwise

-- Security:
-- Requires Certificate signature for catalog access

create procedure sys.sp_MSremove_userscript(
@pubid				uniqueidentifier,
@drop_publication	bit = 0
)as
	declare @retention			int
	declare @retention_period_unit tinyint
	declare @last_snapshot		datetime
	declare @post_snapshot_ver	int
	declare @post_snapshot_type int
	declare @user_script_type	int
	declare @retcode			int
	declare @len				int

	declare @file_path			nvarchar(4000)
	declare @delfile_cmd		nvarchar(4000)
	declare @rmdir_cmd			nvarchar(4000)
	
	select @post_snapshot_type=52
	select @user_script_type=46
	
	if not exists (select * from dbo.sysmergeschemachange where pubid=@pubid and schematype=@user_script_type)
		return (0)
	select @retention=retention, @retention_period_unit = retention_period_unit from dbo.sysmergepublications where pubid=@pubid
	select @last_snapshot=last_validated from dbo.sysmergesubscriptions where pubid=@pubid and subid=@pubid

	--I do not want to remove  script files by setting retention to 0
	if (@retention=0 or sys.fn_add_units_to_date(-@retention, @retention_period_unit, getdate()) < @last_snapshot) and @drop_publication = 0
		return (0)

	select @post_snapshot_ver=schemaversion from dbo.sysmergeschemachange
		where schematype=@post_snapshot_type and pubid=@pubid
		
	--only get those script that can be safely removed
	
	declare #per_script cursor local fast_forward for
		select schematext from dbo.sysmergeschemachange
			where pubid=@pubid and schematype=@user_script_type
			and (schemaversion<@post_snapshot_ver or @drop_publication = 1)
	open #per_script
	fetch #per_script into @file_path
	while (@@fetch_status<>-1)
	begin
		if(left(@file_path, 1) = N'0' or left(@file_path, 1) = N'1')
			select @file_path = right(@file_path, len(@file_path) - 1)
		select @delfile_cmd = N'del "' + sys.fn_escapecmdshellsymbolsremovequotes(@file_path) collate database_default + N'"'
		EXEC @retcode = master.dbo.xp_cmdshell @delfile_cmd, NO_OUTPUT
		if @@ERROR<>0
			goto FAILURE
		select @len=CHARINDEX ( '\' , reverse(@file_path) )
		select @file_path=SUBSTRING(@file_path , 1 , len(@file_path)-@len + 1)
		
		select @delfile_cmd = N'rmdir "' + sys.fn_escapecmdshellsymbolsremovequotes(@file_path) collate database_default + N'"'
		EXEC @retcode = master.dbo.xp_cmdshell @delfile_cmd, NO_OUTPUT
		if @@ERROR<>0
			goto FAILURE
		fetch next from #per_script into @file_path
	end
	
	close #per_script
	deallocate #per_script
	return (0)
FAILURE:
	close #per_script
	deallocate #per_script
	return (1)

 
Last revision 2008RTM
See also

  sp_MSdrop_rladmin (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash