0

I'm trying to create a database using the default paths on several SQL Servers at once. To achieve this I'm trying to use a script that should create the database on each server, however I've run into some difficulties trying to use a variable for the file paths.

Declare @logpath nvarchar(256),
        @datapath nvarchar(256),
        @filenameLog nvarchar(256),
        @filenamedata nvarchar(256);

SET @logpath = (select 
                   LEFT(physical_name, LEN(physical_name) - CHARINDEX('\', REVERSE(physical_name)) + 1) 
                from sys.master_files 
                where name = 'modeldev')

SET @datapath = (select 
                    LEFT(physical_name, LEN(physical_name)  - CHARINDEX('\', REVERSE(physical_name)) + 1) 
                 from sys.master_files 
                 where name = 'modellog')

set @filenamelog = @logpath + 'test.ldf'
set @filenamedata = @datapath + 'test.mdf'

CREATE DATABASE [test]
 CONTAINMENT = NONE
 ON  PRIMARY 
  ( NAME = test, FILENAME = @filenamedata , SIZE = 51200KB , FILEGROWTH = 10240KB )
 LOG ON 
  ( NAME = test_log, FILENAME = @filenamelog , SIZE = 5120KB , FILEGROWTH = 5120KB )
GO

This gives:

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near '@filenamedata'.

What am I missing? I feel like it's something obvious staring right at me but I can't seem to find it.

My source for this script was an msdn article here!

0

1 Answer 1

2

Try using dynamic sql:

SET @createDatabase = 'CREATE DATABASE [test] ON PRIMARY CONTAINMENT = NONE
( NAME = N''test'', FILENAME = ''' + @filenamedata + ''', SIZE = 51200KB , FILEGROWTH = 10240KB
)
LOG ON
( NAME = N''test_log'', FILENAME = ''' + @filenamelog + ''' , SIZE = 5120KB , FILEGROWTH = 5120KB
)'

EXEC sp_executesql @createDatabase

Another member had a similar problem a while back. https://stackoverflow.com/questions/5202257/create-a-database-with-dynamic-database-name-in-sql-server-2005

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.