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
- :
- Query to Create a new Database using Basic MSI
Subscribe
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Subscribe
- Mute
- Printer Friendly Page
‎Dec 05, 2008
03:07 AM
Query to Create a new Database using Basic MSI
hey all,
i'm pretty new to installshield. Can anyone of you suggest me with some tips as to which type of project suits my requirement. Please suggest as to how to proceed.
my requirement is somewhat like this
i have an sql script which creates a database now i gotta create an installer which takes the input from the user which goes as the Database name and use this name to update my script first and then create the database.
Suggest me some tips to proceed and achieve this...
i'm pretty new to installshield. Can anyone of you suggest me with some tips as to which type of project suits my requirement. Please suggest as to how to proceed.
my requirement is somewhat like this
i have an sql script which creates a database now i gotta create an installer which takes the input from the user which goes as the Database name and use this name to update my script first and then create the database.
Suggest me some tips to proceed and achieve this...
(23) Replies
‎Dec 05, 2008
12:57 PM
You may want to consider using the SQL Scripts view in order to accomplish your requirements. Once you add a connection and your SQL script in the view, the SQLLogin dialog will be automatically populated at run-time. In the Catalog Name field on the dialog, you can specify a database that you want to target. The database will be created if does not exist when you click the Next button. Your SQL script will be executed when your application is installed.
‎Dec 08, 2008
08:20 AM
hi,
i tried out running a few scripts after creating a connection object as suggested and i was even successfull in doing so...and i even added scripts which need to be run during uninstall.
how can i make use of the [INSTALLDIR] property in the sql scripts
i tried making use of string replacement feature which didn't work
please help me in this regard
i tried out running a few scripts after creating a connection object as suggested and i was even successfull in doing so...and i even added scripts which need to be run during uninstall.
how can i make use of the [INSTALLDIR] property in the sql scripts
i tried making use of string replacement feature which didn't work
please help me in this regard
‎Dec 08, 2008
09:38 AM
You need to use the Text Replacement functionality. Please see the community thread for more information.
‎Dec 08, 2008
10:43 AM
Hello,
I have been using Install Shield (IS) since IS 5.5. In all that time, I have had to create databases utilizing user input. This is the best method that works for me. Feel free to take a look.
ADO Objects Example:
http://community.acresso.com/showthread.php?t=152884&page=2
Search and Replace information in SQL Files...
http://community.acresso.com/showthread.php?t=161288http://community.acresso.com/showthread.php?t=161288
You may ask what installation project type I use. I use Install Shield Script projects for when I need to control everything the installation does. I do this for several reasons. If you would like to know them, I'm more than will to share my point of view, but at a later time. For now, here is my train of thought with the two examples above.
The Search and Replace function is used to modify the SQL scripts to conform to the user information you gather during the interview phase of your installation. In the interview phase, I have created custom dialogs to gather information on where the database server is, the named instance, the user name and password. All the information I would need to connect to the database server. I then use the ADO example to query the Database Engine to make sure the information provided by the user is correct and to make sure that the machine the user is using has permission to connect to the database engine.
Now here is the tricky bit, since I use my own methodology in running the SQL scripts utilizing ADO objects, I actually load and parse the SQL script file into a StringList object. I do this for a couple of reasons.
1) When I parse the SQL file, I know how many SQL statements are in the file. This allows me to put up a progress bar and display where we are in the SQL file.
2) If the SQL file errors, I know what SQL statement error which gives me the ability to log it into a log file of my own creation along with the error I get from the database engine.
3) Since I can create ADO objects with Install Shield, I can run a SQL query versus our product database. This can be very useful since we tattoo or write a product database version number of our own to keep track of what version our product is using. This is very useful for when I want to patch or upgrade our product. I query the database and get the product database version from a table. I then traverse the SQL file which contains all the updates and find the starting point based on the database version.
4) Since I control the errors that may come from running the SQL file, there are rare cases where an error can be ignored or expected. If such a case happens, you don't have to display the error to the user. you can just continue as if nothing ever happened and the user doesn't panic because they see that an error has happened.
In conclusion, I like using Install Shield script projects for this level of control of the SQL scripts I have to run.
If you would like more information, please contact me.
I have been using Install Shield (IS) since IS 5.5. In all that time, I have had to create databases utilizing user input. This is the best method that works for me. Feel free to take a look.
ADO Objects Example:
http://community.acresso.com/showthread.php?t=152884&page=2
Search and Replace information in SQL Files...
http://community.acresso.com/showthread.php?t=161288http://community.acresso.com/showthread.php?t=161288
You may ask what installation project type I use. I use Install Shield Script projects for when I need to control everything the installation does. I do this for several reasons. If you would like to know them, I'm more than will to share my point of view, but at a later time. For now, here is my train of thought with the two examples above.
The Search and Replace function is used to modify the SQL scripts to conform to the user information you gather during the interview phase of your installation. In the interview phase, I have created custom dialogs to gather information on where the database server is, the named instance, the user name and password. All the information I would need to connect to the database server. I then use the ADO example to query the Database Engine to make sure the information provided by the user is correct and to make sure that the machine the user is using has permission to connect to the database engine.
Now here is the tricky bit, since I use my own methodology in running the SQL scripts utilizing ADO objects, I actually load and parse the SQL script file into a StringList object. I do this for a couple of reasons.
1) When I parse the SQL file, I know how many SQL statements are in the file. This allows me to put up a progress bar and display where we are in the SQL file.
2) If the SQL file errors, I know what SQL statement error which gives me the ability to log it into a log file of my own creation along with the error I get from the database engine.
3) Since I can create ADO objects with Install Shield, I can run a SQL query versus our product database. This can be very useful since we tattoo or write a product database version number of our own to keep track of what version our product is using. This is very useful for when I want to patch or upgrade our product. I query the database and get the product database version from a table. I then traverse the SQL file which contains all the updates and find the starting point based on the database version.
4) Since I control the errors that may come from running the SQL file, there are rare cases where an error can be ignored or expected. If such a case happens, you don't have to display the error to the user. you can just continue as if nothing ever happened and the user doesn't panic because they see that an error has happened.
In conclusion, I like using Install Shield script projects for this level of control of the SQL scripts I have to run.
If you would like more information, please contact me.
‎Dec 09, 2008
12:51 AM
This was really of great help to me...i'm going through the threads to know more. If i have read the threads right then the mentioned piece of code works fine for installscript type of projects.
But i'm working on a basic msi type of project.
Is it possible to achieve the same level of control even in msi type of project ??
I tried using the [INSTALLDIR] in the text replacement feature in sql scripts view for various scripts...Then it fired the error which says
Error 27506 Error executing SQL script
Modify file encountered operating system error 112 (There is not enough space on the disk) while attempting to expand the physical file. (5149)
i even tried making the actions ISSQLServerInstall and ISSQLServerUninstall from derfered execution in system context to just defered execution but no luck the error was same.
However if when i tried using the property [IS_SQLSERVER_DATABASE]
Installation was fine...
Can't i use [INSTALLDIR] in the text replacement option ?????
But i'm working on a basic msi type of project.
Is it possible to achieve the same level of control even in msi type of project ??
I tried using the [INSTALLDIR] in the text replacement feature in sql scripts view for various scripts...Then it fired the error which says
Error 27506 Error executing SQL script
Modify file encountered operating system error 112 (There is not enough space on the disk) while attempting to expand the physical file. (5149)
i even tried making the actions ISSQLServerInstall and ISSQLServerUninstall from derfered execution in system context to just defered execution but no luck the error was same.
However if when i tried using the property [IS_SQLSERVER_DATABASE]
Installation was fine...
Can't i use [INSTALLDIR] in the text replacement option ?????
‎Dec 10, 2008
11:00 AM
I jus tried a simple SQL script as per my requirement
Requirement : create a database whose data and log file has to be stored in my installation directory
User Inputs : User provides the input which is stored in [IS_SQLSERVER_DATABASE] property
Further i even did run the sql profiler to trace the happenings
Trace details when the installation aborted :
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = 'ABC')
DROP DATABASE [ABC]
/****** Object: Database [ABC] Script Date: 11/25/2008 14:29:16 ******/
CREATE DATABASE [ABC] ON PRIMARY
( NAME = N'ABC', FILENAME = N'C:\Program Files\My Company Name\My Product Name\ABC.mdf' , SIZE = 289792KB , MAXSIZE = UNLIMITED, FILEGROWTH =
1024KB )
LOG ON
( NAME = N'ABC_log', FILENAME = N'C:\Program Files\My Company Name\My Product Name\ABC_log.ldf' , SIZE = 1964480KB , MAXSIZE = 2048GB ,
FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS
go
I even observed that the query file is getting updated with both the [INSTALLDIR] and [IS_SQLSERVER_DATABASE] values which are highlighted, but still it fires the same error. Please Let me know how this can be solved.
Requirement : create a database whose data and log file has to be stored in my installation directory
User Inputs : User provides the input which is stored in [IS_SQLSERVER_DATABASE] property
Further i even did run the sql profiler to trace the happenings
Trace details when the installation aborted :
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = 'ABC')
DROP DATABASE [ABC]
/****** Object: Database [ABC] Script Date: 11/25/2008 14:29:16 ******/
CREATE DATABASE [ABC] ON PRIMARY
( NAME = N'ABC', FILENAME = N'C:\Program Files\My Company Name\My Product Name\ABC.mdf' , SIZE = 289792KB , MAXSIZE = UNLIMITED, FILEGROWTH =
1024KB )
LOG ON
( NAME = N'ABC_log', FILENAME = N'C:\Program Files\My Company Name\My Product Name\ABC_log.ldf' , SIZE = 1964480KB , MAXSIZE = 2048GB ,
FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS
go
I even observed that the query file is getting updated with both the [INSTALLDIR] and [IS_SQLSERVER_DATABASE] values which are highlighted, but still it fires the same error. Please Let me know how this can be solved.
‎Dec 10, 2008
11:41 AM
I was able to run the SQL script that you posted from InstallShield against our Microsoft SQL Server 2005 Enterpise Edition on Windows Server 2008 successfully. Could you run the SQL script from Microsoft SQL Server Management Studio, and see if you receive the same error?
‎Dec 11, 2008
03:40 AM
hi,
i checked with the query first it works absolutely fine in MSSQLServer Mgmt Studio with no hassles.
today i was able to spend more time and checked out the installer in different cases
1. I tried to install the application onto a different drive totally in a new location to my surprise it worked with no issues
2. I tried to install the application in the same drive but out of the program files folder but in a new folder which i created while running the installation and even this worked great.
3. But the installation was throwing the same error when it was installing in the default location i.e c:/Programfiles/My Company Name/My Product Name
By testing in all these scenarios i made a few observations.
I was surprised to see the installer didn't work for the default location of program files but worked for a folder outside Program files...it took a while but installation was successfull.
while installing all the sql scripts the data base log and data files were consuming close to 2.5GB space but my c drive had around some 2.52GB very much around the corner. I suspect the lack of space might be a reason for that error.
In that case how can i do a disk cost check before sql scripts are executed.
Is it possible to evaluate the disk cost for running the sql scripts ???
How can i handle this scenario ????
i checked with the query first it works absolutely fine in MSSQLServer Mgmt Studio with no hassles.
today i was able to spend more time and checked out the installer in different cases
1. I tried to install the application onto a different drive totally in a new location to my surprise it worked with no issues
2. I tried to install the application in the same drive but out of the program files folder but in a new folder which i created while running the installation and even this worked great.
3. But the installation was throwing the same error when it was installing in the default location i.e c:/Programfiles/My Company Name/My Product Name
By testing in all these scenarios i made a few observations.
I was surprised to see the installer didn't work for the default location of program files but worked for a folder outside Program files...it took a while but installation was successfull.
while installing all the sql scripts the data base log and data files were consuming close to 2.5GB space but my c drive had around some 2.52GB very much around the corner. I suspect the lack of space might be a reason for that error.
In that case how can i do a disk cost check before sql scripts are executed.
Is it possible to evaluate the disk cost for running the sql scripts ???
How can i handle this scenario ????
‎Dec 11, 2008
06:06 AM
hi,
i was able to clear some space and retest the installer . The problem was defnitely due to lack of space in the installing drive.
Now the issue being confirmed ....how can i actually do a check if enough space is available for installing my database ??
Apart from this
I had one of the script which was run during the uninstall process to drop the database.
Text replacement didn't seem to work in this script as it retains the default catalog name, as a result of which if the user enters a different name the unintallation script is failing. ??
Can u help me in resolving these 2 highlighted issues ???
i was able to clear some space and retest the installer . The problem was defnitely due to lack of space in the installing drive.
Now the issue being confirmed ....how can i actually do a check if enough space is available for installing my database ??
Apart from this
I had one of the script which was run during the uninstall process to drop the database.
Text replacement didn't seem to work in this script as it retains the default catalog name, as a result of which if the user enters a different name the unintallation script is failing. ??
Can u help me in resolving these 2 highlighted issues ???
‎Dec 11, 2008
09:46 AM
This might be a permission issue on the folder you are trying to have your database created in. You may have to change the permission of the folder to allow SQL Server to create the database. Try this:
1) Have the folder created before you do your installation.
2) Give everyone to this folder R/W or full control access.
If that is the issue, look at the command line program "cacls". It allows you to add users and set security permissions on folders.
1) Have the folder created before you do your installation.
2) Give everyone to this folder R/W or full control access.
If that is the issue, look at the command line program "cacls". It allows you to add users and set security permissions on folders.
‎Dec 11, 2008
03:56 PM
i was able to clear some space and retest the installer . The problem was defnitely due to lack of space in the installing drive.
Now the issue being confirmed ....how can i actually do a check if enough space is available for installing my database ??
If you are installing to a local machine, you may want to author the ReserveCosting table to reserve an amount of disk space in any directory that depends on the installation state of a component.
I had one of the script which was run during the uninstall process to drop the database.
Text replacement didn't seem to work in this script as it retains the default catalog name, as a result of which if the user enters a different name the unintallation script is failing. ??
Would it be possible for you to generate a Windows Installer verbose log for the uninstallation and post it here? I also need to know which property you are using for your DROP DATABASE statement.
‎Dec 11, 2008
10:36 PM
TheTraveler wrote:
This might be a permission issue on the folder you are trying to have your database created in. You may have to change the permission of the folder to allow SQL Server to create the database.
Thanks , but there wasn't any security issues...i was able to clear some space in the installation drive and it worked absolutely fine.
‎Dec 12, 2008
04:12 AM
hidenori wrote:
If you are installing to a local machine, you may want to author the ReserveCosting table to reserve an amount of disk space in any directory that depends on the installation state of a component.
Would it be possible for you to generate a Windows Installer verbose log for the uninstallation and post it here? I also need to know which property you are using for your DROP DATABASE statement.
I'm really not sure as to how to get the log while uninstalling, I tried a few things but nothing worked sorry for that though.
I have been using [IS_SQLSERVER_DATABASE] propety value which is populated in the sql scripts
Initially when the connection is created there is a field for entering the catalog value and it is this value which is getting populated when Text replacement feature is run during uninstall.
For Example : while creating the connection say if i give my catalog name as say "ABC" then later during installation if the user changes this catalog name to say "XYZ"
script contains %DBN% variable which i'm replacing by [IS_SQLSERVER_DATABASE] property value.
I've 2 script files one creates a database so i've selected run during install and the other script file which drops the database for which i've selected run during uninstall.
In the script file which runs during installation %DBN% will be replaced by XYZ
but in the script file which runs during uninstallation %DBN% will be replaced by ABC . so database dropping fails.
Please suggest me any alternate ways if any to achieve this simple functionality. I do admit i'm poor in coding so i did prefer Basic Msi type of project.
‎Dec 12, 2008
09:50 AM
InstallShield stores the login information specified during installation in the registry, and reuses them during uninstallation and manitenance. Therefore, the IS_SQLSERVER_DATABASE property is set to "ABC" during uninstallation as you specified in the initial installation. In order to override the values, you need to set 1 to the IS_SQLSERVER_DO_NOT_USE_REG property. Please see the Overriding the Default SQL Run-Time Behavior help topic for more information.
‎Dec 17, 2008
01:41 AM
hidenori wrote:
InstallShield stores the login information specified during installation in the registry, and reuses them during uninstallation and manitenance. Therefore, the IS_SQLSERVER_DATABASE property is set to "ABC" during uninstallation as you specified in the initial installation. In order to override the values, you need to set 1 to the IS_SQLSERVER_DO_NOT_USE_REG property. Please see the Overriding the Default SQL Run-Time Behavior help topic for more information.
Hi,
I created a property IS_SQLSERVER_DO_NOT_USE_REG in the property manager with the value set to 1 I tried to install the application but the results were not as expected. It still behaves the same way ...I hope i've got it right...Please let me know as to how to proceed further...
‎Dec 17, 2008
11:40 AM
Would it be possible for you to email me a sample project that reproduces the behavior at hyamanishi@acresso.com so that I can look into it?
‎Dec 20, 2008
07:57 AM
hidenori wrote:
Would it be possible for you to email me a sample project that reproduces the behavior at hyamanishi@acresso.com so that I can look into it?
Hey hi,
I've mailed you the sample project which failed in my system to the e-mail id specified. Do let me know incase if any more inputs are required. Thanks in advance.
‎Dec 23, 2008
01:21 PM
I have attached the hotfix for InstallShield 2009 SP2 to this post as it turned out to be a bug. Please copy the attached ISSQLSrv.dll to the C:\Program Files\InstallShield\2009\Redist\Language Independent\i386 folder and rebuild your setup, in order to apply the fix. Note that this is not an official release as it is not fully tested by our QA team. I would strongly recommend that you will make a backup copy of the original file before overwriting it. I also submitted the work order #IOC-000077761 so that it will be fixed in the next major release as well.
In addition, I misunderstood your issue as I thought you were trying to drop a different database from the one that you created during installation. Please remove the IS_SQLSERVER_DO_NOT_USE_REG property from your project as you do not need to override the values of your SQL connection settings.
Regards.
In addition, I misunderstood your issue as I thought you were trying to drop a different database from the one that you created during installation. Please remove the IS_SQLSERVER_DO_NOT_USE_REG property from your project as you do not need to override the values of your SQL connection settings.
Regards.
‎Dec 30, 2008
12:46 AM
hidenori wrote:
I have attached the hotfix for InstallShield 2009 SP2 to this post as it turned out to be a bug. Please copy the attached ISSQLSrv.dll to the C:\Program Files\InstallShield\2009\Redist\Language Independent\i386 folder and rebuild your setup, in order to apply the fix. Note that this is not an official release as it is not fully tested by our QA team. I would strongly recommend that you will make a backup copy of the original file before overwriting it. I also submitted the work order #IOC-000077761 so that it will be fixed in the next major release as well.
hey hidenori,
thanks a lot for this timely update, this resolved the issue and the uninstallation works fine now. thanks mate.
