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_attachsubscription(nvarchar @dbname, nvarchar @filename
, int @subscriber_security_mode
, nvarchar @subscriber_login
, nvarchar @subscriber_password
, int @distributor_security_mode
, nvarchar @distributor_login
, nvarchar @distributor_password
, int @publisher_security_mode
, nvarchar @publisher_login
, nvarchar @publisher_password
, nvarchar @job_login
, nvarchar @job_password
, nvarchar @db_master_key_password)
MetaData:
-- -- Name: sp_attachsubscription -- -- Descriptions: -- -- Parameters: as defined in create statement -- -- Returns: 0 - success -- 1 - Otherwise -- -- Security: -- Requires Certificate signature for catalog access -- create procedure sys.sp_attachsubscription ( @dbname sysname, @filename nvarchar(260), @subscriber_security_mode int = NULL, -- 0 standard; 1 integrated -- @subscriber_login sysname = NULL, @subscriber_password sysname = NULL, @distributor_security_mode int = 1, @distributor_login sysname = NULL, @distributor_password sysname = NULL, @publisher_security_mode int = 1, @publisher_login sysname = NULL, @publisher_password sysname = NULL, @job_login nvarchar(257) = NULL, @job_password sysname = NULL, @db_master_key_password nvarchar(524) = NULL ) AS begin SET NOCOUNT ON -- Declarations. declare @cmd nvarchar(4000) ,@retcode int ,@copy_created bit ,@exists bit ,@sa_login sysname select @retcode = 0 ,@copy_created = 0 ,@exists = 0 -- Warn about the parameters no longer being used IF @subscriber_login IS NOT NULL OR @subscriber_password IS NOT NULL OR (@subscriber_security_mode IS NOT NULL AND @subscriber_security_mode != 1) BEGIN -- The subscriber security parameter(s) have been deprecated and should no longer be used. See the 'sp_attachsubscription' documentation for more information. RAISERROR(21827, 10, -1, 'subscriber security', 'sp_attachsubscription') SELECT @subscriber_security_mode = NULL, @subscriber_login = NULL, @subscriber_password = NULL END -- Check to make sure the database does not exists. if exists (select * from master.dbo.sysdatabases where name = @dbname collate database_default) begin raiserror(20621, 16, -1, @dbname) return (1) end -- Only sysadmin can do this if (isnull(is_srvrolemember('sysadmin'),0) = 0) begin raiserror(21089,16,-1) return (1) end -- Check to see if users has permissions to create database -- permissions() have to be run in master to return create db permission. -- declare @pm int -- exec @retcode = master.sys.sp_executesql N'select @pm = permissions()', N'@pm int output', @pm output -- if @@error <> 0 or @retcode <> 0 -- return 1 -- if @pm & 1 = 0 -- begin -- raiserror(20618, 16, -1) -- return 1 -- end -- Decompress the file -- We have to copy the file to another location first. -- (cannot use source as destination') declare @temp_copy nvarchar(260) declare @file_dir nvarchar(260) declare @file_name nvarchar(260) declare @dir_cmd nvarchar(260) -- Set @drive_cmd if needed -- Set temp copy to be the file directory first -- Note @file_dir include '\' if (charindex('\', @filename, 1) = 0) begin select @file_dir = '' select @file_name = @filename end else begin select @file_dir = left(@filename,len(@filename) + 1 - charindex('\', reverse(@filename), 1)) select @file_name = right(@filename, len(@filename) - len(@file_dir)) end -- Get guid name declare @guid_name nvarchar(36) select @guid_name = convert (nvarchar(36), newid()) select @temp_copy = @file_dir + @guid_name + '.tmp' -- copy file select @cmd = 'copy "' + sys.fn_escapecmdshellsymbolsremovequotes(@filename) collate database_default + '" "' + sys.fn_escapecmdshellsymbolsremovequotes(@temp_copy) collate database_default + '"' exec @retcode = master.dbo.xp_cmdshell @cmd, NO_OUTPUT if @@error <> 0 or @retcode <> 0 begin raiserror(21248, 16, -1, @filename) select @retcode = 1 goto Cleanup end exec @retcode = sys.sp_MSget_file_existence @temp_copy, @exists output if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Cleanup end if @exists = 0 begin raiserror(21247, 16, -1, @temp_copy) select @retcode = 1 goto Cleanup end select @copy_created = 1 -- decompress exec @retcode = sys.xp_unpackcab @cabfilename = @temp_copy, @destination_folder = @file_dir, @verbose_level = 0, @destination_file = @file_name, @suppress_messages = 1 if @@error <> 0 begin select @retcode = 1 goto Cleanup end if @retcode in (1030,1029,2005) begin raiserror(20609, 16, -1, @filename) select @retcode = 1 goto Cleanup end else if @retcode <> 0 -- re-issue the command to get errors begin exec @retcode = sys.xp_unpackcab @cabfilename = @temp_copy, @destination_folder = @file_dir, @verbose_level = 0, @destination_file = @file_name, @suppress_messages = 0 select @retcode = 1 goto Cleanup end -- Attach exec @retcode = sys.sp_attach_single_file_db @dbname = @dbname, @physname = @filename if @retcode<>0 or @@error<>0 begin raiserror(21248, 16, -1, @filename) select @retcode = 1 goto Cleanup end if isnull(is_srvrolemember('sysadmin'), 0) <> 0 begin select @sa_login = SUSER_SNAME(0x01) select @cmd = quotename(@dbname) + '.dbo.sp_changedbowner' exec @retcode = @cmd @sa_login if @retcode<>0 or @@error<>0 begin select @retcode = 1 goto Cleanup end end -- Prepare the database for detach. 2 things will be done -- 1. Set a flag to indicate that this is a prepare sub db for detach -- 2. For merge, create table to store dbo.sysservers info for later fixing up after attach select @cmd = quotename(@dbname) + '.sys.sp_MSrestore_sub' exec @retcode = @cmd @subscriber_security_mode = @subscriber_security_mode, @subscriber_login = @subscriber_login, @subscriber_password = @subscriber_password, @distributor_security_mode = @distributor_security_mode, @distributor_login = @distributor_login, @distributor_password = @distributor_password, @publisher_security_mode = @publisher_security_mode, @publisher_login = @publisher_login, @publisher_password = @publisher_password, @job_login = @job_login, @job_password = @job_password, @db_master_key_password = @db_master_key_password if @retcode<>0 or @@error<>0 begin select @retcode = 1 goto Cleanup end Cleanup: if @retcode <> 0 begin -- The files will be deleted if some thing failed. if exists (select * from master.dbo.sysdatabases where name = @dbname collate database_default) begin select @cmd = 'drop database ' + quotename(@dbname) exec (@cmd) end end if @temp_copy is not null begin -- Restore the original file, ignore errors if @retcode <> 0 and @copy_created = 1 begin select @cmd = 'copy "' + sys.fn_escapecmdshellsymbolsremovequotes(@temp_copy) collate database_default + '" "' + sys.fn_escapecmdshellsymbolsremovequotes(@filename) collate database_default + '"' exec master.dbo.xp_cmdshell @cmd, NO_OUTPUT end -- Delete the temp file. select @cmd = 'del "' + sys.fn_escapecmdshellsymbolsremovequotes(@temp_copy) collate database_default + '"' EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT end return @retcode end
No comments:
Post a Comment