I am trying to use the full-text indexes with Sql Server 2005, and wanted to
index pdf files. I have downloaded and installed the Adobe PDF iFilter from
Adobe, and have tested it using the iFiltTst on a directory with PDF files.
It does index the pdf files.
I then moved the files into a sql server table into a varbinary(max) field.
I re-popualted the index, but it is not being indexed. Also, when I use the
"sp_help_fulltext_extensions" command, the ".pdf" extension does not appear.
Any suggestions on how to get this to work?
Steve
issue the following
sp_fulltext_service 'verify_signature', 0
sp_fulltext_service 'load_os_resources',1
bounce sql server and it should work (provided you have the PDF iFilter
installed).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Steven Paplanus" <sapaplanus@.hotmail.com> wrote in message
news:u0j55Zc8EHA.208@.TK2MSFTNGP12.phx.gbl...
> I am trying to use the full-text indexes with Sql Server 2005, and wanted
to
> index pdf files. I have downloaded and installed the Adobe PDF iFilter
from
> Adobe, and have tested it using the iFiltTst on a directory with PDF
files.
> It does index the pdf files.
> I then moved the files into a sql server table into a varbinary(max)
field.
> I re-popualted the index, but it is not being indexed. Also, when I use
the
> "sp_help_fulltext_extensions" command, the ".pdf" extension does not
appear.
> Any suggestions on how to get this to work?
> Steve
>
|||This will work, but beware that making this change makes your SQL instance a
little less secure.
Be sure to read the documentation for these flags so you understand the
risk.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:efcPGQt8EHA.3700@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> issue the following
> sp_fulltext_service 'verify_signature', 0
> sp_fulltext_service 'load_os_resources',1
> bounce sql server and it should work (provided you have the PDF iFilter
> installed).
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Steven Paplanus" <sapaplanus@.hotmail.com> wrote in message
> news:u0j55Zc8EHA.208@.TK2MSFTNGP12.phx.gbl...
wanted
> to
> from
> files.
> field.
> the
> appear.
>
Showing posts with label indexes. Show all posts
Showing posts with label indexes. Show all posts
Sunday, March 11, 2012
Thursday, March 8, 2012
Adding new index to existing table in Merge Replication
I see from other posts that sp_addscriptexec is recommended for
updating/creating indexes. It isn't clear to me if I can use Enterprise
Manager to go into the table on the publisher and add additional indexes on
a table and then have the added indexes automatically populate to the
subscribers.
Additionally, BOL says that an index can contain upto 16 fields but they
recommend upto three. Does that mean I should create many indexes with only
upto three fields in each index? Help or references on this matter would
be greatly appreciated.
WB
The index can be added at the publisher as per usual. It won't get
automatically propagated to the subscribers and that is what
sp_addscriptexec is for. If I just have one or two subscribers then I tend
to create the index by hand, but if you have lots, then it is a way or being
sure they all receive the changes, especially if they are pull
subscriptions.
As for the size, this seems a general question to do with indexes. Have a
look for clustered, nonclustered and covering in BOL. Indexes are sorthed on
the first column, then for duplicates of the first column they are sorted on
the second and so on, so 2 indexes with 3 columns is not the same as one
index with 6 columns. An index with 6 columns will be 'wide' and therefore
slow, but on the other hand it might have been created as a covering index
for a particluar long-running query. This is a really big subject and I'm
sure there are loads of resources on it apart from BOL.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
updating/creating indexes. It isn't clear to me if I can use Enterprise
Manager to go into the table on the publisher and add additional indexes on
a table and then have the added indexes automatically populate to the
subscribers.
Additionally, BOL says that an index can contain upto 16 fields but they
recommend upto three. Does that mean I should create many indexes with only
upto three fields in each index? Help or references on this matter would
be greatly appreciated.
WB
The index can be added at the publisher as per usual. It won't get
automatically propagated to the subscribers and that is what
sp_addscriptexec is for. If I just have one or two subscribers then I tend
to create the index by hand, but if you have lots, then it is a way or being
sure they all receive the changes, especially if they are pull
subscriptions.
As for the size, this seems a general question to do with indexes. Have a
look for clustered, nonclustered and covering in BOL. Indexes are sorthed on
the first column, then for duplicates of the first column they are sorted on
the second and so on, so 2 indexes with 3 columns is not the same as one
index with 6 columns. An index with 6 columns will be 'wide' and therefore
slow, but on the other hand it might have been created as a covering index
for a particluar long-running query. This is a really big subject and I'm
sure there are loads of resources on it apart from BOL.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Saturday, February 25, 2012
Adding indexes to tables
I have a database on Server A and i copy the database over to Server B. But since the data on the database is transformed (I add new columsn to eacht able in the db) during the transfer, I simply transfer the data and nothing else.
Once the transfer is complete I want to add all the indexes on each table in the original db. How would i do that?Why don't you transfer the entire tables, with indexes, and then add your columns?
blindman|||Originally posted by blindman
Why don't you transfer the entire tables, with indexes, and then add your columns?
blindman
Because the indexes i want to add must include the columns i'm adding.|||Originally posted by vmlal
Once the transfer is complete I want to add all the indexes on each table in the original db. How would i do that?
Well then your statement isn't true..they're new indexes and yo need to build them...create a sql script modify them and run...
don't know of an automated way...|||How can the indexes from the tables in the original DB include the columns you are adding? That makes no sense.
You are going to need to write a script that adds whatever indexes you want, and then run the script after you modify your unindexed tables.
blindman|||Yeah i have to add new indexes incl the new columns but all the other indexes excl. the new columns need to be added. i have a scripts to add the new indexes.
Sum of the existing indexes have to be dropped before the new indexes can be added. But is there a way to aleast get the old indexes back?|||If you can come up with a logical way to tell SQL Server what indexes you want to keep and what indexes you don't, then you MAY be able to do this programmatically. Otherwise, SQL Server doesn't have a clue what you want to do.
Write a script. It is the best way to handle this.
blindman
Once the transfer is complete I want to add all the indexes on each table in the original db. How would i do that?Why don't you transfer the entire tables, with indexes, and then add your columns?
blindman|||Originally posted by blindman
Why don't you transfer the entire tables, with indexes, and then add your columns?
blindman
Because the indexes i want to add must include the columns i'm adding.|||Originally posted by vmlal
Once the transfer is complete I want to add all the indexes on each table in the original db. How would i do that?
Well then your statement isn't true..they're new indexes and yo need to build them...create a sql script modify them and run...
don't know of an automated way...|||How can the indexes from the tables in the original DB include the columns you are adding? That makes no sense.
You are going to need to write a script that adds whatever indexes you want, and then run the script after you modify your unindexed tables.
blindman|||Yeah i have to add new indexes incl the new columns but all the other indexes excl. the new columns need to be added. i have a scripts to add the new indexes.
Sum of the existing indexes have to be dropped before the new indexes can be added. But is there a way to aleast get the old indexes back?|||If you can come up with a logical way to tell SQL Server what indexes you want to keep and what indexes you don't, then you MAY be able to do this programmatically. Otherwise, SQL Server doesn't have a clue what you want to do.
Write a script. It is the best way to handle this.
blindman
Adding Indexes to Table
I'm looking for information on how to add indexes to a table in a SQL Server 2000 Database, why add them etc? Any source of good information on the web regarding this?
Hi,
I've been looking for this kind of info too. I have a feeling, and I think I have read somewhere, that the indexing is more or less automatic in SqlServer 2000 and that's why one can't find out how to do it. However, someone else would need to verify this...
Pettrer
|||...indexes to a table ... why add them?
Lookup speed. Imagine using a dictionary that wasn't in alphabetical order...
|||And how could i use Enterprise Manager to add them to a table?|||Hi again,
As I suspected, these are being set automatically for me (in SqlServer Management Studio Express) and are displayed in the "column properties" section. I guess the same goes for Enterprise manager.
Pettrer
|||
pettrer:
Hi again,
As I suspected, these are being set automatically for me (in SqlServer Management Studio Express) and are displayed in the "column properties" section. I guess the same goes for Enterprise manager.
Pettrer
I suspect this is a non clustered index. I was looking at how to explicitly set an index based on the execution plan.
|||Hi,
To create index, you will need to use CREATE INDEX statement.
The syntax can be found from
http://msdn2.microsoft.com/en-us/library/ms188783.aspx
Thursday, February 9, 2012
Adding addition indexes on the subscriber tables
We have snapshot tables which are regenerated each night. Is there a way to
create additional indexes on the subscriber side since the vendor won't allow
us to create the indexes on the publisher?
Tom,
you could use a post-snapshot script for this.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||other than the excellent advice Paul has given you, you can use
sp_addscriptexec to create them on all subscribers deployed through a UNC.
Keep in mind that you want to minimize the indexes on your subscriber(s) to
improve performance of your replication solution.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uSx7mh6PFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Tom,
> you could use a post-snapshot script for this.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Sp_addscriptexec works for transactional and merge, but not snapshot
Rgds,
Paul
|||stop making me look bad, Paul. I am perfectly capable of doing that on my
own.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
create additional indexes on the subscriber side since the vendor won't allow
us to create the indexes on the publisher?
Tom,
you could use a post-snapshot script for this.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||other than the excellent advice Paul has given you, you can use
sp_addscriptexec to create them on all subscribers deployed through a UNC.
Keep in mind that you want to minimize the indexes on your subscriber(s) to
improve performance of your replication solution.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uSx7mh6PFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Tom,
> you could use a post-snapshot script for this.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Sp_addscriptexec works for transactional and merge, but not snapshot
Rgds,
Paul
|||stop making me look bad, Paul. I am perfectly capable of doing that on my
own.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
Subscribe to:
Posts (Atom)