Load on SQL Server Database

Hi,
How do we find the load on the SQL server database. even i am not
asking in the right mannar but i will can explain my problem then u
suggest me the right way to clear my problem.
i have written my code in C++,i am using multi threading and my
database is SQL Server.
i closed the connections perfectly still, i am seeing the connection
leak on Performance Viewer.
So i disabled some of the SQL Statements so very very very very slowly
, the connection leaks are growing. like propostional to disabling the
SQL Statements growing of connection leak.
so i thought it will be load balancing factor of the SQL Server or my
thing will be worng please any body help me.
In short, I want to know if the load on the SQL Server can cause
connection leaks? and if yes, then how can we define load on the SQL
Server database.
Thanks in advance,
RSTake a look at Perfomnace Monitor
http://www.sql-server-performance.com/performance_monitor_counters_memory.asp
<sk.rasheedfarhan@.gmail.com> wrote in message
news:1167284187.612926.263370@.73g2000cwn.googlegroups.com...
> Hi,
> How do we find the load on the SQL server database. even i am not
> asking in the right mannar but i will can explain my problem then u
> suggest me the right way to clear my problem.
> i have written my code in C++,i am using multi threading and my
> database is SQL Server.
> i closed the connections perfectly still, i am seeing the connection
> leak on Performance Viewer.
> So i disabled some of the SQL Statements so very very very very slowly
> , the connection leaks are growing. like propostional to disabling the
> SQL Statements growing of connection leak.
> so i thought it will be load balancing factor of the SQL Server or my
> thing will be worng please any body help me.
> In short, I want to know if the load on the SQL Server can cause
> connection leaks? and if yes, then how can we define load on the SQL
> Server database.
> Thanks in advance,
> RS
>sql

List all SQL/VS.NET objects on an aspx page?

Hi guys,

Being new to ASP.NET, I may be asking something that is dead simple, but please bear with me!

How would I go about listing all the tables, stored procedures and views from a SQL database within an aspx page? I understand how to connect and return a dataset etc, but that's about as far as I can get. I want to be able to list all of these objects from a given db - any ideas?

Additionally (this may not be possible) - is there a way I can list all of the objects within a visual studio.net solution, again within an aspx page?

Thanks for any help,

JonSQL has a number of procs and views that allow you to get at the meta data of its database. It's also got some weird foreach functions that help too.

select * from sysobjects

is one place to start, but there are nicer views.

As for .net you can use reflection and work your way around that.

System.Reflection.Assembly.GetExecutingAssembly();

is another good starting place.|||Thanks alot - that gives me some useful pointers.
I shall have a go and post results (if anyone else is interested in how it's done).

List all databases accessible by a login

Hi all. I am new to SQL-DMO so sorry if what i am asking is really easy.

Is it possible to get a list of all objects (including databases) which are accessible for a particular login.

So far I have:

Dim oSQLServer As SQLDMO.SQLServer
Dim oSQLDatabase As SQLDMO.Database
Dim oSQLLogin As New SQLDMO.Login
Dim oUser As New SQLDMO.User

oSQLServer = New SQLDMO.SQLServer
oSQLServer.Name = _oSQLServer.Host

' Check server is alive

If (oSQLServer.Status = SQLDMO.SQLDMO_SVCSTATUS_TYPE.SQLDMOSvc_Running) Then

Try

oSQLServer.LoginSecure = False

oSQLServer.Connect(_oSQLServer.Host, _oSQLServer.Login, _oSQLServer.Password)

For Each oSQLDatabase In oSQLServer.Databases

'oSQLDatabase.Users.

' Get a list of databases accessible by user

oSQLLogin.Name = _oSQLServer.Login

'oSQLObjectList = oSQLUser.ListOwnedObjects(SQLDMO.SQLDMO_OBJECT_TYPE.SQLDMOObj_Database)

'oSQLDMONameList = oSQLServer.ListMembers(SQLDMO.SQLDMO_ROLE_TYPE.SQLDMORole_All)

System.Diagnostics.Debug.Write(oSQLDatabase.Name.ToString & " : " & oSQLDatabase.IsUser(oSQLLogin.Name))

'oSQLDatabase.IsUser()

Next

Catch ex As Exception

System.Diagnostics.Debug.Write(ex.Message.ToString)

Finally

oSQLDatabase = Nothing

oSQLServer.DisConnect()

oSQLServer = Nothing

End Try

Else

System.Diagnostics.Debug.Write(oSQLServer.StatusInfoRefetchInterval( _

SQLDMO.SQLDMO_STATUSINFO_TYPE.SQLDMOStatInfo_All))

End If

The "oSQLLogin.Name = _oSQLServer.Login" statement does not seem to satisfy your requirements. Another side question, is there a particular reason why you are starting out with DMO? If not, you should consider using SMO, which ships with SQL Server 2005.

I have created a SMO program to meet your needs...The program checks to see if a user exists in the database. By access, you might want to check if the user has certain permissions. You may also need to deal with logins that are mapped to a different user name.

Server srv = new Server("MyServer"); //Yukon

srv.ConnectionContext.LoginSecure = true;

srv.ConnectionContext.Connect();

srv.DefaultTextMode = false;

string checkUser = "bob";

try

{

foreach (Database db in srv.Databases)

{

if (db.Users[checkUser] != null)

{

Console.WriteLine(checkUser + " has access to " + db.Name.ToString());

}

}

}

catch (Exception ex)

{

Console.WriteLine(ex);

}

|||

I am using DMO as when I wrote the specification for the project, I stated that I would be using SQL-DMO. SQL 2005 had not be released and I could not wait for release date, incase of delays.

I am able to get a list of accessible databases for a particular login using

Dim oSQLDatabase As SQLDMO.Database

......

System.Diagnostics.Debug.WriteLine(oSQLDatabase.UserName & " user within database " & oSQLDatabase.Name)

However, it throughs an exception when every it hits the 'model' databases, dont suppose anybody knows why?

Thanks

DAN

|||

I tried the following code and it works against model (i.e no exception). What exception are you seeing? Which version of SQLDMO are you using -- located in "%ProgramFiles%\Microsoft SQL Server\80\Tools\Binn" folder? Which version of SQL Server are you targeting? (I am using the version of SQL-DMO that ships with SQL Server 2005 and I am targeting SQL Server 2000).

foreach (Interop.sqldmo._Database db in srv.Databases)

{

try

{

Console.WriteLine(db.Name + ":" + db.IsUser("bob"));

}

catch (Exception ex)

{

Console.WriteLine(ex);

}

}

|||

Forget about the error, it was problem with the code else where in the applcation, conflicting names me thinks. Took your advice and moved over to SMO, after some alteration to the spec. Things a lot easier!

Thanks for your help Peter.