SQL Scripts Won’t Run Against SQL 2005 SP2

[BrandonK]

I have an IA 6.31 project for the server part of my client server application. During new installs it runs two scripts, one to create the database and another to create tables, relations, etc.

When the scripts are run using Management Studio (SQL 2005) or Query Analyzer (SQL 2000), they finish without error.

My IA project uses the standard “Microsoft SQL Server Script” action. When the install runs the scripts against SQL 2000, they finish without error; both the database and tables are created. When the install runs against SQL 2005 SP2, both script actions return ERROR and the database is not created.

I installed the Alternate SQL Plug-in and converted the script lines to use this new command. Both commands return DATABASE_EXIST_ERROR, even though the database does not exist on that server prior to install. This error occurs with both SQL Server 2000 and 2005.

Is there any way to get more info on what’s going wrong in each case? I’ve noticed several forum posts with similar problems but no solution.

http://forums.installaware.com/viewtopic.php?t=1441
http://forums.installaware.com/viewtopic.php?t=1572
http://forums.installaware.com/viewtopic.php?t=1686

Thanks for your help.

[neillans]

Sorry to hear of your troubles

Can you send me your installation script? You can email it to me at andyn @ installaware dot com.

[gsmmc]

Feel free to add a fourth to the list of SQL 2005 SP2 problem cases...
http://forums.installaware.com/viewtopic.php?t=1964

Having the same issues... scripts executing correctly in every other scenario except inside of InstallAware whether I use the Alternate SQL Plugin or native SQL support.

GS

P.S. I'm not at liberty to send the install script...

[nanashi16]

I have found that I have the best success by placing the script file in the supportdir and then running the file using sqlcmd and having a progress bar run on marquee.

ex
sqlcmd -S localhost\\instance-U sa -P password -i $SUPPORTDIR$\\Script.sql

Just use run program and all is well with the world.



Steven

[neillans]

I've received Brandon's installer, and can confirm that I will be investigating this problem shortly. As soon as I have an answer, I will post back.

[gsmmc]

Hi,

Have you had any luck yet on determining the cause of this problem?

We're holding off release of our product trial until we hear something back...


Thanks,
GS

[neillans]

Still investigating I'm afraid; hope to have an answer soon!

[BrandonK]

Were you able to reproduce the problem using the installer project I sent?

[gsmmc]

Thanks for the update, it's much appreciated!

GS

[neillans]

Hi all,

Ok, first set of results are in

We can replicate this issue, but only if SQL Server can NOT gain access to the directory that you have requested it to create the database in.
If you use the default directory, or verify the necessary permissions on the target direct, installation works perfectly.

Please note that Microsoft have reinforced a lot of security restrictions in later versions of the products (e.g. Vista, SQL Server 2005 etc), so you must take care to double check permissions.

If this does not solve the problem, please let me know.

[BrandonK]

Great find! My install was not using the default SQL Server database directory. I'll check this on my test machines and report back.

[gsmmc]

Thanks for the response,

Unfortunately the nature of SQL Server Express 2005 is to create multiple MSSQL.X directories for each instance. So unless our application is the only application on the computer that uses an SQL Server 2005 Express Instance (and the workstation doesn't have SQL Server full version installed), the script-execution can fail?

Obviously i'm not privy to the content in Brandon's sql script, and I understand that a restore or attach operation would require knowing the correct path to your Instance, but why would script execution against the instance-name demonstrate such behavior? You shouldn't have to specificy (or even need to know) the SQL Server instance path to execute a script against it. Knowing the name of the instance and the machine name on which it resides is enough when you're using SQL Native support. Is InstallAware using some ODBC/DSN method to execute the scripts instead of SQL Native support??

Thanks in advance,
GS

[neillans]

No, we use Native Connectivity through MDAC to talk to the SQL Server.

You do not need to know the actual path of the instance database store; if you simply create a database on a specific instance, and only pass the name, SQL Server will deal with placing it in the correct store (mdf and transaction logs etc).

You only need to supply the path if you wish to store it somewhere totally abstract - such as your my documents folder - which is what a lot of people are attempting.

[BrandonK]

Andy,

It looks like you've found the cause of my installer problem. The machines used to develop the SQL Server scripts run the MSSQLSERVER service using the "LocalSystem" account. However, the virtual machines used for testing had MSSQLSERVER running under "NT AUTHORITY\\NetworkService". Once I switched this over to LocalSystem everything ran as expected!

My reasoning behind allowing the user to specify a non-standard location for my app's database was to better facilitate backups. However, considering that:

- the NetworkService account may not be able to write to the user-specified folder, and
- the user-specified folder may be on a compressed drive (which SQL Server won't allow),

I may yank this option from my installer project. Is there any way for IA to check what user account SQL Server is running under?


GS,

The file path in my original question was used in a CREATE DATABASE statement. All of my other SQL scripts execute without incident using the server, instance, and database name (as one would expect).

[gsmmc]

Alright,
That definately doesn't apply here then, looks like i'm shooting blanks trying to figure this out...

Thanks for your time,
GS