Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_vupgrade_MSsubscription_properties

  No additional text.


Syntax
create procedure sys.sp_vupgrade_MSsubscription_properties
as
begin
    set nocount on

    declare @cmd nvarchar(1000)
            ,@table_name sysname
            ,@table_id int
            ,@column_name nvarchar(128)
            ,@column_type nvarchar(128)
            ,@alter_cmd nvarchar(max)


    -- raiserror('sp_vupgrade_MSsubscription_properties', 0,1)
    select @table_name = N'MSsubscription_properties'
            ,@table_id = OBJECT_ID('MSsubscription_properties')

    if object_id(@table_name,'U') is not NULL
    begin
        declare colcurs cursor LOCAL FAST_FORWARD
        for (select col_name, col_type from (select col_name = 'ftp_address', col_type = 'sysname NULL' --FTP properties
                                                             union all
                                                             select col_name = 'ftp_port', col_type = 'int NULL'
                                                             union all
                                                             select col_name = 'ftp_login', col_type = 'sysname NULL'
                                                             union all
                                                             select col_name = 'ftp_password', col_type = 'sysname NULL'
                                                             union all
                                                             select col_name = 'alt_snapshot_folder', col_type = 'nvarchar(255) NULL' --Portable snapshot (Shiloh)
                                                             union all
                                                             select col_name = 'working_directory', col_type = 'nvarchar(255) NULL'
                                                             union all
                                                             select col_name = 'use_ftp', col_type = 'bit default 0 NOT NULL'-- Subscriber need only set this bit and have valid login info to publisher (Shiloh)
                                                             union all
                                                             select col_name = 'dts_package_name', col_type = 'sysname NULL' -- Transformable subscriptions (Shiloh)
                                                             union all
                                                             select col_name = 'dts_package_password', col_type = 'nvarchar(524) NULL'
                                                             union all
                                                             select col_name = 'dts_package_location', col_type = 'int default 1 NOT NULL'
                                                             union all
                                                             select col_name = 'enabled_for_syncmgr', col_type = 'bit default 0 NOT NULL'-- Windows Synchronization Manager support (Shiloh)
                                                             union all
                                                             select col_name = 'offload_agent', col_type = 'bit default 0 NOT NULL'-- Remote (DCOM) agents support (Shiloh)
                                                             union all
                                                             select col_name = 'offload_server', col_type = 'sysname default NULL'
                                                             union all
                                                             select col_name = 'dynamic_snapshot_location', col_type = 'nvarchar(255) default NULL' -- Dynamic snapshot location (Shiloh)
                                                             union all
                                                             select col_name = 'use_web_sync', col_type = 'bit default 0' --WebSync (Yukon)
                                                             union all
 select col_name = 'internet_url', col_type = 'nvarchar(260) default NULL'
                                                             union all
                                                             select col_name = 'internet_login', col_type = 'sysname default NULL'
                                                             union all
                                                             select col_name = 'internet_password', col_type = 'nvarchar(524) default NULL'
                                                             union all
                                                             select col_name = 'internet_security_mode', col_type = 'int not null default 0'
                                                             union all
                                                             select col_name = 'internet_timeout', col_type = 'int not null default 60'
                                                             union all
                                                             select col_name = 'hostname', col_type = 'sysname default NULL'-- Partition Hostname (Yukon)
                                                             union all
                                                             select col_name = 'publisherlink', col_type = 'sysname NULL'
                                                             union all
                                                             select col_name = 'publisherlinkuser', col_type = 'sysname NULL'
                                                             union all
                                                             select col_name = 'job_step_uid', col_type = 'uniqueidentifier NULL'
                                                             ) as t1
               left outer join
               sys.columns as t2
               on (t1.col_name = t2.name and t2.object_id = object_id('dbo.MSsubscription_properties', 'U'))
               where t2.name is null) --This query gives all the columns in t1 that are not in syscolums
        for read only
        open colcurs
        fetch colcurs into @column_name, @column_type
        if (@@fetch_status <> -1)
        begin
            select @alter_cmd = 'alter table dbo.MSsubscription_properties add ' + @column_name + ' ' + @column_type
            fetch colcurs into @column_name, @column_type
            while(@@fetch_status <> -1)
            begin
                select @alter_cmd = @alter_cmd + ', ' + @column_name + ' ' + @column_type
                fetch colcurs into @column_name, @column_type
            end
            exec (@alter_cmd)
            if @@error <> 0 return 1
        end
        close colcurs
        deallocate colcurs


        -- Since ftp login information is not required to be persisted at the
        -- database anymore and the use_ftp column is now the sole indicator
        -- of whether ftp is going to be used for a particular subscription,
        -- set the use_ftp column to 1 for subscriptions that used to have
        -- a non-null ftp address
        if exists (select * from sys.columns where object_id = @table_id and
            name = 'ftp_address')
        begin
            -- must exec in separate process; deferred syntax will fail if update references non-existent column
            select @cmd = 'update dbo.MSsubscription_properties set use_ftp = 1 where ftp_address is not null and ftp_address <> N'''''
            exec (@cmd)
        end

        --    MSsubscription_properties indexes (SQL7.0 SP1)
        if exists( select publication, publisher_db, publisher
                from MSsubscription_properties
                group by publication, publisher_db, publisher
                having count(*) > 1 )
        begin
            raiserror(21203, 10, 2, @table_name)
        end
  else
        begin
            if not exists( select * from sysindexes
                where name = 'uc1MSsubscription_properties' and id = @table_id )
            begin
                create unique clustered index uc1MSsubscription_properties on
                MSsubscription_properties(publication, publisher_db, publisher)
                if @@ERROR <> 0
                    return 1
            end
        end
        
        -- remove defaults on hostname, internet_url, internet_login, internet_password
        
        declare #colcurs cursor LOCAL FAST_FORWARD for
            select dc.name
            from sys.default_constraints as dc
                join sys.columns as c
                    on dc.parent_object_id = c.object_id
                        and dc.parent_column_id = c.column_id
            where c.object_id = @table_id
                and c.name in (N'hostname'
                                        ,N'internet_url'
                                        ,N'internet_login'
                                        ,N'internet_password')
        open #colcurs
        fetch #colcurs into @column_name
        while (@@fetch_status != -1)
        begin
            
            -- drop the default constraint
            
            select @alter_cmd = N'alter table dbo.MSsubscription_properties drop constraint  ' + sys.fn_replreplacesinglequote(quotename(@column_name))
            exec (@alter_cmd)
            if @@error <> 0
                return 1
            
            -- fetch next in the cursor
            
            fetch #colcurs into @column_name
        end
        close #colcurs
        deallocate #colcurs
    end -- if exists( MSsubscription_properties)
    
    -- all done
    

    return 0
end

 
Last revision 2008RTM
See also

  sp_MScreate_sub_tables_internal (Procedure)
sp_MSrestoredbreplication (Procedure)
sp_vupgrade_subscription_databases (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