Getting the results set from sp_helpuser

[Guest]

From within my code, I need to determine the "Roles" that a particular UserID
is subscribed to. For example, from within the SQL Query Analyzer, if I:

EXEC sp_helpuser 'userID'

the result set will have a row describing each "Role" that UserID belongs
to. However, I don't know how to get access to this result set from within my
VS 2003/2005 VB code. If I ExecuteNonQuery, I only get returned the number of
rows affected, not the actual rows.

From within VS 2003/2005 VB, how can I find out which "Roles" a particular
UserID belongs to?

 

[Robbe Morris [C# MVP]]

Why would you call ExecuteNonQuery when it
is a query that you want?

You'd call the stored procedure like you would
any other proc you expect would return results.

I'm pasting this in from another code sample
from this article that is a code generator
for SQL Server to ADO.NET

http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp

private static SqlCommand PrepareCommand(string CustomerID)
{
SqlCommand oCmd = new SqlCommand();
SqlParameter oPrm;

try
{

oCmd.CommandType=CommandType.StoredProcedure;
oCmd.CommandText="CustOrderHist";

oPrm = new SqlParameter();
oPrm.ParameterName = "@RETURN_VALUE";
oPrm.SqlDbType = SqlDbType.Int;
oPrm.Precision = (byte)int.Parse("0");
oPrm.Size = int.Parse("0");
oPrm.Scale = (byte)int.Parse("0");
oPrm.SourceColumn = null;
oPrm.Direction = ParameterDirection.ReturnValue;
oCmd.Parameters.Add(oPrm);

oPrm = new SqlParameter();
oPrm.ParameterName = "@CustomerID";
oPrm.SqlDbType = SqlDbType.NChar;
oPrm.Precision = (byte)int.Parse("0");
oPrm.Size = int.Parse("5");
oPrm.Scale = (byte)int.Parse("0");
oPrm.SourceColumn = null;
oPrm.Direction = ParameterDirection.Input;
oPrm.Value = CustomerID;
oCmd.Parameters.Add(oPrm);

}
catch (Exception) { throw; }
return oCmd;
}

public static DataTable ToDataTable(string ConnectionString,string
CustomerID)
{
DataTable dt = new DataTable();

try
{

SqlCommand oCmd = PrepareCommand(CustomerID);

using (SqlConnection oConn = new SqlConnection())
{

oConn.ConnectionString = ConnectionString;
oConn.Open();
oCmd.Connection = oConn;

using(SqlDataAdapter da = new SqlDataAdapter(oCmd))
{
da.Fill(dt);
oCmd.Dispose();
}

}

}
catch (Exception) { throw; }
return dt;
}

 

[Guest]

One way is to use DataReader to get data:

Dim SqlDataReader reader = command.ExecuteReader()

Then retrive Role data from the reader.

HTH

Elton Wang

 

[W.G. Ryan - MVP]

Michael - you can use a DataReader to do this. Here's a class I wrote as a
sample which will retrieve the entire text for you. You can use Regexes or
whatever method you want to extract the specifics of the text - I have a
sample application if you are interested that will take whatever objects you
want and script them - so you can have it create a *.sql file for each proc
you have (or other object) and it will name it ObjectName.sql. It will also
write the text out to a textbox as it is populating if you like. Shoot me
your email and I'll send it to you if you're interested, Cheers,

Bill

namespace StoredProcedureGrabber
{
#region Using Statements
using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Collections;
using System.Configuration;

#endregion
/// <summary>
/// Summary description for Procedures.
/// </summary>
public class Procedures
{
public static String ConnectionString =
ConfigurationSettings.AppSettings["ConnectionString"];
public Procedures()
{

}

public ArrayList GetProcedures()
{
String commandText = "SELECT * FROM sysobjects WHERE(xtype = 'P') AND
(LEFT(name, 2) <> 'qs') AND (LEFT(name, 2) <> 'dt') ORDER BY name";
ArrayList procs = new ArrayList();
using(SqlConnection cn = new SqlConnection(Procedures.ConnectionString))
{
SqlCommand cmd = new SqlCommand(commandText, cn);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while(dr.Read())
{
procs.Add(dr[0].ToString());
}
cn.Close();
}
return procs;
}

public String GetProcedureText(String procedureName)
{
String commandText = "sp_helptext";
System.Text.StringBuilder sb = new System.Text.StringBuilder();
using(SqlConnection cn = new SqlConnection(Procedures.ConnectionString))
{

SqlCommand cmd = new SqlCommand(commandText, cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@objname", procedureName);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while(dr.Read())
{
sb.Append(dr[0].ToString());
}
cn.Close();
}
return sb.ToString();
}

}
}

 

[Mark Rae]

If I ExecuteNonQuery, I only get returned the number of
rows affected, not the actual rows.

Of course you do - that's what ExecuteNonQuery does...

From within VS 2003/2005 VB, how can I find out which "Roles" a particular
UserID belongs to?

Call it like you'd call any other stored procedure, and read the results
into a SqlDataReader

 

[Guest]

Thanks to all of you for your help!

I'm using the SQLDataReader approach. I'm using the sp_helpuser stored
procedure, which lives in the Master database. If I call the stored procedure
with a parameter, such as "sp_helpuser 'userID'", I get an error: Could not
find stored procedure 'sp_helpuser 'userID''. I've tried eliminating the
single quotes from around the userID, but I get a similar error. I can call
sp_helpuser without any parameters, but I don't get the results I need. I
tried adding master.dbo. as a prefix to the stored procedure name but that
failed too.

 

[Kevin Yu [MSFT]]

Hi Michael,

Calling a stored procedure in ADO.NET, you have to add parameters to the
command object. Please take a look at Bill's reply. He is using
cmd.Parameters.Add("@objname", procedureName); to add a parameter.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

Michael - If you take a look at the class definition in my post above, that
code is actually live and will work. I would recommend (mainly for ease of
use) that you use a paramaterized query - and the param name is @objName. If
you just plug this into your code, it should work - let me know if you have
any problems.

public String GetProcedureText(String procedureName)
{
String commandText = "sp_helptext";
System.Text.StringBuilder sb = new System.Text.StringBuilder();
using(SqlConnection cn = new SqlConnection(Procedures.ConnectionString))
{

SqlCommand cmd = new SqlCommand(commandText, cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@objname", procedureName);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while(dr.Read())
{
sb.Append(dr[0].ToString());
}
cn.Close();
}
return sb.ToString();
}

 

[Guest]

Now I get it! You guys ROCK! Added the parameter and it worked like a charm!

--
Michael Hockstein

Michael - If you take a look at the class definition in my post above, that
code is actually live and will work. I would recommend (mainly for ease of
use) that you use a paramaterized query - and the param name is @objName. If
you just plug this into your code, it should work - let me know if you have
any problems.

public String GetProcedureText(String procedureName)
{
String commandText = "sp_helptext";
System.Text.StringBuilder sb = new System.Text.StringBuilder();
using(SqlConnection cn = new SqlConnection(Procedures.ConnectionString))
{

SqlCommand cmd = new SqlCommand(commandText, cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@objname", procedureName);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while(dr.Read())
{
sb.Append(dr[0].ToString());
}
cn.Close();
}
return sb.ToString();
}

Thanks to all of you for your help!

I'm using the SQLDataReader approach. I'm using the sp_helpuser stored
procedure, which lives in the Master database. If I call the stored procedure
with a parameter, such as "sp_helpuser 'userID'", I get an error: Could not
find stored procedure 'sp_helpuser 'userID''. I've tried eliminating the
single quotes from around the userID, but I get a similar error. I can call
sp_helpuser without any parameters, but I don't get the results I need. I
tried adding master.dbo. as a prefix to the stored procedure name but that
failed too.

 

[Guest]

Thanks Kevin. I really appreciate everyone's help.

 

[Guest]

Thanks for the help Robbe!

 

[Guest]

The DataReader was the way to go!

 

[Kevin Yu [MSFT]]

You're welcome.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Zastil]

Excellent this post helped me thankyou! and thanks google love google
groups!

Wayne
zastil.co.uk

 

[W.G. Ryan - MVP]

Glad to worked buddy ;-)

Now I get it! You guys ROCK! Added the parameter and it worked like a
charm!

--
Michael Hockstein

Michael - If you take a look at the class definition in my post above,
that
code is actually live and will work. I would recommend (mainly for ease
of
use) that you use a paramaterized query - and the param name is @objName.
If
you just plug this into your code, it should work - let me know if you
have
any problems.

public String GetProcedureText(String procedureName)
{
String commandText = "sp_helptext";
System.Text.StringBuilder sb = new System.Text.StringBuilder();
using(SqlConnection cn = new
SqlConnection(Procedures.ConnectionString))
{

SqlCommand cmd = new SqlCommand(commandText, cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@objname", procedureName);
cn.Open();
SqlDataReader dr =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
while(dr.Read())
{
sb.Append(dr[0].ToString());
}
cn.Close();
}
return sb.ToString();
}

Thanks to all of you for your help!

I'm using the SQLDataReader approach. I'm using the sp_helpuser stored
procedure, which lives in the Master database. If I call the stored
procedure
with a parameter, such as "sp_helpuser 'userID'", I get an error: Could
not
find stored procedure 'sp_helpuser 'userID''. I've tried eliminating
the
single quotes from around the userID, but I get a similar error. I can
call
sp_helpuser without any parameters, but I don't get the results I need.
I
tried adding master.dbo. as a prefix to the stored procedure name but
that
failed too.






--
Michael Hockstein


:


If I ExecuteNonQuery, I only get returned the number of
rows affected, not the actual rows.

Of course you do - that's what ExecuteNonQuery does...

From within VS 2003/2005 VB, how can I find out which "Roles" a
particular
UserID belongs to?

Call it like you'd call any other stored procedure, and read the
results
into a SqlDataReader