Long delay in ISSQLServerValidate Action

[lam1278]

Anything I can do to speed up the ISSQLServerValidate action?


My MSI log states that it takes 22 seconds, which on a bigger network can take even longer:

Action 11:02:05: LicenseAgreement. Dialog created
Action 11:02:27: ISSQLServerValidate.
Action start 11:02:27: ISSQLServerValidate.


A little background info, instead of displaying SQLLogin dialog, the following properties are pre-populated and I have placed the following behavior on the "Next" button of our LicenseAgreement dialog:

IS_SQLSERVER_AUTHENTICATION = 1
IS_SQLSERVER_DATABASE = Master
IS_SQLSERVER_PASSWORD = our secret password
IS_SQLSERVER_SERVER = (local)\SQLEXPRESS
IS_SQLSERVER_USERNAME = sa

Event            Argument                   Condition
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
DoAction        ISSQLServerValidate        1
NewDialog       ReadyToInstall             AgreeToLicense = "Yes" and IS_SQLSERVER_STATUS=0

Unfortunately, this is not acceptable to our customer because when you click "Next", sometimes "Not Responding" appears in the title bar of the InstallShield Wizard on the License Agreement screen.

I thought if I set IS_SQLSERVER_LOCAL_ONLY to 1 in the Property Manager it would not attempt to validate all possible SQL servers on the network... but it didn't seem to help.

Any help would be greatly appreciated!!!!

Thanks,
Lynn

😞
This is a duplicate post from InstallShield 12 forum, but, I am seriously desperate for help here.

Also, if anyone can convince me this is a problem in InstallShield 12 and fixed in InstallShield 2009 can more than likely assist in convincing the powers that be that I need to upgrade.

[hidenori]

I am wondering how long it will take when you try connecting to the same database server from the same machine using the ODBC Data Source Administrator in the Control Panel.

[LIST=1]

Open the Administrative Tools | Data Sources (ODBC) in the Control Panel

Click the Add button in the User DSN tab.

Selec the "SQL Server" from the list and click the Finish button.

Provide a unique DSN name like "TestMyDSN" in the Name field, sepcify "(local)\SQLEXPRESS" in the Server field, and then click the Next button.

Provide valid login credentials, and make sure that "Connect to SQL Server to obtain default settings for the additional configuration options" check box is marked.

Click the Next button, and check to see how long it takes to get the next panel.

[lam1278]

Hidenori,

Thanks so much for responding, I really appreciate it!

It took about 7.5 seconds. That's a bit more reasonable than 22 seconds, but still not great.

Do you have any further thoughts or ideas?

I was thinking instead of calling "DoAction" to call "ISSQLServerValidate" on the "Next" button, I could call my own InstallScript custom action instead and then call ISSQLServerValidate directly, but instead display a message like "Connecting to SQLServer".

Thanks again,
Lynn

[lam1278]

Hidenori,

Is it necessary to call "ISSQLServerFilteredList" in User Interface sequence if I am not displaying "SQLBrowse" or "SQLLogin" dialogs in the UI??

Would that speed anything up?

[hidenori]

7.5 seconds is the performace of the SQL Server ODBC driver provided from Microsoft on your machine. There is not much we can do about it. I would like to check how the SQL Native Client ODBC driver does. Can you do the same test with selecting "SQL Native Client" from the driver list in step 3? If you see a better result, please try forcing InstallShield to use the SQL Server Native Client OLE DB provider by following the steps below, and check if you see any differenece on the performance of the ISSQLServerValidate action:

[LIST=1]

Go to the ISSQLDBMetaData table in the Direct Editor.

Change the value of the AdoDriverName column to "SQLNCLI".

Rebuild your setup.

[hidenori]

If you don't display the Server Name combobox on the SQLLogin dialog, it is not necessary to call the ISSQLServerFilteredList action. However, removing the action wouldn't improve the performance of the ISSQLServerValidate action.

[lam1278]

hidenori wrote:
7.5 seconds is the performace of the SQL Server ODBC driver provided from Microsoft on your machine. There is not much we can do about it. I would like to check how the SQL Native Client ODBC driver does. Can you do the same test with selecting "SQL Native Client" from the driver list in step 3? If you see a better result, please try to force InstallShield using the SQL Server Native Client OLE DB provider by following the steps below, and check if you see any differenece on the performance of the ISSQLServerValidate action:

[LIST=1]

Go to the ISSQLDBMetaData table in the Direct Editor.

Change the value of the AdoDriverName column to "SQLNCLI".

Rebuild your setup.

I understand what your saying about the limitations of the Microsoft provider, that's basically what our database guru here said too.

I got a little better results, about 6.9 secs.

I will do as you suggested and try forcing it to the SQL Native Client connection and rebuild my huge project (takes a bit of time). I'll let you know the results tomorrow.

[lam1278]

hidenori wrote:
If you don't display the Server Name combobox on the SQLLogin dialog, it is not necessary to call the ISSQLServerFilteredList action. However, removing the action wouldn't improve the performance of the ISSQLServerValidate action.

Thanks again... that's kind of what I guessed. It was worth a shot.

Do subsequent calls to ISSQLServerValidate speed things up? Meaning, if I scheduled that action early enough in the sequence, would a call to it a second time be quicker?

[lam1278]

I doubt this is related to ISSQLServerValidate, but, I really only want my (local)\SQLEXPRESS connection validated, not every connection on the network validated.

Is there any correct "value" to set IS_SQLSERVER_LOCAL_ONLY to in the Property Manager?

I set it to 1, but I read on other posts that it may have to be set in a Custom Action? Is this true?

[hidenori]

Do subsequent calls to ISSQLServerValidate speed things up? Meaning, if I scheduled that action early enough in the sequence, would a call to it a second time be quicker?

No, the ISSQLServerValidate action validates the specified connections by attempting to connect to database servers each time it is called.

[hidenori]

I doubt this is related to ISSQLServerValidate, but, I really only want my (local)\SQLEXPRESS connection validated, not every connection on the network validated.

Is there any correct "value" to set IS_SQLSERVER_LOCAL_ONLY to in the Property Manager?

I set it to 1, but I read on other posts that it may have to be set in a Custom Action? Is this true?

The IS_SQLSERVER_LOCAL_ONLY property is designed to list only local database servers in the SQLBrowse dialog. It doesn't affect the behavior of the ISSQLServerValidate action. If you have multiple connections that are configured in the SQL Scripts view, and you want to validate only particular connections, you need to use the IS_SQLSERVER_CONNECTIONS_TO_VALIDATE property.

[hidenori]

I am also wondering how the ISSQLServerValidate action does with a clean and very simple project. Can you try the steps below and see how long it takes to get the next dialog after the SQLLogin dialog?

[LIST=1]

Create a new Basic MSI project.

Add a feature and component in the Setup Design view.

Add a connection in the SQL Scripts view.

Build and run the setup.

Proceed to the SQLLogin dialog, and provide valid login information.

Click the Next button and observe.

[lam1278]

Thank you Hidenori,

I have seen huge improvements in the time of the ISSQLServerValidate function since I switched to using the AdoDriverName to "SQLNCLI" in the ISSQLDBMetaData table.

I also added a specific version of SQL to target in the SQL Scripts view, where previously I didn't have anything selected. Not sure if that helped things any, but I am VERY happy about the improvements.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
( I may open this up in a new thread for the following, but since I have your attention )

I re-read the bug report relating to this issue, and there is still something weird going on with "InstallWelcome" -- when the "Next" button becomes active, it's almost as if I have to click "Next" twice... or it's just that it is not responding. And this weirdness with the Next button ONLY happens for an upgrade, a clean install does not exhibit this behavior... you click "Next"... and it goes right to LicenseAgreement.

I thought it had something to do with some whacky condition where it was calling InstallWelcome and then PatchWelcome or even where SetupInitialization looks like InstallWelcome and some how the "Next" button became active before it was...

But, as far as I can tell, there is nothing out of the ordinary there.

Doesn't SetupInitialization subscribe to a bunch of ActionData and therefore SHOULD stay active and alive for the following sequences and not display the "Next" button????

FindRelatedProducts
CCPSearch
RMCCPSearch
ValidateProductID
CostInitialize
FileCost
IsolateComponents
ResolveSource
CostFinalize
MigrateFeatureStates

It's almost as if SetupInitialization goes straight to InstallWelcome, and the hold up is all of the above sequences, but I am not sure how to prove that...

If this is not your area of expertise, I understand, but you have helped me significantly in getting this all in order!!

Thanks so much!

[hidenori]

I'm glad that your database issue has been solved. Unfortunately, I don't have an answer for your new question. Please open up a new thread.

Regards.

[lam1278]

Thanks, I did just that.

I was digging deeper into my issue, and found some more info and opened up this post.

Thanks again!