Using local VAR as FILENAME in CREATE DB Statement

[Jabba]

I am ripping my hair out here trying to do something on a SQL Server 2000 box using TSQL that is a breeze in SQL 2005 but I can't make it work.

I am trying to do this ... VERSION 1

SET @DatabasePath = N'D:\DATA\Maintenance\'
SET @LOGBackupPath = N'E:\LOGS\Maintenance\'
SET @Retention = 1

/* modify NOTHING below this line !!!!! */

SET @TableName = @DatabasePath + N'Maintenance.mdf'
SET @LogFileName = @DatabasePath + N'Maintenance_log.ldf'

CREATE DATABASE [Maintenance] ON (NAME = N'Maintenance', FILENAME = @TableName, SIZE = 2, FILEGROWTH = 10%)
LOG ON (NAME = N'Maintenance_log', FILENAME = @LogFileName , SIZE = 1, FILEGROWTH = 10%)
COLLATE Latin1_General_CI_AS
GO

instead of this ... VERSION 2


CREATE DATABASE [Maintenance] ON (NAME = N'Maintenance', FILENAME = 'D:\DATA\Maintenance\Maintenance.mdf' , SIZE = 2, FILEGROWTH = 10%)
LOG ON (NAME = N'Maintenance_log', FILENAME = 'E:\LOGS\Maintenance\Maintenance_log.ldf' , SIZE = 1, FILEGROWTH = 10%)
COLLATE Latin1_General_CI_AS
GO

Version 2 works, version 1 will not compile '

it gives error

Line 25: Incorrect syntax near '@TableName'.

In Sql server 2005 I'd just use $(drive) $(Directory) etc ... after setting the values of course with setvar:

I need to be able to distribute this to customers for building backup jobs that most of them seem totally unable to do well .... since each customer is configured slightly differently I want them to change the paths and drives etc at the top of the script.

How can I use local variables such as @TableName as the FILENAME part of the CREATE DATABASE Statement ????

(The database is needed as an audit for the backup process created later as most of my customers are pharmacueticals)

[almaz]

I am ripping my hair out here trying to do something on a SQL Server 2000 box using TSQL that is a breeze in SQL 2005 but I can't make it work...

Try this:

SET @DatabasePath = N'D:\DATA\Maintenance\'
SET @LOGBackupPath = N'E:\LOGS\Maintenance\'
SET @Retention = 1
 
/* modify NOTHING below this line !!!!! */
 
SET @TableName = @DatabasePath + N'Maintenance.mdf'
SET @LogFileName = @DatabasePath + N'Maintenance_log.ldf'
 
exec('
CREATE DATABASE [Maintenance] ON (NAME = N''Maintenance'', FILENAME = ''' + @TableName + ''', SIZE = 2, FILEGROWTH = 10%)
LOG ON (NAME = N''Maintenance_log'', FILENAME = ''' + @LogFileName + ''', SIZE = 1, FILEGROWTH = 10%)
COLLATE Latin1_General_CI_AS')
GO

[nisha081]

if i use ''' + $<varname> + ''' for filename as in the example above, i get the following error:

Msg 5105, Level 16, State 2, Line 35
A file activation error occurred. The physical file name '' +@mdf_path+'' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Msg 1802, Level 16, State 1, Line 35
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

How can i resolve this? some other place i found i should use N'<fname>' but in this case how do i add N? my variables are declared as nvarchar

Thanks,
Nisha