• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

SQL Server 2000 Database Connections

Below is my connection string that is in my application. The connection works fine, but I just had 12 users open the application to test it. The first 9 or 10 were fine, but a couple kept getting a time out error and never get into the application. Does SQL Server have a default that limits the connections to a database?


Private Sub Class_Initialize()

Set mDataAccess = New cDataAccess
mDataAccess.cnxString = "Driver={SQL Server}; Server=dohsdbschd0601; Database=AIDS; Uid=sa; Pwd=caffeine"
mDataAccess.OpenConnection

End Sub
0
jmoneilthe2
Asked:
jmoneilthe2
  • 2
  • 2
1 Solution
 
SashPCommented:
No the default is 32,767.

The default number of maximum connections can be changed.

From Books Online:

How to set the SQL Server connection (Enterprise Manager)
To set the SQL Server connection

Expand a server group, and then expand a server.

Expand Management, right-click SQL Server Agent, and then click Properties.

Or use TSQL

Use the "user connections" option to specify the maximum number of simultaneous user connections allowed on Microsoft® SQL Server™. The actual number of user connections allowed also depends on the version of SQL Server you are using and the limits of your application(s) and hardware. SQL Server allows a maximum of 32,767 user connections.

SELECT @@MAX_CONNECTIONS

user connections helps avoid overloading the server with too many concurrent connections. You can estimate the number of connections based on system and user requirements. For example, on a system with many users, each user would not usually require a unique connection. Connections can be shared among users. Users who are running OLE DB applications need a connection for each open connection object, users who are running ODBC applications need a connection for each active connection handle in the application, and users who are running DB-Library applications need one connection for each process started that calls the DB-Library dbopen function.

Important  If you must use this option, do not set the value too high because each connection takes approximately 40 kilobytes (KB) of overhead regardless of whether the connection is being used. If you exceed the maximum number of user connections, you receive an error message and are not able to connect until another connection becomes available.

user connections is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change user connections only when show advanced options is set to 1. The setting takes effect after stopping and restarting the server.

0
 
SashPCommented:
jmoneilthe2,

If you were exceeding the number of user connections you would recive an error that would indicate the fact.  However your problem could be a network issue at your router, switch or server nics, a server hardware problem an issue.

The worstations that are not able to connect, I assume have connected at some stage?

You may perhaps try a different driver. Have you tried the oledb drvier instead of odbc.

mDataAccess.cnxString ="Provider=sqloledb;Data Source=dohsdbschd0601;Initial Catalog=AIDS;User Id=sa;Password=caffeine;"
0
 
apirniaCommented:
Is this in ASP or VB? There are few things you can do to fix this depending on where you are using the connection.
If it is ASP you can change a time out in your IIS and some code in ASP.

The default for connection strings to DB is 20 secounds. If within 20 secounds you dont get results from DB the connection will be terminted and you will get a time out error. Naturally more people connected to DB it will become a little slower depending on hoe much RAM and CPU speed you have. Basically if you are expecting more than 10 users on your system and it fails you need more RAM and CPU, however you can set the time out to unlimited. But then this way the users wont be to happy.
0
 
apirniaCommented:
There are several places that could affect the time out:

IIS
ASP
VB
SQL Server

Here is how in SQL Server:
In Enterprise Manager, select Microsoft SQLServers. Then go to Tools/Options, go to the Connection tab and change Query time-out(seconds) to whatever you want.

This is how in your code different ways:
dbConnString = "DSN=someconnection;uid=myuserid;pwd=mypassword;"
set dbConn = server.createobject("adodb.connection")
dbConn.CommandTimeout = 300
dbConn.open (dbConnString)


<% Server.ScriptTimeout = 150 %>
0 is (wait indefinitely)


Connection.ConnectionTimeout = 60

Other soloutions:
Index you tables
Return less data from DB
Optimize your Query

0
 
jmoneilthe2Author Commented:
Apirnia,
Thank you for the information. I think I resolved my issue by setting a table to have a primary key. When I stepped through the code, I noticed that it was crapping out when it ran a query to my lookupUser table. In that table, I simply have an ID, and a username but I never set the ID to be a primary key. Do you think that by not having an index to the table that when to many connections were made it locked up? It looks like that was the problem but I'm not sure.

John
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now