Combining full text search results with index server/service

I have a solutions database that I'm setting up Full text search on. Part of
the "solutions" is a huge folder of attachments on the lan.
I've done both FTS alone and also Index server alone.
Are there any whitepapers or good websites that talk about combining the
two? I'd like to conduct the searches via sql server - ideally expanding the
full text index to include the content of the files on the lan.
- Jack
Please refer to the above post.
There is no white paper per se focusing on this. However you might want to
check out this paper which does touch on it.
http://msdn.microsoft.com/library/de...filedatats.asp
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"jack" <jack@.discussions.microsoft.com> wrote in message
news:10938D81-5E92-483F-ABC1-208DDF331112@.microsoft.com...
> I have a solutions database that I'm setting up Full text search on. Part
of
> the "solutions" is a huge folder of attachments on the lan.
> I've done both FTS alone and also Index server alone.
> Are there any whitepapers or good websites that talk about combining the
> two? I'd like to conduct the searches via sql server - ideally expanding
the
> full text index to include the content of the files on the lan.
> - Jack

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
>

Combined Index not using in SQL 7.0 SP4

Table -- Survey_invites
Primary key Clustered index on (survey_id,email_id).
Query 1
select * from survey_invites where survey_id='003' -- by default Index not
used ( need to give hint to make use of index)
with hint it takes 1 sec v/s 3 min without hint !!!
Query 2
select * from survey_invites where survey_id='003' and email_id='nnn' -- by
default Index used
But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
used.
Is this a known problem in SQL 7.0 ? any help appreciated .?
Thanks BinuThe optimizer changes with each release. Generally speaking, fewer than
30-5% of the rows must be returned for a non-clustered index to be used...
Clustered indexes are almost always useful...Make sure index statistics are
up to date, and see what percentage of rows are returned by each query.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Binu Abraham" <abrahambinu@.verizon.net> wrote in message
news:OaKGlso1EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index
not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu
>|||Binu,
The fact SQL-Server 2000 doest a better job does not mean that
SQL-Server 7.0 has "a problem", or even worse "a known problem"!
You did not specify the data type of the survey_id column. Make sure you
use the same data type for the column definition and any literal you
compare it to. For your query, survey_id should be defined as char or
varchar.
If it is not (for example it is defined as int), then data type
conversion may prevent the usage of an index.
Especially in your case. The relevant index is clustered. If the data
type is correct, the clustered index will definitely be seeked!
Hope this helps,
Gert-Jan
Binu Abraham wrote:
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' -- by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binusqlsql

Combined Index not using in SQL 7.0 SP4

Table -- Survey_invites
Primary key Clustered index on (survey_id,email_id).
Query 1
select * from survey_invites where survey_id='003' -- by default Index not
used ( need to give hint to make use of index)
with hint it takes 1 sec v/s 3 min without hint !!!
Query 2
select * from survey_invites where survey_id='003' and email_id='nnn' -- by
default Index used
But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
used.
Is this a known problem in SQL 7.0 ? any help appreciated .?
Thanks Binu
The optimizer changes with each release. Generally speaking, fewer than
30-5% of the rows must be returned for a non-clustered index to be used...
Clustered indexes are almost always useful...Make sure index statistics are
up to date, and see what percentage of rows are returned by each query.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Binu Abraham" <abrahambinu@.verizon.net> wrote in message
news:OaKGlso1EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index
not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu
>
|||Binu,
The fact SQL-Server 2000 doest a better job does not mean that
SQL-Server 7.0 has "a problem", or even worse "a known problem"!
You did not specify the data type of the survey_id column. Make sure you
use the same data type for the column definition and any literal you
compare it to. For your query, survey_id should be defined as char or
varchar.
If it is not (for example it is defined as int), then data type
conversion may prevent the usage of an index.
Especially in your case. The relevant index is clustered. If the data
type is correct, the clustered index will definitely be seeked!
Hope this helps,
Gert-Jan
Binu Abraham wrote:
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' -- by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu

Combined Index not using in SQL 7.0 SP4

Table -- Survey_invites
Primary key Clustered index on (survey_id,email_id).
Query 1
select * from survey_invites where survey_id='003' -- by default Index not
used ( need to give hint to make use of index)
with hint it takes 1 sec v/s 3 min without hint !!!
Query 2
select * from survey_invites where survey_id='003' and email_id='nnn' -- by
default Index used
But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
used.
Is this a known problem in SQL 7.0 ? any help appreciated .?
Thanks BinuThe optimizer changes with each release. Generally speaking, fewer than
30-5% of the rows must be returned for a non-clustered index to be used...
Clustered indexes are almost always useful...Make sure index statistics are
up to date, and see what percentage of rows are returned by each query.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Binu Abraham" <abrahambinu@.verizon.net> wrote in message
news:OaKGlso1EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index
not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu
>|||Binu,
The fact SQL-Server 2000 doest a better job does not mean that
SQL-Server 7.0 has "a problem", or even worse "a known problem"!
You did not specify the data type of the survey_id column. Make sure you
use the same data type for the column definition and any literal you
compare it to. For your query, survey_id should be defined as char or
varchar.
If it is not (for example it is defined as int), then data type
conversion may prevent the usage of an index.
Especially in your case. The relevant index is clustered. If the data
type is correct, the clustered index will definitely be seeked!
Hope this helps,
Gert-Jan
Binu Abraham wrote:
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index no
t
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu

columns in indexes

How are these columns stored? If I am learning correctly, the added non-key columns in the index are not part of the index limit? If I have a large amount of data that I cannot fit onto an index page, what's being done. Just a pointer to the data that's isolated on it's own page? I guess this would be less overheard than performing an index scan with a pointer to the non-key column (I don't know, I'm asking) and no pointer would be better insert performane.

I looked in the BOL and couldn't find how this stuff is stored. Can you help - does my question even make sense?

Just wanted to add what I found.

According to MS documents are a form of Oracle's Index Organized Tables. Does this mean then that the entire table is an index?

Can you help me understand IOTs?

From http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlorcle.mspx:

Table and Index Storage Parameters

With Microsoft SQL Server, using RAID usually simplifies the placement of database objects. A SQL Server clustered index is integrated into the structure of the table, like an Oracle index-organized table.

|||Does this mean then that the entire table is an index?

Yes. When you create a clustered index on a table, all the data in the table is placed in the data pages of the index (at the leaf level). You can read more about this in SQL Server 2005 Books Online in the topic Clustered Index Structures.

You can also create nonclustered indexes. These indexes contain the index key values and row locators that point to the storage location of the table data. See the topic Nonclustered Index Structures in Books Online.

Regards,|||I believe you are referring to composite index key. Here is from BOL

"Up to 16 columns can be combined into a single composite index key. All the columns in a composite index key must be in the same table or view. The maximum allowable size of the combined index values is 900 bytes. For more information about variable type columns in composite indexes, see the Remarks section.

Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index"

So your data will fit into index page.

thanks

columns in full text query

Hi
Is there a system query that tells me which columns are a fulltext index for
a particular catalog?
Cheers
James
you could try sp_help_fulltext_columns in a full text enabled database which
will tell you all the tables and the columns in these tables which are being
full text indexed.
Or you could use sp_help_fulltext_tables_cursor and pass it the catalog name
and then iterate the results set as illustrated below. In the below example
the catalog name is test.
USE pubs
GO
DECLARE @.mycursor CURSOR
EXEC sp_help_fulltext_tables_cursor @.mycursor OUTPUT, 'test'
FETCH NEXT FROM @.mycursor
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
FETCH NEXT FROM @.mycursor
END
CLOSE @.mycursor
DEALLOCATE @.mycursor
GO
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%23WqhRpGsEHA.3712@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a system query that tells me which columns are a fulltext index
for
> a particular catalog?
> Cheers
> James
>

columns in full text query

Hi
Is there a system query that tells me which columns are a fulltext index for
a particular catalog?
Cheers
JamesFrom the BOL:
sp_help_fulltext_columns
Returns the columns designated for full-text indexing.
Rick Sawtell
MCT, MCSD, MCDBA
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:uZCdpjGsEHA.1272@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a system query that tells me which columns are a fulltext index
for
> a particular catalog?
> Cheers
> James
>

columns in full text query

Hi
Is there a system query that tells me which columns are a fulltext index for
a particular catalog?
Cheers
JamesFrom the BOL:
sp_help_fulltext_columns
Returns the columns designated for full-text indexing.
Rick Sawtell
MCT, MCSD, MCDBA
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:uZCdpjGsEHA.1272@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a system query that tells me which columns are a fulltext index
for
> a particular catalog?
> Cheers
> James
>

Column index

Hi there:

Is there any way to retrieve the column index based on its name? I tried using the ColumnCollection property of the Table object, but it is not a "real" collection, so the IndexOf["MyColumnName"] doesn't exist.

I have the Database, Table, ColumnCollection and Column objects available, is there any other way I can retrieve a column's index in the table?

Thank you

Maybe this code will help - it's not exactly a look up, but it'll get you to the info pretty quickly.

Dim colTbl As TableCollection
Dim tbl As Table
Dim colIdx As IndexCollection
Dim idx As Index
db = New Database(srv, "AdventureWorks")
colTbl = db.Tables
For Each tbl In colTbl
colIdx = tbl.Indexes
For Each idx In colIdx
Console.WriteLine(idx.Name)
Next
Next

|||

Hi, Allen, thank you for your reply.

While that code would work to retrieve all indexes names in a table, my problem was retrieving the position of any column in a table based on its name. Unfortunate choice of names (index), but the code I was looking for (and doesn't work) is something like:

CollumnCollection collumnColl = table.Columns;

int columnPos = columnColl.IndexOf("MyColumnName");

It seems to me that your code would properly retrieve all indexes in a table, not necessarily all column positions, no?

Thanks again.

|||

If you create a variable of type Column, say colThisOne, you can populate it by the following statement:

colThisOne = table.Columns("MyColumnName");

Does that help? It doesn't give you the order number of the column in the table, but relational theory says that the column order doesn't matter. If it does, the best I can tell you at this point is that colThisOne.ID may have the value you're looking for.

|||

Column.ID, eh? Hmm, haven't thought that it would have a meaningful value (apart from being unique). It is an int, indeed, so it may work.

I can access the column by name, however I am trying to dynamically populate a list of properties, so the column position (while indeed irrelevant for all intents and purposes) is important for my solution. I am already working on alternative approaches, so I may not need it, but this is not a bad suggestion at all, I will try it and let you know.

Thank you!

Collecting data with profiler

I'm running profiler and collect data which I'll use later for index tuning
wizard.
I'm collecting eventClass,SPID and text Data in profiler. Because
application use procedures, text data in profiler looks like:
exec e_prikazNarIzdelka 'I0202','HRK'
exec e_prikazIzdPoNar 'I0202','EEK',NULL
and so on.
Is it usefull for index tuning wizard?
Or text data should be actual select, insert, or update statements which are
inside procedures?
If so, how can I collect that statements in profiler instead of executing
stored procedures statements?
Thank you,
SimonTheres is an option where you can tell that SQL Server will use the traces
for further use, should should use that, because additional metadata is
stored then. Furtheron you should trace the STMT Event, Transaction, Scans
and further on. A list of useful data can be found here:
http://blog.transactsql.com/2005_01_01_archive.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"simon" wrote:

> I'm running profiler and collect data which I'll use later for index tunin
g
> wizard.
> I'm collecting eventClass,SPID and text Data in profiler. Because
> application use procedures, text data in profiler looks like:
> exec e_prikazNarIzdelka 'I0202','HRK'
> exec e_prikazIzdPoNar 'I0202','EEK',NULL
> and so on.
> Is it usefull for index tuning wizard?
> Or text data should be actual select, insert, or update statements which a
re
> inside procedures?
> If so, how can I collect that statements in profiler instead of executing
> stored procedures statements?
> Thank you,
> Simon
>
>|||Hi Jens,
thank you for your answer.
So, that means that "exec e_prikazNarIzdelka 'I0202','HRK'" is not usefull
for index tuning wizard?
It will not go into procedure e_prikazNarIzdelka and look, which
select,update and insert statements are inside that procedure?
I read somewhere that eventClass and text Data should be enough for index
tuning wizard.
Statement was:" Don't capture more in your profiler trace than you need. The
only events and data columns required by the index tuning wizard include the
SQL:BatchCompleted and the RPC:completed events in the TSQL category and
the eventClass and Text data columns."
So I put only that into my profiler trace.
Regards,
Simon
"Jens Smeyer" <JensSmeyer@.discussions.microsoft.com> wrote in message
news:4A526DCC-3167-4150-A349-CA8D3AE59367@.microsoft.com...
> Theres is an option where you can tell that SQL Server will use the traces
> for further use, should should use that, because additional metadata is
> stored then. Furtheron you should trace the STMT Event, Transaction, Scans
> and further on. A list of useful data can be found here:
> http://blog.transactsql.com/2005_01_01_archive.html
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "simon" wrote:
>