SQL Problems with 7.1.3

[kurtmwa]

We are in the process of upgrading an existing installer for one of our applications.

The SQL scripts that are run have not been upgraded but the programs files have.

The MSI when built with 7.1.3 fails during the "Executing install SQL scripts" with "SQL script parse error: invalid syntax."
When we update the existing project in 6.3 everything runs fine.

Any ideas?

[GabrielBarbu]

Hello,

Please make sure that on the "SQL Scripts" page, the connection and script settings are correct. Also make sure that you have the correct "Statement Separator" set. You may want to double-check your "Connection Type" too. If you still can not find the problem, please send us your .aip project file, a screenshot with the error you are encountering and the installation log to support at advancedinstaller dot com so we can further analyze the issue.

Regards,
Gabriel

[kurtmwa]

Gabriel,

I've figured out the problem (thanks to the logging link).

Some of our scripts have comments after the last go in the script. These comments are normally examples on how to use / call the script. In the older version of your software this did not cause a problem but in the 7.1.x version this seems to cause a parser error.

If possible can you change the logic to ignore the block to the end of the file is there is no SQL to execute? i.e. only comments.

Also is it possible to get a couple of features added to better manage SQL scripts in the installer?
- Sub folders for organisation in the MSI
- Ability to set a directory as the source and the program to add all scripts in the directory with the parameters set by the sub folder.
- Option to configure multiple extensions for SQL scripts e.g. tbl, sp, tr, vew, etc.

Cheers
Kurt

[GabrielBarbu]

Hello,

In order to avoid errors caused by comments, it is recommended that you use the "Strip comments from SQL Script" checkbox so that all comments are stripped before sending the query to the server.

- Sub folders for organisation in the MSI
- Ability to set a directory as the source and the program to add all scripts in the directory with the parameters set by the sub folder.

I added this improvement on our TODO list and it will be implemented in a future version of Advanced Installer. Thank you for your suggestion.
Please note that SQL Scripts are executed in a certain order, and after you bulk-add them from a folder you will have to manually arrange them in the order that best fits your needs.

- Option to configure multiple extensions for SQL scripts e.g. tbl, sp, tr, vew, etc.

You can add and configure files with different extensions using the "*.*" filter in the Browse dialog when adding a script.

Regards,
Gabriel

[kurtmwa]

In order to avoid errors caused by comments, it is recommended that you use the "Strip comments from SQL Script" checkbox so that all comments are stripped before sending the query to the server.

Yes this fixes the problem, but we actually want the comments in the installed version of the script. If nothing else we have a header block in the scripts that lists its version history.

- Sub folders for organisation in the MSI
- Ability to set a directory as the source and the program to add all scripts in the directory with the parameters set by the sub folder.

I added this improvement on our TODO list and it will be implemented in a future version of Advanced Installer. Thank you for your suggestion.
Please note that SQL Scripts are executed in a certain order, and after you bulk-add them from a folder you will have to manually arrange them in the order that best fits your needs.

Thank you. We use the folders on the OS to organise the scripts into the order they need to be applied. If the sync process followed the folder name and then file name by default it would reduce a lot of hassle.

- Option to configure multiple extensions for SQL scripts e.g. tbl, sp, tr, vew, etc.

You can add and configure files with different extensions using the "*.*" filter in the Browse dialog when adding a script.

Even just changing the default would be an improvement. In the last update I had to add 100 scripts to two different connections (Live and Training). It's not even worth using your front end to do this because I would have to press plus, change the file type list then pick the script. Repeat for each script. It would take me hours to do it this way and I would have a high chance of missing a script.

I end up using the back end. Get the line format, a directory listing and use excel to change the data so that it matches your line format, then paste them into the aip file. I know what we are doing is probably not common but we have an installer with ~700 scripts at the moment (1400 when counting the two connections).

[GabrielBarbu]

Hello,

Yes this fixes the problem, but we actually want the comments in the installed version of the script. If nothing else we have a header block in the scripts that lists its version history.

Please note that the comments are only stripped when the SQL is submitted to the server. The .sql file itself is not changed. If you want to install a file, be it a script or some other type, simply place it on the "Files and Folders" page.

Even just changing the default would be an improvement.

We will consider adding this in a future version of Advanced Installer. Nonetheless your problem will be solved once we implement the bulk-add suggestion.

Regards,
Gabriel

[kurtmwa]

Yes this fixes the problem, but we actually want the comments in the installed version of the script. If nothing else we have a header block in the scripts that lists its version history.

Please note that the comments are only stripped when the SQL is submitted to the server. The .sql file itself is not changed. If you want to install a file, be it a script or some other type, simply place it on the "Files and Folders" page.

We want the comments in SQL server. Our program has logic that reports on the version of all database objects based on the code comments.

[GabrielBarbu]

Hello,

We want the comments in SQL server. Our program has logic that reports on the version of all database objects based on the code comments.

I am not sure what you mean by this. Are you referring to the "SQL Server Stored Procedures" feature?

Regards,
Gabriel

[kurtmwa]

We have a scheduled task at each site that routinely reports on the version status of database objects and emails it to the helpdesk. By running some custom SQL that checks the source code in SQL Server. We take the bits on information that we need from the comments in the code. Output example follows.

It's got nothing to do with your installer, except for the fact that we need to keep the comments in the code.

Code: Select all

FN - <-------------------- Functions -------------------->
FN - Build_Subject_fn . . . . . Version 1.00 | 20/04/2009 |
FN - NullTrim_fn . . . . . Version 1.23 | 16/10/2008 |
FN - Restore_Characteristics_fn . . . . . Version 1.00 | 20/04/2009 |
FN - rpad_fn . . . . . Version 1.00 | 05/03/2009 |
FN - Strip_Characteristics_fn . . . . . Version 1.00 | 20/04/2009 |
FN - Strip_CRLF_fn . . . . . Version 1.00 | 20/04/2009 |
FN - Trim_fn . . . . . Version 1.21 | 16/10/2008 | 

[GabrielBarbu]

Hello,

I am not sure I understand how your SQL code ends up to be stored on the server in the first place. Understanding how it gets there is the first step in finding a way to preserve the comments. When the SQL commands get executed, they are not normally stored on the server.

Regards,
Gabriel