I am trying to create a database based on variables from xp_regread.
Somehow I cant 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)
GOMichael (agentmikie@.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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> 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, esquel@.sommarskog.se
Thank you for your reply.
The link seems to be exactly what I was looking for. :)
Regards
Michael.
