intermitent connection problem asp to sql2000

I have an asp page (w2003 IIS) which grabs a very small amount of data from
a table on our
sql server (sql2000).
Most of the time the page works fine, with no problems. However
intermitently it falls over with:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver]Timeout expired
/MIS/Searches/main_search.asp, line 8
this is the connection and query which brings back about 300 records:
Dim MM_uLiveDatabase_STRING
MM_uLiveDatabase_STRING = "dsn=database;uid=username;pwd=password
Dim getID
Dim getID_numRows
Set getID = Server.CreateObject("ADODB.Recordset")
getID.ActiveConnection = MM_uLiveDatabase_STRING
getID.Source = "select fltf_value from caps_users left join
caps_user_filters on uflt_for_user = user_id inner join caps_filter_fields
on fltf_filter_flt = uflt_filter_flt where user_ref like '" +
Replace(getID__theUser, "'", "''") + "'"
getID.CursorType = 0
getID.CursorLocation = 2
getID.LockType = 1
getID.Open()
THis is rapidly becoming a major problem! Anyone got any ideas?
john
You'd want to start by monitoring for locking, blocking
issues in SQL Server when you experience the timeouts. You
can monitor for these issues using sp_who2, sp_lock and
querying master..sysprocesses.
-Sue
On Tue, 1 Feb 2005 17:43:41 -0000, "John" <john@.home.com.uk>
wrote:

>I have an asp page (w2003 IIS) which grabs a very small amount of data from
>a table on our
>sql server (sql2000).
>Most of the time the page works fine, with no problems. However
>intermitently it falls over with:
>Microsoft OLE DB Provider for ODBC Drivers error '80004005'
>[Microsoft][ODBC SQL Server Driver]Timeout expired
>/MIS/Searches/main_search.asp, line 8
>
>this is the connection and query which brings back about 300 records:
>Dim MM_uLiveDatabase_STRING
>MM_uLiveDatabase_STRING = "dsn=database;uid=username;pwd=password
>Dim getID
>Dim getID_numRows
>Set getID = Server.CreateObject("ADODB.Recordset")
>getID.ActiveConnection = MM_uLiveDatabase_STRING
>getID.Source = "select fltf_value from caps_users left join
>caps_user_filters on uflt_for_user = user_id inner join caps_filter_fields
>on fltf_filter_flt = uflt_filter_flt where user_ref like '" +
>Replace(getID__theUser, "'", "''") + "'"
>getID.CursorType = 0
>getID.CursorLocation = 2
>getID.LockType = 1
>getID.Open()
>
>THis is rapidly becoming a major problem! Anyone got any ideas?
>john
>

intermitent connection problem asp to sql2000

I have an asp page (w2003 IIS) which grabs a very small amount of data from
a table on our
sql server (sql2000).
Most of the time the page works fine, with no problems. However
intermitently it falls over with:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver]Timeout expired
/MIS/Searches/main_search.asp, line 8
this is the connection and query which brings back about 300 records:
Dim MM_uLiveDatabase_STRING
MM_uLiveDatabase_STRING = "dsn=database;uid=username;pwd=password
Dim getID
Dim getID_numRows
Set getID = Server.CreateObject("ADODB.Recordset")
getID.ActiveConnection = MM_uLiveDatabase_STRING
getID.Source = "select fltf_value from caps_users left join
caps_user_filters on uflt_for_user = user_id inner join caps_filter_fields
on fltf_filter_flt = uflt_filter_flt where user_ref like '" +
Replace(getID__theUser, "'", "''") + "'"
getID.CursorType = 0
getID.CursorLocation = 2
getID.LockType = 1
getID.Open()
THis is rapidly becoming a major problem! Anyone got any ideas'
johnYou'd want to start by monitoring for locking, blocking
issues in SQL Server when you experience the timeouts. You
can monitor for these issues using sp_who2, sp_lock and
querying master..sysprocesses.
-Sue
On Tue, 1 Feb 2005 17:43:41 -0000, "John" <john@.home.com.uk>
wrote:

>I have an asp page (w2003 IIS) which grabs a very small amount of data from
>a table on our
>sql server (sql2000).
>Most of the time the page works fine, with no problems. However
>intermitently it falls over with:
>Microsoft OLE DB Provider for ODBC Drivers error '80004005'
>[Microsoft][ODBC SQL Server Driver]Timeout expired
>/MIS/Searches/main_search.asp, line 8
>
>this is the connection and query which brings back about 300 records:
>Dim MM_uLiveDatabase_STRING
>MM_uLiveDatabase_STRING = "dsn=database;uid=username;pwd=password
>Dim getID
>Dim getID_numRows
>Set getID = Server.CreateObject("ADODB.Recordset")
>getID.ActiveConnection = MM_uLiveDatabase_STRING
>getID.Source = "select fltf_value from caps_users left join
>caps_user_filters on uflt_for_user = user_id inner join caps_filter_fields
>on fltf_filter_flt = uflt_filter_flt where user_ref like '" +
>Replace(getID__theUser, "'", "''") + "'"
>getID.CursorType = 0
>getID.CursorLocation = 2
>getID.LockType = 1
>getID.Open()
>
>THis is rapidly becoming a major problem! Anyone got any ideas'
>john
>

Inter Database Security

I have a stored procedure db1.dbo.sp1
this Stored Procedure grabs data from another database. Say db2.dbo.tbl1

If i call the qry1.
how does the security from db2 come in to play? if at all?

For example, If I am SQL Server Authenticated, for DB1 but not for DB2 will the SP fail. etc.If you have security to db1 but not db2, and you run it as yourself, it will fail.

HTH|||Tnx for the info. I picked up some additional tips and was able to confirm your suggestion on our sql servers over here.

Is there anyway to use NT Authentication in the first db. Then from within a stored procedure (in the 1st database), access the 2nd database using the sa account?

I.E. pass a sa login request to the 2nd database.