Adding users from cursor

Hi. Perhaps I am missing something obvious, but I am confused. I can
add a SQL Server 2005 user with this command:
CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
However, I have 100 users to add, and I want to use TRANSACT-SQL to
create the users automatically and pull their initial password info
from a table. I use a cursor to do this, replacing the 'AUser' and
'hello' with variables, for example:
CREATE LOGIN @.usr WITH PASSWORD = @.pwd
I get an Incorrect syntax near '@.usr' error. Somehow this is not the
correct datatype, I would imagine. I used this method with sp_addlogin
in SQL 2000 without a problem.
I've tried declaring @.usr as a varchar or nvarchar without success.
Please offer any suggestions.
Thank you.
Excellent idea. I was hung up on trying to get it to accept the
variable.
Tibor Karaszi wrote:
> Seems CREATE LOGIN doesn't accept a variable for one of both those parameters. You can build the
> CREATE LOGIN command in a variable and then use dynamic SQL to execute it:
> EXEC(@.sql)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>

Adding users from cursor

Hi. Perhaps I am missing something obvious, but I am confused. I can
add a SQL Server 2005 user with this command:
CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
However, I have 100 users to add, and I want to use TRANSACT-SQL to
create the users automatically and pull their initial password info
from a table. I use a cursor to do this, replacing the 'AUser' and
'hello' with variables, for example:
CREATE LOGIN @.usr WITH PASSWORD = @.pwd
I get an Incorrect syntax near '@.usr' error. Somehow this is not the
correct datatype, I would imagine. I used this method with sp_addlogin
in SQL 2000 without a problem.
I've tried declaring @.usr as a varchar or nvarchar without success.
Please offer any suggestions.
Thank you.Seems CREATE LOGIN doesn't accept a variable for one of both those parameters. You can build the
CREATE LOGIN command in a variable and then use dynamic SQL to execute it:
EXEC(@.sql)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jim Johnson" <easytorememberemailaddress@.gmail.com> wrote in message
news:1166460727.281101.297550@.79g2000cws.googlegroups.com...
> Hi. Perhaps I am missing something obvious, but I am confused. I can
> add a SQL Server 2005 user with this command:
> CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
> However, I have 100 users to add, and I want to use TRANSACT-SQL to
> create the users automatically and pull their initial password info
> from a table. I use a cursor to do this, replacing the 'AUser' and
> 'hello' with variables, for example:
> CREATE LOGIN @.usr WITH PASSWORD = @.pwd
> I get an Incorrect syntax near '@.usr' error. Somehow this is not the
> correct datatype, I would imagine. I used this method with sp_addlogin
> in SQL 2000 without a problem.
> I've tried declaring @.usr as a varchar or nvarchar without success.
> Please offer any suggestions.
> Thank you.
>|||Excellent idea. I was hung up on trying to get it to accept the
variable.
Tibor Karaszi wrote:
> Seems CREATE LOGIN doesn't accept a variable for one of both those parameters. You can build the
> CREATE LOGIN command in a variable and then use dynamic SQL to execute it:
> EXEC(@.sql)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>|||This is a multi-part message in MIME format.
--=_NextPart_000_029F_01C72286.20FC34B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You need to use sp_executesql (Dynamic SQL) to accomplish this task. =Something like this:
DECLARE
@.User nvarchar(20),
@.pwd nvarchar(20),
@.Sql nvarchar(200)
SELECT @.User =3D 'Mary', @.pwd =3D 'test'
SET @.Sql =3D 'CREATE LOGIN ''' + @.User + ''' WITH PASSWORD =3D ''' + =@.pwd + ''''
EXECUTE sp_executesql @.Sql
See Erland's excellent article about using Dynamic SQL.
Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html=20
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill without getting a little closer to =the top yourself.
- H. Norman Schwarzkopf
"Jim Johnson" <easytorememberemailaddress@.gmail.com> wrote in message =news:1166460727.281101.297550@.79g2000cws.googlegroups.com...
> Hi. Perhaps I am missing something obvious, but I am confused. I can
> add a SQL Server 2005 user with this command:
> > CREATE LOGIN 'AUser' WITH PASSWORD =3D 'hello', DEFAULT_DATABASE =3D =TEST1
> > However, I have 100 users to add, and I want to use TRANSACT-SQL to
> create the users automatically and pull their initial password info
> from a table. I use a cursor to do this, replacing the 'AUser' and
> 'hello' with variables, for example:
> > CREATE LOGIN @.usr WITH PASSWORD =3D @.pwd
> > I get an Incorrect syntax near '@.usr' error. Somehow this is not the
> correct datatype, I would imagine. I used this method with =sp_addlogin
> in SQL 2000 without a problem.
> > I've tried declaring @.usr as a varchar or nvarchar without success.
> > Please offer any suggestions.
> > Thank you.
>
--=_NextPart_000_029F_01C72286.20FC34B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You need to use sp_executesql (Dynamic =SQL) to accomplish this task. Something like this:
DECLARE @.User nvarchar(20), @.pwd nvarchar(20), @.Sql nvarchar(200)
SELECT @.User ==3D 'Mary', @.pwd =3D 'test'
SET @.Sql =3D 'CREATE LOGIN ''' =+ @.User + ''' WITH PASSWORD =3D ''' + @.pwd + ''''
EXECUTE sp_executesql =@.Sql
See Erland's excellent article about =using Dynamic SQL.
Dynamic SQL - The Curse =and Blessings of Dynamic SQLhttp://www.sommarskog.se/dynamic_sql.html">http://www.sommarskog.=se/dynamic_sql.html -- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
"Jim Johnson" wrote in message news:1166460727.281101.297550@.79g2000cws.googlegroups.com=...> =Hi. Perhaps I am missing something obvious, but I am confused. I can> =add a SQL Server 2005 user with this command:> > CREATE LOGIN ='AUser' WITH PASSWORD =3D 'hello', DEFAULT_DATABASE =3D TEST1> > =However, I have 100 users to add, and I want to use TRANSACT-SQL to> create the =users automatically and pull their initial password info> from a =table. I use a cursor to do this, replacing the 'AUser' and> 'hello' with variables, for example:> > CREATE LOGIN @.usr WITH PASSWORD ==3D @.pwd> > I get an Incorrect syntax near '@.usr' error. Somehow this is not the> correct datatype, =I would imagine. I used this method with sp_addlogin> in SQL 2000 =without a problem.> > I've tried declaring @.usr as a varchar or =nvarchar without success.> > Please offer any suggestions.> => Thank you.>

---

--=_NextPart_000_029F_01C72286.20FC34B0--

Adding users from cursor

Hi. Perhaps I am missing something obvious, but I am confused. I can
add a SQL Server 2005 user with this command:
CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
However, I have 100 users to add, and I want to use TRANSACT-SQL to
create the users automatically and pull their initial password info
from a table. I use a cursor to do this, replacing the 'AUser' and
'hello' with variables, for example:
CREATE LOGIN @.usr WITH PASSWORD = @.pwd
I get an Incorrect syntax near '@.usr' error. Somehow this is not the
correct datatype, I would imagine. I used this method with sp_addlogin
in SQL 2000 without a problem.
I've tried declaring @.usr as a varchar or nvarchar without success.
Please offer any suggestions.
Thank you.Seems CREATE LOGIN doesn't accept a variable for one of both those parameter
s. You can build the
CREATE LOGIN command in a variable and then use dynamic SQL to execute it:
EXEC(@.sql)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jim Johnson" <easytorememberemailaddress@.gmail.com> wrote in message
news:1166460727.281101.297550@.79g2000cws.googlegroups.com...
> Hi. Perhaps I am missing something obvious, but I am confused. I can
> add a SQL Server 2005 user with this command:
> CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
> However, I have 100 users to add, and I want to use TRANSACT-SQL to
> create the users automatically and pull their initial password info
> from a table. I use a cursor to do this, replacing the 'AUser' and
> 'hello' with variables, for example:
> CREATE LOGIN @.usr WITH PASSWORD = @.pwd
> I get an Incorrect syntax near '@.usr' error. Somehow this is not the
> correct datatype, I would imagine. I used this method with sp_addlogin
> in SQL 2000 without a problem.
> I've tried declaring @.usr as a varchar or nvarchar without success.
> Please offer any suggestions.
> Thank you.
>|||Excellent idea. I was hung up on trying to get it to accept the
variable.
Tibor Karaszi wrote:
> Seems CREATE LOGIN doesn't accept a variable for one of both those paramet
ers. You can build the
> CREATE LOGIN command in a variable and then use dynamic SQL to execute it:
> EXEC(@.sql)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>|||You need to use sp_executesql (Dynamic SQL) to accomplish this task. Somethi
ng like this:
DECLARE
@.User nvarchar(20),
@.pwd nvarchar(20),
@.Sql nvarchar(200)
SELECT
@.User = 'Mary',
@.pwd = 'test'
SET @.Sql = 'CREATE LOGIN ''' + @.User + ''' WITH PASSWORD = ''' + @.pwd + ''''
EXECUTE sp_executesql @.Sql
See Erland's excellent article about using Dynamic SQL.
Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Jim Johnson" <easytorememberemailaddress@.gmail.com> wrote in message news:1166460727.281101
.297550@.79g2000cws.googlegroups.com...
> Hi. Perhaps I am missing something obvious, but I am confused. I can
> add a SQL Server 2005 user with this command:
>
> CREATE LOGIN 'AUser' WITH PASSWORD = 'hello', DEFAULT_DATABASE = TEST1
>
> However, I have 100 users to add, and I want to use TRANSACT-SQL to
> create the users automatically and pull their initial password info
> from a table. I use a cursor to do this, replacing the 'AUser' and
> 'hello' with variables, for example:
>
> CREATE LOGIN @.usr WITH PASSWORD = @.pwd
>
> I get an Incorrect syntax near '@.usr' error. Somehow this is not the
> correct datatype, I would imagine. I used this method with sp_addlogin
> in SQL 2000 without a problem.
>
> I've tried declaring @.usr as a varchar or nvarchar without success.
>
> Please offer any suggestions.
>
> Thank you.
>sql

Adding Column AVGs on Matrix Reports

Hi,
Does anyone know how to add an AVG of a column in a matrix report? Its
obvious how to do a subtotal but I can't figure out how to add an
aggregate AVG for all columns. I also need to have the AVG below the
Column headers not at the bottom of the report as usual (see sample
below).
Column1 Column2 Column3
-- -- -- --
AVG 1.67 1.34 2
Row1 2 3 1
Row2 3 1 1
Row3 0 0 4
Any response is much appreciated.
Thanks,
ClaudeYou could write a simple expression like
=Sum(fields!Detail.Value)/Count(fields!Detail.Value) where detail is the
field pointing to 2,3...etc. instead of using subtotal. Insert a static row
at the top and use this expression. You could use tables instead of matrix (I
find it much cleaner than matrix specially when you dont have column
groupings and can do with row groupings)
"Claude" wrote:
> Hi,
> Does anyone know how to add an AVG of a column in a matrix report? Its
> obvious how to do a subtotal but I can't figure out how to add an
> aggregate AVG for all columns. I also need to have the AVG below the
> Column headers not at the bottom of the report as usual (see sample
> below).
> Column1 Column2 Column3
> -- -- -- --
> AVG 1.67 1.34 2
> Row1 2 3 1
> Row2 3 1 1
> Row3 0 0 4
> Any response is much appreciated.
> Thanks,
> Claude
>