Sql script - Incorrect syntax error

Question

When I executed the following script, I am getting the following error. What should I do to correct this issue?

Error

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '@path'.

Script

GO
declare @path varchar(2000)
declare @path1 varchar(2000)
select @path=(SELECT [filename] FROM master..sysdatabases WHERE [name] LIKE 'master')
set @path= REPLACE(@path, 'master', 'test')
set @path1= REPLACE(@path, 'test.mdf', 'test_log.ldf')
select @path
select @path1
CREATE DATABASE [test] ON 
( FILENAME = @path ),
( FILENAME =N'D:\Program Files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\test_log.ldf' )
 FOR ATTACH
GO

This attempts to read the location of the master database which does not have to be identical to the configured default data path (it's not on any of my machines, for instance) — marc_s
– marc_s, Commented May 19, 2011 at 7:50

Phil Helmer · Accepted Answer · 2011-05-19 07:41:56Z

3

Replace

CREATE DATABASE [test] ON 
( FILENAME = @path ),
( FILENAME =N'D:\Program Files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\test_log.ldf' )
 FOR ATTACH

with

DECLARE @sql nvarchar(400);
SET @sql = '
CREATE DATABASE [test] ON 
( FILENAME = ' + quotename(@path,'''') + ' ),
( FILENAME =N' + quotename(@path1,'''') + ' )
 FOR ATTACH
'
EXECUTE sp_executesql @sql;

edited May 19, 2011 at 7:41

answered May 19, 2011 at 7:34

Phil Helmer

1,2408 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Maanu Over a year ago

My aim is to copy the mdf and ldf files to the sql server installation folder and then create the database. Is this the right way to do this? Should I make any changes in the script?

Phil Helmer Over a year ago

I would use the paths referenced in the article @marc_s linked to. Is this for a more "product-style" deployment where you're distributing DBs without knowing the server info ahead of time or is this for a more "internal-style" deployment where you need 1 process to deploy to dev/QA/UAT/prod? Generally speaking the path to the master DB won't have any bogus (injected) values in it, but it's always a good idea to check values before concatenating & executing.

marc_s · Accepted Answer · 2011-05-19 07:49:56Z

1

You're not really reading the configured default data directory - you're just reading the directory where the master database is located (and that doesn't have to be the configured default directory for data files).

If you really want to know the real directory - you need to peek into the registry. See this blog post here at SQL Server Central for all the gory details...

answered May 19, 2011 at 7:49

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Collectives™ on Stack Overflow

Sql script - Incorrect syntax error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related