Hi all,
I have a nvarchar(255) column on a tblKeyword table with many queries in a
verified FTS form. i.e-
"Microsoft Corporation"
("Hewelet Packard") OR HP
Google OR Froogle
Sun
I want to issue a combined FTS query that will gather all (*) information
from the indexed table, based on all existing queries in the tblKeyword
table.
Ie, something like:
SELECT * FROM myftstable
WHERE CONTAINS(*,[("Microsoft Corporation") OR (("Hewelet Packard") OR HP)
OR (Google OR Froogle) OR (Sun)])
Is it possible to do it dynamiclly in some sort? other then connecting all
strings and sending them all to the contains?
Thanks!
Guy,
Yes, there is a way to do this via a stored proc:
use pubs
go
-- DROP PROCEDURE usp_FTSearchPubsInfo
CREATE PROCEDURE usp_FTSearchPubsInfo ( @.vcSearchText varchar(7800))
AS
declare @.s as varchar (8000)
set @.s='select pub_id, pr_info from pub_info where
contains(pr_info,'+''''+@.vcSearchText+''''+')'
exec (@.s)
go
-- returns 2 rows
EXEC usp_FTSearchPubsInfo '("books" and "publisher")'
go
-- Using your example:
EXEC usp_FTSearchPubsInfo '("Microsoft Corporation" or ("Hewelet Packard" or
"HP") or ("Google" or "Froogle") or ("Sun"))'
Regards,
John
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:Oz8p5UXKEHA.3728@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have a nvarchar(255) column on a tblKeyword table with many queries in a
> verified FTS form. i.e-
> "Microsoft Corporation"
> ("Hewelet Packard") OR HP
> Google OR Froogle
> Sun
> I want to issue a combined FTS query that will gather all (*) information
> from the indexed table, based on all existing queries in the tblKeyword
> table.
> Ie, something like:
> SELECT * FROM myftstable
> WHERE CONTAINS(*,[("Microsoft Corporation") OR (("Hewelet Packard") OR HP)
> OR (Google OR Froogle) OR (Sun)])
> Is it possible to do it dynamiclly in some sort? other then connecting all
> strings and sending them all to the contains?
> Thanks!
>
|||Hi John,
I meant how to do it programatically, so that the queries exists in
tblKeyword will be populated automatically as a long (@.vcSearchText varchar)
Guy
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%23nWpPQYKEHA.1272@.tk2msftngp13.phx.gbl...
> Guy,
> Yes, there is a way to do this via a stored proc:
> use pubs
> go
> -- DROP PROCEDURE usp_FTSearchPubsInfo
> CREATE PROCEDURE usp_FTSearchPubsInfo ( @.vcSearchText varchar(7800))
> AS
> declare @.s as varchar (8000)
> set @.s='select pub_id, pr_info from pub_info where
> contains(pr_info,'+''''+@.vcSearchText+''''+')'
> exec (@.s)
> go
> -- returns 2 rows
> EXEC usp_FTSearchPubsInfo '("books" and "publisher")'
> go
> -- Using your example:
> EXEC usp_FTSearchPubsInfo '("Microsoft Corporation" or ("Hewelet Packard"
or[vbcol=seagreen]
> "HP") or ("Google" or "Froogle") or ("Sun"))'
> Regards,
> John
>
>
> "Guy Brom" <guy_brom@.yahoo.com> wrote in message
> news:Oz8p5UXKEHA.3728@.TK2MSFTNGP12.phx.gbl...
a[vbcol=seagreen]
information[vbcol=seagreen]
HP)[vbcol=seagreen]
all
>
|||Guy,
I'm not sure what you're asking for here... Could you provide some examples?
Are you looking for a client-side (IE-based) solution or a server-side
(T-SQL based) solution? If the former, you may want to checkout KB article
246800 (Q246800) "INF: Correctly Parsing Quotation Marks in FTS Queries" at:
http://support.microsoft.com//defaul...b;EN-US;246800
Regards,
John
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:OMfprgdKEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi John,
> I meant how to do it programatically, so that the queries exists in
> tblKeyword will be populated automatically as a long (@.vcSearchText
varchar)[vbcol=seagreen]
> Guy
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%23nWpPQYKEHA.1272@.tk2msftngp13.phx.gbl...
Packard"[vbcol=seagreen]
> or
in[vbcol=seagreen]
> a
> information
tblKeyword
> HP)
> all
>
|||John hi,
I need a server-side solution (T-SQL based) for connecting all of the
records appear in tblKeyword into 1 long string. Is it possible?
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OJ%238ruhKEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Guy,
> I'm not sure what you're asking for here... Could you provide some
examples?
> Are you looking for a client-side (IE-based) solution or a server-side
> (T-SQL based) solution? If the former, you may want to checkout KB article
> 246800 (Q246800) "INF: Correctly Parsing Quotation Marks in FTS Queries"
at:[vbcol=seagreen]
> http://support.microsoft.com//defaul...b;EN-US;246800
> Regards,
> John
>
> "Guy Brom" <guy_brom@.yahoo.com> wrote in message
> news:OMfprgdKEHA.1312@.TK2MSFTNGP12.phx.gbl...
> varchar)
> Packard"
queries[vbcol=seagreen]
> in
> tblKeyword
OR[vbcol=seagreen]
connecting
>
|||Hi Guy,
Ok. I looked back over your original posting and I didn't realize that you
had two tables - tblKeyword and myftstable - and that in affect you wanted
to "pass" the tblKeyword table values to CONTAINS search_condition clause.
I'm sure there are other ways of doing this, but for now, I've developed two
cursor based solutions - assuming I'm understanding your question properly:
The below examples use the database (pubs) and the FT-enabled table
(authors) and the table (keyword) is your tblKeyword table:
use pubs
go
create table keyword(kword varchar(50))
go
insert into keyword values ('white')
insert into keyword values ('("john" or "paul")')
insert into keyword values ('Yokomoto')
go
select * from keyword
go
-- Simple Cursor Fetch with a CONTAINS statement...
SET NOCOUNT ON
DECLARE keyword_cursor CURSOR FAST_FORWARD
FOR
select kword from keyword
OPEN keyword_cursor
DECLARE @.keyword varchar(50)
FETCH NEXT FROM keyword_cursor INTO @.keyword
WHILE (@.@.fetch_status <> -1)
BEGIN
select * from authors where contains(*,@.keyword)
FETCH NEXT FROM keyword_cursor INTO @.keyword
END
CLOSE keyword_cursor
DEALLOCATE keyword_cursor
SET NOCOUNT OFF
GO
-- Complex Cursor Fetch into a temp table and then select from it...
set nocount on
DECLARE keyword_cursor CURSOR FAST_FORWARD
FOR
select kword from keyword
CREATE TABLE #authors_PK (author_pk char(11))
OPEN keyword_cursor
DECLARE @.keyword varchar(50), @.author_pks char(11), @.sql nvarchar(600)
-- Fetch the first row in the cursor.
FETCH NEXT FROM keyword_cursor INTO @.keyword
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.sql = 'insert into #authors_PK (author_pk) select au_id from
authors where contains(*, ''' + @.keyword + ''')'
exec(@.sql)
FETCH NEXT FROM keyword_cursor INTO @.keyword
END
CLOSE keyword_cursor
DEALLOCATE keyword_cursor
select * from #authors_PK
drop table #authors_PK
go
-- Clean-up
drop table keyword
go
Let me know if this is what you're looking for.
Regards,
John
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:urOPK8jKEHA.3492@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> John hi,
> I need a server-side solution (T-SQL based) for connecting all of the
> records appear in tblKeyword into 1 long string. Is it possible?
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OJ%238ruhKEHA.1340@.TK2MSFTNGP12.phx.gbl...
> examples?
article[vbcol=seagreen]
> at:
varchar(7800))[vbcol=seagreen]
> queries
Packard")
> OR
> connecting
>
|||Exactly!!
Thank you John!
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uwMwhCnKEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi Guy,
> Ok. I looked back over your original posting and I didn't realize that you
> had two tables - tblKeyword and myftstable - and that in affect you wanted
> to "pass" the tblKeyword table values to CONTAINS search_condition clause.
> I'm sure there are other ways of doing this, but for now, I've developed
two
> cursor based solutions - assuming I'm understanding your question
properly:[vbcol=seagreen]
> The below examples use the database (pubs) and the FT-enabled table
> (authors) and the table (keyword) is your tblKeyword table:
> use pubs
> go
> create table keyword(kword varchar(50))
> go
> insert into keyword values ('white')
> insert into keyword values ('("john" or "paul")')
> insert into keyword values ('Yokomoto')
> go
> select * from keyword
> go
> -- Simple Cursor Fetch with a CONTAINS statement...
> SET NOCOUNT ON
> DECLARE keyword_cursor CURSOR FAST_FORWARD
> FOR
> select kword from keyword
> OPEN keyword_cursor
> DECLARE @.keyword varchar(50)
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> select * from authors where contains(*,@.keyword)
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> END
> CLOSE keyword_cursor
> DEALLOCATE keyword_cursor
> SET NOCOUNT OFF
> GO
> -- Complex Cursor Fetch into a temp table and then select from it...
> set nocount on
> DECLARE keyword_cursor CURSOR FAST_FORWARD
> FOR
> select kword from keyword
> CREATE TABLE #authors_PK (author_pk char(11))
> OPEN keyword_cursor
> DECLARE @.keyword varchar(50), @.author_pks char(11), @.sql nvarchar(600)
> -- Fetch the first row in the cursor.
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.sql = 'insert into #authors_PK (author_pk) select au_id from
> authors where contains(*, ''' + @.keyword + ''')'
> exec(@.sql)
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> END
> CLOSE keyword_cursor
> DEALLOCATE keyword_cursor
> select * from #authors_PK
> drop table #authors_PK
> go
> -- Clean-up
> drop table keyword
> go
> Let me know if this is what you're looking for.
> Regards,
> John
>
>
> "Guy Brom" <guy_brom@.yahoo.com> wrote in message
> news:urOPK8jKEHA.3492@.TK2MSFTNGP09.phx.gbl...
> article
Queries"
> varchar(7800))
> Packard")
>
Showing posts with label fts. Show all posts
Showing posts with label fts. Show all posts
Sunday, March 25, 2012
Combining FTS and Index Server
I have searched goolge groups for this, but I'm a little new at content
indexing, so I'm not sure exactly what I'm looking for.
We are building an intranet for a company, and they need a search engine
that can search both their library of uploaded documents (probably word and
PDF mainly), as well as the various database-driven content (news, events,
message boards). We will have the ability to search each of the areas
separately, but we also need to provide a "search all" option to scan
through both the physical files and the content stored in the database, and
provide combined results, ranked by relevance.
I'm familiar w/ FTS, as I have used this for a message board on another
site. But I am new at file indexing/searching. I have read about Index
Server, but have not seen too many examples of applications searching on
both files and database content at the same time.
I have read about storing the physical files in the database (as text), and
just using FTS to perform the searches. Is this the route I need to take?
If so, how do I go about extracting raw data from binary files such as Word
and PDF files for storing in the database?
Also, any "best practices" regarding search queries
(phrase/include/exclude), as well as how to calculate weighted relevance
(based on title, keywords, author, and body)?
Thanks in advance.
Jerad,
You can search Google Groups with the following query and find most of the
posts related to combining both SQL Server FTS and Indexing Service:
http://groups.google.com/groups?&q=openquery+MSIDXS Additionally, there are
both advantages to both approaches (storing all files in SQL Server vs.
storing only pointers in SQL Server and the files on the disk) and while
there have been many "religious wars" on this topic, I'd only advise you to
test in your environment, both approaches and determine what is best for
your application.
There are two approaches to this that you can use:
1. Use the Indexing Service OLEDB Provider ('MSIDXS') and define a Linked
Server and then use OpenQuery to query the IS from SQL Server, for example:
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
SELECT * FROM OPENQUERY( lsIndexServer,
'SELECT Path, Filename FROM IS_DDrive..SCOPE()
WHERE CONTAINS( ''john'' ) AND CONTAINS( ''kane'' )' ) AS jtk
See SQL Server 2000 BOL titles sp_addlinkedserver and "OLE DB Provider for
Microsoft Indexing Service" for more info.
2. If you have most or all of your content already stored in SQL Server
2000, you can use "Full-text Search" (FTS) and search on the content of MS
Word and other MS Office file formats via CONTAINS or FREETEXT. You will
need to store the documents in an image datatype and define a 'file
extenstion' column to identify the type of file, i.e., 'doc' for MS Word
documents, for example:
use northwind:
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT ("ba" weight (.2) )', 10) as A
where
A.[KEY] = e.EmployeeID
See SQL Server 2000 BOL titles "Filtering Supported File Types",
containstable or freetexttable.
Finally, you can also combine the two methods, per the below example:
use master
go
EXEC sp_addlinkedserver 'Monarch', '', 'MSIDXS', 'Web', NULL, NULL
EXEC sp_addlinkedsrvlogin 'Monarch', 'FALSE', NULL, 'abc', ''
go
-- MSIDXS combined or UNIONed with SQL FTS query...
select * from titles where contains(*, 'books')
union
select * from OpenQuery(Monarch,
'select Directory, FileName, size, Create, Write
from SCOPE() where CONTAINS(Contents,''Index'')> 0 ')
As for best practices, there are a few FTS rules in the Best Practices
Analyzer Tool for Microsoft SQL Server 2000 1.0 that can be downloaded from
Microsoft at
http://www.microsoft.com/downloads/d...isplaylang=en.
However, the rule covered here are primarly related to FT Catalog placement,
and recommendations not to use more than one CONTAINS or FREETEXT clause per
query. As for how to calculate weighted relevance, well that's a whole
separate chapter!
Regards,
John
"Jerad Rose" <no@.spam.com> wrote in message
news:OEhrnfv1EHA.1124@.tk2msftngp13.phx.gbl...
> I have searched goolge groups for this, but I'm a little new at content
> indexing, so I'm not sure exactly what I'm looking for.
> We are building an intranet for a company, and they need a search engine
> that can search both their library of uploaded documents (probably word
and
> PDF mainly), as well as the various database-driven content (news, events,
> message boards). We will have the ability to search each of the areas
> separately, but we also need to provide a "search all" option to scan
> through both the physical files and the content stored in the database,
and
> provide combined results, ranked by relevance.
> I'm familiar w/ FTS, as I have used this for a message board on another
> site. But I am new at file indexing/searching. I have read about Index
> Server, but have not seen too many examples of applications searching on
> both files and database content at the same time.
> I have read about storing the physical files in the database (as text),
and
> just using FTS to perform the searches. Is this the route I need to take?
> If so, how do I go about extracting raw data from binary files such as
Word
> and PDF files for storing in the database?
> Also, any "best practices" regarding search queries
> (phrase/include/exclude), as well as how to calculate weighted relevance
> (based on title, keywords, author, and body)?
> Thanks in advance.
>
|||Thank you John for your quick response.
At first glance, most of what you said is over my head. I did see similar
posts on other threads, but wasn't sure if it was relevant to what I am
trying to do. But I realize this will take some more research on my part,
and I think your tips will be a great starting point to get me going in the
right direction.
I meant to specify this also -- this intranet will probably never house more
than 10,000 or so documents, and probably will not exceed 2GB of total
storage. The database content will see similar numbers -- probably staying
under the 2GB mark. So performance *shouldn't* be much of an issue.
As I dig into this a little more, I may have other (more specific)
questions, which I'll post on this thread.
Thanks again for your help.
Jerad
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OMiQfwv1EHA.4000@.TK2MSFTNGP10.phx.gbl...
> Jerad,
> You can search Google Groups with the following query and find most of the
> posts related to combining both SQL Server FTS and Indexing Service:
> http://groups.google.com/groups?&q=openquery+MSIDXS Additionally, there
are
> both advantages to both approaches (storing all files in SQL Server vs.
> storing only pointers in SQL Server and the files on the disk) and while
> there have been many "religious wars" on this topic, I'd only advise you
to
> test in your environment, both approaches and determine what is best for
> your application.
>
> There are two approaches to this that you can use:
> 1. Use the Indexing Service OLEDB Provider ('MSIDXS') and define a Linked
> Server and then use OpenQuery to query the IS from SQL Server, for
example:
> 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
> SELECT * FROM OPENQUERY( lsIndexServer,
> 'SELECT Path, Filename FROM IS_DDrive..SCOPE()
> WHERE CONTAINS( ''john'' ) AND CONTAINS( ''kane'' )' ) AS jtk
> See SQL Server 2000 BOL titles sp_addlinkedserver and "OLE DB Provider for
> Microsoft Indexing Service" for more info.
> 2. If you have most or all of your content already stored in SQL Server
> 2000, you can use "Full-text Search" (FTS) and search on the content of MS
> Word and other MS Office file formats via CONTAINS or FREETEXT. You will
> need to store the documents in an image datatype and define a 'file
> extenstion' column to identify the type of file, i.e., 'doc' for MS Word
> documents, for example:
> use northwind:
> SELECT e.LastName, e.FirstName, e.Title, e.Notes
> from Employees AS e,
> containstable(Employees, Notes, 'ISABOUT ("ba" weight (.2) )', 10) as
A
> where
> A.[KEY] = e.EmployeeID
> See SQL Server 2000 BOL titles "Filtering Supported File Types",
> containstable or freetexttable.
> Finally, you can also combine the two methods, per the below example:
> use master
> go
> EXEC sp_addlinkedserver 'Monarch', '', 'MSIDXS', 'Web', NULL, NULL
> EXEC sp_addlinkedsrvlogin 'Monarch', 'FALSE', NULL, 'abc', ''
> go
> -- MSIDXS combined or UNIONed with SQL FTS query...
> select * from titles where contains(*, 'books')
> union
> select * from OpenQuery(Monarch,
> 'select Directory, FileName, size, Create, Write
> from SCOPE() where CONTAINS(Contents,''Index'')> 0 ')
> As for best practices, there are a few FTS rules in the Best Practices
> Analyzer Tool for Microsoft SQL Server 2000 1.0 that can be downloaded
from
> Microsoft at
>
http://www.microsoft.com/downloads/d...isplaylang=en.
> However, the rule covered here are primarly related to FT Catalog
placement,
> and recommendations not to use more than one CONTAINS or FREETEXT clause
per[vbcol=seagreen]
> query. As for how to calculate weighted relevance, well that's a whole
> separate chapter!
> Regards,
> John
>
> "Jerad Rose" <no@.spam.com> wrote in message
> news:OEhrnfv1EHA.1124@.tk2msftngp13.phx.gbl...
> and
events,[vbcol=seagreen]
> and
> and
take?
> Word
>
|||I think that Sharepoint Portal server is your best option. It will index
these diverse data sources and you can use the coerce function to weight
different properties in the overall rank calculation.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Jerad Rose" <no@.spam.com> wrote in message
news:OEhrnfv1EHA.1124@.tk2msftngp13.phx.gbl...
>I have searched goolge groups for this, but I'm a little new at content
> indexing, so I'm not sure exactly what I'm looking for.
> We are building an intranet for a company, and they need a search engine
> that can search both their library of uploaded documents (probably word
> and
> PDF mainly), as well as the various database-driven content (news, events,
> message boards). We will have the ability to search each of the areas
> separately, but we also need to provide a "search all" option to scan
> through both the physical files and the content stored in the database,
> and
> provide combined results, ranked by relevance.
> I'm familiar w/ FTS, as I have used this for a message board on another
> site. But I am new at file indexing/searching. I have read about Index
> Server, but have not seen too many examples of applications searching on
> both files and database content at the same time.
> I have read about storing the physical files in the database (as text),
> and
> just using FTS to perform the searches. Is this the route I need to take?
> If so, how do I go about extracting raw data from binary files such as
> Word
> and PDF files for storing in the database?
> Also, any "best practices" regarding search queries
> (phrase/include/exclude), as well as how to calculate weighted relevance
> (based on title, keywords, author, and body)?
> Thanks in advance.
>
|||Thanks Hilary.
I don't know if that's an investment my company's willing to make at this
point. And unfortunately, we're coming into this pretty late in the game,
so we're limited on time to learn and implement this.
I have been able to get Index Server going, but I'm not having any luck
connecting to it via a SQL linked server. I did as you suggested, John:
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 = 'TRH' -- IS Catalog
... but when I run this query:
SELECT *
FROM OPENQUERY( lsIndexServer,
'SELECT Path, Filename FROM JERAD.TRH..SCOPE()
WHERE CONTAINS( ''plan'' ) ) AS SearchTable
I get:
OLE DB provider 'MSIDXS' reported an error.
[OLE/DB provider returned message: Unspecified error]
[OLE/DB provider returned message: Invalid catalog name 'TRH'.
SQLSTATE=42000 ]
I googled both web and groups, and found several threads (some you responded
to) where people were having this problem, but never found a solution that
worked for me. It may be a permissions issue, but I'm not sure the steps I
need to take to narrow that out.
Thanks again to you both for your help.
Jerad
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e7dKB7w1EHA.2112@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> I think that Sharepoint Portal server is your best option. It will index
> these diverse data sources and you can use the coerce function to weight
> different properties in the overall rank calculation.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Jerad Rose" <no@.spam.com> wrote in message
> news:OEhrnfv1EHA.1124@.tk2msftngp13.phx.gbl...
events,[vbcol=seagreen]
take?
>
|||Does this work?
SELECT * FROM OPENQUERY( lsIndexServer, 'SELECT Path, Filename FROM SCOPE()
WHERE CONTAINS( ''plan'' ) ')
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jerad Rose" <no@.spam.com> wrote in message
news:OQ5$sVy1EHA.936@.TK2MSFTNGP12.phx.gbl...
> Thanks Hilary.
> I don't know if that's an investment my company's willing to make at this
> point. And unfortunately, we're coming into this pretty late in the game,
> so we're limited on time to learn and implement this.
> I have been able to get Index Server going, but I'm not having any luck
> connecting to it via a SQL linked server. I did as you suggested, John:
> 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 = 'TRH' -- IS Catalog
> ... but when I run this query:
> SELECT *
> FROM OPENQUERY( lsIndexServer,
> 'SELECT Path, Filename FROM JERAD.TRH..SCOPE()
> WHERE CONTAINS( ''plan'' ) ) AS SearchTable
> I get:
> OLE DB provider 'MSIDXS' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> [OLE/DB provider returned message: Invalid catalog name 'TRH'.
> SQLSTATE=42000 ]
> I googled both web and groups, and found several threads (some you
> responded
> to) where people were having this problem, but never found a solution that
> worked for me. It may be a permissions issue, but I'm not sure the steps
> I
> need to take to narrow that out.
> Thanks again to you both for your help.
> Jerad
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e7dKB7w1EHA.2112@.TK2MSFTNGP15.phx.gbl...
> events,
> take?
>
|||Jerad,
In my OpenQuery example "IS_DDrive..SCOPE()", IS_DDrive is a non-default IS
Catalog name on my server JTKWin2003 where both SQL Server 2000 and the
Indexing Service reside. In your example, is JERAD a local server or a
remote server, ie. a server with the IS Catalog separate from the server
where SQL Server 2000 is located. Both of the following SQL OpenQuery's
work on my local server:
SELECT * FROM OPENQUERY( lsIndexServer,
'SELECT Path, Filename FROM JTKWin2003.IS_DDrive..SCOPE()
WHERE CONTAINS( ''john'' ) AND CONTAINS( ''kane'' )' ) AS jtk
-- and
SELECT * FROM OPENQUERY( lsIndexServer,
'SELECT Path, Filename FROM IS_DDrive..SCOPE()
WHERE CONTAINS( ''john'' ) AND CONTAINS( ''kane'' )' ) AS jtk
It is also possible that this may be a permissions issue, and you may need
to use sp_addlinkedsrvlogin (using my datasource name) :
EXEC sp_addlinkedsrvlogin 'JTKWin2003_IS', 'FALSE', NULL, 'IS_DDrive', ''
Regards,
John
"Jerad Rose" <no@.spam.com> wrote in message
news:OQ5$sVy1EHA.936@.TK2MSFTNGP12.phx.gbl...
> Thanks Hilary.
> I don't know if that's an investment my company's willing to make at this
> point. And unfortunately, we're coming into this pretty late in the game,
> so we're limited on time to learn and implement this.
> I have been able to get Index Server going, but I'm not having any luck
> connecting to it via a SQL linked server. I did as you suggested, John:
> 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 = 'TRH' -- IS Catalog
> ... but when I run this query:
> SELECT *
> FROM OPENQUERY( lsIndexServer,
> 'SELECT Path, Filename FROM JERAD.TRH..SCOPE()
> WHERE CONTAINS( ''plan'' ) ) AS SearchTable
> I get:
> OLE DB provider 'MSIDXS' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> [OLE/DB provider returned message: Invalid catalog name 'TRH'.
> SQLSTATE=42000 ]
> I googled both web and groups, and found several threads (some you
responded
> to) where people were having this problem, but never found a solution that
> worked for me. It may be a permissions issue, but I'm not sure the steps
I[vbcol=seagreen]
> need to take to narrow that out.
> Thanks again to you both for your help.
> Jerad
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e7dKB7w1EHA.2112@.TK2MSFTNGP15.phx.gbl...
engine[vbcol=seagreen]
word[vbcol=seagreen]
> events,
database,[vbcol=seagreen]
another[vbcol=seagreen]
Index[vbcol=seagreen]
on[vbcol=seagreen]
text),[vbcol=seagreen]
> take?
relevance
>
|||Jerad,
A more detailed follow-up... Are there any more OLDEB errors, other than
Invalid catalog name 'TRH'? You may need to use sp_addlinkedsrvlogin along
with your sp_addlinkedserver, for example:
EXEC sp_addlinkedserver
@.server = 'lsIndexServer', -- Name
@.srvproduct = 'Index Server', -- product name of the OLE DB data source
@.provider = 'MSIDXS', -- Indexing Services (IS) OLE DB Provider
@.datasrc = 'IS_DDrive' -- IS Catalog
go
From BOL title "sp_addlinkedsrvlogin"
A. Connect all local logins to the linked server using their own user
credentials This example creates a mapping to ensure that all logins to the
local server connect through to the linked server Accounts using their own
user credentials.
EXEC sp_addlinkedsrvlogin 'Accounts'
Or
EXEC sp_addlinkedsrvlogin 'Accounts', 'true'
B. Connect all local logins to the linked server using a specified user and
password This example creates a mapping to ensure that all logins to the
local server connect through to the linked server Accounts using the same
login SQLUser and password Password.
EXEC sp_addlinkedsrvlogin 'Accounts', 'false', NULL, 'SQLUser', 'Password'
I'd also be interested in knowing what type of an account (DOMAIN\account or
System account [LocalSystem]?) you have the MSSQLServer service started
under where you are defining the Link Server.
Thanks,
John
"John Kane" <jt-kane@.comcast.net> wrote in message
news:Og1a#d11EHA.3120@.TK2MSFTNGP12.phx.gbl...
> Jerad,
> In my OpenQuery example "IS_DDrive..SCOPE()", IS_DDrive is a non-default
IS[vbcol=seagreen]
> Catalog name on my server JTKWin2003 where both SQL Server 2000 and the
> Indexing Service reside. In your example, is JERAD a local server or a
> remote server, ie. a server with the IS Catalog separate from the server
> where SQL Server 2000 is located. Both of the following SQL OpenQuery's
> work on my local server:
> SELECT * FROM OPENQUERY( lsIndexServer,
> 'SELECT Path, Filename FROM JTKWin2003.IS_DDrive..SCOPE()
> WHERE CONTAINS( ''john'' ) AND CONTAINS( ''kane'' )' ) AS jtk
> -- and
> SELECT * FROM OPENQUERY( lsIndexServer,
> 'SELECT Path, Filename FROM IS_DDrive..SCOPE()
> WHERE CONTAINS( ''john'' ) AND CONTAINS( ''kane'' )' ) AS jtk
> It is also possible that this may be a permissions issue, and you may need
> to use sp_addlinkedsrvlogin (using my datasource name) :
> EXEC sp_addlinkedsrvlogin 'JTKWin2003_IS', 'FALSE', NULL, 'IS_DDrive', ''
> Regards,
> John
>
> "Jerad Rose" <no@.spam.com> wrote in message
> news:OQ5$sVy1EHA.936@.TK2MSFTNGP12.phx.gbl...
this[vbcol=seagreen]
game,[vbcol=seagreen]
> responded
that[vbcol=seagreen]
steps[vbcol=seagreen]
> I
index[vbcol=seagreen]
weight[vbcol=seagreen]
content[vbcol=seagreen]
> engine
> word
areas[vbcol=seagreen]
scan[vbcol=seagreen]
> database,
> another
> Index
searching[vbcol=seagreen]
> on
> text),
as
> relevance
>
|||A couple more points about this. I built did the search application for
variety magazine, and I will be shortly embarking on a stint with one of the
major news services helping with their search services.
Both companies faced the same problems that you have - that of diverse data
sources. The solution I implemented at Variety was to push the content out
of the database and into the file system and let Indexing Services or Site
Server Search pick up the documents.
This decision was made as idq and ixsso (the com objects that allow you to
query Indexing Services) are much faster than msidxs, and there are a few
bugs in msidxs with pattern matching that made the decision to use msidxs a
poor one.
You will find that the performance you get using a linked server is not
optimal. You get far better performance if you use Indexing Service (free by
the away) on your content in the file system, than if you use a linked
server to Indexing Services and join the results set against your database.
Indexing performance with Indexing Services is faster as well.
Here is a link on how to take your content out of the database and render it
as html documents. Each html document is named after the primary key value,
so you can figure out which record the html document represents in your
database.
http://groups.google.com/groups?hl=e...rp1.dej a.com
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eSCqJw01EHA.304@.TK2MSFTNGP11.phx.gbl...
> Does this work?
> SELECT * FROM OPENQUERY( lsIndexServer, 'SELECT Path, Filename FROM
> SCOPE()
> WHERE CONTAINS( ''plan'' ) ')
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Jerad Rose" <no@.spam.com> wrote in message
> news:OQ5$sVy1EHA.936@.TK2MSFTNGP12.phx.gbl...
>
|||Thanks again to you both for your detailed responses.
Last night, I had to make the decision to abandon the idea of hitting my
Index Server via linked server. Our client was a little flexible, so we've
decided to return results separately. This means we can return search
results for the document files in one section, and search results from the
database content in another section. This will allow us to hit the Index
Server directly (I'm still using OLEDB to run queries, which, I assume, uses
MSIDXS), and then run searches on the database using FTS. As you both have
said, this should be better performance anyway. But Hilary, you said MSIDXS
was not the best choice for searching, because of various bugs. Will this
affect me now that I'm hitting the IS directly? If so, do you recommend I
consider one of the other two COM you suggested? As I said, since we're on
a tight schedule, I probably don't have enough time allocated to make a
major change, but if this is something simple, I would definitely consider
it.
Sorry I didn't specify, John, but just FYI, yes -- my Index Server is a
remote machine, which is what I think was causing my linked server problems.
The Index Service was running on the DB server under the
DOMAIN/Administrator account, which should've had full access to the Index
Server, which was running on my local machine. I'm sure if they were both
running on the same server, I could get it to work. But the document files
will have to be kept on the web server, separate from the database server.
Anyway, I've made decent headway, now that I decided to hit the Index Server
directly. This should get me going now.
Thank you both again for taking the time to respond. I have actually
learned quite a bit with this stuff, thanks to you two.
Jerad
"Jerad Rose" <no@.spam.com> wrote in message
news:OQ5$sVy1EHA.936@.TK2MSFTNGP12.phx.gbl...
> Thanks Hilary.
> I don't know if that's an investment my company's willing to make at this
> point. And unfortunately, we're coming into this pretty late in the game,
> so we're limited on time to learn and implement this.
> I have been able to get Index Server going, but I'm not having any luck
> connecting to it via a SQL linked server. I did as you suggested, John:
> 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 = 'TRH' -- IS Catalog
> ... but when I run this query:
> SELECT *
> FROM OPENQUERY( lsIndexServer,
> 'SELECT Path, Filename FROM JERAD.TRH..SCOPE()
> WHERE CONTAINS( ''plan'' ) ) AS SearchTable
> I get:
> OLE DB provider 'MSIDXS' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> [OLE/DB provider returned message: Invalid catalog name 'TRH'.
> SQLSTATE=42000 ]
> I googled both web and groups, and found several threads (some you
responded
> to) where people were having this problem, but never found a solution that
> worked for me. It may be a permissions issue, but I'm not sure the steps
I[vbcol=seagreen]
> need to take to narrow that out.
> Thanks again to you both for your help.
> Jerad
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e7dKB7w1EHA.2112@.TK2MSFTNGP15.phx.gbl...
engine[vbcol=seagreen]
word[vbcol=seagreen]
> events,
database,[vbcol=seagreen]
another[vbcol=seagreen]
Index[vbcol=seagreen]
on[vbcol=seagreen]
text),[vbcol=seagreen]
> take?
relevance
>
indexing, so I'm not sure exactly what I'm looking for.
We are building an intranet for a company, and they need a search engine
that can search both their library of uploaded documents (probably word and
PDF mainly), as well as the various database-driven content (news, events,
message boards). We will have the ability to search each of the areas
separately, but we also need to provide a "search all" option to scan
through both the physical files and the content stored in the database, and
provide combined results, ranked by relevance.
I'm familiar w/ FTS, as I have used this for a message board on another
site. But I am new at file indexing/searching. I have read about Index
Server, but have not seen too many examples of applications searching on
both files and database content at the same time.
I have read about storing the physical files in the database (as text), and
just using FTS to perform the searches. Is this the route I need to take?
If so, how do I go about extracting raw data from binary files such as Word
and PDF files for storing in the database?
Also, any "best practices" regarding search queries
(phrase/include/exclude), as well as how to calculate weighted relevance
(based on title, keywords, author, and body)?
Thanks in advance.
Jerad,
You can search Google Groups with the following query and find most of the
posts related to combining both SQL Server FTS and Indexing Service:
http://groups.google.com/groups?&q=openquery+MSIDXS Additionally, there are
both advantages to both approaches (storing all files in SQL Server vs.
storing only pointers in SQL Server and the files on the disk) and while
there have been many "religious wars" on this topic, I'd only advise you to
test in your environment, both approaches and determine what is best for
your application.
There are two approaches to this that you can use:
1. Use the Indexing Service OLEDB Provider ('MSIDXS') and define a Linked
Server and then use OpenQuery to query the IS from SQL Server, for example:
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
SELECT * FROM OPENQUERY( lsIndexServer,
'SELECT Path, Filename FROM IS_DDrive..SCOPE()
WHERE CONTAINS( ''john'' ) AND CONTAINS( ''kane'' )' ) AS jtk
See SQL Server 2000 BOL titles sp_addlinkedserver and "OLE DB Provider for
Microsoft Indexing Service" for more info.
2. If you have most or all of your content already stored in SQL Server
2000, you can use "Full-text Search" (FTS) and search on the content of MS
Word and other MS Office file formats via CONTAINS or FREETEXT. You will
need to store the documents in an image datatype and define a 'file
extenstion' column to identify the type of file, i.e., 'doc' for MS Word
documents, for example:
use northwind:
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT ("ba" weight (.2) )', 10) as A
where
A.[KEY] = e.EmployeeID
See SQL Server 2000 BOL titles "Filtering Supported File Types",
containstable or freetexttable.
Finally, you can also combine the two methods, per the below example:
use master
go
EXEC sp_addlinkedserver 'Monarch', '', 'MSIDXS', 'Web', NULL, NULL
EXEC sp_addlinkedsrvlogin 'Monarch', 'FALSE', NULL, 'abc', ''
go
-- MSIDXS combined or UNIONed with SQL FTS query...
select * from titles where contains(*, 'books')
union
select * from OpenQuery(Monarch,
'select Directory, FileName, size, Create, Write
from SCOPE() where CONTAINS(Contents,''Index'')> 0 ')
As for best practices, there are a few FTS rules in the Best Practices
Analyzer Tool for Microsoft SQL Server 2000 1.0 that can be downloaded from
Microsoft at
http://www.microsoft.com/downloads/d...isplaylang=en.
However, the rule covered here are primarly related to FT Catalog placement,
and recommendations not to use more than one CONTAINS or FREETEXT clause per
query. As for how to calculate weighted relevance, well that's a whole
separate chapter!
Regards,
John
"Jerad Rose" <no@.spam.com> wrote in message
news:OEhrnfv1EHA.1124@.tk2msftngp13.phx.gbl...
> I have searched goolge groups for this, but I'm a little new at content
> indexing, so I'm not sure exactly what I'm looking for.
> We are building an intranet for a company, and they need a search engine
> that can search both their library of uploaded documents (probably word
and
> PDF mainly), as well as the various database-driven content (news, events,
> message boards). We will have the ability to search each of the areas
> separately, but we also need to provide a "search all" option to scan
> through both the physical files and the content stored in the database,
and
> provide combined results, ranked by relevance.
> I'm familiar w/ FTS, as I have used this for a message board on another
> site. But I am new at file indexing/searching. I have read about Index
> Server, but have not seen too many examples of applications searching on
> both files and database content at the same time.
> I have read about storing the physical files in the database (as text),
and
> just using FTS to perform the searches. Is this the route I need to take?
> If so, how do I go about extracting raw data from binary files such as
Word
> and PDF files for storing in the database?
> Also, any "best practices" regarding search queries
> (phrase/include/exclude), as well as how to calculate weighted relevance
> (based on title, keywords, author, and body)?
> Thanks in advance.
>
|||Thank you John for your quick response.
At first glance, most of what you said is over my head. I did see similar
posts on other threads, but wasn't sure if it was relevant to what I am
trying to do. But I realize this will take some more research on my part,
and I think your tips will be a great starting point to get me going in the
right direction.
I meant to specify this also -- this intranet will probably never house more
than 10,000 or so documents, and probably will not exceed 2GB of total
storage. The database content will see similar numbers -- probably staying
under the 2GB mark. So performance *shouldn't* be much of an issue.
As I dig into this a little more, I may have other (more specific)
questions, which I'll post on this thread.
Thanks again for your help.
Jerad
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OMiQfwv1EHA.4000@.TK2MSFTNGP10.phx.gbl...
> Jerad,
> You can search Google Groups with the following query and find most of the
> posts related to combining both SQL Server FTS and Indexing Service:
> http://groups.google.com/groups?&q=openquery+MSIDXS Additionally, there
are
> both advantages to both approaches (storing all files in SQL Server vs.
> storing only pointers in SQL Server and the files on the disk) and while
> there have been many "religious wars" on this topic, I'd only advise you
to
> test in your environment, both approaches and determine what is best for
> your application.
>
> There are two approaches to this that you can use:
> 1. Use the Indexing Service OLEDB Provider ('MSIDXS') and define a Linked
> Server and then use OpenQuery to query the IS from SQL Server, for
example:
> 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
> SELECT * FROM OPENQUERY( lsIndexServer,
> 'SELECT Path, Filename FROM IS_DDrive..SCOPE()
> WHERE CONTAINS( ''john'' ) AND CONTAINS( ''kane'' )' ) AS jtk
> See SQL Server 2000 BOL titles sp_addlinkedserver and "OLE DB Provider for
> Microsoft Indexing Service" for more info.
> 2. If you have most or all of your content already stored in SQL Server
> 2000, you can use "Full-text Search" (FTS) and search on the content of MS
> Word and other MS Office file formats via CONTAINS or FREETEXT. You will
> need to store the documents in an image datatype and define a 'file
> extenstion' column to identify the type of file, i.e., 'doc' for MS Word
> documents, for example:
> use northwind:
> SELECT e.LastName, e.FirstName, e.Title, e.Notes
> from Employees AS e,
> containstable(Employees, Notes, 'ISABOUT ("ba" weight (.2) )', 10) as
A
> where
> A.[KEY] = e.EmployeeID
> See SQL Server 2000 BOL titles "Filtering Supported File Types",
> containstable or freetexttable.
> Finally, you can also combine the two methods, per the below example:
> use master
> go
> EXEC sp_addlinkedserver 'Monarch', '', 'MSIDXS', 'Web', NULL, NULL
> EXEC sp_addlinkedsrvlogin 'Monarch', 'FALSE', NULL, 'abc', ''
> go
> -- MSIDXS combined or UNIONed with SQL FTS query...
> select * from titles where contains(*, 'books')
> union
> select * from OpenQuery(Monarch,
> 'select Directory, FileName, size, Create, Write
> from SCOPE() where CONTAINS(Contents,''Index'')> 0 ')
> As for best practices, there are a few FTS rules in the Best Practices
> Analyzer Tool for Microsoft SQL Server 2000 1.0 that can be downloaded
from
> Microsoft at
>
http://www.microsoft.com/downloads/d...isplaylang=en.
> However, the rule covered here are primarly related to FT Catalog
placement,
> and recommendations not to use more than one CONTAINS or FREETEXT clause
per[vbcol=seagreen]
> query. As for how to calculate weighted relevance, well that's a whole
> separate chapter!
> Regards,
> John
>
> "Jerad Rose" <no@.spam.com> wrote in message
> news:OEhrnfv1EHA.1124@.tk2msftngp13.phx.gbl...
> and
events,[vbcol=seagreen]
> and
> and
take?
> Word
>
|||I think that Sharepoint Portal server is your best option. It will index
these diverse data sources and you can use the coerce function to weight
different properties in the overall rank calculation.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Jerad Rose" <no@.spam.com> wrote in message
news:OEhrnfv1EHA.1124@.tk2msftngp13.phx.gbl...
>I have searched goolge groups for this, but I'm a little new at content
> indexing, so I'm not sure exactly what I'm looking for.
> We are building an intranet for a company, and they need a search engine
> that can search both their library of uploaded documents (probably word
> and
> PDF mainly), as well as the various database-driven content (news, events,
> message boards). We will have the ability to search each of the areas
> separately, but we also need to provide a "search all" option to scan
> through both the physical files and the content stored in the database,
> and
> provide combined results, ranked by relevance.
> I'm familiar w/ FTS, as I have used this for a message board on another
> site. But I am new at file indexing/searching. I have read about Index
> Server, but have not seen too many examples of applications searching on
> both files and database content at the same time.
> I have read about storing the physical files in the database (as text),
> and
> just using FTS to perform the searches. Is this the route I need to take?
> If so, how do I go about extracting raw data from binary files such as
> Word
> and PDF files for storing in the database?
> Also, any "best practices" regarding search queries
> (phrase/include/exclude), as well as how to calculate weighted relevance
> (based on title, keywords, author, and body)?
> Thanks in advance.
>
|||Thanks Hilary.
I don't know if that's an investment my company's willing to make at this
point. And unfortunately, we're coming into this pretty late in the game,
so we're limited on time to learn and implement this.
I have been able to get Index Server going, but I'm not having any luck
connecting to it via a SQL linked server. I did as you suggested, John:
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 = 'TRH' -- IS Catalog
... but when I run this query:
SELECT *
FROM OPENQUERY( lsIndexServer,
'SELECT Path, Filename FROM JERAD.TRH..SCOPE()
WHERE CONTAINS( ''plan'' ) ) AS SearchTable
I get:
OLE DB provider 'MSIDXS' reported an error.
[OLE/DB provider returned message: Unspecified error]
[OLE/DB provider returned message: Invalid catalog name 'TRH'.
SQLSTATE=42000 ]
I googled both web and groups, and found several threads (some you responded
to) where people were having this problem, but never found a solution that
worked for me. It may be a permissions issue, but I'm not sure the steps I
need to take to narrow that out.
Thanks again to you both for your help.
Jerad
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e7dKB7w1EHA.2112@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> I think that Sharepoint Portal server is your best option. It will index
> these diverse data sources and you can use the coerce function to weight
> different properties in the overall rank calculation.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Jerad Rose" <no@.spam.com> wrote in message
> news:OEhrnfv1EHA.1124@.tk2msftngp13.phx.gbl...
events,[vbcol=seagreen]
take?
>
|||Does this work?
SELECT * FROM OPENQUERY( lsIndexServer, 'SELECT Path, Filename FROM SCOPE()
WHERE CONTAINS( ''plan'' ) ')
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jerad Rose" <no@.spam.com> wrote in message
news:OQ5$sVy1EHA.936@.TK2MSFTNGP12.phx.gbl...
> Thanks Hilary.
> I don't know if that's an investment my company's willing to make at this
> point. And unfortunately, we're coming into this pretty late in the game,
> so we're limited on time to learn and implement this.
> I have been able to get Index Server going, but I'm not having any luck
> connecting to it via a SQL linked server. I did as you suggested, John:
> 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 = 'TRH' -- IS Catalog
> ... but when I run this query:
> SELECT *
> FROM OPENQUERY( lsIndexServer,
> 'SELECT Path, Filename FROM JERAD.TRH..SCOPE()
> WHERE CONTAINS( ''plan'' ) ) AS SearchTable
> I get:
> OLE DB provider 'MSIDXS' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> [OLE/DB provider returned message: Invalid catalog name 'TRH'.
> SQLSTATE=42000 ]
> I googled both web and groups, and found several threads (some you
> responded
> to) where people were having this problem, but never found a solution that
> worked for me. It may be a permissions issue, but I'm not sure the steps
> I
> need to take to narrow that out.
> Thanks again to you both for your help.
> Jerad
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e7dKB7w1EHA.2112@.TK2MSFTNGP15.phx.gbl...
> events,
> take?
>
|||Jerad,
In my OpenQuery example "IS_DDrive..SCOPE()", IS_DDrive is a non-default IS
Catalog name on my server JTKWin2003 where both SQL Server 2000 and the
Indexing Service reside. In your example, is JERAD a local server or a
remote server, ie. a server with the IS Catalog separate from the server
where SQL Server 2000 is located. Both of the following SQL OpenQuery's
work on my local server:
SELECT * FROM OPENQUERY( lsIndexServer,
'SELECT Path, Filename FROM JTKWin2003.IS_DDrive..SCOPE()
WHERE CONTAINS( ''john'' ) AND CONTAINS( ''kane'' )' ) AS jtk
-- and
SELECT * FROM OPENQUERY( lsIndexServer,
'SELECT Path, Filename FROM IS_DDrive..SCOPE()
WHERE CONTAINS( ''john'' ) AND CONTAINS( ''kane'' )' ) AS jtk
It is also possible that this may be a permissions issue, and you may need
to use sp_addlinkedsrvlogin (using my datasource name) :
EXEC sp_addlinkedsrvlogin 'JTKWin2003_IS', 'FALSE', NULL, 'IS_DDrive', ''
Regards,
John
"Jerad Rose" <no@.spam.com> wrote in message
news:OQ5$sVy1EHA.936@.TK2MSFTNGP12.phx.gbl...
> Thanks Hilary.
> I don't know if that's an investment my company's willing to make at this
> point. And unfortunately, we're coming into this pretty late in the game,
> so we're limited on time to learn and implement this.
> I have been able to get Index Server going, but I'm not having any luck
> connecting to it via a SQL linked server. I did as you suggested, John:
> 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 = 'TRH' -- IS Catalog
> ... but when I run this query:
> SELECT *
> FROM OPENQUERY( lsIndexServer,
> 'SELECT Path, Filename FROM JERAD.TRH..SCOPE()
> WHERE CONTAINS( ''plan'' ) ) AS SearchTable
> I get:
> OLE DB provider 'MSIDXS' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> [OLE/DB provider returned message: Invalid catalog name 'TRH'.
> SQLSTATE=42000 ]
> I googled both web and groups, and found several threads (some you
responded
> to) where people were having this problem, but never found a solution that
> worked for me. It may be a permissions issue, but I'm not sure the steps
I[vbcol=seagreen]
> need to take to narrow that out.
> Thanks again to you both for your help.
> Jerad
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e7dKB7w1EHA.2112@.TK2MSFTNGP15.phx.gbl...
engine[vbcol=seagreen]
word[vbcol=seagreen]
> events,
database,[vbcol=seagreen]
another[vbcol=seagreen]
Index[vbcol=seagreen]
on[vbcol=seagreen]
text),[vbcol=seagreen]
> take?
relevance
>
|||Jerad,
A more detailed follow-up... Are there any more OLDEB errors, other than
Invalid catalog name 'TRH'? You may need to use sp_addlinkedsrvlogin along
with your sp_addlinkedserver, for example:
EXEC sp_addlinkedserver
@.server = 'lsIndexServer', -- Name
@.srvproduct = 'Index Server', -- product name of the OLE DB data source
@.provider = 'MSIDXS', -- Indexing Services (IS) OLE DB Provider
@.datasrc = 'IS_DDrive' -- IS Catalog
go
From BOL title "sp_addlinkedsrvlogin"
A. Connect all local logins to the linked server using their own user
credentials This example creates a mapping to ensure that all logins to the
local server connect through to the linked server Accounts using their own
user credentials.
EXEC sp_addlinkedsrvlogin 'Accounts'
Or
EXEC sp_addlinkedsrvlogin 'Accounts', 'true'
B. Connect all local logins to the linked server using a specified user and
password This example creates a mapping to ensure that all logins to the
local server connect through to the linked server Accounts using the same
login SQLUser and password Password.
EXEC sp_addlinkedsrvlogin 'Accounts', 'false', NULL, 'SQLUser', 'Password'
I'd also be interested in knowing what type of an account (DOMAIN\account or
System account [LocalSystem]?) you have the MSSQLServer service started
under where you are defining the Link Server.
Thanks,
John
"John Kane" <jt-kane@.comcast.net> wrote in message
news:Og1a#d11EHA.3120@.TK2MSFTNGP12.phx.gbl...
> Jerad,
> In my OpenQuery example "IS_DDrive..SCOPE()", IS_DDrive is a non-default
IS[vbcol=seagreen]
> Catalog name on my server JTKWin2003 where both SQL Server 2000 and the
> Indexing Service reside. In your example, is JERAD a local server or a
> remote server, ie. a server with the IS Catalog separate from the server
> where SQL Server 2000 is located. Both of the following SQL OpenQuery's
> work on my local server:
> SELECT * FROM OPENQUERY( lsIndexServer,
> 'SELECT Path, Filename FROM JTKWin2003.IS_DDrive..SCOPE()
> WHERE CONTAINS( ''john'' ) AND CONTAINS( ''kane'' )' ) AS jtk
> -- and
> SELECT * FROM OPENQUERY( lsIndexServer,
> 'SELECT Path, Filename FROM IS_DDrive..SCOPE()
> WHERE CONTAINS( ''john'' ) AND CONTAINS( ''kane'' )' ) AS jtk
> It is also possible that this may be a permissions issue, and you may need
> to use sp_addlinkedsrvlogin (using my datasource name) :
> EXEC sp_addlinkedsrvlogin 'JTKWin2003_IS', 'FALSE', NULL, 'IS_DDrive', ''
> Regards,
> John
>
> "Jerad Rose" <no@.spam.com> wrote in message
> news:OQ5$sVy1EHA.936@.TK2MSFTNGP12.phx.gbl...
this[vbcol=seagreen]
game,[vbcol=seagreen]
> responded
that[vbcol=seagreen]
steps[vbcol=seagreen]
> I
index[vbcol=seagreen]
weight[vbcol=seagreen]
content[vbcol=seagreen]
> engine
> word
areas[vbcol=seagreen]
scan[vbcol=seagreen]
> database,
> another
> Index
searching[vbcol=seagreen]
> on
> text),
as
> relevance
>
|||A couple more points about this. I built did the search application for
variety magazine, and I will be shortly embarking on a stint with one of the
major news services helping with their search services.
Both companies faced the same problems that you have - that of diverse data
sources. The solution I implemented at Variety was to push the content out
of the database and into the file system and let Indexing Services or Site
Server Search pick up the documents.
This decision was made as idq and ixsso (the com objects that allow you to
query Indexing Services) are much faster than msidxs, and there are a few
bugs in msidxs with pattern matching that made the decision to use msidxs a
poor one.
You will find that the performance you get using a linked server is not
optimal. You get far better performance if you use Indexing Service (free by
the away) on your content in the file system, than if you use a linked
server to Indexing Services and join the results set against your database.
Indexing performance with Indexing Services is faster as well.
Here is a link on how to take your content out of the database and render it
as html documents. Each html document is named after the primary key value,
so you can figure out which record the html document represents in your
database.
http://groups.google.com/groups?hl=e...rp1.dej a.com
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eSCqJw01EHA.304@.TK2MSFTNGP11.phx.gbl...
> Does this work?
> SELECT * FROM OPENQUERY( lsIndexServer, 'SELECT Path, Filename FROM
> SCOPE()
> WHERE CONTAINS( ''plan'' ) ')
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Jerad Rose" <no@.spam.com> wrote in message
> news:OQ5$sVy1EHA.936@.TK2MSFTNGP12.phx.gbl...
>
|||Thanks again to you both for your detailed responses.
Last night, I had to make the decision to abandon the idea of hitting my
Index Server via linked server. Our client was a little flexible, so we've
decided to return results separately. This means we can return search
results for the document files in one section, and search results from the
database content in another section. This will allow us to hit the Index
Server directly (I'm still using OLEDB to run queries, which, I assume, uses
MSIDXS), and then run searches on the database using FTS. As you both have
said, this should be better performance anyway. But Hilary, you said MSIDXS
was not the best choice for searching, because of various bugs. Will this
affect me now that I'm hitting the IS directly? If so, do you recommend I
consider one of the other two COM you suggested? As I said, since we're on
a tight schedule, I probably don't have enough time allocated to make a
major change, but if this is something simple, I would definitely consider
it.
Sorry I didn't specify, John, but just FYI, yes -- my Index Server is a
remote machine, which is what I think was causing my linked server problems.
The Index Service was running on the DB server under the
DOMAIN/Administrator account, which should've had full access to the Index
Server, which was running on my local machine. I'm sure if they were both
running on the same server, I could get it to work. But the document files
will have to be kept on the web server, separate from the database server.
Anyway, I've made decent headway, now that I decided to hit the Index Server
directly. This should get me going now.
Thank you both again for taking the time to respond. I have actually
learned quite a bit with this stuff, thanks to you two.
Jerad
"Jerad Rose" <no@.spam.com> wrote in message
news:OQ5$sVy1EHA.936@.TK2MSFTNGP12.phx.gbl...
> Thanks Hilary.
> I don't know if that's an investment my company's willing to make at this
> point. And unfortunately, we're coming into this pretty late in the game,
> so we're limited on time to learn and implement this.
> I have been able to get Index Server going, but I'm not having any luck
> connecting to it via a SQL linked server. I did as you suggested, John:
> 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 = 'TRH' -- IS Catalog
> ... but when I run this query:
> SELECT *
> FROM OPENQUERY( lsIndexServer,
> 'SELECT Path, Filename FROM JERAD.TRH..SCOPE()
> WHERE CONTAINS( ''plan'' ) ) AS SearchTable
> I get:
> OLE DB provider 'MSIDXS' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> [OLE/DB provider returned message: Invalid catalog name 'TRH'.
> SQLSTATE=42000 ]
> I googled both web and groups, and found several threads (some you
responded
> to) where people were having this problem, but never found a solution that
> worked for me. It may be a permissions issue, but I'm not sure the steps
I[vbcol=seagreen]
> need to take to narrow that out.
> Thanks again to you both for your help.
> Jerad
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e7dKB7w1EHA.2112@.TK2MSFTNGP15.phx.gbl...
engine[vbcol=seagreen]
word[vbcol=seagreen]
> events,
database,[vbcol=seagreen]
another[vbcol=seagreen]
Index[vbcol=seagreen]
on[vbcol=seagreen]
text),[vbcol=seagreen]
> take?
relevance
>
Subscribe to:
Posts (Atom)