[thelist] Weird SQL Timeout Message

[Rob Smith]

> 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
LexJet
rob.smith at lexjet.com
http://www.lexjet.com
(800)453-9538
(941)330-1210 Int'l
(941)330-1220 Fax
1680 Fruitville Road, 3rd Floor
Sarasota, FL 34236