PDA

View Full Version : MDF file location



jrahma
11-08-2009, 07:09 AM
I am includin g SQL Express 2005 in my setup package and specefying the MDF & LDF files locations in the SQL script but when running the project it still creating both files in the default SQL Server folder in the Program Files!! why?

this is the create part in SQL Script:

/****** Object: Database the_internet_cafe Script Date: 03/11/2009 9:12:58 PM ******/
CREATE DATABASE [the_internet_cafe] ON (NAME = N'the_internet_cafe', FILENAME = N'C:\Program Files\Jassim Rahma\TICS\Database\the_internet_cafe.mdf' , SIZE = 3, FILEGROWTH = 1) LOG ON (NAME = N'the_internet_cafe_log', FILENAME = N'C:\Program Files\Jassim Rahma\TICS\Database\the_internet_cafe_log.LDF' , SIZE = 1, FILEGROWTH = 10%)
GO

hidenori
11-10-2009, 01:40 PM
Make sure that you uncheck the "Create Catalog If Absent" option on the Connection | General tab.

jrahma
11-11-2009, 03:37 AM
I uncheck it but now I am getting the attached error!!

Mrunmayee
11-11-2009, 09:47 AM
Make sure that the folder exists before you execute your SQL query.So that your ldf and mdf file will be created in that particular path.

-Mrunmayee

hidenori
11-11-2009, 11:07 AM
Please check out this community thread (http://community.flexerasoftware.com/showthread.php?t=190402).

jrahma
11-11-2009, 01:44 PM
how can i make sure folder is created?!

I have already assigned the folder in the project property and created the script n the SQL script!

then what?!

jrahma
11-13-2009, 08:35 AM
please help....

hidenori
11-13-2009, 10:41 AM
If you target a local Microsoft SQL server, you can create the folder by authoring the CreateFolder table of your MSI project. When you create an empty component that targets the folder in the Setup Design view, InstallShield automatically creates that entry for you. If you target a remote server, you have to create the folder manually.

jrahma
11-13-2009, 05:43 PM
ok..in a simple language..

I have created the folders in setup design including my data folder located at C:\Program FIles\Jassim Rahma\TICS\Data

and in my FILENAME in the SQL Script I have refferred to the same folder but it was not creating creating the file in that folder and after unchecking the Create Catalog if Absent I am getting an error..

now what?

hidenori
11-13-2009, 07:31 PM
Sorry, I was refering a wrong other community thread in my earlier post in this thread. Please check it out one more time as I have corrected it. If the folder has already created before running the SQL script, the problem is that you target the database that you are trying to create. So you need to specify an empty string for the Target Catalog setting, or insert the USE master statement before the CRATE DATBASE statement.

Hope that helps.

jrahma
11-13-2009, 07:48 PM
so you mean by just using USE mater before the CREATE my problem should be resolved?

I will give it a try and let you know...

hidenori
11-13-2009, 08:27 PM
After I was thinking a bit more, I realized that inserting the USE master statement will not fix the problem. InstallShield will be trying to connect to the database that you specify for the Target Catalog setting when verifying SQL login credentials. I am assuming that you specify the database that you are trying to create. Please just try specifying an empty string for the Target Catalog setting if it fixes the error.

If you run other scripts that modify that database, please read this community thread (http://community.flexerasoftware.com/showthread.php?t=190402).

jrahma
11-14-2009, 09:01 AM
I tried the USE and it didn't work just like you said.

there is one problem if i set empty catelog... the database name in the SQL login dialog will be empty..

my current scenario: i have set the catalog to the_internet_cafe and the server to (local)\JASSIMSQL and then I set the enable property to false for both because I don't want the user to change it which works fine if I want data to be copied to the default MS SQL data folder but when I want data to be copied in my Data folder then I am having this problem?

I even tried creating the Data folder before the running the setup but I am getting the same error I have attached before.

What I need is very simple:


i want the MDF & LDF files to be copied in my specefic folder
I don't want the user to select the server. I want to force my instance.
I don't want the use to change the name of the catalog.


what's the solution?

hidenori
11-15-2009, 02:09 AM
In order to accomplish your requirements, you may need to use two connections as follows:


Create a new Basic MSI project.
Go to the Property Manager, and create a property named IS_SQLSERVER_DATABASE2 with an empty value.
Go to the SQL Scripts view, and add two connections.
Uncheck the "Create Catalog If Absent" option on both connections.
For the first connection, select IS_SQLSERVER_DATABASE2 for the Target Catalog Property Name in the Advanced tab.
Add the script that creates the database to the first connection, and select only the "Run Script During Install" option in the runtime tab.
For the second connection, specify (local)\JASSIMSQL for the Target Server setting, and _internet_cafe for the Target Catalog setting.
If you have other scripts that modify the database, attach to the second connection.
Go to the Dialog Editor, and expand the SQLLogin dialog.
Add the following event to the Next button and place it before the DoAction event:
[IS_SQLSERVER_CONNECTIONS_TO_VALIDATE] <Connection1> 1
where <Connection1> is the name of the first connection.
Set the Enabled setting to false for the cboServers and edtCatalog controls on the SQLLogin dialog.
Build and run the setup.

jrahma
11-16-2009, 01:42 AM
i did the same exactly.. in the last part (the SQLLogin dialog), here is what i did to confirm:

Event: [IS_SQLSERVER_CONNECTIONS_TO_VALIDATE]
Argument: <TICS_Connection_>
Condition: 1

TICS_Connection_ is my first connection name (ends with _) and TICS_Connection is my second connection

but I am still having the same attached error..

hidenori
11-16-2009, 11:22 AM
For the Argument setting, you need to specify the name of the first connection without angle brackets as follows:

Event: [IS_SQLSERVER_CONNECTIONS_TO_VALIDATE]
Argument: TICS_Connection_
Condition: 1

Do you receive the error when you click the Next button on the SQLLogin dialog or when the script is executed? Is the problem reproducible if you run the same script from Microsoft SQL Server Management Studio?

jrahma
11-17-2009, 08:36 AM
I tried without brackets but it's still the same problem. This problem doesn't exist in the previous scenario as explained when i don't care about the MDF location and the Create Catalog if Absent is checked.

The error occurs during executing the script.

I also checked at that level when the error was thrown and found that the folder Data was already created! so where is thr problem?

now what's the solution?

and Is there any way to expport my settings in order to show it here?

hidenori
11-17-2009, 08:46 AM
Could you send me a sample project at hyamanishi@flexerasoftware.com so that I can look into it further?

Thank you for your patience and cooperation.

jrahma
11-17-2009, 08:49 AM
which file or folder i should send?

hidenori
11-17-2009, 12:12 PM
Please send .ism and .sql files.

jrahma
11-17-2009, 01:48 PM
sent to your email as requested..

hidenori
11-17-2009, 04:56 PM
Thank you for providing your project. I think that it is a permission issue of your custom database folder. If you change the Script Error Handling setting on the Runtime tab of your script files to "On Error, Abort Installation", I bet you will receive the "CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file '....mdf'. error.

If that is the case, you need to go to Microsoft SQL Server Configuration Manager and click on the Services node from the left pane. From the right pane select the SQL Server service that you target and go to its properties. Change the builtin login from 'Nerwork Service' to 'Local system' and restart the service.

Also, I would suggest that you move the first CREATE TABLE statement through the end of the file to the second script, so that they will run against the the_internet_cafe database, not master.

Hope that helps.

jrahma
11-17-2009, 05:53 PM
If that is the case, you need to go to Microsoft SQL Server Configuration Manager and click on the Services node from the left pane. From the right pane select the SQL Server service that you target and go to its properties. Change the builtin login from 'Nerwork Service' to 'Local system' and restart the service.

but i don't have a specefic server! I am installing the SQL Server Express 2005 included in my setup package so in every setup it will be a fresh installation? any way to grant the required previllages to the script?

hidenori
11-17-2009, 06:34 PM
You can change the logon service account during the Microsoft SQL Server 2005 Express installation. For silent installations, you can specify via the SQLACCOUNT command line parameter. Please check out How to: Install SQL Server 2005 from the Command Prompt (http://msdn.microsoft.com/en-us/library/ms144259(SQL.90).aspx) for more details.

If you want to assign the Write permission to the folder for the Network Service account, you need to use the new Locked-Down Permission method introduced in InstallShield 2010. Please check out Securing Files, Folders, and Registry Keys in a Locked-Down Environment (http://helpnet.flexerasoftware.com/Robo/BIN/Robo.dll?mgr=agm&tpc=%2Frobo%2Fprojects%2Finstallshield16helplib%2FSecurityPermissions.htm&wnd=InstallShieldLivingHelp%7CMain&agt=wsm&ctxid=) for more details.

Regards.

jrahma
11-18-2009, 08:41 AM
Also, I would suggest that you move the first CREATE TABLE statement through the end of the file to the second script, so that they will run against the the_internet_cafe database, not master..

Thanks for your help.. I tried it on C:\TEMP and it worked so it's for sure a permission issue. I will check the SQLACCOUNT and let you know

but i didn't understand what do you mean byu the above about the CREATE TABLE..

Do you mean I keep the CREATE DATABASE in the first connection and the rest of the CREATE TABLEs in the second connection? so my frst connection will only have CREATE DATABASE? is that what you are trying to say?

hidenori
11-18-2009, 10:50 AM
but i didn't understand what do you mean byu the above about the CREATE TABLE..

Do you mean I keep the CREATE DATABASE in the first connection and the rest of the CREATE TABLEs in the second connection? so my frst connection will only have CREATE DATABASE? is that what you are trying to say?
Yes, the script under the first connection will run against the master database, and the other script under the second connection will run against the the_internet_cafe database. Therefore, you need to move the line 64 through the end of the file in the first script to the second connection.

jrahma
11-18-2009, 05:40 PM
YAHOOOOO!!!!! :)


Finally working properly

can you please explain to me plz what did you do in your 1 to 13 steps so that I know what to do instead of just doing it like an idiot :D

Thank you sooo much :)

hidenori
11-18-2009, 07:11 PM
I am glad that your issue has been solved!

What I suggested was to create two connections; the first one for connecting to the master database to run the script that will create and configure the the_internet_cafe database, and the second one for connecting to the the_internet_cafe database to run the rest of the script that will add items such as tables to the the_internet_cafe database.

Since the the_internet_cafe database does not exist when you click the Next button on the SQLLogin dialog, and both connections use the same login credentials but target a different database, I added a control event so that only the first connection will be validated by the action.

Regards.