using 'ODBC Driver 17 for SQL Server' in sql scripts

Having trouble running Advanced Installer? Got a bug to report? Post it all here.
Post Reply
geetarman
Posts: 5
Joined: Fri Mar 06, 2020 3:35 pm

using 'ODBC Driver 17 for SQL Server' in sql scripts

Post by geetarman » Fri Mar 06, 2020 4:07 pm

Hi,
We had our configuration set to use 'Sql Server' for the odbc driver when running sql install scripts from within the installer but we have issues when only tls1.2 is enabled where we were getting an odbc timeout.
I have used the following driver 'ODBC Driver 17 for SQL Server' instead and this solves the problem but I have the following question since it appears that the list of odbc drivers visible in the installer is dependent on what is actually installed on the machine.

Does the above mean that the installer requires the 'ODBC Driver 17 for SQL Server' to be preinstalled?

If so can the installer be configured to install this as a prerequisite?

regards
Steve

Catalin
Posts: 3380
Joined: Wed Jun 13, 2018 7:49 am

Re: using 'ODBC Driver 17 for SQL Server' in sql scripts

Post by Catalin » Fri Mar 06, 2020 4:30 pm

Hello Steve and welcome to Advanced Installer forums,

Indeed, as you have mentioned, the list of drivers is taken from your machine. You can see a comprehensive list of your installed drivers by making use if the "ODBC Data Source Administrator" (odbcad32.exe) tool --> "Drivers" tab.
Does the above mean that the installer requires the 'ODBC Driver 17 for SQL Server' to be preinstalled?
Since some users may not have that installed, then you might need to install this beforehand, as a prerequisite. To do so, please have a look on the following forum thread in which I have explained how this can be acheived:

Re: How to install an ODBC connection 13 automatically

Hope this helps.

Best regards,
Catalin
Catalin Gheorghe - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

geetarman
Posts: 5
Joined: Fri Mar 06, 2020 3:35 pm

Re: using 'ODBC Driver 17 for SQL Server' in sql scripts

Post by geetarman » Thu Mar 12, 2020 11:54 am

Hi, thanks but this doesn't work as we would like it.
The dependency installs but the install doesn't pick up that the driver has been installed until after out install has run rather than during it which we need.

As an alternative , it there any way for the install to downgrade to a version of the driver that will work if the 'ODBC Driver 17' isn't installed on the server. This would be useful because we don't actually have that many customers with only TLS1.2 enabled so they will moan if they have to install this driver separately.
Or if you have any other suggestions.

regards
Steve

Catalin
Posts: 3380
Joined: Wed Jun 13, 2018 7:49 am

Re: using 'ODBC Driver 17 for SQL Server' in sql scripts

Post by Catalin » Thu Mar 12, 2020 3:20 pm

Hello Steve,
The dependency installs but the install doesn't pick up that the driver has been installed until after out install has run rather than during it which we need.
Have you added the prerequisite as a pre-install prerequisite and it did not work?

If so, did you change the "ODBC Driver" field in "SQL Databases" page and also the "SQLConnectionString"?

Also, could you please elaborate a bit more on this:
install doesn't pick up that the driver has been installed until after out install has run rather than during it which we need.
as I am not quite sure I understand it.

Best regards,
Catalin
Catalin Gheorghe - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

geetarman
Posts: 5
Joined: Fri Mar 06, 2020 3:35 pm

Re: using 'ODBC Driver 17 for SQL Server' in sql scripts

Post by geetarman » Thu Mar 12, 2020 4:14 pm

In other words we need the ODBC driver to be installed and ready to use whilst we are running our installation program rather than having to be pre-installed before we run our installation program.
As far as I am aware we used the same methodology that was in the link that you gave use for installing the ODBC 13 driver as a pre-requisite (although I didn't do this personally).
We only changed the 'ODBC Driver' field in the 'SSQL Databases' page - we are using a predefined connection that does not have a sqconnection string option. However, that works if the 'ODBC 17' driver s already pre-installed.
Is is possibly relevant that we are only on version 13.4 of advanced installer?

Hope that is a bit clearer.

regards
Steve

Collins
Posts: 115
Joined: Wed Oct 12, 2016 2:57 pm

Re: using 'ODBC Driver 17 for SQL Server' in sql scripts

Post by Collins » Mon Mar 16, 2020 1:45 pm

geetarman wrote:
Thu Mar 12, 2020 4:14 pm
In other words we need the ODBC driver to be installed and ready to use whilst we are running our installation program rather than having to be pre-installed before we run our installation program.
regards
Steve
The way I worked around it was to add the MSODBCSQL_17.2.0.1 as a temp file then call it using the Run PowerShell Inline Script with the following parameters: -msiPath "[AI_MSODBCSQL_17.2.0.1_X64.MSI]"

# Block for declaring the script parameters.
Param($msiPath, $MSIArguments = "/i $msiPath /passive IACCEPTMSODBCSQLLICENSETERMS=YES")


Start-Process "msiexec.exe" -ArgumentList $MSIArguments -Wait -NoNewWindow

I added the custom action under Dialog Stage Paths Resolution right after CostFinalize. I also added a registry search (ODBC_DRIVER) for the ODBC driver:
Root: HKLM
Key: SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers
Name: ODBC Driver 17 for SQL Server
Type: Retrieve the raw value

Then I used it in the custom action condition so that it would only run if it was not installed: (ODBC_DRIVER <> "Installed") AND (VersionNT > 501) OR (VersionNT = 501 AND ServicePackLevel >= 2)

This way the ODBC driver will installed and ready to use before you need it in the dialog or install stage.

Catalin
Posts: 3380
Joined: Wed Jun 13, 2018 7:49 am

Re: using 'ODBC Driver 17 for SQL Server' in sql scripts

Post by Catalin » Mon Mar 16, 2020 6:28 pm

Hello Steve, Collins,

@Steve,
In other words we need the ODBC driver to be installed and ready to use whilst we are running our installation program rather than having to be pre-installed before we run our installation program.
As far as I am aware we used the same methodology that was in the link that you gave use for installing the ODBC 13 driver as a pre-requisite (although I didn't do this personally).
The method presented by me in the thread should do just this.

Could you please give me some more details (perhaps, if possible, provide some screenshots of the issue and also on how you have configured the prerequisite) about what is not working by following the method I've provided?

Also, you can have a look over the solution presented by Collins in the above thread.

@Collins,

Thank you very much for sharing your solution with us, it is highly appreciated!

Best regards,
Catalin
Catalin Gheorghe - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

geetarman
Posts: 5
Joined: Fri Mar 06, 2020 3:35 pm

Re: using 'ODBC Driver 17 for SQL Server' in sql scripts

Post by geetarman » Wed Mar 18, 2020 9:29 am

Thanks guys.

I don't know exactly what the issue was but it looks like the prerequisite install required a reboot first or something like that which didn't really satisfy our needs - maybe that is a difference between the 13 odbc driver install and the 17?
Anyway I decided to run the scripts using a custom dll instead using the c# System.Data class which works just fine so that has sorted my immediate problem.
I may decide to go back and look at it using the installer again at some point because it is simpler and more convenient....

regards
Steve

Catalin
Posts: 3380
Joined: Wed Jun 13, 2018 7:49 am

Re: using 'ODBC Driver 17 for SQL Server' in sql scripts

Post by Catalin » Wed Mar 18, 2020 4:56 pm

You are always welcome, Steve!

I am really glad you figured things out and that everything is working as expected now.

Best regards,
Catalin
Catalin Gheorghe - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

Post Reply