April 16, 2012

sp_attachsubscription (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_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

Total Pageviews