R

Hi,

I have a bit of code written in class ASP that connects to a database with Command.Execute. It works fine with SQL 2000 but after an upgrade to SQL 2005 causes the asp page to hang while w3wp runs at at 100%

I'm running SQL 2005 SP1 on the same machine as IIS. I can't see anything in the event log or the SQL logs that would explain this (no errors in either). SQL profiler shows the stored procedure is called.

The code is below. Does anyone know of anything that might be causing this. I've spent two days so far trying to find a cause. The ODBC connection SDB1 is using SQL Native Client.

MyConn.Open "DSN=SDB1;Initial Catalog=database","username","password"

Set MyCommand.ActiveConnection=MyConn
MyCommand.CommandType = adCmdStoredProc
MyCommand.CommandText = "[owner].[procname]"
Set MyParam=MyCommand.CreateParameter("in_ip",adVarChar,adParamInput,10)
MyCommand.Parameters.Append MyParam
Set MyResult=MyCommand.CreateParameter("ip_country",adVarChar,adParamOutput,50)
MyCommand.Parameters.Append MyResult

MyCommand.Execute

Can anyone shed any light on this?

For anybody elses reference, I have found the cause of this.

The database user had connect permission but nothing else. This meant the user could connect to the database but not select/update or delete. Giving it the necessary rights solved the problem.

However, why this was causing IIS to run at 100% is still a mystery.

Regards,

Mark.

sql

Quick Q: regarding static dataAccess class and concurrency

Im new to SQL and DB in general.
Ive been experimenting and right now I have a 1 static dataAccess class that handles every query to the DB.

Q: Do Static classes such as a static dataAccess class need to be threadsafe as there is only one instance of these within the entire application.See the second bullet here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpgenref/html/cpconThreadingDesignGuidelines.asp