Hello again;
I'm struggling with how to get an SQL database script to run properly within InstallAware so that the database content gets created.
I have included the sql db creation script in the 'Server Configuration'>'SQL Databases' feature and the database is created, however all the content (tables, etc) is missing.
I am using the variable $TARGETDIR$ in the SQL script (to ensure that the database gets created in whatever path is defined by the user during the installation process).
I know that the sql script is good, because if I run it manually the complete database is created. (Of course I do have to sub-in the path 'C:\\Program Files\\My Company\\My Application\\Database\\IACheck.mdf' in place of '$TARGETDIR$\\Database\\IACheck.mdf' prior to manually running the script)
Originally I thought I had discovered the cause of the failure to create the DB content when I realised that I was encountering a permission issue in attempting to write to the 'Program File...' directory. However I got around that security issue by using the fantastic 'Access Control' feature of InstallAware. However, having resolved the permissions issue I am still left with the same failure in creating the database content via the InstallAware SQL Script Databases>Script feature.
So, to summarise...
+When run through IA, the sql scripting process of IA fails to create the database content.
+If I manually run the sql script through SQL Management Studio, the databse and content is correctly created.
+Note that I have also tested this in IA with the $TARGETDIR$ removed from the sql script and the full path inserted instead of the $TARGETDIR$, however this also fails to create the db content too.
Any help would be greatly appreciated!
Steve
Following is a small section of the script that shows how I am defining the $TARGETDIR$.
CREATE DATABASE [IACheck]
ON (NAME = N'IACheck_Data', FILENAME = N'$TARGETDIR$\\Database\\IACheck.mdf' , SIZE = 18, FILEGROWTH = 10%)
LOG ON (NAME = N'IACheck_Log', FILENAME = N'$TARGETDIR$\\Database\\IACheck_log.ldf' , SIZE = 10, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
My SQL database script will not create the DB in IA...
Try putting
USE [master]
GO
Before the CREATE DATABASE. Also make sure you login with a user that can actually CREATE a database (Login->Server Roles and Permissions tabs, under SSMS).
USE [master]
GO
Before the CREATE DATABASE. Also make sure you login with a user that can actually CREATE a database (Login->Server Roles and Permissions tabs, under SSMS).
Panagiotis Kefalidis
Software Design Team Lead
\"In order to succeed, your desire for success should be greater than your fear of failure\"
InstallAware Software Corporation
Software Design Team Lead
\"In order to succeed, your desire for success should be greater than your fear of failure\"
InstallAware Software Corporation
Set Access to "Everyone" to that folder, and try again.
I suspect that the Installation Process (Service) does not have access to the folder, to write or sth.. Try putting a rule for "Everyone" and allow everything and try again. Or if you don't want to create it for everyone make sure that the SQLUserAccount of your SQL Instance HAS access to that directory.
I suspect that the Installation Process (Service) does not have access to the folder, to write or sth.. Try putting a rule for "Everyone" and allow everything and try again. Or if you don't want to create it for everyone make sure that the SQLUserAccount of your SQL Instance HAS access to that directory.
Panagiotis Kefalidis
Software Design Team Lead
\"In order to succeed, your desire for success should be greater than your fear of failure\"
InstallAware Software Corporation
Software Design Team Lead
\"In order to succeed, your desire for success should be greater than your fear of failure\"
InstallAware Software Corporation
Re:
Gizm0 wrote:Set Access to "Everyone" to that folder, and try again.
I suspect that the Installation Process (Service) does not have access to the folder, to write or sth.. Try putting a rule for "Everyone" and allow everything and try again. Or if you don't want to create it for everyone make sure that the SQLUserAccount of your SQL Instance HAS access to that directory.
This works by the way. (Setting access to read/write for everyone for the database directory).
Re: My SQL database script will not create the DB in IA...
gibbie99,
Thank you!
Thank you!
Aviv Giladi
InstallAware Software
White Papers (HowTos) - http://www.installaware.com/publication ... papers.htm
Product Guides - http://www.installaware.com/publication ... guides.htm
InstallAware Help -F1 anywhere in the InstallAware IDE
InstallAware Software
White Papers (HowTos) - http://www.installaware.com/publication ... papers.htm
Product Guides - http://www.installaware.com/publication ... guides.htm
InstallAware Help -F1 anywhere in the InstallAware IDE
Re: My SQL database script will not create the DB in IA...
My apologies to everyone looking here for a solution for the last 5 years...
At the time InstallAware support was not able to assist and I became so frustrated with this problem that I put the entire project aside for several months and had completely forgotten about this post by the time I finally got around to figuring out a solution. I just happened across it again while looking for a solution to another problem and thought that I should be nice and update the post.
So this is what I determined...
InstallAware does not like to run complex database creation scripts solely from the 'Server Configuration > SQL Databases > MS SQL Server > SQL Script' UI.
It seems that the 'SQL Script' frame in the 'MS SQL Server' UI is ONLY ABLE TO CREATE AN EMPTY DATABASE (...by this I mean a database that contains no Tables, Stored Proceedures, etc.)
So as long as the SQL database creation script that you paste into the UI (shown below) is designed only to create an empty database and log file, you will be successful.
Following is a screen capture of the 'MS SQL Server' UI settings from my project:
...and following is the complete script that is pasted into the 'SQL Script' dialog in order to create an empty SQL 2005 database:
As you probably guessed, the script above will not run successfully until you have defined the variables (PRODDATABASENAME, INSTANCENAME, COMPUTERNAME, etc.) within your InstallAware script. Following is an example of how and where you would define those variables after the 'Apply Install' area of the InstallAware script:
Now you are probably thinking, "Ok Steve, so you created an empty database, but how do you add the tables, stored procedures and such to that empty database?", well that's a good question. I'm glad you asked!
I took the remainder of my original SQL script (that part that creates the tables, etc) and I saved it as a separate SQL script file called 'mysqltables.sql'.
I then added 'mysqltables.sql' and Microsoft's 'osql.exe' (which you need in order to execute an SQL script file outside of Microsoft's 'SQL Management Studio') to InstallAware's 'Creatives' UI.
Please note that I have not included the content of my 'mysqltables.sql' script (that creates the tables and stored procedures) here because the database content requirements for your application will, of course, be very different than mine.
I then went to the MSI code section of installAware and added 'Alternate SQL Server' from the 'MSIcode' located in InstallAware's drag and drop area on the right side the screen. When you drag and drop this function into your InstallAware script, the following dialog box will appear:
I dragged and dropped the 'Alternate SQL Server' function into the code above where you see the line:
[Run Program $SUPPORTDIR$\osql.exe -S$COMPUTERNAME$\$INSTANCENAME$ -d$PRODDATABASENAME$ -E -i$SUPPORTDIR$\mysqltables.sql, startup in folder $SUPPORTDIR$ (WAIT, get result into variable SQLSUCCESS)].
So, if you have properly defined all of the variables and you have properly segregated your original SQL script into two separate scripts (one that creates the empty Db and one that creates the tables & sp's. etc), then you should have no difficulty getting InstallAware to create a fully functional database!
Just as a side note, I would highly recommend that you take advantage of the 'INSTANCENAME' variable since creating a custom name for your instance of SQL Server will differentiate it from the default instance name of 'SQLExpress' used by Microsoft during a typical installation of SQL Express 2005. If you fail to use a unique Instance name for your application's SQL Express installation, then you risk having your application broken should someone (or some other poorly behaved application) uninstall MS SQL Express.
Well that's it... How simple was that! (lol)
Please let me know if you feel that I have missed anything or if any of this requires further clarification.
Good luck with your application!
Best regards,
-Steve
At the time InstallAware support was not able to assist and I became so frustrated with this problem that I put the entire project aside for several months and had completely forgotten about this post by the time I finally got around to figuring out a solution. I just happened across it again while looking for a solution to another problem and thought that I should be nice and update the post.
So this is what I determined...
InstallAware does not like to run complex database creation scripts solely from the 'Server Configuration > SQL Databases > MS SQL Server > SQL Script' UI.
It seems that the 'SQL Script' frame in the 'MS SQL Server' UI is ONLY ABLE TO CREATE AN EMPTY DATABASE (...by this I mean a database that contains no Tables, Stored Proceedures, etc.)
So as long as the SQL database creation script that you paste into the UI (shown below) is designed only to create an empty database and log file, you will be successful.
Following is a screen capture of the 'MS SQL Server' UI settings from my project:
...and following is the complete script that is pasted into the 'SQL Script' dialog in order to create an empty SQL 2005 database:
Code: Select all
CREATE DATABASE $PRODDATABASENAME$ ON ( NAME = N'$PRODDATABASENAME$_Data', FILENAME = N'$PRODTARGET$\$PRODDATABASENAME$.mdf',SIZE = 102400KB, MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB) LOG ON (NAME = N'$PRODDATABASENAME$_Log', FILENAME = N'$PRODTARGET$\$PRODDATABASENAME$_log.ldf', SIZE = 20480KB, MAXSIZE = UNLIMITED, FILEGROWTH = 20480KB) COLLATE SQL_Latin1_General_CP1_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'$PRODDATABASENAME$', @new_cmptlevel=90
GO
ALTER DATABASE [$PRODDATABASENAME$] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [$PRODDATABASENAME$] SET ANSI_NULLS OFF
GO
ALTER DATABASE [$PRODDATABASENAME$] SET ANSI_PADDING OFF
GO
ALTER DATABASE [$PRODDATABASENAME$] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [$PRODDATABASENAME$] SET ARITHABORT OFF
GO
ALTER DATABASE [$PRODDATABASENAME$] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [$PRODDATABASENAME$] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [$PRODDATABASENAME$] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [$PRODDATABASENAME$] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [$PRODDATABASENAME$] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [$PRODDATABASENAME$] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [$PRODDATABASENAME$] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [$PRODDATABASENAME$] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [$PRODDATABASENAME$] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [$PRODDATABASENAME$] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [$PRODDATABASENAME$] SET DISABLE_BROKER
GO
ALTER DATABASE [$PRODDATABASENAME$] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [$PRODDATABASENAME$] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [$PRODDATABASENAME$] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [$PRODDATABASENAME$] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [$PRODDATABASENAME$] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [$PRODDATABASENAME$] SET READ_WRITE
GO
ALTER DATABASE [$PRODDATABASENAME$] SET RECOVERY FULL
GO
ALTER DATABASE [$PRODDATABASENAME$] SET MULTI_USER
GO
ALTER DATABASE [$PRODDATABASENAME$] SET PAGE_VERIFY TORN_PAGE_DETECTION
GO
ALTER DATABASE [$PRODDATABASENAME$] SET DB_CHAINING OFF
GO
As you probably guessed, the script above will not run successfully until you have defined the variables (PRODDATABASENAME, INSTANCENAME, COMPUTERNAME, etc.) within your InstallAware script. Following is an example of how and where you would define those variables after the 'Apply Install' area of the InstallAware script:
Code: Select all
Apply Install (get result into variable SUCCESS)
if Variable SUCCESS not Equals ERROR
if Variable SUCCESS not Equals CANCEL
Run Program $TARGETDIR$\test.exe -i (WAIT)
Set Variable INSTANCENAME to MYSQLEXPRESS
Get System Setting Logged on Computer Name into COMPUTERNAME
Set Variable PROGRESSMODE to MARQUEE
Set Variable PRODTARGET to $TARGETDIR$\Local Data Repository\Database
Set Variable PRODDATABASENAME to MYSQLDATABASE
Grant Complete Access to File System Object "$TARGETDIR$\Local Data Repository\Database"
Configure Windows Firewall - add port opening 1433 TCP
Configure Windows Firewall - add port opening 1434 UDP
Set Variable SQLSUCCESS to
Microsoft SQL Server Script : Connect to database master on instance $COMPUTERNAME$\$INSTANCENAME$ (get result into variable SQLSUCCESS)
Comment: MessageBox: Test, Create Database$NEWLINE$$SQLSUCCESS$
Comment: Alternate SQL Server : Connect to database $proddatabasename$ on instance (local)\mysqlexpress using Windows authentication , run SQL script in file c:\my_project\mysqltables.sql (get result into variable sqlsuccess)
Run Program $SUPPORTDIR$\osql.exe -S$COMPUTERNAME$\$INSTANCENAME$ -d$PRODDATABASENAME$ -E -i$SUPPORTDIR$\mysqltables.sql, startup in folder $SUPPORTDIR$ (WAIT, get result into variable SQLSUCCESS)
Comment: MessageBox: Test, Create Tables & Procs$NEWLINE$$SQLSUCCESS$
Schedule Task Check for MyProject Updates to run weekly
Set Variable PROGRESSMODE to
end
end
end
Now you are probably thinking, "Ok Steve, so you created an empty database, but how do you add the tables, stored procedures and such to that empty database?", well that's a good question. I'm glad you asked!
I took the remainder of my original SQL script (that part that creates the tables, etc) and I saved it as a separate SQL script file called 'mysqltables.sql'.
I then added 'mysqltables.sql' and Microsoft's 'osql.exe' (which you need in order to execute an SQL script file outside of Microsoft's 'SQL Management Studio') to InstallAware's 'Creatives' UI.
Please note that I have not included the content of my 'mysqltables.sql' script (that creates the tables and stored procedures) here because the database content requirements for your application will, of course, be very different than mine.
I then went to the MSI code section of installAware and added 'Alternate SQL Server' from the 'MSIcode' located in InstallAware's drag and drop area on the right side the screen. When you drag and drop this function into your InstallAware script, the following dialog box will appear:
I dragged and dropped the 'Alternate SQL Server' function into the code above where you see the line:
[Run Program $SUPPORTDIR$\osql.exe -S$COMPUTERNAME$\$INSTANCENAME$ -d$PRODDATABASENAME$ -E -i$SUPPORTDIR$\mysqltables.sql, startup in folder $SUPPORTDIR$ (WAIT, get result into variable SQLSUCCESS)].
So, if you have properly defined all of the variables and you have properly segregated your original SQL script into two separate scripts (one that creates the empty Db and one that creates the tables & sp's. etc), then you should have no difficulty getting InstallAware to create a fully functional database!
Just as a side note, I would highly recommend that you take advantage of the 'INSTANCENAME' variable since creating a custom name for your instance of SQL Server will differentiate it from the default instance name of 'SQLExpress' used by Microsoft during a typical installation of SQL Express 2005. If you fail to use a unique Instance name for your application's SQL Express installation, then you risk having your application broken should someone (or some other poorly behaved application) uninstall MS SQL Express.
Well that's it... How simple was that! (lol)
Please let me know if you feel that I have missed anything or if any of this requires further clarification.
Good luck with your application!
Best regards,
-Steve
Last edited by Steve on Fri Jun 17, 2011 8:28 am, edited 6 times in total.
Re: My SQL database script will not create the DB in IA...
Thank you so much Steve!
Aviv Giladi
InstallAware Software
White Papers (HowTos) - http://www.installaware.com/publication ... papers.htm
Product Guides - http://www.installaware.com/publication ... guides.htm
InstallAware Help -F1 anywhere in the InstallAware IDE
InstallAware Software
White Papers (HowTos) - http://www.installaware.com/publication ... papers.htm
Product Guides - http://www.installaware.com/publication ... guides.htm
InstallAware Help -F1 anywhere in the InstallAware IDE
Who is online
Users browsing this forum: Google [Bot] and 70 guests