CODEWITHSUNDEEP
sqlsql-server-2008t-sql
HuwD
HuwD

Reputation: 1800

Error creating database from query

I am trying to create a database from a SQL query I've been sent. Should point out SQL is not my strong point.

The following is declared at the start of the query:

:setvar DatabaseName "Database"
:setvar DefaultDataPath ""
:setvar DefaultLogPath ""

I have run the query in command mode and am getting the following error:

Creating Database...
Msg 5105, Level 16, State 2, Line 1
A file activation error occurred. The physical file name 'DB.mdf' may be incorrect. Diagnose and correct additional errors, and retry the operation.

Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
An error was encountered during execution of batch. Exiting.

Any ideas?

Thanks

Can't post full query as is very long and contains sensitive info but is cut down version up to the point that seems to cause the error.

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
GO

:setvar DatabaseName "Database"
:setvar DefaultDataPath ""
:setvar DefaultLogPath ""
GO

:on error exit
GO

USE [master]
GO

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL
AND DATABASEPROPERTYEX(N'$(DatabaseName)','Status') <> N'ONLINE')
BEGIN
RAISERROR(N'The state of the target database, %s, is not set to ONLINE. To deploy to this database, its state must be set to ONLINE.', 16, 127,N'$(DatabaseName)') WITH NOWAIT
RETURN
END
GO

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [$(DatabaseName)];
END
GO

PRINT N'Creating $(DatabaseName)...'
GO

CREATE DATABASE [$(DatabaseName)]
ON 
PRIMARY(NAME = [DB], FILENAME = '$(DefaultDataPath)DB.mdf', SIZE = 3072 KB, FILEGROWTH = 1024 KB)
LOG ON (NAME = [DB_log], FILENAME = '$(DefaultLogPath)DB_log.ldf', SIZE = 1024 KB, MAXSIZE = 2097152 MB, FILEGROWTH = 10 %) COLLATE SQL_Latin1_General_CP1_CI_AS
GO

EXECUTE sp_dbcmptlevel [$(DatabaseName)], 100;

Upvotes: 0

Views: 1327

Answers (1)

Vikram
Vikram

Reputation: 8333

put some physical path in the following line:

:setvar DefaultDataPath ""

like;

:setvar DefaultDataPath "D:\Database"

Upvotes: 2

Related Questions