Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_attachsubscription

  No additional text.


Syntax

-- 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

 
Last revision 2008RTM
See also

  sp_helpmergepullsubscription (Procedure)
sp_helppullsubscription (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