Distributed Queries in SQL 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Distributed Queries in SQL 2000

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-07-19 : 21:08:25

Rob Mitchell writes "I hope you can shed light on this one.

I've been working with this new feature in SQL 7 now in SQL 2000. The ability to connect to other relational data from within SQL server to query that linked server against it's data. You can combine a query to the linked server with a query to a table in SQL, a very powerful and useful tool.

I've set Index Server as the linked server. My index server catalogs the filesystem I specify (which is my website). I am able to call from an ASP page with the following query and it works fine.

DIM searchstring
searchstring = Request("SearchString")

'create the SQL query string
strSQL = "SELECT * FROM OPENQUERY(FileSystem," & _
"'SELECT vPath, Create, Size, Filename, Characterization, DocTitle, Rank " & _
"FROM SCOPE() " & _
"WHERE CONTAINS(Contents, '' " & """" & replace(searchstring,"'","''") & """" & "'')" & _
" ORDER BY Rank DESC' )"

'fill a recordset with the results

set RM_objRS = Server.CreateObject("ADODB.Recordset")
RM_objRS.ActiveConnection = "Provider=SQLOLEDB;user id=xxx;password=xxx;initial catalog=test1;data source=test"
RM_objRS.Source = strSQL
RM_objRS.CursorType = 1 '0-adOpenForwardOnly,1-adOpenKeyset,2-adOpenDynamic,3-adOpenStatic
RM_objRS.CursorLocation = 2 '2-Server Side
RM_objRS.LockType = 3 '1-adLockReadOnly,2-adLockPessimistic,3-adLockOptimisitc,4-adLockBatchOptimistic
RM_objRS.Open()

My issue is this, how come I cannot get the COUNT property of the recordset that I've created. I've tried to adjust my locktype and cursortype properties but no such luck.

I'm using this query to build a search engine for my site which I would like to offer recordset paging to my viewers.

I know this is a wiered one but I have got this query to work, all I need now is the number of records found propery to complete the script.

Thanks for your time, much appreciated.

Rob Mitchell"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources