How to access system tables from ASP Query

[DigitalBuilder]

SELECT [table_name] FROM [INFORMATION_SCHEMA].[Tables]

1. Works in the query manager
2. In an asp Recordset with the SA account it returns -1
3. I also want to use it without SA, its on a commercial web host facility

Any ideas?

 

[bslintx]

how are you opening the sql atatement? it may be read attributes that aren't set correctly...if you're using after the sql statement rs.open sql, conn then open with the attributes rs.open sql, conn, 3, 3

 

[DigitalBuilder]

Well i know it opens other queries, does it need more than just read permissions?

 

[bslintx]

can't do much w/o code..please post if still having trouble-thanks

 

[DigitalBuilder]

thanks for helping
<%
Set DT =Query("SELECT [table_name] FROM [INFORMATION_SCHEMA].[Tables] WHERE [Table_Name] LIKE '%TT_SF_Arr_%'")
Set DT =Query("ListTables")
Redim DTA(DT.recordcount)
response.write dt.recordcount
For x = 1 to DT.recordcount
DTA(x) = "Drop Table "&DT("Table_name")
DT.movenext
next

<%

SQLProvider="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & Server.MapPath("../database/RentAFlat.mdb")

SQLProvider="Provider=SQLOLEDB;"&_
"Data Source=127.0.0.1;"&_
"Initial Catalog=DB_RentAFlat;"&_
"User Id=sa;"&_
"Password=REMOVED"

Function Query(queryStr)
'response.write queryStr
'response.end
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open SQLProvider
set adoRec = server.createobject("adodb.recordset")
adoRec.open queryStr, objConn, 3, 3
Set Query = adoRec
End Function
%>

 

[JamesLean]

So the problem is that the RecordCount property of the recordset is always coming back as -1? This is a problem with the cursor type/location of the recordset - I suggest you post in the ASP forum for help with this.

--James