Attach DataBase Syntax Error

Question

I am trying to attach a DB and can't figure out the syntax.

DECLARE @path1 varchar(250)=N'D:\MSSQL\DATA\ProductDB_Temp.mdf';
DECLARE @path2 varchar(250)=N'D:\MSSQL\DATA\ProductDB_Temp_log.ldf';

/* Attach Database */
CREATE DATABASE ProductDB_Temp ON 
( FILENAME = @path1 ) ,
( FILENAME = @path2 )
FOR ATTACH

Error message:

Incorrect syntax near '@path1'. Expecting ID,Integer,Quoted_Id,string or text_lex

if I construct my script like this:

DECLARE @path1 varchar(250)=N'D:\MSSQL\DATA\ProductDB_Temp.mdf';
DECLARE @path2 varchar(250)=N'D:\MSSQL\DATA\ProductDB_Temp_log.ldf';

/* Attach Database */
CREATE DATABASE ProductDB_Temp ON 
( FILENAME = N'D:\MSSQL\DATA\ProductDB_Temp.mdf' ) ,
( FILENAME = N'D:\MSSQL\DATA\ProductDB_Temp_log.ldf' )
FOR ATTACH

it works but this is not really what I would like to do.

I am trying to avoid using EXEC. any suggestions?

Why are you trying to avoid dynamic sql? This is easily accomplished using dynamic sql. — Jason B.
– Jason B., Commented Jul 28, 2016 at 15:36

Community · Accepted Answer · 2017-05-23 11:53:43Z

0

I don't think you will be able to create the database this way.

You will need dynamic SQL for that. Take a look at this answer.

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Jul 28, 2016 at 14:54

marcelovca90

2,8223 gold badges30 silver badges35 bronze badges

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

Comments

TheGameiswar · Accepted Answer · 2016-07-28 15:29:12Z

0

Use dynamic SQL like below..

DECLARE @path1 varchar(250)=N'D:\MSSQL\DATA\ProductDB_Temp.mdf';
DECLARE @path2 varchar(250)=N'D:\MSSQL\DATA\ProductDB_Temp_log.ldf';

/* Attach Database */


declare @sql nvarchar(max)
SET @Sql = 'CREATE DATABASE newdatabase ON
(filename = ' +@path1+'),
(filename = '+@path2+')
FOR ATTACH'

print @sql

check output ,in this case :

CREATE DATABASE newdatabase ON
(filename = D:\MSSQL\DATA\ProductDB_Temp.mdf),
(filename = D:\MSSQL\DATA\ProductDB_Temp_log.ldf)
FOR ATTACH

finally

EXEC(@SQL)

answered Jul 28, 2016 at 15:29

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

Collectives™ on Stack Overflow

Attach DataBase Syntax Error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest