Hi,
I seem to have missed something here...
Made a script for creating users on a SQL2005 - and it works just fine.
To make things easier I would like to add a couple of variables to get
around typing e.g. the username 3 times.
My script looks like this:
USE [master]
GO
DECLARE @brugernavn as char(50)
DECLARE @password as char(50)
set @brugernavn = 'testuser'
set @password = N'testpassword'
select @brugernavn
select @password
CREATE LOGIN @brugernavn WITH PASSWORD= @password,
DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=ON, CHECK_POLICY=ON
USE [testbase]
CREATE USER @brugernavn FOR LOGIN @brugernavn
GO
The 2 select-statemens is for verifying the contents of the variables.
The script fails at the line 'CREATE LOGIN...' with the following messages:
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '@brugernavn'.
Msg 319, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'with'. If this statement is a common
table expression or an xmlnamespaces clause, the previous statement must
be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near '@brugernavn'.
How do I get the script to do what I want it to ?
Best regards,
Brian 1 7395
How do I get the script to do what I want it to ?
Not all DCL commands accept variables (see BOL syntax) so you'll need to use
dynamic SQL. For example:
DECLARE @SqlStatement nvarchar(MAX);
SET @SqlStatement =
N'CREATE LOGIN ' +
QUOTENAME(@brugernavn) +
N' WITH PASSWORD = ' +
QUOTENAME(@password, '''');
EXECUTE (@SqlStatement);
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Brian Sprogø" <br*@mailme.invalidwrote in message
news:47***********************@dtext01.news.tele.d k...
Hi,
I seem to have missed something here...
Made a script for creating users on a SQL2005 - and it works just fine.
To make things easier I would like to add a couple of variables to get
around typing e.g. the username 3 times.
My script looks like this:
USE [master]
GO
DECLARE @brugernavn as char(50)
DECLARE @password as char(50)
set @brugernavn = 'testuser'
set @password = N'testpassword'
select @brugernavn
select @password
CREATE LOGIN @brugernavn WITH PASSWORD= @password,
DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=ON, CHECK_POLICY=ON
USE [testbase]
CREATE USER @brugernavn FOR LOGIN @brugernavn
GO
The 2 select-statemens is for verifying the contents of the variables.
The script fails at the line 'CREATE LOGIN...' with the following
messages:
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '@brugernavn'.
Msg 319, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'with'. If this statement is a common
table expression or an xmlnamespaces clause, the previous statement must
be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near '@brugernavn'.
How do I get the script to do what I want it to ?
Best regards,
Brian
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Rahul Chatterjee |
last post by:
Hello All
I have an asp page in which I am performing the following
1. Querying a database view
2. Returning rows in to a recordset.
3. Looping thru the recordset and printing the data
4....
|
by: Daniel Roy |
last post by:
Hi gurus,
I just started to look at a very slow-running SQL statement
generated by an application (Siebel). I spooled the SQL from the
application, replaced the bind variables by their values, and...
|
by: TinTin |
last post by:
Hello,
How do I concatinate a variable. Here's the scenarios:
declare @var1 varchar(20)
declare @var2 varchar(20)
declare @var3 varchar(20)
declare @var4 varchar(20)
..
..
|
by: Sai Hertz And Control Systems |
last post by:
Dear all,
Their was a huge rore about MySQL recently for something in java functions
now theirs one more
http://www.mysql.com/doc/en/News-5.0.x.html
Does this concern anyone.
What I...
|
by: Ina Schmitz |
last post by:
Hi all,
could anyone please tell me how to declare and use variables in DB2 UDB 8.2?
I did it in SQL Server and Oracle without any problems:
DECLARE @my_var FLOAT
SET @my_var = -0.33;
SELECT...
|
by: Harry Simpson |
last post by:
I've come from the old ASP camp where session variables were not used. When
i started using ASP.NET in 2001, I started using them again because it was
ok from what I'd read.
I've been merrily...
|
by: Mark A |
last post by:
DB2 8.2 for Linux, FP 10 (also performs the same on DB2 8.2 for Windoes, FP
11).
Using the SAMPLE database, tables EMP and EMLOYEE.
In the followng stored procedure, 2 NULL columns (COMM) are...
|
by: Ed |
last post by:
I have some questions in regards to Session variables and IIS Recycling:
1. Does the IIS setting 'Shutdown worker process after being idle' affect
an
application's session variables?
Or is IIS...
|
by: Frank Swarbrick |
last post by:
A few weeks ago I had posed a question about how one my create a cursor for
a query where the predicate condition is dynamic. Meaning that the query
might want to have one of several possible...
|
by: misha |
last post by:
Hello.
I was wandering if someone could explain to me (or point to some
manual) the process of mapping the addresses of host variables by DB2.
Especially I would like to know when DB2 decides to...
|
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...
|
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...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
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...
| |