Using the SQL plugin to create a database in SQL 2000

[DanielW]

I am unable to execute a simple sql for creating a database. I can run the sql plugin to verify connection. I can also execute the sql code from the SQL Analyzer with no problem, but when I place it in the sql form plugin for Installaware it will not execute. The code executes with no error and no database created.
Thanks for any comments.
Dan

My settings are as follows:
Server: $SELECTEDSERVER$
Instance: 2000
Database: IKWhsData
User:
Password:
Return result in variable: databasecreated
CREATE DATABASE IKWhsData
ON
( NAME = IKWhsData,
FILENAME = 'd:\\mssql\\data\\IKWhsData.mdb',
SIZE = 3MB,
FILEGROWTH = 10%)
GO

USE IKWhsData
GO

EXEC sp_addlogin
'IKDBUSR', --loginname
'IKPassword', --Password
'IKWhsData' --Default DB
GO

EXEC sp_adduser
'IKDBUSR' -- loginname
GO

[neillans]

Can you post your IA and SQL Server (inc edition) version details?

[DanielW]

I am using SQL Enterprise Manager Version 8.0 and InstallAware 6.3 evaluation software.

[neillans]

Very strange; I can't think of any problem that would cause this off the top of my head.

Have you tried verifying if SQL Server receives the commands from the installer by using SQL Server Profiler?

Would you be able to send your installer script to us for testing?

[DanielW]

I could, but is very simple to recreate. I used the "SQL server connection" and added the "MS SQL Server" plugin. Attached the SQL code above and ran it.

I also found that there is a alternate SQL plugin that I am testing, but I get the "cannot load Web Media Block error". Have no clue on the problem here. Can you help with the alternate plugin error?

Dan

[DanielW]

Also notice on the SQL code that the database extension should be 'mdf' not 'mdb'.

[neillans]

I can replicate this against SQL Server 2005 and the plugin.

However, there is a simple work around.
Assuming you know the database does not exist (you are creating it after all), tick the "Create database if absent" tick box.

Alternatively, specify the Database to be "master".

The problem is occuring because the plugin is attempting to bind to the database - which does not exist. The master database should exist on every server, so is fairly safe - assuming you are logging in using credentials that support accessing this table!

[DanielW]

That is not going to work for me. I am trying to create a "new" database. Master already exists. Somewhere in the forum, it stated to create the database first with an sql and use other sql to setup other parameters. This should work if I could get the code to create the first database. Reducing the sql to create only the new database still does not work with the SQL and alternate SQL plugin. I must being doing something wrong. This is pretty basic stuff.

[neillans]

That's just what I was explaining...

Before you can do anything with SQL Server, you need to establish a connection - which includes binding to a database.
Most applications will bind to a system database, for example master, if you do not specify one -- this is to allow you to create your own database.

Please see that attached two screenshots.

using master.jpg shows the configuration that I have for binding the master database, and using your own create script in order to create the relevant database.

creating.jpg shows another way; simply ticking the "Create database if absent" box, however, this does not give you any control over where the database is created or it's parameters.

File Attached:

screenshots.zip

[DanielW]

Andy, Thanks for your help, although I couldn't get your solution to work, I was able to get the 'create database' to work. Here is how I did it:

When one creates the SQL Project in IA the "SQL Server" plugin is also created to verify connection with the master table with a 'GO' command in the script window. Reviewing this layout I simple plugged my code into the script window, ran the code and POOF, it created the table. This script has variables for the name server- $SERVERNAME$, Instance- $INSTANTNAME$,User-$SQLUSER$, Password - $SQLPASSWORD$, returned result in SQLTEST. These variables, I believe are requred to make the plugin work. At least I could not get it to work without these variables.
I also tried to add a database with the "Alternate SQL Server" Plugin using the same variables as above with an attached file. This did not work and I don't know how to get this plugin to create a database. Any ideas, it look the same but with an attached file reference.

[Gizm0]

Make sure SQLServer has access to the directory you're trying to create the database. It's a common mistake to miss that part. If you want to create a database somewhere outside the default Data\\ folder, you need to give access to SQL Server there, through ACL.Try adding for the beginning Full access to "Everyone" and try running the script again and see how it goes.