Hello all I am trying to write an app that limits the concurrent users. I have not done this before in SQL as most of my programming has been stateless.

How do I connect to the DB at startup?

How do I count the current users?

thanks

tal

Refer to this Winnet Mag article.

Thanks for the link. I Cannot use a Windows 2000 solution as I am not sure of the networks that my app will be running on. I was hoping to count the people logged into my SQL server.

tal

Then use SP_WHO2 from query analyzer

I know about sp_Who, my problem is that I have always just connected to the database when I wanted to make a call in the following fasion:

Open connection
get data
Close connection

How can I open the connection for the entire application?

I would like to open a connection similar to the one that is opened when you launch Query analyzer.


thanks again

Tal

Keep the connection open all the times to the database may cause performance issues rather re-connecting from application code.

I don't know what is the reason behind to do so, but once you apply connection to the server it will not get disconnected until you specify disconnect method from the code. SQL Server doesn't disconnect the client connection deliberately.

I am trying to only allow a licensed number of clients... I also want the connection to die if there is a hard close of the client.

How much of a hit is there if I keep the connection open at all times?

thanks again

tal

BTW what is the licensing mode currently on the server?

By any chance you cannot assign SQL to close the connection if the client disappers from the network. Until unless you restart the services the connection will be shown as sleeping status.

To know the performance bottlenecks you better deploy PERFMON and capture the counters.

Tal, SQL Server & NT handle license complience rather well. Could you explain your situation a little bit more?

Have you thought of writing a stored procedure to check counts and idle connections? You could then schedule the sp to run at regular intervals. if you were out of complience on licenses or found idle connections you could send an e-mail or remidy the problem.

O.k. Maybe I was not specific enoughso here is the scenario:

I work for a small company that sells a piece of software that is backended on a SQL server. Currently, the software is sold on a per client basis. We would like to make the software licensed on a total concurrent user basis.

Currently, I open connection, do data stuff, close connection.

This obviousely would not work as the connections are only open for a few seconds at a time.

My plan is Open a connection
Count Currently open connections

return false if the Licenses are all filled up
Message the user that the licensed copies are in use
Close the Connection

-OR-

Return true if there is a connection available
Leave the connection open until application close


Will this Scenario Work?

tal

Tal, IMNSHO, I think you are on the right track with your last post. I think keeping a connection open for a short period of time COULD be a good thing.

I would hold the connection open for as long as needed and then close the connection. This allows more users to use a smaller number of licenses, just don't close a connection before your work is done!

I want to plug up the licenses, We sell them for a few hundred a piece. what are the drawbacks to leaving an ADO connection open for 30 minutes?

tal

To my knowledge, none. If you need to be connected for 30 min. that's what you need to do. You do take a memory hit for each connection but an idle connection isn't going to hurt your server.

Cool Thats what I thought....
thanks

tal