CODEWITHSUNDEEP
sql-serversql-server-2005
Maanu
Maanu

Reputation: 5203

Sql script - Incorrect syntax error

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

Upvotes: 0

Views: 2230

Answers (2)

marc_s
marc_s

Reputation: 754668

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...

Upvotes: 1

Phil Helmer
Phil Helmer

Reputation: 1240

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;

Upvotes: 3

Related Questions