Description:

When you are referring to http://msdn.microsoft.com/en-us/library/windows/desktop/aa368562(v=vs.85).aspx , and browsing through the examples, you may find that while rest of the commands works as mentioned in the msdn link, the update command throws an error at times

C:\>Cscript WiRunSQL.vbs Test.msi "UPDATE `Error` SET `Error`.`Message`='Testing' WHERE `Error`.`Error`='1322'"

Microsoft (R) Windows Script Host Version 5.8

Copyright (C) Microsoft Corporation. All rights reserved.

Msi API Error 80004005: OpenView,Sql

1: 2232 2: B.msi 3: 1322 4: UPDATE `Error` SET `Error`.`Message`= 'Testing' WHERE `Error`.`Error`='1322'

Resolution:

Check the columns that you are updating. If the column you are updating is a primary key, it will fail with the above error.

To resolve the above issue you need to specify MSIMODIFY_REPLACE value 4. From the msdn documentation: it Updates or deletes and inserts a record into a table. Must first call **MsiViewFetch** with the same record. Updates record if the primary keys are unchanged. **Deletes old row and inserts new if primary keys have changed**. Fails with a read-only database. This mode cannot be used with a view containing joins.

Cause

The above update syntax is trying to update the changes in the table directly because of which it fails. In the script below we are fetching the record making the necessary changes and then adding the record back to the table.

Find the script code below

====================

Const msiOpenDatabaseModeDirect = 2

Const MSIMODIFY_REPLACE = 4

Dim sPathToMSI, sNewVersion, retVal, objWI, objDB, objView, objRecord

WScript.Echo "Specify Select statement: "

        sQuery = WScript.StdIn.ReadLine

        If sQuery="" Then

        WScript.Quit

        End IF

WScript.Echo "Specify Column Value: "

        sNewVersion = WScript.StdIn.ReadLine

        If sNewVersion="" Then

        WScript.Quit

        End IF

Main

Sub Main

    Dim objArgs, ArgCount, cArgument, objFS, sArgument

    retVal = 1

    'Get the command line parameters.

    Set objArgs    = WScript.Arguments

    ArgCount    = objArgs.Count

    If ArgCount = 0 Then

        WScript.Quit

    End If

    sPathToMSI = CStr(WScript.Arguments(0))

    Set objFS = CreateObject("Scripting.FileSystemObject")

    If Not objFS.FileExists(sPathToMSI) Then

        Fail "File: '" & sPathToMSI & "' doesn't exist!"

    End If

    Set objWI = CreateObject("WindowsInstaller.Installer")

    CheckError

    Set objDB = objWI.OpenDatabase(sPathToMSI, msiOpenDatabaseModeDirect)

    CheckError

    Set objView = objDB.OpenView(sQuery)

    CheckError

    objView.Execute

    Set objRecord = objView.Fetch

    If Not objRecord Is Nothing Then

        sOldVersion = objRecord.StringData(1)

        objRecord.StringData(1) = sNewVersion

        objView.Modify MSIMODIFY_REPLACE, objRecord

        CheckError

        objView.Close

        objDB.Commit

    Else

        Fail "No Matching Records found"

    End If

    WScript.Quit retVal

End Sub

Sub CheckError 

    Dim message, errRec 

    If Err = 0 Then Exit Sub End If

    message = Err.Source & " " & Hex(Err) & ": " & Err.Description

    If Not objWI Is Nothing Then 

        Set errRec = objWI.LastErrorRecord 

        If Not errRec Is Nothing Then message = message & vbLf & errRec.FormatText 

    End If

    Fail message

End Sub

Sub Fail(message)

    WScript.Echo message

    WScript.Quit 2

End Sub

=======================================

Content by : Saurav Basu