Problems with SQL script

[kellelein]

Hi all

I have made an MSI project in InstallShield 2008 for my application. I have a prerequist that installs microsoft sql server express 2005 on the target, this works very well.

Then I made a sql script, with the database import wizard. Still no problems, everything seems to work very fine, and the script looks ok, to me.

When I try to install the application I get the folowing message, when the installation tryes to run the script:

Error 27506. Error executing SQL script myScript. Line 3 CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (1802).

I have tryed to check what the error 1802 is, and this is an error about memory, which I have plenty of on the target.

I have altso tryed to make a very simple database with only one table, still I get the error message above??? I am loged in as administrator on the machine (who runs windows XP)

Please could anyone help...

best regards

Kellelein


The script looks like this:

CREATE DATABASE [test2] ON (NAME = N'test2', FILENAME = N'C:\Programmer\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\test2.mdf' , SIZE = 3, FILEGROWTH = 1) LOG ON (NAME = N'test2_log', FILENAME = N'C:\Programmer\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\test2_log.ldf' , SIZE = 1, MAXSIZE = 2097152, FILEGROWTH = 10%)
GO

exec sp_dboption N'test2', N'autoclose', N'false'
GO

exec sp_dboption N'test2', N'bulkcopy', N'false'
GO

exec sp_dboption N'test2', N'trunc. log', N'true'
GO

exec sp_dboption N'test2', N'torn page detection', N'false'
GO

exec sp_dboption N'test2', N'read only', N'false'
GO

exec sp_dboption N'test2', N'dbo use', N'false'
GO

exec sp_dboption N'test2', N'single', N'false'
GO

exec sp_dboption N'test2', N'autoshrink', N'false'
GO

exec sp_dboption N'test2', N'ANSI null default', N'false'
GO

exec sp_dboption N'test2', N'recursive triggers', N'false'
GO

exec sp_dboption N'test2', N'ANSI nulls', N'false'
GO

exec sp_dboption N'test2', N'concat null yields null', N'false'
GO

exec sp_dboption N'test2', N'cursor close on commit', N'false'
GO

exec sp_dboption N'test2', N'default to local cursor', N'false'
GO

exec sp_dboption N'test2', N'quoted identifier', N'false'
GO

exec sp_dboption N'test2', N'ANSI warnings', N'false'
GO

exec sp_dboption N'test2', N'auto create statistics', N'true'
GO

exec sp_dboption N'test2', N'auto update statistics', N'true'
GO

if( (@@microsoftversion / power(2, 24) = 😎 and (@@microsoftversion & 0xffff >= 724) )
exec sp_dboption N'test2', N'db chaining', N'false'
GO
CREATE TABLE [dbo].[Table_1] (
[ID] [int] NULL
) ON [PRIMARY]
GO

[hidenori]

Is the script successfully executed from Microsoft SQL Server Management Studio?

[kellelein]

No the script is made with the wizard in installshield...

Best regards

Kellelein

[hidenori]

Please try to run the script from Microsoft SQL Server Management Studio in order to verify whether it is an InstallShield specific issue or a generice SQL script issue. If you receive the same error, it is possible that an option or parameter of the CREATE DATABASE statement is incorrect. I guess that the path of 'C:\Programmer\Microsoft SQL Server\MSSQL.3\MSSQL\DATA' does not exist on the target machine, or 'test2.mdf' or 'test2_log.ldf' already exists and cannot be overwritten for some reasons.

[kellelein]

It is true that the path:

'C:\Programmer\Microsoft SQL Server\MSSQL.3\MSSQL\DATA'

Don't exist on the target machine, because the target, in this case is running an English version of windows xp.

How do I "tell" InstallScript to make an "path free" script without absolut path?

I can make it work, if I manually remove the path and write:

USE master
GO

if NOT exists (select * from dbo.sysdatabases where name = 'myDatabase')
CREATE DATABASE ['myDatabase']
GO

USE ['myDatabase']
GO

Best regards

Kellelein

[hidenori]

You need to use the Text Replacement functionality to solve your problem.

[LIST=1]

Add the the following Text Replacement entry to your SQL script by clicking the Add button in the Text Replacement tab:

Find What: %DBDATAPATH%
Replace With:

Change your SQL Script:

CREATE DATABASE [test2] ON (NAME = N'test2', FILENAME = N'%DBDATAPATH%\test2.mdf' , SIZE = 3, FILEGROWTH = 1) LOG ON (NAME = N'test2_log', FILENAME = N'%DBDATAPATH%\test2_log.ldf' , SIZE = 1, MAXSIZE = 2097152, FILEGROWTH = 10%)
GO

Hope this helps.

[john703]

Hello,

I am getting this exact same error.

I have tested the script on my local machine with the valid code in Sql Server Manager and it works fine. However when I test this on a test laptop, it seems to choke. Even if I make sure the path exist on the test machine, it still does not seem to work.

my code is;
CREATE DATABASE [john] ON PRIMARY
( NAME = N'john_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\john_Data.MDF' , SIZE = 9024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 80KB )
LOG ON
( NAME = N'john_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\john_Log.LDF' , SIZE = 9216KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

COLLATE SQL_Latin1_General_CP1_CI_AS
GO

Any help will be much appreciated.

Thank you,

[Nick_Umanski]

I was getting this exact problem and after jumping through hoops trying to get the path with a system search and then doing a text replacement in the SQL Script view. I then hit a wall of how do you get the path if the SQL Server is on a remote machine?

Then some helpful chap on this forum pointed out that the first line of my script should simple be:

CREATE DATABASE [DBName]

You don't need any of the other garbage, unless you are doing something funny with the Size, Maxsize or FileGrowth properties - let SQL Server handle that automatically, it is quite capable.

[john703]

Thanks,

For some reason it only allow me to create the DB at MSSQL.1\MSSQL\Data.

The above solution work too.

[Nick_Umanski]

john703 wrote:
For some reason it only allow me to create the DB at MSSQL.1\MSSQL\Data.

Yes this is dependent on where the /Data folder is for SQL Server. This could be [ProgramFilesFolder]\Microsoft SQL Server\MSSQL.1\MSSQL\Data or it could be just [ProgramFilesFolder]\Microsoft SQL Server\MSSQL\Data, I think it is dependent on version - 2000/MSDE = latter, 2005/SQLExpress=former but not sure.

The point is it is fixed for the machine, so why do you need to specify it in your script? - But as there are possible variations between machine, you need to either search for the path and then do a text replacement or go with CREATE DATABASE [DBName]

[TheTraveler]

You could also check to make sure that the database doesn't already exist.

IF NOT EXISTS (SELECT NAME FROM master.dbo.sysdatabases WHERE NAME = 'MyDatabase')
BEGIN
    CREATE DATABASE MyDatabase
    ON PRIMARY  
    ( NAME = 'MyDatabase_dat',
      FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL$ENOTE\Data\MyDatabase_dat.mdf',
      SIZE = 10,
      MAXSIZE = UNLIMITED,
      FILEGROWTH = 10% ),

    FILEGROUP INDEXGROUP
    ( NAME = 'MyDatabase_indx',
      FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL$ENOTE\Data\MyDatabase_indx.ndf',
      SIZE = 10,
      MAXSIZE = UNLIMITED,
      FILEGROWTH = 10% )

    LOG ON
    ( NAME = 'MyDatabase_log',
      FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL$ENOTE\Log\MyDatabase_log.ldf',
      SIZE = 10,
      MAXSIZE = UNLIMITED,
      FILEGROWTH = 10% )  
END
GO