SQL Server Plugin - script fails

[compgumby]

The script creates a new database, its associated tables, users, logins, etc. When it executes, or fails, that is, I get nothing useful from IA - just "ERROR". However, the SQL log is interesting...

I use a variable in the SQL Script - $TARGETDIR$. I do this to have SQL Express create a database in a specific location. In my case, I've got the following:

CREATE FILE encountered... error 5 (Access is denied)... the physical file 'C:\\Program Files\\My AppDirectory\\Database\\MyDB_Data.MDF'.

The script section of the SQL plugin reads:

USE [master]
GO
/****** Object: Database [Coda] Script Date: 01/01/2008 16:56:22 ******/
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDB')
BEGIN
CREATE DATABASE [MyDB] ON PRIMARY
( NAME = N'MyDB_Data', FILENAME = N'$TARGETDIR$\\Database\\MyDB.MDF' ,

SIZE = 2880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 80KB )
LOG ON
( NAME = N'MyDB_Log', FILENAME = N'$TARGETDIR$\\Database\\MyDB_Log.LDF' , SIZE

= 10176KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
END
GO

The rest of the config is as follows:

Server: $SERVERNAME$
Instance: $SQLCHECK$
Database: master
Create database if absent: FALSE
User: blank - want to use Windows Auth
Password: blank
Return result in variable: SQLRESULT
Abort execution on script error: TRUE

If I run the script in Management Studio (after changing $TARGETDIR$ to the appropriate value), everything is fine.

Lastly - I'm running this on a stock XP SP 2 VM. It takes a while because I have to load both .NET 2.0, then SQL Express, and finally - run the script.

Please help...

Thanks,
M.

[RonaldPB]

I've had related problems with SQL Scripts. After experimenting quite a lot I discovered it was caused because I actually did not connect to SQL Server.

I added the following code:
I declared a new variable machinename.
I read the following registrykey in machinenename:
ROOT = HEKEY_LOCAL_MACHINE
KEY = software\\microsoft\\microsoft sql server\\90\\machines
VALUE = originalmachinename

On the MS SQL Server script screen enter $machinename$ into the server-field.
Instance = SQLEXPRESS.

Hope this will help you!

[Roostar2004]

I am having the same problem but your fix did not work for me RonaldPB.

Server: $mcachinename$
Instance: FLOWCAL
Database: TESTIT
Create database if absent: TRUE
User and Password are set to the predefined values as set by the SQl express install.

******************* Database Setup Script **********************
USE [MASTER]
GO
-- Make sure that BUILTIN\\Users do not have SysAdmin permission
EXEC sp_dropsrvrolemember 'BUILTIN\\Users', 'sysadmin'
GO
-- SQL Server 2005 grants SysAdmin (Super Privelege, same as 'sa' account) to BuiltIn\\Administrators by default
-- Take this permission away
EXEC sp_dropsrvrolemember 'BUILTIN\\Administrators', 'sysadmin'
GO


-- Drop Existing TESTIT Database
DROP DATABASE [TESTIT]
GO

-- Create New TESTIT Database
CREATE DATABASE [TESTIT]
GO

The rest of the script just creates the tables and columns for my DB. I can get the DB created by using the Create if DB is absent but I can not get any tables created in it. I have also tried running it with the create database if absent sent to FALSE.