SQL Script Version Numbers Ignored
Guys,
a question, we have a package here in which we're using the SQL Script Version number function to control execution of SQL scripts. As we understand it putting in a version 1234.12345.1234 will cause the MSI to make a table in your database called InstallShield, in which it stores the schema version. If then a sql script wants to run that has a version that's lower than the schema version stored in de DB, it will not run.
Turns out however the scripts always run regardless of these Script Versions. Does anyone have experience with this feature of Windows Installer and this specific problem?
I'll add the relevant log entries here.
The line
1: The SQL script will be executed because no schema version is found on the target database.
is what's got us stumped. Are we missing something that has to be defined in the DB, and if so, where? Cause the InstallShield table exists in the DB
Any help, as always, would be appreciated.
PJ
a question, we have a package here in which we're using the SQL Script Version number function to control execution of SQL scripts. As we understand it putting in a version 1234.12345.1234 will cause the MSI to make a table in your database called InstallShield, in which it stores the schema version. If then a sql script wants to run that has a version that's lower than the schema version stored in de DB, it will not run.
Turns out however the scripts always run regardless of these Script Versions. Does anyone have experience with this feature of Windows Installer and this specific problem?
I'll add the relevant log entries here.
MSI (64:E4) [14:01:05:128]: Component: _01_event_report_last_run.ddl_SQLComponent; Installed: Absent; Request: Local; Action: Local; Client State: Unknown
.
.
.
MSI (64!18) [14:01:15:779]: Creating MSIHANDLE (18221) of type 790531 for thread 4120
1: The SQL script '_01_event_report_last_run.ddl' is scheduled to execute. Note that the final decision will be made later if you have a schema version specified for this script. Script Execution=RunOnInstall, Action=Install, Component State=Install, Schema=0001.00001.3200
MSI (64!18) [14:01:15:779]: Closing MSIHANDLE (18221) of type 790531 for thread 4120
.
.
.
.
MSI (64!18) [14:01:15:873]: Creating MSIHANDLE (18241) of type 790531 for thread 4120
1: Changed database context to 'master'.
MSI (64!18) [14:01:15:873]: Closing MSIHANDLE (18241) of type 790531 for thread 4120
MSI (64!18) [14:01:15:873]: Creating MSIHANDLE (18242) of type 790531 for thread 4120
1: Attempting to execute SQL scripts...
MSI (64!18) [14:01:15:873]: Closing MSIHANDLE (18242) of type 790531 for thread 4120
MSI (64!18) [14:01:15:873]: Creating MSIHANDLE (18243) of type 790531 for thread 4120
1: Determining if the SQL script '_01_event_report_last_run.ddl' will be executed...
MSI (64!18) [14:01:15:873]: Closing MSIHANDLE (18243) of type 790531 for thread 4120
MSI (64!18) [14:01:15:873]: Creating MSIHANDLE (18244) of type 790531 for thread 4120
1: The SQL script will be executed because no schema version is found on the target database.
MSI (64!18) [14:01:15:873]: Closing MSIHANDLE (18244) of type 790531 for thread 4120
MSI (64!18) [14:01:15:873]: Creating MSIHANDLE (18245) of type 790531 for thread 4120
1: Prepare executing SQL script file: '_01_event_report_last_run.ddl'. Path: 'C:\DOCUME~1\SE765~1.U28\LOCALS~1\Temp\~1068.tmp'
MSI (64!18) [14:01:15:873]: Closing MSIHANDLE (18245) of type 790531 for thread 4120
MSI (64!18) [14:01:15:873]: Creating MSIHANDLE (18246) of type 790531 for thread 4120
Action 14:01:15: Progress Custom Action. Executing SQL Install Script...
_01_event_report_last_runThe line
1: The SQL script will be executed because no schema version is found on the target database.
is what's got us stumped. Are we missing something that has to be defined in the DB, and if so, where? Cause the InstallShield table exists in the DB
Any help, as always, would be appreciated.
PJ
0 Comments
[ + ] Show comments
Answers (5)
Please log in to answer
Posted by:
timmsie
14 years ago
so are you saying that the installshield table exists with a ISSchema column which contains your schema version?
this thread may be of interest to you.
http://community.flexerasoftware.com/showthread.php?t=151522&highlight=isschema
this thread may be of interest to you.
http://community.flexerasoftware.com/showthread.php?t=151522&highlight=isschema
Posted by:
pjgeutjens
14 years ago
yep, that's what I'm saying, the table does get created on the target DB, with Schema versions in it for each script that gets run.
One would imagine, upon running the MSI again, since the schema version evaluates as equal to the existing one, that the scripts wouldn't run against the DB again, but no luck.
The MSI log just says it can't find any versions...
One would imagine, upon running the MSI again, since the schema version evaluates as equal to the existing one, that the scripts wouldn't run against the DB again, but no luck.
The MSI log just says it can't find any versions...
Posted by:
timmsie
14 years ago
I edited my post above with a link that maybe of interest.
But it appears your doing everything right to make it work. Which version of installshield do you have?
But it appears your doing everything right to make it work. Which version of installshield do you have?
Posted by:
pjgeutjens
14 years ago
it's Installshield 2009 Professional, Adminstudio Edition, and to be complete, version 15 SP1
Posted by:
pjgeutjens
14 years ago
Ok, the mystery has been solved.
For those who might be interested, here's what we think happened:
make sure the USE [table] statements in your SQL scripts don't conflict with the catalog name you defined for the connection. Turns out the SQL database's catalog name was defined as "master" in the MSI, where all scripts started with a USE [X] statement (for the record, it wasn't us that wrote the sql scripts [:D]). Result was, the InstallShield table was created as X > InstallShield, where as the lookup action for the versions was done in Master > InstallShield.
Ofcourse there were, as the log so elegantly stated, no schema versions to be found in the target database.
(I suspect my colleague lost some sleep over this [;)])
For those who might be interested, here's what we think happened:
make sure the USE [table] statements in your SQL scripts don't conflict with the catalog name you defined for the connection. Turns out the SQL database's catalog name was defined as "master" in the MSI, where all scripts started with a USE [X] statement (for the record, it wasn't us that wrote the sql scripts [:D]). Result was, the InstallShield table was created as X > InstallShield, where as the lookup action for the versions was done in Master > InstallShield.
Ofcourse there were, as the log so elegantly stated, no schema versions to be found in the target database.
(I suspect my colleague lost some sleep over this [;)])
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.