My SQL database script will not create the DB in IA...

Got a problem you cannot solve? Try here.

My SQL database script will not create the DB in IA...

Postby Steve » Mon Nov 13, 2006 12:35 pm

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
Steve
 
Posts: 101
Joined: Fri Jun 16, 2006 9:51 pm
Location: Canada eh

Postby Gizm0 » Mon Nov 13, 2006 2:51 pm

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).
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
Gizm0
 
Posts: 339
Joined: Wed Nov 09, 2005 8:47 pm

Postby Steve » Tue Dec 12, 2006 12:13 pm

Hi Gizm0

Thanks for the recommedation. Unfortunately this did not resolve the problem. Any other thoughts?
Steve
 
Posts: 101
Joined: Fri Jun 16, 2006 9:51 pm
Location: Canada eh

Postby Gizm0 » Tue Dec 12, 2006 1:51 pm

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.
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
Gizm0
 
Posts: 339
Joined: Wed Nov 09, 2005 8:47 pm

Postby DanielW » Fri Feb 23, 2007 6:07 pm

Steve,
Did you ever get a resolution to this connecting to SQL database problem? I am having the same problem?

Dan
DanielW
 
Posts: 25
Joined: Fri Feb 23, 2007 7:40 am

Re:

Postby gibbie99 » Wed Jan 19, 2011 7:42 am

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).
gibbie99
 
Posts: 4
Joined: Fri Jan 14, 2011 9:57 am

Re: My SQL database script will not create the DB in IA...

Postby giaviv » Wed Jan 19, 2011 10:43 am

gibbie99,

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
giaviv
 
Posts: 2039
Joined: Fri Dec 17, 2010 1:39 pm

Re: My SQL database script will not create the DB in IA...

Postby Steve » Thu Jun 16, 2011 5:06 pm

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:
SQL_Db_Creation.JPG
SQL_Db_Creation.JPG (51.57 KiB) Viewed 3612 times


...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:
Alternate_SQL_Server.JPG
Alternate_SQL_Server.JPG (33.13 KiB) Viewed 3612 times

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.
Steve
 
Posts: 101
Joined: Fri Jun 16, 2006 9:51 pm
Location: Canada eh

Re: My SQL database script will not create the DB in IA...

Postby giaviv » Thu Jun 16, 2011 8:24 pm

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
giaviv
 
Posts: 2039
Joined: Fri Dec 17, 2010 1:39 pm


Return to Technical Support

Who is online

Users browsing this forum: Google [Bot], Yahoo [Bot] and 11 guests