Solved

Arithmetic overflow error in custom Columns_Updated() function - need math help

Posted on 2004-03-31
2
2,017 Views
Last Modified: 2007-12-19
/*
This dynamic trigger was found on isnotnull.com
http://isnotnull.com/20010806.htm

I have emailed paul for assistance and got no reply so I'm trying here.
It really is a slick tool and I've come to use it extensively. I think it would really be worthwhile to try to fix this audit system as it is so close to working.

I'm pretty sure the error is in udf_GenColUpdated(). Specifically, the way it calculates the bit mask. The error only occurs on tables more than 32 columns and it then I get errors only on the first 8 columns.

I've modified the original usp_Audit function but not changed the udf_GenColUpdated() function at all.

The reply might be, "Well, just write manual Updated() checks for ever column but if I wanted to do that I wouldn't be posting here.

Here is a comment from the page where the code is located that has some info about the special implementation of Columns_Updated():

"Donny Beard brought to my attention that the dynamic audit trigger failed to properly record the last columns of the Customer table.
Upon debugging, I found that the Columns_Updated() system function does not actually operate the way books on-line explains. BOL states that The columns are represented in the bits from left to right. They are in byte from left to right, but within each byte it's from right to left. To handle the strange bit representation, I created a user defined function to simulate the columns_updated() system function. A bitwise AND between the system function and the user defined function is true when the column is updated."


*/


use tempdb

--------------------------------
--------------------------------
--the table that holds the audits
--------------------------------
--------------------------------
if exists (select * from sysobjects where id = object_id('dbo.Audit') and sysstat & 0xf = 3)
      drop table dbo.Audit
GO
CREATE TABLE dbo.Audit (
      OperationTimestamp datetime NOT NULL ,
      Operation char (1) NOT NULL ,
      AuditID int IDENTITY (1, 1) NOT NULL ,
      SysUser varchar (50) NOT NULL ,
      Application varchar (50) NOT NULL ,
      TableName varchar (50) NOT NULL ,
      PrimaryKey int NOT NULL ,
      [Column] varchar (50) NOT NULL ,
      OldValue nvarchar (255) NULL ,
      NewValue nvarchar (255) NULL
)
go

--------------------------------
--------------------------------
--a table with a lot of fields (more than 32)
--------------------------------
--------------------------------
if exists (select * from sysobjects where id = object_id('dbo.Table1') and sysstat & 0xf = 3)
      drop table dbo.Table1
GO

CREATE TABLE dbo.Table1
      (
      col1 int IDENTITY (1, 1) NOT NULL,
      col2 char(10) NULL,
      col3 char(10) NULL,
      col4 char(10) NULL,
      col5 char(10) NULL,
      col6 char(10) NULL,
      col7 char(10) NULL,
      col8 char(10) NULL,
      col9 char(10) NULL,
      col10 char(10) NULL,
      col11 char(10) NULL,
      col12 char(10) NULL,
      col13 char(10) NULL,
      col14 char(10) NULL,
      col15 char(10) NULL,
      col16 char(10) NULL,
      col17 char(10) NULL,
      col18 char(10) NULL,
      col19 char(10) NULL,
      col20 char(10) NULL,
      col21 char(10) NULL,
      col22 char(10) NULL,
      col23 char(10) NULL,
      col24 char(10) NULL,
      col25 char(10) NULL,
      col26 char(10) NULL,
      col27 char(10) NULL,
      col28 char(10) NULL,
      col29 char(10) NULL,
      col30 char(10) NULL,
      col31 char(10) NULL,
      col32 char(10) NULL,
      col33 char(10) NULL,
      col34 char(10) NULL,
      col35 char(10) NULL,
      col36 char(10) NULL,
      col37 char(10) NULL,
      col38 char(10) NULL,
      col39 char(10) NULL,
      col40 char(10) NULL
      )
go


--------------------------------
--------------------------------
--the stub trigger that calls the dynamic audit with temp tables
--------------------------------
--------------------------------
Create Trigger dbo.trg_table1_Audit_after_iu
On dbo.table1
For Insert, Update
NOT FOR REPLICATION

As
      
      /* Dynamic Audit Trail Code Begin
            (c)2001 Paul Nielsen */
            
      Declare
            @Col_Updated       VarBinary(1028),
            @TableName            nvarchar(100),
            @PrimaryKey            sysname

      set nocount on

      -- Set up the Audit data
      Set @TableName = 'table1'  -- set to the table name
      Set @PrimaryKey = 'col1'  -- set to the column to identify the row
      Set @Col_Updated = Columns_Updated()
      Select * into #TempIn from Inserted
      Select * into #TempDel from Deleted

      -- call the audit stored procedure
      exec dbo.usp_Audit @Col_Updated, @TableName, @PrimaryKey
      
      /* Dynamic Audit Trail Code End */

go


--------------------------------
--------------------------------
--the dynamic audit procedure
--------------------------------
--------------------------------

if exists (select * from sysobjects where id = object_id('dbo.usp_Audit') and sysstat & 0xf = 4)
      drop procedure dbo.usp_Audit
go

CREATE    Proc dbo.usp_Audit

      (
            @Col_Updated       VarBinary(1028),
            @TableName            nvarchar(100),
            @PrimaryKey            sysname
      )

As
      /* dynamic auto-audit trigger/stored procedure

            Copyright 2001 Paul Nielsen
            
            logic:            
                  in the calling trigger the inserted and deleted views were selected into temp tables
                        so that  the data could be available to (1) this stored procedure
                        and (2) passed into the exec / dynamic SQL
                  Uses the Columns_Updated bits to further optimize      
                  handles multi-row inserts / updates without a cursor:
                        join between inserted.PK = deleted.PK AND inserted.column != deleted.column selects only rows with updates in that column
            still needs:
                  no error handling yet
                  this version is for primary tabels / a version for secondary tables is also needed
                        doesn't handle foreign key updates
                        doesn't handle logging as change to primary table
                  lock handling so large updates inserts don't hog the audit table

      */
      
      set nocount on

      Declare
            @ColTotal             int,
            @ColCounter            int,
            @ColUpdatedTemp int,
            @ColName                  sysname,
            @BlankString       char(1),
            @SQLStr                  nvarchar(1000),
            @ColNull             nvarchar(50),
            @SysUser                  nvarchar(100),
            @ColumnDataType      int,
            @IsUpdate            bit,
            @tempError            int,
            @OperationTimestamp            nvarchar(50)
            
      --convert some variables to strings because the dynamic sql later likes that
      Set @OperationTimestamp = convert(nvarchar(50), getdate(), 21) -- convert to ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
      Set @SysUser = suser_sname()
      Set @BlankString = ''

      -- Initialize Col variables
      Select @ColCounter = 0
      Select @ColTotal = Count(*)
            From SysColumns                                
            Join SysObjects
                  On SysColumns.id = SysObjects.id
            Where SysObjects.name = @TableName

      -- Set IsUpdated Flag
      If Exists(Select * from #tempDel)
            Select @IsUpdate = 1
      Else
            Select @IsUpdate = 0

      -- Column Updates
      While ((Select @ColCounter) != @ColTotal)  -- run through some columns (problems with greater numbers - must find out why!)
      Begin
            Select @ColCounter = @ColCounter + 1
      
            Set @ColUpdatedTemp = dbo.udf_GenColUpdated(@ColCounter,@ColTotal)

            If (@Col_Updated & @ColUpdatedTemp) = @ColUpdatedTemp -- bitwise AND between updated bits and the selected column bit
            Begin
                  Select @ColNull = null
                  Select @ColName = SysColumns.name, @ColumnDataType = SysColumns.xtype      -- get the column name & Data Type
                        From SysColumns                                
                        Join SysObjects
                              On SysColumns.id = SysObjects.id
                        Where SysObjects.name = @TableName                          
                              and SysColumns.ColID = @ColCounter
                  
                  If @ColName NOT IN ('') --specify field changes not to appear in the audit log
                  Begin
      
                        -- text columns
                        If  @ColumnDataType IN ( 175, 239, 99, 231, 35, 231, 98, 167 )
                              Select @ColNull =  ''''''                  

                        -- numeric +  bit columns
                        Else If  @ColumnDataType IN (  106, 62, 56, 60, 108, 59, 52, 122, 104 )
                              Select @ColNull = '0'                  

                        -- date columns
                        Else If  @ColumnDataType IN ( 61, 58 )
                              Select @ColNull =  '''1/1/1980'''

                        -- uniqueidentifier columns
                        Else If  @ColumnDataType IN ( 36 )
                              Select @ColNull =  ''''''

                        If @ColNull Is Not Null
                        Begin
                              If @IsUpdate = 1
                                    Select @SQLStr =                   
      
                                    ' Insert Audit(TableName, PrimaryKey, SysUser, [Column],' +
                                            ' Application, OldValue, NewValue, Operation, OperationTimestamp)' +
      
                                    ' Select '''+ @TableName + ''',#tempIn.['+ @PrimaryKey + '], ''' + @SysUser + ''', ' +
                                          '''' + @ColName + ''', App_Name(),' +
                                          ' IsNull(convert(nvarchar(100),#tempDel.[' + @ColName + ']),''<null>''), ' +
                                          ' IsNull(convert(nvarchar(100),#tempIn.[' + @ColName + ']),''<null>''),''U''' +
                                          ', ''' + @OperationTimestamp + ''' ' +

      
                                    ' From #tempIn' +
                                          '  Join #tempDel' +
                                                ' On #tempIn.['+ @PrimaryKey + '] = #tempDel.['+ @PrimaryKey + ']' +
                                                      ' AND isnull(#tempIn.' + @ColName +   ',' + @ColNull + ') != isnull(#tempDel.' + @ColName +   ',' + @ColNull + ')' +
      
                                    ' Where Not (#tempIn.[' + @ColName + '] Is Null and #tempDel.[' + @ColName + ']  Is Null)'
      
                              Else -- Insert
                                    Select @SQLStr =                   
      
                                    ' Insert Audit(TableName, PrimaryKey, SysUser, [Column],' +
                                            ' Application, OldValue, NewValue, Operation, OperationTimestamp)' +
      
                                    ' Select '''+ @TableName + ''',#tempIn.['+ @PrimaryKey + '], ''' + @SysUser + ''', ' +
                                          '''' + @ColName + ''', App_Name(),' + ' Null, ' +
                                          ' IsNull(convert(nvarchar(100),#tempIn.[' + @ColName +   ']),''<null>''),''I''' +
                                          ', ''' + @OperationTimestamp + ''' ' +
      
      
                                    ' From #tempIn' +
                                    ' Where Not (#tempIn.[' + @ColName + '] Is Null)'
      
                                      --print @SQLStr
                                      --print @ColName
                                    exec sp_executesql  @SQLStr
                                      Set @TempError = @@Error
                                    If @TempError <> 0
                                    Begin
                                          -- Rollback -- turn this on only if you want a failure to record audit to cancel the data modification operation
                                          Raiserror ('Audit Trail Error', 15, 1)
                                    END
                                    

                        End            
                  End
            End
      End

return

go

--------------------------------
--------------------------------
--------------------------------
--here's the problem as i see it
--------------------------------
--------------------------------
--------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_GenColUpdated]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_GenColUpdated]
go

CREATE  Function dbo.udf_GenColUpdated
      ( @Col int, @ColTotal int)
Returns int
as
Begin
      -- Copyright 2001 Paul Nielsen
      -- This function simulates the Columns_Updated() system function
      Declare       @ColByte int,
                        @ColTotalByte int,
                        @ColBit      int

      -- Calculate Byte Positions
      Set @ColTotalByte =       1 + ((@ColTotal-1) /8)
      Set @ColByte =             1 + ((@Col-1)/8)
      Set @ColBit = @col - ((@colByte-1) * 8)

      -- generate Columns_Updated() value for given column position
      Return Power(2, @colbit + ((@ColTotalByte-@ColByte) * 8)-1)
End


GO


--------------------------------
--------------------------------
--test insert
--------------------------------
--------------------------------

INSERT INTO tempdb.dbo.Table1(col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18, col19, col20)
VALUES(2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20)


/*

results:
all records inserted
audit entries made for fields 9 through 20
returns the following errors (9 errors 8 rows skipped auditing)

Server: Msg 232, Level 16, State 3, Procedure udf_GenColUpdated, Line 19
Arithmetic overflow error for type int, value = 4294967296.000000.
Server: Msg 232, Level 16, State 1, Procedure udf_GenColUpdated, Line 19
Arithmetic overflow error for type int, value = 8589934592.000000.
Server: Msg 232, Level 16, State 1, Procedure udf_GenColUpdated, Line 19
Arithmetic overflow error for type int, value = 17179869184.000000.
Server: Msg 232, Level 16, State 1, Procedure udf_GenColUpdated, Line 19
Arithmetic overflow error for type int, value = 34359738368.000000.
Server: Msg 232, Level 16, State 1, Procedure udf_GenColUpdated, Line 19
Arithmetic overflow error for type int, value = 68719476736.000000.
Server: Msg 232, Level 16, State 1, Procedure udf_GenColUpdated, Line 19
Arithmetic overflow error for type int, value = 137438953472.000000.
Server: Msg 232, Level 16, State 1, Procedure udf_GenColUpdated, Line 19
Arithmetic overflow error for type int, value = 274877906944.000000.
Server: Msg 232, Level 16, State 1, Procedure udf_GenColUpdated, Line 19
Arithmetic overflow error for type int, value = 549755813888.000000.
Server: Msg 232, Level 16, State 1, Procedure udf_GenColUpdated, Line 19
Arithmetic overflow error for type int, value = 2147483648.000000.

*/

0
Comment
Question by:sandhill
2 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 10738281
Maximum value int datatype can take are from -2147483647 to 2147483647. Consider using numeric(25,5).

create table inttest
(
 myint int
)

go

-- This will fail
insert inttest
select 4294967296

-- This wins
insert inttest
select 2147483647

regards-
0
 

Accepted Solution

by:
sqlkrog earned 325 total points
ID: 10739161
You only need to change three lines in the whole script to get it to work correctly.  All three are simply changing the datatype from int to bigint.

1.  In usp_Audit, in the declare statement, change one of the variable types:
     @ColUpdatedTemp bigint,

2.  In udf_GenColUpdated, in the create function statement, change the returns clause:  
     Returns bigint

3.  Also in udf_GenColUpdated, in the return statement at the end of the function, cast the 2 to bigint in the power function's first parameter:
     Return Power(cast(2 as bigint), @colbit + ((@ColTotalByte-@ColByte) * 8)-1)

That should do it.

For clarity, the entire script is pasted below with these changes.  It should run without the arithmetic overflow error and should populate your Audit table correctly.



--------------------------------
--------------------------------
--the table that holds the audits
--------------------------------
--------------------------------
if exists (select * from sysobjects where id = object_id('dbo.Audit') and sysstat & 0xf = 3)
     drop table dbo.Audit
GO
CREATE TABLE dbo.Audit (
     OperationTimestamp datetime NOT NULL ,
     Operation char (1) NOT NULL ,
     AuditID int IDENTITY (1, 1) NOT NULL ,
     SysUser varchar (50) NOT NULL ,
     Application varchar (50) NOT NULL ,
     TableName varchar (50) NOT NULL ,
     PrimaryKey int NOT NULL ,
     [Column] varchar (50) NOT NULL ,
     OldValue nvarchar (255) NULL ,
     NewValue nvarchar (255) NULL
)
go

--------------------------------
--------------------------------
--a table with a lot of fields (more than 32)
--------------------------------
--------------------------------
if exists (select * from sysobjects where id = object_id('dbo.Table1') and sysstat & 0xf = 3)
     drop table dbo.Table1
GO

CREATE TABLE dbo.Table1
     (
     col1 int IDENTITY (1, 1) NOT NULL,
     col2 char(10) NULL,
     col3 char(10) NULL,
     col4 char(10) NULL,
     col5 char(10) NULL,
     col6 char(10) NULL,
     col7 char(10) NULL,
     col8 char(10) NULL,
     col9 char(10) NULL,
     col10 char(10) NULL,
     col11 char(10) NULL,
     col12 char(10) NULL,
     col13 char(10) NULL,
     col14 char(10) NULL,
     col15 char(10) NULL,
     col16 char(10) NULL,
     col17 char(10) NULL,
     col18 char(10) NULL,
     col19 char(10) NULL,
     col20 char(10) NULL,
     col21 char(10) NULL,
     col22 char(10) NULL,
     col23 char(10) NULL,
     col24 char(10) NULL,
     col25 char(10) NULL,
     col26 char(10) NULL,
     col27 char(10) NULL,
     col28 char(10) NULL,
     col29 char(10) NULL,
     col30 char(10) NULL,
     col31 char(10) NULL,
     col32 char(10) NULL,
     col33 char(10) NULL,
     col34 char(10) NULL,
     col35 char(10) NULL,
     col36 char(10) NULL,
     col37 char(10) NULL,
     col38 char(10) NULL,
     col39 char(10) NULL,
     col40 char(10) NULL
     )
go


--------------------------------
--------------------------------
--the stub trigger that calls the dynamic audit with temp tables
--------------------------------
--------------------------------
Create Trigger dbo.trg_table1_Audit_after_iu
On dbo.table1
For Insert, Update
NOT FOR REPLICATION

As
     
     /* Dynamic Audit Trail Code Begin
          (c)2001 Paul Nielsen */
         
     Declare
          @Col_Updated      VarBinary(1028),
          @TableName          nvarchar(100),
          @PrimaryKey          sysname

     set nocount on

     -- Set up the Audit data
     Set @TableName = 'table1'  -- set to the table name
     Set @PrimaryKey = 'col1'  -- set to the column to identify the row
     Set @Col_Updated = Columns_Updated()
     Select * into #TempIn from Inserted
     Select * into #TempDel from Deleted

     -- call the audit stored procedure
     exec dbo.usp_Audit @Col_Updated, @TableName, @PrimaryKey
     
     /* Dynamic Audit Trail Code End */

go


--------------------------------
--------------------------------
--the dynamic audit procedure
--------------------------------
--------------------------------

if exists (select * from sysobjects where id = object_id('dbo.usp_Audit') and sysstat & 0xf = 4)
     drop procedure dbo.usp_Audit
go

CREATE    Proc dbo.usp_Audit

     (
          @Col_Updated      VarBinary(1028),
          @TableName          nvarchar(100),
          @PrimaryKey          sysname
     )

As
     /* dynamic auto-audit trigger/stored procedure

          Copyright 2001 Paul Nielsen
         
          logic:          
               in the calling trigger the inserted and deleted views were selected into temp tables
                    so that  the data could be available to (1) this stored procedure
                    and (2) passed into the exec / dynamic SQL
               Uses the Columns_Updated bits to further optimize    
               handles multi-row inserts / updates without a cursor:
                    join between inserted.PK = deleted.PK AND inserted.column != deleted.column selects only rows with updates in that column
          still needs:
               no error handling yet
               this version is for primary tabels / a version for secondary tables is also needed
                    doesn't handle foreign key updates
                    doesn't handle logging as change to primary table
               lock handling so large updates inserts don't hog the audit table

     */
     
     set nocount on

     Declare
          @ColTotal           int,
          @ColCounter          int,
          @ColUpdatedTemp bigint,
          @ColName               sysname,
          @BlankString      char(1),
          @SQLStr               nvarchar(1000),
          @ColNull           nvarchar(50),
          @SysUser               nvarchar(100),
          @ColumnDataType     int,
          @IsUpdate          bit,
          @tempError          int,
          @OperationTimestamp          nvarchar(50)
         
     --convert some variables to strings because the dynamic sql later likes that
     Set @OperationTimestamp = convert(nvarchar(50), getdate(), 21) -- convert to ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
     Set @SysUser = suser_sname()
     Set @BlankString = ''

     -- Initialize Col variables
     Select @ColCounter = 0
     Select @ColTotal = Count(*)
          From SysColumns                          
          Join SysObjects
               On SysColumns.id = SysObjects.id
          Where SysObjects.name = @TableName

     -- Set IsUpdated Flag
     If Exists(Select * from #tempDel)
          Select @IsUpdate = 1
     Else
          Select @IsUpdate = 0

     -- Column Updates
     While ((Select @ColCounter) != @ColTotal)  -- run through some columns (problems with greater numbers - must find out why!)
     Begin
          Select @ColCounter = @ColCounter + 1
     
          Set @ColUpdatedTemp = dbo.udf_GenColUpdated(@ColCounter,@ColTotal)

          If (@Col_Updated & @ColUpdatedTemp) = @ColUpdatedTemp -- bitwise AND between updated bits and the selected column bit
          Begin
               Select @ColNull = null
               Select @ColName = SysColumns.name, @ColumnDataType = SysColumns.xtype     -- get the column name & Data Type
                    From SysColumns                          
                    Join SysObjects
                         On SysColumns.id = SysObjects.id
                    Where SysObjects.name = @TableName                      
                         and SysColumns.ColID = @ColCounter
               
               If @ColName NOT IN ('') --specify field changes not to appear in the audit log
               Begin
     
                    -- text columns
                    If  @ColumnDataType IN ( 175, 239, 99, 231, 35, 231, 98, 167 )
                         Select @ColNull =  ''''''              

                    -- numeric +  bit columns
                    Else If  @ColumnDataType IN (  106, 62, 56, 60, 108, 59, 52, 122, 104 )
                         Select @ColNull = '0'              

                    -- date columns
                    Else If  @ColumnDataType IN ( 61, 58 )
                         Select @ColNull =  '''1/1/1980'''

                    -- uniqueidentifier columns
                    Else If  @ColumnDataType IN ( 36 )
                         Select @ColNull =  ''''''

                    If @ColNull Is Not Null
                    Begin
                         If @IsUpdate = 1
                              Select @SQLStr =                
     
                              ' Insert Audit(TableName, PrimaryKey, SysUser, [Column],' +
                                     ' Application, OldValue, NewValue, Operation, OperationTimestamp)' +
     
                              ' Select '''+ @TableName + ''',#tempIn.['+ @PrimaryKey + '], ''' + @SysUser + ''', ' +
                                   '''' + @ColName + ''', App_Name(),' +
                                   ' IsNull(convert(nvarchar(100),#tempDel.[' + @ColName + ']),''<null>''), ' +
                                   ' IsNull(convert(nvarchar(100),#tempIn.[' + @ColName + ']),''<null>''),''U''' +
                                   ', ''' + @OperationTimestamp + ''' ' +

     
                              ' From #tempIn' +
                                   '  Join #tempDel' +
                                        ' On #tempIn.['+ @PrimaryKey + '] = #tempDel.['+ @PrimaryKey + ']' +
                                             ' AND isnull(#tempIn.' + @ColName +   ',' + @ColNull + ') != isnull(#tempDel.' + @ColName +   ',' + @ColNull + ')' +
     
                              ' Where Not (#tempIn.[' + @ColName + '] Is Null and #tempDel.[' + @ColName + ']  Is Null)'
     
                         Else -- Insert
                              Select @SQLStr =                
     
                              ' Insert Audit(TableName, PrimaryKey, SysUser, [Column],' +
                                     ' Application, OldValue, NewValue, Operation, OperationTimestamp)' +
     
                              ' Select '''+ @TableName + ''',#tempIn.['+ @PrimaryKey + '], ''' + @SysUser + ''', ' +
                                   '''' + @ColName + ''', App_Name(),' + ' Null, ' +
                                   ' IsNull(convert(nvarchar(100),#tempIn.[' + @ColName +   ']),''<null>''),''I''' +
                                   ', ''' + @OperationTimestamp + ''' ' +
     
     
                              ' From #tempIn' +
                              ' Where Not (#tempIn.[' + @ColName + '] Is Null)'
     
                                --print @SQLStr
                                --print @ColName
                              exec sp_executesql  @SQLStr
                                Set @TempError = @@Error
                              If @TempError <> 0
                              Begin
                                   -- Rollback -- turn this on only if you want a failure to record audit to cancel the data modification operation
                                   Raiserror ('Audit Trail Error', 15, 1)
                              END
                             

                    End          
               End
          End
     End

return

go

--------------------------------
--------------------------------
--------------------------------
--here's the problem as i see it
--------------------------------
--------------------------------
--------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udf_GenColUpdated]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udf_GenColUpdated]
go

CREATE  Function dbo.udf_GenColUpdated
     ( @Col int, @ColTotal int)
Returns bigint
as
Begin
     -- Copyright 2001 Paul Nielsen
     -- This function simulates the Columns_Updated() system function
     Declare      @ColByte int,
                    @ColTotalByte int,
                    @ColBit     int

     -- Calculate Byte Positions
     Set @ColTotalByte =      1 + ((@ColTotal-1) /8)
     Set @ColByte =           1 + ((@Col-1)/8)
     Set @ColBit = @col - ((@colByte-1) * 8)

     -- generate Columns_Updated() value for given column position
     Return Power(cast(2 as bigint), @colbit + ((@ColTotalByte-@ColByte) * 8)-1)
End


GO


--------------------------------
--------------------------------
--test insert
--------------------------------
--------------------------------

INSERT INTO dbo.Table1(col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18, col19, col20)
VALUES(2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now