ODBC DSN - Connection Not Open

[scherpdk]

Hello,
I have a problem with a middle-tier windows service written in MFC C++. It's a service that at certain times performs a large number of database calls in a multi-threaded scenario. Basically, work items get picked up from the database, queued up and then processed by the worker threads. Each work item performs a great many reads/writes to the database. We've recently moved this process to a new middle-tier virtual server (running windows 2003). Database is SqlServer 2005. On this server when the high volume of work is run (typically starting at midnight) the connections can't be opened to perform reads/writes but then the error that the database can't be opened is logged to the database using the exact same connection string.

CDatabase Database;
BOOL bOpen;
try
{
	bOpen = Database.OpenEx("ODBC Connection String", CDatabase::noOdbcDialog|CDatabase::useCursorLib);
}
catch ( CDBException * pEx )
{
	CString strError;
	strError = CString("Error:") + pEx->m_strError;
	LogErrorMsg( strError);
	pEx->Delete();
	return 0;
}
<snip>

Database.ExecuteSql("pMyStoredProc");       

The Database.OpenEx causes an exception but then the LogErrorMessage() in the catch block writes the error to the database using the exact same paradigm - CDatabase, OpenEx using the exact same connection string, ExecuteSql with a stored procedure. And then the error is logged to the database. The error message in the pEx->m_strError is "Connection not open."

I know there are lots of components at play (the code, the network, and the database). We do enable connection pooling using

SQLSetEnvAttr( NULL, SQL_ATTR_CONNECTION_POOLING,(SQLPOINTER)SQL_CP_ONE_PER_DRIVER, 0 );

Does anyone have any idea what might be happening here?

Thanks in advance!

[bartoli]

Windows Xp forbids more than 10 new socket connexions per second. That might be why you can't connect when there is a big number of DB access, but you can connect the BD to save the error when they have all been stopped?

[scherpdk]

Does the same limit apply to Windows Server 2003 (that's what the middle tier is running on)? And is there a configuration setting somewhere to allow more than that?

[polar]

have u configured your tcp/ip to allow the connection if you have confgured all the sockets (the sql server 2003 suppourts upto 4 sockets in the stadad edition and 8 sockets in the enterprise editon )then try to open a connection avoid exeptionand include sql type.h,sql.h,sqltext.h

[scherpdk]

I have a Windows Server 2003 server connected to SQL Server 2003 database. The application worked just fine when I was on a previous physical server. Now that I've moved it to a virtual machine (VSX) I'm experiencing the problem.