MS SQL Crashes

Is this an easy error message to avoid?

[jgar]

We have moved to our own server (Windows 2003 Server + SQL Server on one machine). There have been a few headaches as we the site has been crashing ... the first errors seemed to be with our ASP programming, and since we made improvements (eg closing data connections after use etc) performance has increased.

We now appear to have hit the next "bottleneck" - Now IIS is working better, it seems that SQL Server is now the weak link ...

This is the error we get about once a day:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_DBC failed

The piece of code where it is always happening is in an asp script run using server.execute in global.asa:

Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.Open "Driver={SQL Server};server=(local);database=master;uid=sa;pwd="
SQL ="SELECT * FROM Sites WITH (NOLOCK) WHERE Site = '"&session("site")&"'"
Set RSSite = OBJdbConnection.Execute(SQL)

we added "with (NOLOCK)" recently in the hope this would improve performance .. in fact we got the same error message before we added this ...

Anyone got any ideas as to how we might improve server stability?

Thanks for any insights ...

[wackal]

odbc connections are not that reliable. you might want to try connecting to the DB using an OLE DB connection string. I mainly use Access so I'm not sure what the syntax for that would be, but you could probably find an example by searchingon Google.

You might also want to explicitly name the fields you are calling in your SQL query, this will also speed things up.