Using variables when creating a database

[katehol]

I am trying to create a database using variables, so that the variables can be passed in when the script is run. But when I run the code below I keep on getting an error - any clues what I have done wrong? I’ve not used variables before, so sorry if it is obvious.

(NB The script was creating using the auto-create functions within SQL server)

DECLARE @database	varchar(30)	

SET @database	= 'TESTdb'


USE [master]
GO

CREATE DATABASE [@database] ON  PRIMARY 
( NAME = N''+@database+'_dat', FILENAME = N'D:\Data\'+@database+'.mdf' , SIZE = 1932672KB , MAXSIZE = UNLIMITED, FILEGROWTH = 20%)
LOG ON 
( NAME = N''+@database+'_log', FILENAME = N'L:\LOGS\'+@database+'_log.LDF' , SIZE = 8653056KB , MAXSIZE = UNLIMITED, FILEGROWTH = 20%)
 COLLATE Latin1_General_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname=N@database, @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [@database].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [@database] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [@database] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [@database] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [@database] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [@database] SET ARITHABORT OFF 
GO
ALTER DATABASE [@database] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [@database] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [@database] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [@database] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [@database] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [@database] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [@database] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [@database] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [@database] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [@database] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [@database] SET  DISABLE_BROKER 
GO
ALTER DATABASE [@database] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [@database] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [@database] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [@database] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [@database] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [@database] SET  READ_WRITE 
GO
ALTER DATABASE [@database] SET RECOVERY FULL 
GO
ALTER DATABASE [@database] SET  MULTI_USER 
GO
ALTER DATABASE [@database] SET PAGE_VERIFY TORN_PAGE_DETECTION  
GO
ALTER DATABASE [@database] SET DB_CHAINING OFF

 

[mrdenny]

CREATE DATABASE and ALTER DATABASE don't accept variables for the database name.

Unfornitually you have to use Dynamic SQL to do this.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katehol]

Thanks for that. Is it still possible to save the database to a specific location? I have found that I need to use

Exec('Create Database ' + @database)

But I can't find how to save it to my chosen drives.
Thanks

 

[mrdenny]

By specifing the location of the physical files just like before.

declare @cmd varchar(8000)
set @cmd = 'CREATE DATABASE [@database] ON  PRIMARY 
( NAME = N'''+@database+'_dat'', FILENAME = N''D:\Data\'+@database+'.mdf'' , SIZE = 1932672KB , MAXSIZE = UNLIMITED, FILEGROWTH = 20%)
LOG ON 
( NAME = N'''+@database+'_log'', FILENAME = N''L:\LOGS\'+@database+'_log.LDF'' , SIZE = 8653056KB , MAXSIZE = UNLIMITED, FILEGROWTH = 20%)
 COLLATE Latin1_General_CI_AS'
exec (@cmd)

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katehol]

Thanks for that. Much appreciated.

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katehol]

One more question - sorry...
I also need to run a 'USE' statement using the variable. I have tried to write it in the same format as you did previously and tried the following, but it it comes up with an error saying it can't find the database named '@database'

DECLARE @database varchar(30)
DECLARE @USE varchar(100)
SET @database = 'CONNKateTEST'
SET @USE = 'USE [@database]'
EXEC(@USE)

Thanks

 

[katehol]

I have also tried

DECLARE @database varchar(30)
DECLARE @USE varchar(100)
SET @database = 'CONNKateTEST'
SET @USE = 'USE ['+@database+']'
EXEC(@USE)

This runs with no errors, but it does not change the database to the one I want...

 

[sunila7]

Execute (exec) statment cannot be used to change the database.

From SQL Books Online

While the context switch to the database user is active, any attempt to access resources outside the database will cause the statement to fail. This includes USE database statements, distributed queries, and queries that reference another database by using three- or four-part identifiers. To extend the scope of the context switch outside the current database, see Extending Database Impersonation by Using EXECUTE AS.

Sunil

 

[sunila7]

Here is the link on msdn

http://msdn2.microsoft.com/en-us/library/ms188332.aspx

Sunil

 

[katehol]

Thanks for that - I will take a look

 

[katehol]

Me again... Have got the database creation working fine - thanks. Have also got tables being added using variables.

However, I am now stuck on creating views using the variables. I keep on getting the 'CREATE VIEW' must be the first statement in a query batch error. I understand that views need to be the first statement, but I have a lot of views that need to reference specific variables - is there any way to do this?

The code I am using is as follows:

DECLARE @view		varchar(MAX)
DECLARE @database	varchar(30)	
SET @database	= 'KateTEST3'

--Insert views
SET @view = 'USE [KateTEST3]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

CREATE view [dbo].[userssupplier]
as
SELECT *
FROM         dbo.Users
WHERE     (User_Type = 4.0) OR
                      (User_Type = 5.2)'



CREATE view [dbo].[usersclient]
AS
SELECT *
FROM         dbo.Users
WHERE     (User_Type = 4.1) OR
                     (User_Type = 5.0)
GO

EXEC(@view)

Even if I just run the script with the first view, rather than both of them, I still get the same error.
Any help would be greatly appreciated.

Thanks

 

[katehol]

Have noticed an error in my code above (this error is not in my code - I just copied the wrong bit!). Instead of it saying SET @view = 'USE [KateTEST3] it actually says SET @view = 'USE ['+@database+'].


I have tried the code (as seen below) without the variables, and it works fine, but of course, if I leave the GO statements in, it causes an error.

USE [KateTEST3]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE view [dbo].[userssupplier]
as
SELECT *
FROM         dbo.Users
WHERE     (User_Type = 4.0) OR
                      (User_Type = 5.2)
GO