Windows Installer cannot find MSSQL subfolders or files

[DLee65]

I have logged an incident with InstallShield but thought I would also post here just in case anyone here has any insights.

I have a database .mdf file that I successfully install and attach to a SQL Instance. I can verify that the file physically exists. I can view the contents using Query Analyzer. However, whenever I start our application, Windows Installer self repair kicks in. The event viewer reports the following error:

Detection of product '{3237E527-970C-420E-AA6E-E75918BF806C}', feature 'Core', component '{4D1E0B9E-817C-D5EA-41C3-227F80551E76}' failed. The resource 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DeLormeSampleDB.mdf' does not exist.

Default user access to the MSSQL.1\MSSQL folder and all subfolders is restricted - as it should be. If I manually change the permissions to the MSSQL folder then the resiliency check passes and the application starts without launching windows installer. However, of course I cannot change the permissions on this folder within my installer - that would be bad business.

So my question is: 1. Why does it appear that the resiliency check cannot see the file when it does exist.
2. Doesn't resiliency run under the SYSTEM context and therefore have rights to the folder?
3. Is there a better way to handle DB files? I would rather not create the DB file on the fly, but it is looking like I may have no other choice.

[thepeter]

the problem here is that DeLormeSampleDB.mdf is a key for the component with GUID {4D1E0B9E-817C-D5EA-41C3-227F80551E76}; just remove the key from that component.

[DLee65]

I had thought of that as well. I removed the file as the key file, however, the MSSQL\Data is now used as the keypath and it cannot see the path either so the same resiliency problem occurs.

My solution was to install the MDF file to INSTALLDIR and then duplicate the file to the MSSQL\Data folder. Not the cleanest of solutions but workable.

[thepeter]

- why do you write under 'C:\Program Files\Microsoft SQL Server\MSSQL.1'? (.1 means that that was the first instance installed on the machine)
- what SQL instance are you using? your own?
- how do you attach the database?

You should leave it in your INSTALLDIR and use it from there. By the name (DeLormeSampleDB.mdf) I imagine it's already pre-populated, not empty. Leave this file in a component without a key path. You will get validation warnings, but that's ok.

[Christopher_Pai]

Can you export the database to an sql file and load it using sqlconnections/sql scripts?

[DLee65]

thepeter wrote:
- why do you write under 'C:\Program Files\Microsoft SQL Server\MSSQL.1'? (.1 means that that was the first instance installed on the machine)
- what SQL instance are you using? your own?
- how do you attach the database?

You should leave it in your INSTALLDIR and use it from there. By the name (DeLormeSampleDB.mdf) I imagine it's already pre-populated, not empty. Leave this file in a component without a key path. You will get validation warnings, but that's ok.

I was not the developer who implemented this, but I believe the lead developer for the project asked for it to be placed into the SQL Data directory. The developer who implemented this did a look up of the path for our Instance in the registry.

Christopher, I can import the data once I have a final dataset. Right now it is just a placeholder with data that must NOT be distributed. 🙂

[thepeter]

I strongly advise you to do a bit more reading on installing SQL databases. You need to keep in mind all possible configurations that you might find on your clients computers. Also, can your database be shared with multiple clients? You could deploy it to a dedicated instance instead of installing it locally on all clients (an Enterprise scenario). Ask your developer how the application connects to the SQL db - do you need a particular instance name? What about permissions?
Once again, read more about this and consider every scenario you might find out there! Good luck.