cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Barbara
Level 7

Use ISSQLQueryDatabase to retrive a list of SQL databases

I want to use the InstallShield CA ISSQLQueryDatabases to retrieve a list of databases for a given server. The list should be given in a MSI property. I do the following:
1. I use the dialog SQLLogin to select the SQLServer
2. On the next button of the dialog I set the property IS_SQL_DB_BROWSE=1 and execute the action ISSQLQueryDatabase.
3. As I understand the logic, the property IS_SQLSERVER_LIST should be filled with the names of the databases, but the property is empty.

When I use the dialog SQLBrowse I get a correct list of the present databases for the selected server.

So my question: What property is populated with the database names, so that I can retrieve the property an check the names without using the dialog SQLBrowse.

Thank you
Barbara
Labels (1)
0 Kudos
(3) Replies
hidenori
Level 17

It is stored in the IS_SQLSERVER_FLATLIST Windows Installer property.
0 Kudos
Barbara
Level 7

The property IS_SQLSERVER_FLATLIST is not set. I attach a part of the logfile, where you can see which custom actions are executed.

[CODE]
Aktion 09:23:04: SQLLogin. Dialog created
MSI (c) (DC:70) [09:23:04:488]: PROPERTY CHANGE: Deleting MsiSelectionTreeSelectedFeature property. Its current value is 'SQLDatabase'.
MSI (c) (DC:70) [09:23:04:488]: PROPERTY CHANGE: Deleting MsiSelectionTreeSelectedAction property. Its current value is '3'.
MSI (c) (DC:70) [09:23:04:488]: PROPERTY CHANGE: Deleting MsiSelectionTreeSelectedCost property. Its current value is '0'.
MSI (c) (DC:70) [09:23:08:170]: Doing action: ISSQLServerValidate
Aktion 09:23:08: ISSQLServerValidate.
Aktion gestartet um 09:23:08: ISSQLServerValidate.
MSI (c) (DC:F8) [09:23:08:224]: Invoking remote custom action. DLL: C:\Users\ADMINI~1.TES\AppData\Local\Temp\MSIF9CC.tmp, Entrypoint: ISSQLServerValidate
MSI (c) (DC!14) [09:23:08:275]: PROPERTY CHANGE: Adding ISSQLServerValidateLogNode0 property. Its value is 'Beginning SQL Server Costing Process...'.
MSI (c) (DC!14) [09:23:08:278]: PROPERTY CHANGE: Adding ISSQLServerValidateLogNode1 property. Its value is 'Reading the SQL script data from ISSQLConnection table...'.
MSI (c) (DC!14) [09:23:08:291]: PROPERTY CHANGE: Adding ISSQLServerValidateLogNode2 property. Its value is 'ISSQLRequirement table does not exist...'.
MSI (c) (DC!14) [09:23:08:319]: PROPERTY CHANGE: Adding ISSQLServerValidateLogNode3 property. Its value is 'Finished SQL Server Costing Process...'.
MSI (c) (DC!14) [09:23:08:530]: PROPERTY CHANGE: Adding IS_SQLSERVER_STATUS property. Its value is '0'.
Aktion beendet um 09:23:08: ISSQLServerValidate. Rückgabewert 1.
MSI (c) (DC:70) [09:23:08:534]: Doing action: GetServerInstanceName
Aktion 09:23:08: GetServerInstanceName.
Aktion gestartet um 09:23:08: GetServerInstanceName.
MSI (c) (DC:60) [09:23:09:763]: Invoking remote custom action. DLL: C:\Users\ADMINI~1.TES\AppData\Local\Temp\MSIFB44.tmp, Entrypoint: f9
MSI (c) (DC!2C) [09:23:10:819]: PROPERTY CHANGE: Adding SQLSERVERNAME property. Its value is 'PROTEAM2'.
MSI (c) (DC!2C) [09:23:10:819]: PROPERTY CHANGE: Adding SQLSERVERINSTANCE property. Its value is 'MSSQLSERVER'.
MSI (c) (DC:60) [09:23:10:851]: NOTE: custom action GetServerInstanceName unexpectedly closed the hInstall handle (type MSIHANDLE) provided to it. The custom action should be fixed to not close that handle.
Aktion beendet um 09:23:10: GetServerInstanceName. Rückgabewert 1.
MSI (c) (DC:70) [09:23:10:853]: PROPERTY CHANGE: Adding IS_SQL_DB_BROWSE property. Its value is '1'.
MSI (c) (DC:70) [09:23:10:854]: PROPERTY CHANGE: Adding IS_SQL_SERVER_BROWSE property. Its value is '0'.
MSI (c) (DC:70) [09:23:10:854]: Doing action: ISSQLQueryDatabases
Aktion 09:23:10: ISSQLQueryDatabases.
Aktion gestartet um 09:23:10: ISSQLQueryDatabases.
MSI (c) (DC:A0) [09:23:10:921]: Invoking remote custom action. DLL: C:\Users\ADMINI~1.TES\AppData\Local\Temp\MSI45D.tmp, Entrypoint: ISSQLQueryDatabases
MSI (c) (DC:C0) [09:23:10:930]: Note: 1: 2262 2: ListBox 3: -2147287038
MSI (c) (DC:C0) [09:23:10:931]: PROPERTY CHANGE: Deleting IS_SQLSERVER_STATUS property. Its current value is '0'.
MSI (c) (DC:C0) [09:23:10:973]: PROPERTY CHANGE: Adding IS_SQLSERVER_STATUS property. Its value is '0'.
MSI (c) (DC:C0) [09:23:10:974]: Note: 1: 2262 2: ListBox 3: -2147287038
Aktion beendet um 09:23:10: ISSQLQueryDatabases. Rückgabewert 1.
MSI (c) (DC:70) [09:23:10:981]: Doing action: CheckProomDatabase
Aktion 09:23:10: CheckProomDatabase.
Aktion gestartet um 09:23:10: CheckProomDatabase.
MSI (c) (DC:38) [09:23:12:621]: Invoking remote custom action. DLL: C:\Users\ADMINI~1.TES\AppData\Local\Temp\MSI4DB.tmp, Entrypoint: f17
MSI (c) (DC:38) [09:23:18:752]: NOTE: custom action CheckProomDatabase unexpectedly closed the hInstall handle (type MSIHANDLE) provided to it. The custom action should be fixed to not close that handle.
Aktion beendet um 09:23:18: CheckProomDatabase. Rückgabewert 1.
[/CODE]

I cannot find the property IS_SQLSERVER-FLATLIST in the entire logfile. Nevertheless, if I use the SQLBrowse dialog I get a complete list of the available databases displayed.

Barbara
0 Kudos
hidenori
Level 17

Another thing you may want to try is to query the ListBox table items tied to the IS_SQLSERVER_LIST property. The following sample VBScript illustrates how you query the database names from the ListBox table and set to the property named MYDATABASES in a comma-delimited manner.

Const IDOK = 1

Function GetDatabases()

' open and execute a view to the ListBox table
Set viewlist = Database.OpenView("SELECT * FROM `ListBox` WHERE `Property`='IS_SQLSERVER_LIST'")
viewlist.Execute
Set reclist = viewlist.Fetch

Dim databases
While Not (reclist Is Nothing)
If databases <> vbNullString Then
databases = databases & ","
End If
databases = databases & reclist.StringData(4)

' fetch the next ListBox record
Set reclist = viewlist.Fetch
Wend

Session.Property("MYDATABASES") = databases

' clean up
viewlist.Close
' return success to MSI
GetDatabases= IDOK
End Function
0 Kudos