Re: Variables in SQL-script SQL2005

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ø" <brs@xxxxxxxxxxxxxx> wrote in message news:479086ad$0$15886$edfadb0f@xxxxxxxxxxxxxxxxxxxxxxx
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

.

Relevant Pages

  • Re: Variables in SQL-script SQL2005
    ... DECLARE @brugernavn as char ... DECLARE @SqlStatement nvarchar; ... N'CREATE LOGIN ' + ...
    (comp.databases.ms-sqlserver)
  • Re: homogenous syntax for type declarations
    ... > syntax to declare a typed variable is different each time: ... Another difference with specialized lambda lists in DEFMETHOD is ...
    (comp.lang.lisp)
  • Re: Help writing a new filter
    ... if we just told you the correct syntax for the array declaration you require and you didnt bother to learn about c# syntax then every single c# syntax error you ever got would result in a newsgroup post. ... This would be completely tedious for both you and the newsgroup. ... error, bad array declarator. ... To declare a managed array the rank ...
    (microsoft.public.win32.programmer.directx.video)
  • Steve Perkins advice!
    ... the syntax SELECT * FROM [tablename] ... Nothing special comes wen i type it into the password and login ID(I ... dint type exactly the same thing since its the syntax, ... Just invalid password or Login appears, ...
    (alt.2600)
  • Re: How user can change their password with sp_password.
    ... Only members of the sysadmin role can change the password ... > for another user?s login. ... > Name' in the sp_password syntax. ... > SQL Server Developer Support Engineer, MCDBA, MCSE, MCT ...
    (microsoft.public.sqlserver.security)