Filtered list displaying specific instance of SQL Server on SQL Browse

[savy17]

Is it possible to show only the specified instance of SQL Server in a dialog that opens when SQL Browse button is clicked.

Suppose, I have 2 computers on a network having SQL Server Express installed and each computer has 2 instances. Say Comp1/Instance1, Comp1/Instance2, Comp2/Instance1 and Comp2/Instance2

Right now when I click SQL Browse, the list shows all of them - Comp1/Instance1, Comp1/Instance2, Comp2/Instance1 and Comp2/Instance2

I want to filter and only show the Instance1 when clicking the SQL Browse button. So that the dialog should only show Comp1/Instance1, Comp2/Instance2.

Is there any way to filter the list???

[savy17]

Can anybody help me out with this problem? Or is it simply not possible?

savy17 wrote:
Is it possible to show only the specified instance of SQL Server in a dialog that opens when SQL Browse button is clicked.

Suppose, I have 2 computers on a network having SQL Server Express installed and each computer has 2 instances. Say Comp1/Instance1, Comp1/Instance2, Comp2/Instance1 and Comp2/Instance2

Right now when I click SQL Browse, the list shows all of them - Comp1/Instance1, Comp1/Instance2, Comp2/Instance1 and Comp2/Instance2

I want to filter and only show the Instance1 when clicking the SQL Browse button. So that the dialog should only show Comp1/Instance1, Comp2/Instance2.

Is there any way to filter the list???

[hidenori]

If you are using a Basic MSI project, you can write a custom action that will modify items of a List Box. The following is a sample VBScript that will delete the "(local)\SQLEXPRESS" entry from the IS_SQLSERVER_LIST List Box.

Const msiViewModifyDelete = 6
Const IDOK = 1

Function CustomizeSQLServerList( )

' 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

While Not (reclist Is Nothing)
    If reclist.StringData(4)="(local)\SQLEXPRESS" Then
         ' delete the ListBox record
         viewlist.Modify msiViewModifyDelete, reclist
    End If

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

' clean up
viewlist.Close
' return success to MSI
CustomizeSQLServerList = IDOK
End Function

Hope that helps.

[savy17]

Hi hidenori,

Thanks for your reply. I tried as you said. Created a VBScript and in Custom Action, said to Run the VBScript stored at Binary. Its Install Exec Sequence is set to execute 'After InstallInitialize'

However it is making no changes. I can see (local)\SQLEXPRESS instance in the Browse button.

I also tried to run this custom action through DOAction event on the Browse Button on SQLLogin dialog but no effect.

I am not sure what I am doing wrong.

Also, from the IS_SQLSERVER_LIST, I would like to filter out all the SQLEXPRESS instances. Be it (local)\SQLEXPRESS or somecomputer\SQLEXPRESS on network like Computer1234\SQLEXPRESS, Computer1235\SQLEXPRESS. To acheive this, what changes need to be made to the script function?

Hope to get some solution.

Thanks

hidenori wrote:
If you are using a Basic MSI project, you can write a custom action that will modify items of a List Box. The following is a sample VBScript that will delete the "(local)\SQLEXPRESS" entry from the IS_SQLSERVER_LIST List Box.

Const msiViewModifyDelete = 6
Const IDOK = 1

Function CustomizeSQLServerList( )

' 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

While Not (reclist Is Nothing)
    If reclist.StringData(4)="(local)\SQLEXPRESS" Then
         ' delete the ListBox record
         viewlist.Modify msiViewModifyDelete, reclist
    End If

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

' clean up
viewlist.Close
' return success to MSI
CustomizeSQLServerList = IDOK
End Function

Hope that helps.

[hidenori]

You need to run your custom action from the BtnSQLBrowse push button control on the SQLLogin dialog through the DoAction event. It needs to be sequenced between "DoAction ISSQLServerList" and "SpawnDialog SQLBrowse" events.

Also, you may want to use the InStr() VBScript function in order to find all instances of SQLEXPRESS:

if(InStr(reclist.StringData(4), "\SQLEXPRESS") > 0)

[savy17]

Thanks for the reply. However I am getting Error 2762. Cannot write script record. Transaction not started.

So far what I have done is:
Created a VB Script with the code that you have given.
Created the custom action to run the vb script. In Custom Action, I set the following properties
In-Script Execution -> Deferred Execution in System Context
Install Exec Sequence -> After RemoveRegistryValues

Then I am running this custom action from BtnSQLBrowse push button control on SQLLogin dialog. I added a DoAction event which is sequenced between "DoAction ISSQLServerList" and "SpawnDialog SQLBrowse" events.

However when I run the installer and click BtnSQLBrowse push button, it gives the above mentioned error 2762.

Please let me know what different I have to do

hidenori wrote:
You need to run your custom action from the BtnSQLBrowse push button control on the SQLLogin dialog through the DoAction event. It needs to be sequenced between "DoAction ISSQLServerList" and "SpawnDialog SQLBrowse" events.

Also, you may want to use the InStr() VBScript function in order to find all instances of SQLEXPRESS:

if(InStr(reclist.StringData(4), "\SQLEXPRESS") > 0)

[hidenori]

You must use an immediate execution custom action for this because of the Limitations of deferred execution custom actions.

[savy17]

I have also tried with immediate execution but it is also not working..

However when I have immediate execution, I am not getting earlier error, but when I click the BtnSqlBrowse push button, The InstallShield Wizard Completed dialog is shown with the message "The wizard was interrupted before MyApplication could be completely installed."

If I look through the msi.log, it has following entry

Action start 12:31:34: ISSQLServerList.
Action ended 12:31:41: ISSQLServerList. Return value 1.
Action 12:31:41: CustomizeSQLServerList.
Action start 12:31:41: CustomizeSQLServerList.
Action ended 12:31:41: CustomizeSQLServerList. Return value 3.
Info 2896.Executing action CustomizeSQLServerList failed.
Action ended 12:31:41: InstallWelcome. Return value 3.
Action 12:31:41: SetupCompleteError.

CustomizeSQLServerList is the name of my custom action

While creating a Custom action, I have chosen
location ->Stored in a Binary Table
Source -> \script files\CustomizeSQLServerList.vbs
Target -> CustomizeSQLServerList( ) which is my function name
Return Processing -> Synchronous (Check exit code)
In-Script Execution -> Immediate Execution
Execution Scheduling -> Always execute
Install UI Sequence -> Absent from sequence
Install Execute Sequence -> Absent from sequence

I don't know what wrong I am doing but I am not able to get it right.. Please help.

hidenori wrote:
You must use an immediate execution custom action for this because of the Limitations of deferred execution custom actions.

[hidenori]

You need to specify a function name without parentheses for the Target property of the custom action. If it still fails, please make sure that you have no VBScript syntax errors. You may want to put some MsgBox calls to check if your function gets executed properly.

[savy17]

Thanks hidenori for all the suggestion and help. It is working now.

[savy17]

Hidenori,
I still have one issue. With the vbscript that you gave, it works fine when I click the browse button on SQL Login page. It opens list box without SQLEXPRESS instances.

However if you click on the combo box (drop down control), you can still be able to see SQLEXPRESS instance of local server and it shows two entries

1. (local)\MyInstance
2. (local)\SQLEXPRESS

Instead I would like to see only MyInstance. So (local)\MyInstance.

I guess I would have to write another vb script to do that but don't know which properties to modify.

Can you please suggest me what to do to get this?

Thanks,

[hidenori]

You need to create a similar custom action that will modify the items of the IS_SQLSERVER_SERVER Combo Box (OpenView("SELECT * FROM `ComboBox` WHERE `Property`='IS_SQLSERVER_SERVER'")). The custom action needs to be called after the ISSQLServerFilteredList action.

[Singaravelan]

Hello Hidenori,

Thanks for your inputs for this point. I am trying to configure my requirements where instead of the SQL Server listing, I want to display the list of the connected user in the system for the user to decide whether to proceed with the installation or not.

I did make a custom action like you mentioned. If I have a function defined, somehow the system control is not getting into the function call. The Msgbox given before the function call gets executed successfully.

I am not sure I understand what you said about the Target property which should have the function without paranthesis. Could you please help?

Thanks,
Velan