Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSis_identity_insert

  No additional text.


Syntax
create procedure sys.sp_MSis_identity_insert
(
    @publication sysname
    ,@article sysname
    ,@identity_insert bit output
    ,@artid int = NULL   -- If pass in @artid, you don't need to pass in @publication and @article
    ,@mode tinyint = 1  -- 1 = sync tran proc scripting, 2 = custom cmd proc scripting
)
as
begin
    set nocount on
    declare @pubid int
                ,@source_objid int
                ,@schema_option binary(8)
                ,@modescriptforsynctranproc tinyint
                ,@modescriptforcustomcmdproc tinyint

    
    -- initialize
    
    select @identity_insert = 0
            ,@modescriptforsynctranproc = 1
            ,@modescriptforcustomcmdproc = 2
    
    -- validate @mode
    
    if (@mode not in (@modescriptforsynctranproc, @modescriptforcustomcmdproc))
        return 1
    
    -- Get @artid if not there
    
    if @artid is null
    begin
        select @pubid = pubid from syspublications where name = @publication
        select @artid = artid
                ,@schema_option = schema_option
        from sysarticles where pubid = @pubid and name = @article
    end
    else
    begin
        select @pubid = pubid
                ,@schema_option = schema_option
        from sysarticles where artid = @artid
    end
    if (@artid is null)
        return (1)
    
    -- We need to script identity insert if the following is true:
    -- Publication should exist and
    -- the table should have an identity column and
    -- the NFR property on the column should be disabled
    -- if the mode is for custom procedures then we should
    -- ensure the schema_option for identity scripting is enabled
    
    if exists (select * from syspublications where pubid = @pubid)
    begin
        if exists (select sc.object_id
                    from (( sysarticles sa join sysarticlecolumns sac
                                    on sa.artid = sac.artid )
                                join sys.columns sc
                                    on sc.object_id = sa.objid and sc.column_id = sac.colid)
                    where sa.artid = @artid
                        and OBJECTPROPERTY(sa.objid, 'tablehasidentity') = 1
                        and COLUMNPROPERTY(sa.objid, sc.name, 'IsIdentity') = 1
                        and COLUMNPROPERTY(sa.objid, sc.name, 'IsIdNotForRepl') = 0
                        and (@mode = @modescriptforsynctranproc
                                or (@mode = @modescriptforcustomcmdproc
                                    and (cast(@schema_option as int) & 0x4) != 0 )))
        begin
            select @identity_insert = 1
        end
    end
    
    -- all done
    
    return 0
end

 
Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)
sp_MSscript_sync_ins_proc (Procedure)
sp_MSscript_sync_upd_proc (Procedure)
sp_scriptinsproccore (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