This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
- Revenera Community
- :
- InstallShield
- :
- InstallShield Forum
- :
- Re: Problems with SQL script
Subscribe
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Feb 20, 2008
01:08 AM
Problems with SQL script
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
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
(10) Replies
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Feb 20, 2008
08:54 AM
Is the script successfully executed from Microsoft SQL Server Management Studio?
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Feb 22, 2008
01:09 AM
No the script is made with the wizard in installshield...
Best regards
Kellelein
Best regards
Kellelein
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Feb 22, 2008
08:32 AM
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.
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Feb 25, 2008
03:06 AM
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
'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
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Feb 25, 2008
02:42 PM
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:
Change your SQL Script:
Hope this helps.
[LIST=1]
Find What: %DBDATAPATH%
Replace With:
Replace With:
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
GO
Hope this helps.
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎May 29, 2008
12:02 PM
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,
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,
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎May 30, 2008
05:10 AM
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.
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.
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎May 30, 2008
10:18 AM
Thanks,
For some reason it only allow me to create the DB at MSSQL.1\MSSQL\Data.
The above solution work too.
For some reason it only allow me to create the DB at MSSQL.1\MSSQL\Data.
The above solution work too.
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎May 30, 2008
11:24 AM
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]
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎May 30, 2008
02:03 PM
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
![](/skins/images/8C9D13A71C7F7695F3262372C0172D68/responsive_peak/images/icon_anonymous_message.png)