473,320 Members | 2,189 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Problem with FILENAME as @variable

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
Jul 20 '05 #1
2 8034
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
Jul 20 '05 #2
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.


Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Steve Dondley | last post by:
Take the following variable: $prefix = 'start'; And assume there is an object that has a property called 'start_time'. How can I access that property in the object using the $prefix variable?...
1
by: Philippe Rousselot | last post by:
Hi, On a page, I have a form with a variable number of fields. to print these fields, I have such a code : while ($i !=0){ echo '<td> <input type="text" name="component'.$i.'" size="20"...
3
by: ziemon | last post by:
// index.html <a href="test.html?menu=menu1">menu1</a> <a href="test.html?menu=menu1_1">menu1_1</a> // index.html // test.html
5
by: cpp | last post by:
When I create an instance of ofstream, what is the name of the member variable that holds the filename? For example: ofstream ofs("Output.txt"); cout << ofs.WhatIsThePathVariable; If there...
0
by: Przemys³aw Bana¶ | last post by:
Hello! Can anyone help me with calling Novell function from dll? I think my main problem is in translating C variable types to C# types. Here is the code: using System; using...
0
by: manoj339 | last post by:
I have varchar variable declared .When I print this var it shows me correct value before a C function. Inside a c function this variable value is getting truncated . The c function does not pass...
8
by: sheriff | last post by:
Hi friends, I am opening and reading a file and writing it in a new file. int main() { ifstream inputFile("C:\\TEMP\\test.txt"); ofstream outputFile; ...
0
by: Tim Rowe | last post by:
2008/9/24 <dudeja.rajat@gmail.com>: I'm surprised it runs at all -- as far as I can see "mod" in "mod.update(a)" and "print mod.a" is not defined. Did you mean "mod1"? If I change it to that,...
2
by: sunnypirate | last post by:
The following code treats abcd as a variable and gives the error variable undefined in firebug return $data = "<a href='#' onclick='ajaxcall(abcd);'>".$name."</a>"; The following code gives the...
1
by: sentenza | last post by:
Hello everyone When the trigger executes the function which I copy below: IF (TG_OP = 'UPDATE') THEN EXECUTE 'CREATE TABLE '||NEW.nome_tabella||' (ordinativo serial PRIMARY KEY CHECK...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.