sql query for getting your users email servers

this is a query that get you a list of the servers of the users emails that they registered with, for example if you have 100 people that has a hotmail account and 200 users with gmail you will get this table:
count server
-----------------
100 hotmail
200 gmail
...

here is the query:

select * from
(SELECT Count(users.UserID) AS Count, right(users.Useremail,len(users.Useremail)-instr(users.Useremail,'@')) as server
FROM users
where users.useremail <> ''
GROUP BY right(users.Useremail,len(users.Useremail)-instr(users.Useremail,'@')))
order by count

connecting a database with c# / asp.net

this is the code of database connection and sql executing functions

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;

///

/// data base class
///

public class db
{
OleDbConnection gDbConn;
OleDbCommand gDbCmd;
public string error;
public db(string connStr)
{
try
{
gDbCmd = new OleDbCommand();
gDbConn = new OleDbConnection();
gDbConn.ConnectionString = connStr;
error="class built successfully";
}
catch (Exception e)
{
error = "[class constructor] building class faild, error in connecting database error discribe: "+e.Message ;
}
}
public OleDbDataReader executeSQL(String sql)
{
try
{
OleDbDataReader rs ;
gDbConn.Open();
gDbCmd.Connection = gDbConn;
gDbCmd.CommandText = sql;
if (sql.Contains("select") )
rs = gDbCmd.ExecuteReader();
else
{
gDbCmd.ExecuteNonQuery();
rs = null;
}
error = "executeSQL executed successfully";
return rs;
}
catch(Exception e)
{
error = "[execute sql] failed to execute sql statement error discribe:" + e.Message
return null;
}
}
}

an example of using this class here: http://byramix.blogspot.com/2006/10/using-data-base-class.html

Cannot open database requested in login 'databaseName'. Login fails

it happened to me with sql server 2000 and 2005, after restoring the database i couldn't login from asp or asp.net script.
this solution is for sql 2000 but i think it will work also with 2005.
i noticed that after the restore the user that i'm tring to loggin with appears on the logins of the specifice database but the culomn of Login Name is empty. i looked at the same place in the original database and i saw something there... it was lost with the restore! i don't know if it's a bug or what, but i lost few days of fighting with it!
anyway, you need to put the the user name... it's not so easy, i didn't find the way to do it, i had to delete it and add it again.. that also not so simple! so you can delete the login it shouldn;t own nothing... so i had to change all the objects that were owned by this login and then could do it!!
i hope you found this blog before losing too much time on this!! the time that i spent is enough :)
if that helped you so write a commemnt
have a nice programming!!

Discuss Post

SQL Server 2005 SQLExpress error: ...provider: Named Pipes Provider, error 40 - Could not open connection to SQL Server

I got this error immediately after installing VS2005 & SQL Server 2005 Express and trying to establish my first connection using the new server - not a good start at all - and by the looks of it, it's happened to many hundreds, if not thousands, of others too.Generally this error occurs if you cannot connect to the SQL server - as the message says (no sh*t). However, what's not obvious is why...First suggestion is to make sure that you specifiy the instance name as well as the server name (christ knows why, but when MS refer to "Server Name" they really mean "Server Instance Name") eg if your server was named 'bigturnip' then you need to specify 'bigturnip\sqlexpress' (where sqlexpress is the instance name - this one just happens to be the default used by SQL Server 2005 SQLExpress).If that doesn't help, then go into the SQL Server Configuration Manager and make sure you've enabled Named Pipes & TCP (if you're using it) and also go into the Surface Area Configuration tool and make sure you've set it for local &/or remote connection for the connection types you want to use. Then restart the SQL Server (instance) service.
source: http://geekswithblogs.net/timh/archive/2006/01/30/67586.aspx

How to configure SQL Server 2005 to allow remote connections

Enable remote connections for SQL Server 2005 Express or SQL Server 2005 Developer EditionYou must enable remote connections for each instance of SQL Server 2005 that you want to connect to from a remote computer. To do this, follow these steps:
1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply. Note Click OK when you receive the following message:Changes to Connection Settings will not take effect until you restart the Database Engine service.
4. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

Discuss Post