Problem with FILENAME as @variable

[Michael]

Hi.

I am trying to create a database based on variables from xp_regread.
Somehow I can´t pass @dBDir as FILENAME.

I get the following error-message :
Server: Msg 170, Level 15, State 1, Line 24
Line 24: Incorrect syntax near '@dBDir'.
What am I doing wrong ?
I think the problem is somehow related to a datatype-problem or
it is simply not possible to pass a variable to a "create
database"-statement.

I have checked the xp_regread-output...naturally :) ...and it is correct.
Hope someone can help :)

regards
Michael

----------------------------------------
Source-code is as follows:

USE master
GO

DECLARE @dBDir nvarchar(128)
DECLARE @dBlog nvarchar(128)

EXECUTE xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\School-Project\IRPF
Database\','dBDir',@param = @dBDir OUTPUT

SET @dBDir =@dBDir+'\IRPF.mdf'
SET @dBlog =@dBDir+'\IRPF.ldf'

CREATE DATABASE IRPF
ON PRIMARY
( NAME = IRPF_db,
FILENAME = @dBDir)
LOG ON
( NAME = 'IRPF_log',
FILENAME = @dBlog)
GO

[Erland Sommarskog]

Michael (ag********@cFJERNDETTE.dk) writes:

I get the following error-message :
Server: Msg 170, Level 15, State 1, Line 24
Line 24: Incorrect syntax near '@dBDir'.
What am I doing wrong ?
I think the problem is somehow related to a datatype-problem or
it is simply not possible to pass a variable to a "create
database"-statement.

The latter. You will have to use dynamic SQL. The short story is:

SELECT @sql = 'CREATE DATABASE IRPF ON PRIMARY ( NAME = ''IRPF_db'', ' +
' FILENAME = ' + quotename(@dBDir) + ' LOG ON ( NAME = ' +
''IRPF_log'', FILENAME = ' + quotename(@dBlog) + ')')
EXEC (@sql)

The feature demonstrated here, is one to be used with care. For this
kind of thing, it is the right thing, but there are many cases where
dynamic SQL is not the answer. So there is a full story on
http://www.sommarskog.se/dynamic_sql.html.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Michael]

The latter. You will have to use dynamic SQL. The short story is:

SELECT @sql = 'CREATE DATABASE IRPF ON PRIMARY ( NAME = ''IRPF_db'', ' +
' FILENAME = ' + quotename(@dBDir) + ' LOG ON ( NAME = ' +
''IRPF_log'', FILENAME = ' + quotename(@dBlog) + ')')
EXEC (@sql)

The feature demonstrated here, is one to be used with care. For this
kind of thing, it is the right thing, but there are many cases where
dynamic SQL is not the answer. So there is a full story on
http://www.sommarskog.se/dynamic_sql.html.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Thank you for your reply.

The link seems to be exactly what I was looking for. :)

Regards
Michael.