January 13, 2004 at 6:00 pm
Hi,
I am trying to run the following script in Query Analyzer, and cannot get it to work.
The reason why i am trying to pass a string parameter here is because the application that i am building, allows the user to select which drive the installation of a Database can go on, so i need to pass the file path to the query.
The ON Function does not seem to like '',comma's in it, is there are way for this to work?
****Script*****
Declare
@dirMDF varchar(100),
@dirLDF varchar(100)
set @dirMDF = 'd:\mssql\mssql\data\VMConfig_Data.MDF'
set @dirLDF = 'd:\mssql\mssql\data\VMConfig_Log.LDF'
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = 'VMConfig')
DROP DATABASE [VMConfig]
CREATE DATABASE VMConfig
ON
( NAME = VMConfig,FILENAME = @dirMDF )
select 0 as Result_Code
*** Error ***
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near '@dirMDF'.
January 13, 2004 at 6:54 pm
You will need to use dynamic sql for your create statement.
DECLARE @sql nvarchar(4000)
set @sql = 'CREATE DATABASE FOOBAR2.... FILENAME= ' + @dirMDF
exec sp_executesql @sql
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply