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 New Measure to OLAP Cube

To add a record count measure to the olap cube. Create the cube as
usual then run the following VB code with command line parameters
Step1: Build your cube as usual
Step2: Convert the blow vb code to exe prog
Step3: Run the exe with the <Ananlysis server name> <cube name>
parameters (e.g) OLAPcount.exe <Analysis Server> <Cube Name>
Public Sub main()
Dim dsoServer As New DSO.Server
Dim dsoDB As DSO.MDStore
Dim dsoCube As DSO.MDStore
Dim dsoMea As DSO.Measure
Dim dsoAssFactCube As DSO.Cube
Dim dsoPortAnalyzerCube As DSO.Cube
'for storing initial command line arguments as entered by user
Dim strArgs() As String
'for storing the parsed command line arguments
Dim ParsedArgs As String
'for storing the final array of command line arguments
Dim finalArgs() As String
'Splitting the command line arguments based on a space
strArgs = Split(Command$, " ")
'Parsing the command line arguments to generate the parsed string
For i = 0 To UBound(strArgs)
If Len(Trim(strArgs(i))) > 0 Then
ParsedArgs = ParsedArgs & Trim(strArgs(i)) & " "
End If
Next
'Splitting the parsed string into final array of arguments
finalArgs = Split(ParsedArgs, " ")
'Check for correct number of arguments
If UBound(finalArgs) < 2 Then
MsgBox ("Wrong Syntax..." & "or wrong number of
arguments....Correcet Syntax : OLAPcount.exe <Analysis Server> <Cube
Name> (e.g)OLAPcount.exe livdwqprj03 AIGTMSReport1")
Else
'connect to the server (Analysis Server name)
dsoServer.Connect (finalArgs(0))
'Examine whether all necessary components are present (Cube
name)
If dsoServer.MDStores.Find(finalArgs(1)) = False Then
GoTo err_no_database
End If
'Connect with the data base (CUBE) (Cube name)
Set dsoDB = dsoServer.MDStores(finalArgs(1))
If dsoDB.DataSources.Count = 0 Then
GoTo err_no_datasource
ElseIf dsoDB.Dimensions.Count = 0 Then
GoTo err_no_dimensions
ElseIf dsoDB.MDStores.Find("MSP_ASSN_FACT") = False Then
GoTo err_no_fact_cube
ElseIf dsoDB.MDStores.Find("MSP_PORTFOLIO_ANALYZER") = False
Then
GoTo err_no_analyzer
End If
'Set the cube table to use
Set dsoAssFactCube = dsoDB.MDStores("MSP_ASSN_FACT")
Set dsoPortAnalyzerCube =
dsoDB.MDStores("MSP_PORTFOLIO_ANALYZER")
'Specify the name of the new measure
Set dsoMea = dsoAssFactCube.Measures.AddNew("Total
Assignments")
'Specify the source column based on which the operation need to
be performed
'dsoMea.SourceColumn =
"""MSP_CUBE_ASSN_FACT"".""ENT_ASSIGNMENT_CODE6"""
dsoMea.SourceColumn = """MSP_CUBE_ASSN_FACT"".""PROJ_UID"""
'The datatype for the column
dsoMea.SourceColumnType = ADODB.DataTypeEnum.adDecimal
'The method for the column aggSum or aggCount aggregates the
column by summation or counts.
dsoMea.AggregateFunction = aggCount
'update the cube
dsoAssFactCube.Update
dsoAssFactCube.Process
'dsoAnalyzerCube represents a virtual Cube. the measure of a
virtual Cubes has
'the characteristics of the measure of the material cubes
Set dsoMea = dsoPortAnalyzerCube.Measures.AddNew("Total
Assignments")
'The column is indicated in "more normal" form, since the
measure belongs to the virtual Cube!
'dsoMea.SourceColumn = "MSP_ASSN_FACT.FIXED COST"
dsoMea.SourceColumn = "MSP_ASSN_FACT.Total Assignments"
dsoPortAnalyzerCube.Update
dsoPortAnalyzerCube.Process
dsoDB.Process
leave_now:
UserOLAPUpdate = 0
' Exit Function
err_no_database:
l_errnum = 1
s_errdesc = "Datenbank konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 1
' Exit Function
err_no_datasource:
l_errnum = 1
s_errdesc = "Datenquelle konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 2
' Exit Function
err_no_dimensions:
l_errnum = 1
s_errdesc = "Dimensionen konnten nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 3
' Exit Function
err_no_fact_cube:
l_errnum = 1
s_errdesc = "Cube MSP_ASSN_FACT konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 4
' Exit Function
err_no_analyzer:
l_errnum = 1
s_errdesc = "Cube MSP_PORTFOLIO_ANALYZER konnte nicht gefunden
werden! "
UserOLAPUpdate = vbObjectError + 5
' Exit Function
error_handler:
l_errnum = Err.Number
s_errdesc = Err.Description
UserOLAPUpdate = 1 ' although it could be any non-zero value
' to indicate an error
End If
End SubPerhaps you should head for the ng
http://www.microsoft.com/communitie...sqlserver.olap
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"wilsonjust@.gmail.com" wrote:

> To add a record count measure to the olap cube. Create the cube as
> usual then run the following VB code with command line parameters
> Step1: Build your cube as usual
> Step2: Convert the blow vb code to exe prog
> Step3: Run the exe with the <Ananlysis server name> <cube name>
> parameters (e.g) OLAPcount.exe <Analysis Server> <Cube Name>
> Public Sub main()
> Dim dsoServer As New DSO.Server
> Dim dsoDB As DSO.MDStore
> Dim dsoCube As DSO.MDStore
> Dim dsoMea As DSO.Measure
> Dim dsoAssFactCube As DSO.Cube
> Dim dsoPortAnalyzerCube As DSO.Cube
> 'for storing initial command line arguments as entered by user
> Dim strArgs() As String
> 'for storing the parsed command line arguments
> Dim ParsedArgs As String
> 'for storing the final array of command line arguments
> Dim finalArgs() As String
> 'Splitting the command line arguments based on a space
> strArgs = Split(Command$, " ")
> 'Parsing the command line arguments to generate the parsed string
> For i = 0 To UBound(strArgs)
> If Len(Trim(strArgs(i))) > 0 Then
> ParsedArgs = ParsedArgs & Trim(strArgs(i)) & " "
> End If
> Next
> 'Splitting the parsed string into final array of arguments
> finalArgs = Split(ParsedArgs, " ")
> 'Check for correct number of arguments
> If UBound(finalArgs) < 2 Then
> MsgBox ("Wrong Syntax..." & "or wrong number of
> arguments....Correcet Syntax : OLAPcount.exe <Analysis Server> <Cube
> Name> (e.g)OLAPcount.exe livdwqprj03 AIGTMSReport1")
> Else
> 'connect to the server (Analysis Server name)
> dsoServer.Connect (finalArgs(0))
> 'Examine whether all necessary components are present (Cube
> name)
> If dsoServer.MDStores.Find(finalArgs(1)) = False Then
> GoTo err_no_database
> End If
> 'Connect with the data base (CUBE) (Cube name)
> Set dsoDB = dsoServer.MDStores(finalArgs(1))
> If dsoDB.DataSources.Count = 0 Then
> GoTo err_no_datasource
> ElseIf dsoDB.Dimensions.Count = 0 Then
> GoTo err_no_dimensions
> ElseIf dsoDB.MDStores.Find("MSP_ASSN_FACT") = False Then
> GoTo err_no_fact_cube
> ElseIf dsoDB.MDStores.Find("MSP_PORTFOLIO_ANALYZER") = False
> Then
> GoTo err_no_analyzer
> End If
> 'Set the cube table to use
> Set dsoAssFactCube = dsoDB.MDStores("MSP_ASSN_FACT")
> Set dsoPortAnalyzerCube =
> dsoDB.MDStores("MSP_PORTFOLIO_ANALYZER")
> 'Specify the name of the new measure
> Set dsoMea = dsoAssFactCube.Measures.AddNew("Total
> Assignments")
> 'Specify the source column based on which the operation need to
> be performed
> 'dsoMea.SourceColumn =
> """MSP_CUBE_ASSN_FACT"".""ENT_ASSIGNMENT_CODE6"""
> dsoMea.SourceColumn = """MSP_CUBE_ASSN_FACT"".""PROJ_UID"""
> 'The datatype for the column
> dsoMea.SourceColumnType = ADODB.DataTypeEnum.adDecimal
> 'The method for the column aggSum or aggCount aggregates the
> column by summation or counts.
> dsoMea.AggregateFunction = aggCount
> 'update the cube
> dsoAssFactCube.Update
> dsoAssFactCube.Process
> 'dsoAnalyzerCube represents a virtual Cube. the measure of a
> virtual Cubes has
> 'the characteristics of the measure of the material cubes
> Set dsoMea = dsoPortAnalyzerCube.Measures.AddNew("Total
> Assignments")
> 'The column is indicated in "more normal" form, since the
> measure belongs to the virtual Cube!
> 'dsoMea.SourceColumn = "MSP_ASSN_FACT.FIXED COST"
> dsoMea.SourceColumn = "MSP_ASSN_FACT.Total Assignments"
> dsoPortAnalyzerCube.Update
> dsoPortAnalyzerCube.Process
> dsoDB.Process
> leave_now:
> UserOLAPUpdate = 0
> ' Exit Function
> err_no_database:
> l_errnum = 1
> s_errdesc = "Datenbank konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 1
> ' Exit Function
> err_no_datasource:
> l_errnum = 1
> s_errdesc = "Datenquelle konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 2
> ' Exit Function
> err_no_dimensions:
> l_errnum = 1
> s_errdesc = "Dimensionen konnten nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 3
> ' Exit Function
> err_no_fact_cube:
> l_errnum = 1
> s_errdesc = "Cube MSP_ASSN_FACT konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 4
> ' Exit Function
> err_no_analyzer:
> l_errnum = 1
> s_errdesc = "Cube MSP_PORTFOLIO_ANALYZER konnte nicht gefunden
> werden! "
> UserOLAPUpdate = vbObjectError + 5
> ' Exit Function
> error_handler:
> l_errnum = Err.Number
> s_errdesc = Err.Description
> UserOLAPUpdate = 1 ' although it could be any non-zero value
> ' to indicate an error
> End If
> End Sub
>

Adding Field or Stored Procedure to MSDE

Is there a way to add a field or a stored procedure to a server running MSDE? Like a script on the command line or?? how can this be done.

Thank you,Sure can. Check out the osql command-line utility, which lets you run individual T-SQL statements or script files. Or, there are several tools you can use to manage MSDE:

ASP.NET Enterprise Manager, an open source SQL Server and MSDE management tool.

ASP.NET WebMatrix (which includes a database management tool) from this web site (click on the Web Matrix tab at the top of this page).

Microsoft's Web Data Administrator is a free web-based MSDE management program written using C# and ASP.NET, and includes source code.

You can also access MSDE using Access.

Don|||If I write a stored procedure and then script it, how can I use osql to run the script and add the sp to a database on the server? Can you show me an example? Do I create a cmd file that contains the script?

Thank you,|||Here's how you run a script file:

osql -E -i "myScripts.sql"

And to run a line of T-SQL directly:

osql -E -Q "DROP DATABASE pubs"

There are a gaggle of options for osql, so you'll need to look at the docs to use the ones appropriate for your setup.

Don|||Can you show me a sample of a stored procedure to add a field to a table that is already in a database?

Thanks again for your help,|||

CREATE PROCEDURE MyProc
AS
 ALTER TABLE myTable ADD newCol VARCHAR(20) NULL
GO