Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
Instead of SET @logpath = (select ..., I'd recommend to just use SELECT @logpath = .... –  marc_s Jun 20 at 15:21

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer
    
Edit - You since fixed the errors I noticed in your response, so I guess the feedback is no longer relevant. Thank you for your answer and the referenced question, I had not come accross it while googling my issue (Probably missing the keyword 'dynamic') –  Reaces Jun 20 at 13:17
    
your welcome -) –  g2server Jun 20 at 13:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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