FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Scripting DB to VSS: Question about Diffs





MrBaseball34
In the following portion of David Jackson's larger script, I am
scripting out my Database tables.

My problem now lies in the fact that if there have been no changes to
the table, I would like to not modify the VSS history for the table by
checking in a version that is the same as the last checked in version.

How would I "diff" the file in oTS with the VSS item oVSSItem?
(see comments below)

Code:

For Each oDatabaseObject In oDatabase.Tables
  If oDatabaseObject.SystemObject Then
    'do nothing (bypass system objects)
  Else
    iScriptOptions = SQLDMOScript_Default + SQLDMOScript_Drops + _           
                     SQLDMOScript_Indexes
    sScript = oDatabaseObject.Script(iScriptOptions)
    sVSSItemPath = VSS_ROOTPROJECT_NAME & sObjectType & "/" & _
                   oDatabaseObject.Name & ".sql"
    sFileName = sCurrDirectory & "\" & oDatabaseObject.Name & ".sql"
    On Error Resume Next
    Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
    If Err = 0 Then 'item is already on SourceSafe
      oVSSItem.Checkout "Checked out by automated process", _
                         sFileName
      Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
      oTS.WriteLine (sScript)
      oTS.Close
      '
      ' Here is where I need to DIFF the file and not check it in
      ' if it is the same version.
      '
      ' If Not FileIsSame Then
          oVSSItem.Checkin "Schema Altered", sFileName
      ' Else
      '   oVSSItem.UndoCheckout
      ' End If
      itemCounter = itemCounter + 1
    Else 'item does not yet exist on SourceSafe; add it
      Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
      oTS.Write (sScript)
      oTS.Close
      Set oVSSItem = oVSSDatabase.VSSItem(VSS_ROOTPROJECT_NAME & _
                     sObjectType & "/")
      oVSSItem.Add sFileName
      itemCounter = itemCounter + 1
    End If
    Set oVSSItem = Nothing
  End If
Next
MrBaseball34
Here is the final working solution:
Code:

Case "Tables"
    For Each oDatabaseObject In oDatabase.Tables
        If oDatabaseObject.SystemObject Then
            'do nothing (bypass system objects)
        Else
            iScriptOptions = SQLDMOScript_Default + SQLDMOScript_Drops + _
                             SQLDMOScript_Indexes
            sScript = oDatabaseObject.Script(iScriptOptions)
            sVSSItemPath = VSS_ROOTPROJECT_NAME & sObjectType & "/" & _
                           oDatabaseObject.Name & ".sql"
            sFileName = sCurrDirectory & "\" & oDatabaseObject.Name & ".sql"
            On Error Resume Next
            Set oVSSItem = oVSSDatabase.VSSItem(sVSSItemPath)
            If Err = 0 Then 'item is already on SourceSafe
                oVSSItem.Checkout "Checked out by automated process", sFileName
                Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
                oTS.WriteLine (sScript)
                oTS.Close
                bDifferent = oVSSItem.IsDifferent(sFileName)
                If bDifferent Then
                    oVSSItem.Checkin "Table " & oDatabaseObject.Name & _
                                     " Altered", sFileName
                    itemCounter = itemCounter + 1
                Else
                    bCheckedOut = oVSSItem.IsCheckedOut
                    If bCheckedOut > 0 Then
                        oVSSItem.UndoCheckout
                    End If
                End If                       
            Else 'item does not yet exist on SourceSafe; add it
                Set oTS = oFSO.OpenTextFile(sFileName, ForWriting, True)
                oTS.Write (sScript)
                oTS.Close
                Set oVSSItem = oVSSDatabase.VSSItem(VSS_ROOTPROJECT_NAME & _
                                                    sObjectType & "/")
                oVSSItem.Add sFileName
                itemCounter = itemCounter + 1
            End If
            Set oVSSItem = Nothing
        End If
    Next


If you'd like the full script check out this thread:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=149&messageid=311601
Related topics
Good old question: What is the the best CMS
Advanced MySQL db merging Tutorials
speaker sizes and power ratings
Creating a poll
script no longer working on server 2 ????
question aux codeurs ! php noob InsiDe!!
Mod Projects lots of info
Question: PHP scripting + cron jobs
dumb nu-b question on phpmyadmin
Problems with Member System (RMB-SCRIPTING)
Ruby on Rails aliasing db fields
help getting form info into mysql db
Sessions & cookies - member no longer active question
Uploading site, public_html folder and server question
Reply to topic    Frihost Forum Index -> Scripting -> Others

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.