How to diagnose SQL actions?

[buri]

I made an installer with a custom SQL connection. It has a transaction for that connection and a couple scripts under that transaction, one to run on install, one to run on uninstall. When I run the msi, the installer gets to "Generating actions to configure databases for install SQL script execution" and just hangs. The progress bar does its glowy thing now and then every second or so. This tells me the installer itself hasn't hung. I did find some errors with my custom connection string and fixed those. The same is true of my SQL scripts. Those should no longer be potential issues.

How do I go about debugging what else is going on to see why it hangs?

[buri]

I turned on MSI logging in the registry and this is what I see.

Code: Select all

Action start 16:12:52: AI_SqlInstall.
MSI (c) (38:B0) [16:12:52:767]: Creating MSIHANDLE (9275) of type 790531 for thread 7344
MSI (c) (38:B0) [16:12:52:767]: Closing MSIHANDLE (9275) of type 790531 for thread 7344
MSI (s) (A8:10) [16:12:52:787]: Creating MSIHANDLE (3973) of type 790542 for thread 1808
MSI (s) (A8:40) [16:12:52:788]: Invoking remote custom action. DLL: C:\WINDOWS\Installer\MSI75CE.tmp, Entrypoint: OnSqlInstall
MSI (s) (A8!24) [16:12:52:794]: Creating MSIHANDLE (3974) of type 790541 for thread 8228
MSI (s) (A8!24) [16:12:52:794]: Creating MSIHANDLE (3975) of type 790540 for thread 8228
MSI (s) (A8!24) [16:12:52:794]: Creating MSIHANDLE (3976) of type 790531 for thread 8228
MSI (s) (A8!24) [16:12:52:795]: Closing MSIHANDLE (3976) of type 790531 for thread 8228
MSI (s) (A8!24) [16:12:52:795]: Creating MSIHANDLE (3977) of type 790531 for thread 8228
MSI (s) (A8!24) [16:12:52:795]: Closing MSIHANDLE (3977) of type 790531 for thread 8228
MSI (s) (A8!24) [16:12:52:795]: Creating MSIHANDLE (3978) of type 790531 for thread 8228
MSI (s) (A8!24) [16:12:52:796]: Closing MSIHANDLE (3978) of type 790531 for thread 8228
MSI (s) (A8!24) [16:12:52:796]: Creating MSIHANDLE (3979) of type 790531 for thread 8228
MSI (s) (A8!24) [16:12:52:796]: Closing MSIHANDLE (3979) of type 790531 for thread 8228
MSI (s) (A8!24) [16:12:52:796]: Closing MSIHANDLE (3975) of type 790540 for thread 8228
MSI (s) (A8!24) [16:12:52:796]: Creating MSIHANDLE (3980) of type 790540 for thread 8228
MSI (s) (A8!24) [16:12:52:796]: Creating MSIHANDLE (3981) of type 790531 for thread 8228
MSI (s) (A8!24) [16:12:52:797]: Closing MSIHANDLE (3981) of type 790531 for thread 8228
MSI (s) (A8!24) [16:12:52:797]: Creating MSIHANDLE (3982) of type 790531 for thread 8228
MSI (s) (A8!24) [16:12:52:797]: Closing MSIHANDLE (3982) of type 790531 for thread 8228
MSI (s) (A8!24) [16:12:52:797]: Creating MSIHANDLE (3983) of type 790531 for thread 8228
MSI (s) (A8!24) [16:12:53:044]: Closing MSIHANDLE (3983) of type 790531 for thread 8228
MSI (s) (A8!24) [16:12:53:044]: Creating MSIHANDLE (3984) of type 790531 for thread 8228
MSI (s) (A8!24) [16:12:53:044]: Closing MSIHANDLE (3984) of type 790531 for thread 8228
MSI (s) (A8!24) [16:12:53:123]: Creatin

That's not an incomplete copy/paste. This is the entirety of my log from the AI_SqlInstall action to the end. It cuts off mid-word on the last line.

[Dan]

Hello,

I'm not sure why you encountered this behavior.
You can try to run your installation package from Advanced Installer using the Run >> Run and log option from the Home tab Ribbon control. Using this option, it will launch the current project in logging mode. If the project is not already built, this option will first build the project and then run the generated package.

Also, you can take a look on the Test SQL Connection article which may be useful to you.

Please let us know if that helped, otherwise give us more details.

Best regards,
Dan

[buri]

Attached is a zip containing the rest of the MSI log file I pasted earlier as well as the log the installer itself presented as it had a "View Log" checkbox on the last dialog. Note that it took several hours for the entire process to finish. Also attached is my .aip file. If the paths look a little odd, I've scrubbed some values from it. Also, I took out a bunch of the Creating/Closing MSIHANDLE lines since, even compressed, the zip file was over 1 MB. Here are a few values that might be of immediate import in relation to SQL connections and the like.

Code: Select all

  <COMPONENT cid="caphyon.advinst.msicomp.SqlConnectionComponent">
    <ROW ConnectionName="CustomConnection" Condition="1" Dbms="0" Flags="4" Order="0" LoginTimeout="60" ConnectionString="Driver={SQL Server};Server=[MF_SQL_SERVER];[MF_SQL_AUTH];"/>
  </COMPONENT>
  <COMPONENT cid="caphyon.advinst.msicomp.SqlReplaceComponent">
    <ROW ScriptName="Create  Database" Token="_" Replacement="[MF_SQL_DATABASE_NAME]" Flags="0"/>
    <ROW ScriptName="Drop  Database" Token="_" Replacement="[MF_SQL_DATABASE_NAME]" Flags="0"/>
    <ROW ScriptName="Create  Database" Token="user_password" Replacement="[MF_USER_PASSWORD]" Flags="0"/>
    <ROW ScriptName="Create  Database" Token="admin..localhost.com" Replacement="[MF_ADMIN_URL]" Flags="0"/>
    <ROW ScriptName="Create  Database" Token="demo..localhost.com" Replacement="[MF_DEMO_URL]" Flags="0"/>
  </COMPONENT>
  <COMPONENT cid="caphyon.advinst.msicomp.SqlScriptComponent">
    <ROW ScriptName="Create  Database" ConnectionName="CustomConnection" Condition="1" Separator="GO" ScriptText="Other Resources\SQL Scripts\Create  Database.sql" Flags="274" Order="0" Transaction="Transaction"/>
    <ROW ScriptName="Drop  Database" ConnectionName="CustomConnection" Condition="1" Separator="GO" ScriptText="Other Resources\SQL Scripts\Drop  Database.sql" Flags="280" Order="1" Transaction="Transaction"/>
    <ATTRIBUTE name="ImpersonateUser" value="true"/>
  </COMPONENT>

I did take a look at the how to test SQL connections article but I don't see what the article says I should for the TestODBCConnection action. Below is what I see. Even if I add the SQLConnectionDlg to my project, this page looks the same.

http://postimg.org/image/jo6m0xnjp/

[Dan]

Hello,

Indeed, the SQL script execution phase may take longer time when large scripts are used. This is happening because we process the script file for expanding any formatted references which might be used by our "SQL Scripts Replacement" support.
We have added support to automatically skip at install time the processing of SQL Scripts for expanding any formatted references which might be used by our "SQL Scripts Replacement" support. However, if you are using our "SQL Scripts Replacement" support, then this processing will be always executed at install time.

So, if you have large SQL scripts (added on our "SQL Scripts" page) containing SQL scripts replacements, then as a workaround, if the added replacements are used only for a small portion of your script you can proceed like this:
- split your SQL script into two (or multiple) scripts: one (or more) small scripts containing the SQL replacements and one script which doesn't contain any replacement
- in this way at install time only the related script processing will be executed only for the scripts containing replacements

If you have other questions, please let us know.

Best regards,
Dan

[buri]

Why does it take potentially hours to do a simple find and replace in a sql script? I don't have a lot of replacements. Notepad can do a find/replace in about 10 seconds in my script.

[Dan]

Hello,

Please note that there is not just a find and replace operation. Because your script contains formatted references then all the strings within your script will be analyzed
to see if contains formatted references and expanding accordingly. This is the Windows Installer behavior.
So, this process of analyzing your script in order to expand your formatted references is a time consumer.

You can take a look on the MsiFormatRecord article to see how this function works.

As a workaround, you can use the one I've provided in my previos reply.

Best regards,
Dan