Full text setup questions

My company has a web site that we bought several years ago, and we have had
to replace the machines that the site runs on. I have inherited the DBA role
for this system recently. The site search engine has not been working for
some time, and after digging through the asp code, I found this query:
sSql="SELECT a.article_id,srch.size,srch.rank "&_
"FROM OPENQUERY(FileSystem,"&_
"'SELECT FileName, Size,rank "&_
"FROM SCOPE('' """ & FormScope & """ '') "&_
"WHERE CONTAINS(Contents,''" & sSearch & "'') order by rank desc,write
desc') srch,articles a"
if NOT isCMS Then
sSql=sSql & ",columns c"
End If
sSql=sSql & " where cast(a.article_id as varchar(15))+'.htm'=srch.filename"
if NOT isCMS Then sSql=sSql & " AND a.status_id in (4,5) AND
c.column_id=a.column_id
So, after some searching, I discovered that this was using the Full-Text
Search function in SQL Server to search through the content news articles
under the wwwroot folder on the web server. What I'm having a hard time
doing now is creating a linked server in SQL Server for the folder on the web
server that actually seems to work. I also haven't found enough
documentation on what needs to be set up on both servers for this to work.
What I have done so far may be incorrect, but I have set up a linked server
like so:
sp_addlinkedserver @.server = 'FileSystem'
, @.srvproduct = 'Indexing Service'
, [ @.provider = 'MSIDXS'
, [ @.datasrc = 'FormScope'
, [ @.location = '\\devserver\wwwroot\content\production\'
, [ @.catalog = 'Web'
I have created a Full-Text catalog on the SQL Server called 'Web' and a
virtual directory on the IIS server called 'Web' that points to the devserver
folder above. When I run the search from the web site, I get this error:
An error occurred while processing your query: [OLE/DB provider returned
message: There is no catalog. ]
So I'm guessing that the catalog has not been populated with the content
from the web server, but I have no idea how to do that, or even if I'm
missing one or more steps. I'm scouring the documentation I can find but no
luck so far. Any ideas?
Karl Wiggins
This is not SQL FTS. This is using Indexing Services. You need to create a
catalog called web using the IS MMC (go to a command prompt) and type
ciadv.msc to administer it. To create the catalog in the IS MMC go to
action, new and catalog.
Then when your catalog is created right click on it and select properties
and tracking. In the WWW Server drop down box select your web server.
It also looks like you are indexing your NNTP server. Is this true?
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Karl" <kwiggins@.randallpub.com.(nospam)> wrote in message
news:BD830BC0-F393-4FBE-A1DC-EB92E2C0CE83@.microsoft.com...
> My company has a web site that we bought several years ago, and we have
had
> to replace the machines that the site runs on. I have inherited the DBA
role
> for this system recently. The site search engine has not been working for
> some time, and after digging through the asp code, I found this query:
> sSql="SELECT a.article_id,srch.size,srch.rank "&_
> "FROM OPENQUERY(FileSystem,"&_
> "'SELECT FileName, Size,rank "&_
> "FROM SCOPE('' """ & FormScope & """ '') "&_
> "WHERE CONTAINS(Contents,''" & sSearch & "'') order by rank desc,write
> desc') srch,articles a"
> if NOT isCMS Then
> sSql=sSql & ",columns c"
> End If
> sSql=sSql & " where cast(a.article_id as
varchar(15))+'.htm'=srch.filename"
> if NOT isCMS Then sSql=sSql & " AND a.status_id in (4,5) AND
> c.column_id=a.column_id
> So, after some searching, I discovered that this was using the Full-Text
> Search function in SQL Server to search through the content news articles
> under the wwwroot folder on the web server. What I'm having a hard time
> doing now is creating a linked server in SQL Server for the folder on the
web
> server that actually seems to work. I also haven't found enough
> documentation on what needs to be set up on both servers for this to work.
> What I have done so far may be incorrect, but I have set up a linked
server
> like so:
> sp_addlinkedserver @.server = 'FileSystem'
> , @.srvproduct = 'Indexing Service'
> , [ @.provider = 'MSIDXS'
> , [ @.datasrc = 'FormScope'
> , [ @.location = '\\devserver\wwwroot\content\production\'
> , [ @.catalog = 'Web'
> I have created a Full-Text catalog on the SQL Server called 'Web' and a
> virtual directory on the IIS server called 'Web' that points to the
devserver
> folder above. When I run the search from the web site, I get this error:
> An error occurred while processing your query: [OLE/DB provider returned
> message: There is no catalog. ]
> So I'm guessing that the catalog has not been populated with the content
> from the web server, but I have no idea how to do that, or even if I'm
> missing one or more steps. I'm scouring the documentation I can find but
no
> luck so far. Any ideas?
> --
> Karl Wiggins
|||Well FTS still has to be set up on the SQL Server doesn't it?
No, we aren't using NNTP, we are indexing articles in our proprietary
content-management system.
"Hilary Cotter" wrote:

> This is not SQL FTS. This is using Indexing Services. You need to create a
> catalog called web using the IS MMC (go to a command prompt) and type
> ciadv.msc to administer it. To create the catalog in the IS MMC go to
> action, new and catalog.
> Then when your catalog is created right click on it and select properties
> and tracking. In the WWW Server drop down box select your web server.
> It also looks like you are indexing your NNTP server. Is this true?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
>
> "Karl" <kwiggins@.randallpub.com.(nospam)> wrote in message
> news:BD830BC0-F393-4FBE-A1DC-EB92E2C0CE83@.microsoft.com...
> had
> role
> varchar(15))+'.htm'=srch.filename"
> web
> server
> devserver
> no
>
>
|||No you don't need SQL FTS - at least it doesn't appear you need it with the
query you have supplied.
If you don't believe me try creating a catalog called web using Ciadv.msc
and see if you still get the no catalog error.
"Karl" <kwiggins@.randallpub.com.(nospam)> wrote in message
news:F2B03DDE-C4EC-4BFF-A27C-DCC50933BA4C@.microsoft.com...[vbcol=seagreen]
> Well FTS still has to be set up on the SQL Server doesn't it?
> No, we aren't using NNTP, we are indexing articles in our proprietary
> content-management system.
> "Hilary Cotter" wrote:
|||Karl,
To clarify a couple of things... First of all, this is SQL Server T-SQL
code, just not the specific T-SQL CONTAINS implementation. The key
difference can be found in the "FROM SCOPE" spec used with OPENQUERY &
FileSystem. Normal T-SQL CONTAINS is use in a WHERE clause, such as the
below:
select * from pubs_info WHERE CONTAINS(*,'"books"')
The key error "[OLE/DB provider returned message: There is no catalog. ]"
indicates an incorrectly configured or missing Indexing Service Catalog in
the sp_addlinkedserver definition.
Regards,
John
"Karl" <kwiggins@.randallpub.com.(nospam)> wrote in message
news:F2B03DDE-C4EC-4BFF-A27C-DCC50933BA4C@.microsoft.com...[vbcol=seagreen]
> Well FTS still has to be set up on the SQL Server doesn't it?
> No, we aren't using NNTP, we are indexing articles in our proprietary
> content-management system.
> "Hilary Cotter" wrote:
a[vbcol=seagreen]
properties[vbcol=seagreen]
have[vbcol=seagreen]
DBA[vbcol=seagreen]
for[vbcol=seagreen]
Full-Text[vbcol=seagreen]
articles[vbcol=seagreen]
time[vbcol=seagreen]
the[vbcol=seagreen]
work.[vbcol=seagreen]
a[vbcol=seagreen]
error:[vbcol=seagreen]
returned[vbcol=seagreen]
content[vbcol=seagreen]
but[vbcol=seagreen]
|||Okay, I'm getting this error now:
An error occurred while processing your query: [OLE/DB provider returned
message: Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done.]
I set up a Indexing Services catalog called FormScope on the web server that
points to the content folder. My concern is that I don't have the linked
server on the SQL Server side set up correctly. My T-SQL code to add the
linked server looks like this:
sp_addlinkedserver @.server = 'FileSystem'
, @.srvproduct = 'Indexing Service'
, @.provider = 'MSIDXS'
, @.location = '\\DevApps03\devwwwroot\content\siteproduction\'
, @.catalog = 'FormScope'
Is this a valid location? What am I missing here?
"John Kane" wrote:

> Karl,
> To clarify a couple of things... First of all, this is SQL Server T-SQL
> code, just not the specific T-SQL CONTAINS implementation. The key
> difference can be found in the "FROM SCOPE" spec used with OPENQUERY &
> FileSystem. Normal T-SQL CONTAINS is use in a WHERE clause, such as the
> below:
> select * from pubs_info WHERE CONTAINS(*,'"books"')
> The key error "[OLE/DB provider returned message: There is no catalog. ]"
> indicates an incorrectly configured or missing Indexing Service Catalog in
> the sp_addlinkedserver definition.
> Regards,
> John
>
> "Karl" <kwiggins@.randallpub.com.(nospam)> wrote in message
> news:F2B03DDE-C4EC-4BFF-A27C-DCC50933BA4C@.microsoft.com...
> a
> properties
> have
> DBA
> for
> Full-Text
> articles
> time
> the
> work.
> a
> error:
> returned
> content
> but
>
>
|||Karl,
I think that the location spec in your sp_addlinkedserver is either
incorrect (UNC or machine_name) or not necessary when used with Indexing
Service (MSIDXS) provider. While I don't have a remote server to test this
type of configuration, I've used the following sp_addlinkedserver to define
a Linked Server to my Indexing Service Catalog on my primary server:
EXEC sp_addlinkedserver
@.server = 'lsIndexServer', -- Name
@.srvproduct = 'Index Server', -- product name of the OLE DB data source
@.provider = 'MSIDXS', -- Indexing Servics (IS) OLE DB Provider
@.datasrc = 'IS_DDrive' -- IS Catalog
go
Note, if this a Remote Indexing Service, i.e., an Indexing Service Catalog
that is not on the same server as your SQL Server, you can access it via the
following SCOPE Syntax:
Remote_Serve_rName.Remote_Server_IS_Catalogs_Name. .SCOPE(), for example:
SELECT *
FROM OPENQUERY( lsIndexServer,
'SELECT Path, Filename FROM JTKWin2003.IS_DDrive..SCOPE()
WHERE CONTAINS( ''john'' )
AND CONTAINS( ''kane'' )' ) AS jtk
Note, JTKWin2003 is my primary server name.
Thanks,
John
"Karl" <kwiggins@.randallpub.com.(nospam)> wrote in message
news:F093E09E-D972-4B4E-9503-38B0DC198EE2@.microsoft.com...
> Okay, I'm getting this error now:
> An error occurred while processing your query: [OLE/DB provider returned
> message: Multiple-step OLE DB operation generated errors. Check each OLE
DB
> status value, if available. No work was done.]
> I set up a Indexing Services catalog called FormScope on the web server
that[vbcol=seagreen]
> points to the content folder. My concern is that I don't have the linked
> server on the SQL Server side set up correctly. My T-SQL code to add the
> linked server looks like this:
> sp_addlinkedserver @.server = 'FileSystem'
> , @.srvproduct = 'Indexing Service'
> , @.provider = 'MSIDXS'
> , @.location = '\\DevApps03\devwwwroot\content\siteproduction\'
> , @.catalog = 'FormScope'
> Is this a valid location? What am I missing here?
> "John Kane" wrote:
catalog. ]"[vbcol=seagreen]
in[vbcol=seagreen]
create[vbcol=seagreen]
type[vbcol=seagreen]
to[vbcol=seagreen]
server.[vbcol=seagreen]
the[vbcol=seagreen]
working[vbcol=seagreen]
query:[vbcol=seagreen]
desc,write[vbcol=seagreen]
hard[vbcol=seagreen]
on[vbcol=seagreen]
to[vbcol=seagreen]
linked[vbcol=seagreen]
and[vbcol=seagreen]
the[vbcol=seagreen]
I'm[vbcol=seagreen]
find[vbcol=seagreen]