Database

14-Sep-04 8:29

I am trying to write a small app to map out sql server logins that are no longer mapped to any databases. simple enuff. got the algorithm worked out. however having problem with ado.net returning varbinary value to compare against.

First i create a reader (a) with the following query: "select name,sid from master.dbo.syslogins"

Then I create a reader (b) with the following query: "select name from master.dbo.sysdatabases"

Everything okay so far.

Now I want to loop through each login and check to see if the sid is mapped to any user in the databases.

So I create a query as such:
cString = select count(*) from " + b["name"].tostring() + ".dbo.sysusers where sid = " + a["sid"].tostring();

then next statement is to setup a sqlcommand and executeacaler the cString query.

I go through with the debugger and check to see what the value of cString becomes and everytime it is translated to:
"select count(*) from Broadband.dbo.sysusers where sid = System.Byte[]"

this generates a sql exception error. I can put single quotes around it but it still looks like:
"select count(*) from Broadband.dbo.sysusers where sid = 'System.Byte[]'"

this does not generate a sql exception but neither does it work. no sid values equals the string System.Byte[]

Need help. Confused | :confused:

cpritt

Jon Rista14-Sep-04 8:44

14-Sep-04 8:44

If the variable 'a' is a SqlDataReader (or any data reader, for that matter), you have a whole slough of methods to convert the data. Try a.GetString(1) to get a string value from a["sid"], or try a.GetInt32(1) to get an int value.

cpritt14-Sep-04 9:52

14-Sep-04 9:52

variable "a" is a SqlDataReader. I have tried the other options like a.GetSqlBinary(1) and everything still is being interpreted as System.Byte[].

what i mean is that the c# text:
cString = "Select whatever where sid = " + a.GetSqlBinary(1)

is sent to sql server as "Select whatever where sid = System.Byte[]"

whereas a SqlException is generated.

what i want is:
"Select whatever where sid = 0x27FA32BE455789"

not sure how to get this

Jon Rista14-Sep-04 13:17

14-Sep-04 13:17

"SELECT " + whatever + " WHERE sid=0x" + a.GetString(1);

When you build a query to execute with a SqlCommand, its just a string. Don't call GetSqlBinary, because its going to return the native type for binary data: byte[]. Call GetString....the SqlDataReader should properly convert from the native Sql datatype to a string, and then you just append that string to the rest of the query.

you'll end up with a SELECT statement thats something like:

"SELECT col1, col2, col3 FROM table WHERE sid=0x27FA32BE455789"

If that select statement executes fine if you run it directly on the database, it should run fine through a SqlCommand. Its just a matter of retrieving the data from the resultset in the way you want it. Just because something is an int in the resultset doesn't mean you have to call GetInt().

cpritt14-Sep-04 18:57

14-Sep-04 18:57

Thanks for the input and patience to respond to my questions. I tried the GetString(1) and it generated a exception for illegal typecast. Not sure where to go from here. Just in case we are missing each other in the interpretation of what i am trying to do in the code, here is the code in question. Pretty short and simple. I have walked through this step by step and where known sid's are in the sysusers table the executescalar count is still 0 when it should be 1. Of course that could be where my understanding is causing me to blieve the code is not working:

private void GetLoginsNotMapped()
{
int count;
bool sidFound;
string user;
SqlTransaction my_SqlTransaction;
SqlCommand my_Command;
SqlDataReader my_Reader;
SqlConnection my_Connection;
SqlCommand m_Command;
SqlDataReader m_Reader;
SqlConnection m_Connect;
SqlCommand my_SqlCommand;
SqlCommand m_SqlCommand = new SqlCommand();
SqlConnection t_Connection;

try
{
m_Connect = ConnectToServer();
my_Connection = ConnectToServer();
t_Connection = ConnectToServer();

if (sqlSuccess == true)
{
cString = "Select name, sid from master.dbo.syslogins";
my_Command = new SqlCommand(cString,my_Connection);
my_Reader = my_Command.ExecuteReader();

m_SqlCommand.Connection = t_Connection;

while (my_Reader.Read())
{
sidFound = false;
user = my_Reader["name"].ToString(); //Used only for troublshooting

cString = "Select name from master.dbo.sysdatabases";
m_Command = new SqlCommand(cString,m_Connect);
m_Reader = m_Command.ExecuteReader();

while (m_Reader.Read())
{
cString = "Select count(*) from " + m_Reader["name"].ToString() + ".dbo.sysusers ";
cString = cString + "where sid = " + my_Reader.GetString(1) + "";

m_SqlCommand.CommandText = cString;

try
{
count = (int) m_SqlCommand.ExecuteScalar();
if (count > 0 ) sidFound = true;
}
catch (SqlException e)
{
logErrorMessage(serverName,e.Message);
}
}
m_Reader.Close();
m_Command.Dispose();
if (sidFound == false)
{
try
{
my_SqlTransaction = mySqlConnection.BeginTransaction();
my_SqlCommand = mySqlConnection.CreateCommand();
my_SqlCommand.Transaction = my_SqlTransaction;
my_SqlCommand.CommandText = "Insert into tblNotMapped (" +
"serverName, loginName) Values ('" + serverName +
"', '" + my_Reader["name"].ToString() + "') ";
my_SqlCommand.ExecuteNonQuery();
my_SqlTransaction.Commit();
my_SqlTransaction.Dispose();
}
catch (SqlException ex)
{
logErrorMessage(serverName,ex.Message);
}
}
}
my_Reader.Close();
my_Command.Dispose();
m_SqlCommand.Dispose();
}
}
catch (SqlException err)
{
logErrorMessage(serverName,err.Message);
}
}

Thanks ahead of time for any tips can be sent my way.

/cp

Jon Rista15-Sep-04 7:16

15-Sep-04 7:16

Well, lets see what we can do here. It seems its not possible to have ADO.NET automatically cast a varbinary to a string. So, what we need to do is find a way to manually cast it ourselvs.

I think there are several ways of doing this. So, after a few minutes research, here are the best options I came up with.

Try using my_Reader.GetSqlBinary(). From this SqlBinary object, call sqlBinary.ToString() to convert the value to a string. I'm not certain if it will convert byte by byte or if it will just do something similar to calling Encoding.ASCII.GetString(byte[]).

If calling ToString doesn't work right, you could try calling sqlBinary.ToSqlGuid(), then call sqlGuid.ToString(). You should only do this if the binary data being returned is 128 bits, or 16 bytes, long.

If the data is not 16 bytes long, and calling GetSqlBinary doesn't result in useful data, I'd manually build a string from your byte array. Something like:

byte[] binary = my_Reader.GetSqlBinary().Value;
string binStr = "";
for (int i=0; i

cpritt15-Sep-04 8:54

15-Sep-04 8:54

Whooo Hwoooooo!!!!!!!!!!

You duh MAN!!!!!!!!

that worked. I used the byte[] array and looped through each byte in the array.

I'm rockin and rollin now.

thanks a whole lot. saved my butt and allowed me to complete a project on time.

Jon Rista30-Sep-04 18:52

Extreme performance issues (SQL Server 2000/ADO.NET/C#)

30-Sep-04 18:52

I'm glad I could be of service. Smile | :)

Jon Rista14-Sep-04 8:14