[thelist] Weird SQL Timeout Message

Ken Schaefer Ken at adOpenStatic.com
Wed Apr 12 20:40:53 CDT 2006


Since you have solved your immediate issue, here is some other advice:

Stop implicitly creating ADO connection objects, and you'll dramatically
increase the scalability of your web application.

Explicitly create the ADO Connection object, and then use Set to assign it to
the recordset:

Set of.ActiveConnection = objConn


What you're doing might work for you in testing where you're the only user,
but will come back to bite you in the backside once you start deploying these
type of thing out to users.

Cheers
Ken

--
My IIS Blog: www.adOpenStatic.com/cs/blogs/ken
Tech.Ed Boston 2006 See you there: Everything the web administrator needs to
know about MOM 2005

:  -----Original Message-----
:  From: thelist-bounces at lists.evolt.org [mailto:thelist-
:  bounces at lists.evolt.org] On Behalf Of Rob Smith
:  Sent: Wednesday, 12 April 2006 11:44 PM
:  To: thelist at lists.evolt.org
:  Subject: Re: [thelist] Weird SQL Timeout Message
:  
:  > Do you get the same error if you use the OLEDB Provider?
:  
:  No. A different one though:
:  
:  Code:
:  Set of = Server.CreateObject("ADODB.Recordset")
:  			of.ActiveConnection =
:  "Provider=sqloledb;Server=Server07;Database=CommerceSQL_Report;User
:  Id=sa;Password=********"
:  			of.CursorType = 0
:  			of.CursorLocation = 2
:  			of.LockType = 1
:  			of.Source = "WITH SubsCTE AS (SELECT dept_id,
:  dept_name, 0 AS lvl, CAST(1 AS VARBINARY(MAX)) AS sortpath,
:  dept_listing_rank,parent_id FROM dbo.lexjet_dept WHERE dept_id = 0 UNION
:  ALL  SELECT C.dept_id, C.dept_name, P.lvl + 1, P.sortpath +
:  CAST(ROW_NUMBER() OVER (PARTITION BY C.parent_id ORDER BY C.dept_name)
:  AS BINARY(4)),  C.dept_listing_rank, C.parent_id FROM SubsCTE AS P JOIN
:  dbo.lexjet_dept AS C ON  C.parent_id = P.dept_id)  SELECT TOP 100
:  PERCENT dept_id,  REPLICATE('&nbsp;</td><td>', lvl) + dept_name AS
:  dept_name, dept_listing_rank, parent_id, lvl FROM SubsCTE WHERE
:  dept_listing_rank > 0 ORDER BY ROW_NUMBER() OVER (ORDER BY sortpath);"
:  			'response.write of.Source & "<br><BR>"
:  			of.Open
:  
:  Error:
:  Error Type:
:  Microsoft OLE DB Provider for SQL Server (0x80040E14)
:  Incorrect syntax near the keyword 'WITH'.
:  /RunCatalog.asp, line 49
:  
:  Does the OLE DB provider allow for CTE's? (Common table expressions -
:  new to 2005)
:  
:  Rob Smith




More information about the thelist mailing list