Find the current/next value of Identity column in SQL Server

While creating some data scripts today I needed to find a way to insert values for Identity column - that's fine I can use 'SET IDENTITY_INSERT TableName ON' and specify the values but how do I know what value to start from?

Well after some searching I came across a snippet of code that gets the current value of the Identity column and the value that needs to be added to it to get the next valid number to use. Here's some sample code that demonstrates getting values and inserting data to prove that the values match what SQL Server would have done.

CREATE TABLE TestIdentity (
  Id int NOT NULL IDENTITY (1, 5),
  Value varchar(20)
)

SELECT IDENT_CURRENT('TestIdentity')
SELECT IDENT_INCR('TestIdentity')

--------------------------------------- ---------------------------------------
1                                       5

(1 row(s) affected)

SELECT IDENT_CURRENT('TestIdentity')+ IDENT_INCR('TestIdentity') 
---------------------------------------
6

INSERT INTO TestIdentity VALUES ('Test1')
INSERT INTO TestIdentity VALUES ('Test2')

SELECT * FROM TestIdentity

Id          Value
----------- --------------------
1           Test
6           Test

(2 row(s) affected)

Testing thrown exceptions (ArgumentException)

While creating some unit tests I wanted to make sure that the correct exception was being thrown and as I was testing for ArgumentExceptions that the correct parameter was causing the ArgumentException. So after referring to my developers guide aka Google. I came across a post on StackOverflow

A small example from the post: -

var ex = Assert.Throws<ArgumentNullException>(() => foo.Bar(null));
Assert.That(ex.ParamName, Is.EqualTo("bar"));

Word Macro to insert Watermark

At work we have a service that generates Word documents to send out to customers. The documents are generated by combining paragraphs of text to make a single document. When a paragraph is changed any document that references it must be checked to make sure that the change has been made correctly and the layout of the document is still correct.

Normally the UAT department will go through each document to check that it's correct. This is ok until a change is made to a common paragraph that effects several documents. A while back somebody asked if we could automatically generate every single document and add a watermark to it to indicate which document it was once printed.

Below is the VBA code written to automatically add a watermark to a document (or in this case every open document).  The CreateDictionary is basically a sub that populates a global dictionary with every possible filename and it's description - an SQL statement is used to pull out all the document names/description combinations.

Sub AddWaterMarks()

    Dim liCounter As Integer
    Dim liUnderScorePos As Integer
    Dim lsDocName As String
    Dim lsWatermarkText As String
    
    CreateDictionary
    
    For liCounter = 1 To Documents.Count          
        lsDocName = Documents(liCounter).Name        
        liUnderScorePos = InStr(lsDocName, "_")        
        If liUnderScorePos > 0 Then            
            lsDocName = Mid$(lsDocName, 1, liUnderScorePos - 1)            
            Documents(liCounter).Activate                    
            lsWatermarkText = gDict(lsDocName)            
            InsertWaterMark lsDocName & " - " & lsWatermarkText                    
        End If            
    Next liCounter
    
    MsgBox "Done!", vbInformation + vbOKOnly, "Document Watermarker"
    
End Sub

Sub InsertWaterMark(TextToInsert As String)

    ActiveDocument.Sections(1).Range.Select
    ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
    Selection.HeaderFooter.Shapes.AddTextEffect(msoTextEffect1 _
        , TextToInsert, "Calibri", 1, _
         False, False, 0, 0).Select
    Selection.ShapeRange.TextEffect.NormalizedHeight = False
    Selection.ShapeRange.Line.Visible = False
    Selection.ShapeRange.Fill.Visible = True
    Selection.ShapeRange.Fill.Solid
    Selection.ShapeRange.Fill.ForeColor.RGB = RGB(192, 192, 192)
    Selection.ShapeRange.Fill.Transparency = 0.5
    Selection.ShapeRange.Rotation = 315
    Selection.ShapeRange.LockAspectRatio = True
    Selection.ShapeRange.Height = CentimetersToPoints(3.06)
    Selection.ShapeRange.Width = CentimetersToPoints(19.38)
    Selection.ShapeRange.WrapFormat.AllowOverlap = True
    Selection.ShapeRange.WrapFormat.Side = wdWrapNone
    Selection.ShapeRange.WrapFormat.Type = 3
    Selection.ShapeRange.RelativeHorizontalPosition = _
        wdRelativeVerticalPositionMargin
    Selection.ShapeRange.RelativeVerticalPosition = _
        wdRelativeVerticalPositionMargin
    Selection.ShapeRange.Left = wdShapeCenter
    Selection.ShapeRange.Top = wdShapeCenter
    ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
    
End Sub

So hopefully now I've blogged it next time my normal.dot gets blasted away I'll have a backup of at least one macro I've written :-)