Attach database (filename contains variable)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Attach database (filename contains variable)

Author

Topic

dhjackal
Starting Member

42 Posts

Posted - 2009-02-26 : 12:39:26

Hello all,

Hoping someone can help. I'm trying to attach a database with the following


DECLARE @Today VARCHAR(8)

SET @Today = replace(convert(char(10),getdate(),120,'-','')

CREATE DATABASE newdatabase ON
(filename = 'C:\Data\newdatabase_' + @Today + '.mdf'),
(filename = 'C:\Log\newdatabase_' + @Today + '.ldf'),
FOR ATTACH

But I keep getting the error

Msg 102, level 15, state 1, line 18
Incorrect syntax near '+'.

I've tried several combinations of double and single quotes but to no avail :o(

Any help appreciated

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-26 : 12:46:06

You need to use dynamic SQL for this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dhjackal
Starting Member

42 Posts

Posted - 2009-02-26 : 13:05:00

I tried the following dynamically but couldn't get it to work either


DECLARE @Today VARCHAR(8)
DECLARE @Sql VARCHAR(4000)

SET @Today = replace(convert(char(10),getdate(),120,'-','')

SET @Sql = 'CREATE DATABASE newdatabase ON
(filename = ''C:\Data\newdatabase_' + @Today + '.mdf''),
(filename = ''C:\Log\newdatabase_' + @Today + '.ldf'')
FOR ATTACH'

But the error I get now is

Msg 2812, Level 16, State 62, Line 21
Could not find stored procedure 'CREATE DATABASE newdatabase ON
(filename = N'C:\Data\newdatabase_20090226.mdf'),
(filename = N'C:\Log\newdatabase_20090226.ldf')
FOR ATTACH'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-26 : 13:28:49

Show us PRINT @Sql and show us your EXEC command.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dhjackal
Starting Member

42 Posts

Posted - 2009-02-27 : 03:33:57

Apologies tkizer i should really have added these earlier.

The revised block is.....


DECLARE @Today VARCHAR(8)
DECLARE @Sql VARCHAR(4000)

SET @Today = replace(convert(char(10),getdate(),120,'-','')

SET @Sql = 'CREATE DATABASE newdatabase ON
(filename = ''C:\Data\newdatabase_' + @Today + '.mdf''),
(filename = ''C:\Log\newdatabase_' + @Today + '.ldf'')
FOR ATTACH'
PRINT @Sql
EXEC @Sql

The output from PRINT @Sql is ;


CREATE DATABASE newdatabase ON 
( FILENAME = N'C:\Data\newdatabase_20090227.mdf'),
( FILENAME = N'C:\Log\newdatabase_20090227.ldf')
FOR ATTACH

Which if i copy, paste and execute works fine!

Any ideas?

dhjackal
Starting Member

42 Posts

Posted - 2009-02-27 : 05:04:10

FIXED IT :o)

I was forgetting to put brackets around the @Sql :o)

The corrected, working code is

DECLARE @Today VARCHAR(8)
DECLARE @Sql VARCHAR(4000)

SET @Today = replace(convert(char(10),getdate(),120,'-','')

SET @Sql = 'CREATE DATABASE newdatabase ON
(filename = ''C:\Data\newdatabase_' + @Today + '.mdf''),
(filename = ''C:\Log\newdatabase_' + @Today + '.ldf'')
FOR ATTACH'
PRINT @Sql
EXEC (@Sql)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-27 : 12:13:55

Glad you got it working.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources