Prevent SQLScripts from running on Update

[bobmcm461]

I am using an Installscript MSI project, and need to prevent the SQL Scripts from running on update installs. I have set a registry entry when the databases get installed and I check for that entry. If it is there, I use FeatureSetItem to set the Feature that contains the SQLScript compononents to False, but the scripts still run overwriting my existing database. How do I prevent this from happening. I thought that if I prevented the Feature from being installed, the scripts would also not run.

Thanks
Robert McMahan

[hidenori]

Try setting a script condition using the Runtime tab for your SQL script.

[bobmcm461]

I will try this again. I tried this before, but it did not seem to work. I got the impression that the MSI Properties were not available after the file copy was completed causing that option to fail.

Thanks,
Robert McMahan

[hidenori]

Make sure that you are using an immediate custom action to set the MSI property, and it is sequenced before the ISSQLServerCosting action.

[bobmcm461]

I'm trying to do this in the OnFirstUIBefore event. Can this not be set there?

On a similar note. Why do scripts that run fine in the SQL Manager not run properly under Installshield. I designed this script is the sql manager.

[CODE]USE [master]
GO
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'%DBCATALOG%')
RESTORE DATABASE [%DBCATALOG%] FROM DISK = N'%DIR%\PSAIMCLEAN.BAK' WITH FILE = 1, MOVE N'PSAIM' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PSAIMDB.mdf', MOVE N'PSAIM_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PSAIMDB_log.ldf', NOUNLOAD, REPLACE, STATS = 10
GO[/CODE]

I updated the %DIR% with INSTALLDIR and %DBCATALOG% with IS_SQLSERVER_DATABASE. It worked fine in the sql manager, but fails in Installshield. If I remove the IF NOT EXISTS line, the script works so I believe that the substitutions are working properly. As there is no way to debug it, I'm kind of stuck.

Getting this to work would resolve my problem, only restoring the database if it does not exists. This is what prompted me to try and prevent the script from running.

Thanks,
Robert McMahan

[ch_eng]

Regarding your question about the SQL script failing, try replacing your IF statement with this:

IF db_id('%DBCATALOG%') IS NULL

HTH

[hidenori]

The OnFirstUIBefore event handler is only called for a first-time installation. If you want to execute your code for a maintenace installation, try moving it to the OnMaintUIBefore event handler and see if it works. Regarding the SQL script failure, monitoring SQL scripts issued to your database engine using Microsoft SQL Server Profiler might help to troubleshoot.

[bobmcm461]

I'm sorry, I may have caused some confusion. I have been working with a test install, using OnFirstUIBefore. I have a virtual machine that I can configure quickly with the appropriate Database and registry configurations to create the conditions for the script to act as though the database is already installed. I planned on moving the code over to OnMaintUIBefore in the full installation once I have everything working.

Robert McMahan

[bobmcm461]

ch_eng wrote:
Regarding your question about the SQL script failing, try replacing your IF statement with this:

IF db_id('%DBCATALOG%') IS NULL

HTH

I think I had a bit of an 'lightbulb' moment. I believe that the problem that I am having with the SQL Script is in the connection. I have 'Create Catalog If Absent' checked. This is causing the script to see the catalog when I check if it exists (with either set of code.) With it unchecked, it is failing to connect. I am working through the 'Creating a Sample Installation that Creates a SQL Server Database by Running Customized SQL Script' help entry to try and work around this issue.

Thanks,
Robert McMahan
Siemens Energy

[ch_eng]

OK, good luck! If you need the SQL engine's default filepaths, you can find the them for the master.mdf and mastlog.ldf files this way:

--default .mdf path:
declare @DataFileDefaultPath VARCHAR(255)
SET @DataFileDefaultPath = (select replace(filename, 'master.mdf', '') from master.dbo.sysdatabases where name='master')

--default .ldf path:
declare @LogFileDefaultPath VARCHAR(255)
SET @LogFileDefaultPath = rtrim((select replace(filename, 'mastlog.ldf','') from master.dbo.sysfiles where (status & 0x40) <>0))

HTH

[bobmcm461]

Thanks for the help everyone. I have resolved the issue.

The problem with the SQL Script not running as expected was in fact the 'Create Catalog If Absent' checkbox. The Catalog was being created at login time causing the script to appear to fail when I checked the catalog.

The information "Creating as Sample Installation that Creates a SQL Server Database by Running Customized SQL Script" also causes the same problem as it creates the database catalog when the connection is established. It also has the side effect of displaying 2 SQLLogin boxes one right after another. One to create the database and one to perform the restore. I was unable to place the database restore in this script it is installed with the other components.

To resolve the problem, I have removed the ability to change the catalog by hiding that box on the SQLLogin screen and setting the Catalog to master. This allows me to login and restore my the database. The check for the catalog then works correctly and prevents the restore command from running if a copy of the database already exists.

Thanks again,
Robert McMahan
Siemens Energy