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.
| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2004-07-30 : 14:28:04
|
| Instead of CREATE DATABASE [MyDB] ON (NAME = N'MyDB_Data', FILENAME = N'C:\MyDB.mdf' ) LOG ON (NAME = N'MyDB_Log', FILENAME = N'C:\MyDB.LDF' )I want to write something like:DECLARE @Path VarChar(100)SET @Path = 'C:\'CREATE DATABASE [MyDB] ON (NAME = N'MyDB_Data', FILENAME = @Path+'MyDB.mdf' ) LOG ON (NAME = N'MyDB_Log', FILENAME = @Path+'MyDB.LDF' )But I get error when compile in QA. How can I use variables for FILENAME in CREATE DATABASE?Canada DBA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-30 : 14:42:56
|
| You'll need to use dynamic sql. Save the command string to a variable, then EXEC (@VariableName).Tara |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-30 : 14:55:23
|
| Tara...why not hand him a loaded gun while you're at it....How many databases do you need to create?And who is supplying the path?Do you want to maybe check that the path actually exists first?Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-30 : 15:04:39
|
| But the difference is that this is for administration purposes. Dynamic sql is bad for performance and security. Don't have those problems here. You just need to be careful about the string that you are building. Make sure to PRINT out @SQL to make sure it is built correctly before executing it.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-30 : 15:17:30
|
quote:
Originally posted by tduggan
But the difference is that this is for administration purposes.
Bet a 'rita on that?Brett8-) |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-07-31 : 01:36:19
|
Also, you might want to check if the storage size is relative to your database size need.quote:
Originally posted by X002548
Tara...why not hand him a loaded gun while you're at it....How many databases do you need to create?And who is supplying the path?Do you want to maybe check that the path actually exists first?Brett8-)
|
|
|
|
|
|
|
Use variable for FILENAME