Re: Cannot use a CONTAINS or FREETEXT predicate on table...

Hi

thanks for the post

I run

sp_changepublication 'pub name, 'sync_method', 'native'

and I get

Msg 14013, Level 16, State 1, Procedure sp_MSrepl_changepublication, Line 94
This database is not enabled for publication.

.....which makes me want to cry. again.

Alastair Jones



"Raymond Mak [MSFT]" wrote:

Alastair, you are likely running into the issue described in the following
thread (with workarounds) which will be fixed in SP2:

http://groups.google.com/group/microsoft.public.sqlserver.replication/browse_thread/thread/42ac4e7d540d339a/b8650a7cc8fee3b4?lnk=gst&q=Raymond+Mak&rnum=18#b8650a7cc8fee3b4

-Raymond
"Methodology" <Methodology@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EEE6C511-C64E-4856-ADCA-239829B45C94@xxxxxxxxxxxxxxxx
it still doesnt work.

i create a new db on the replica server, tick the box AND run the 'SP'
command.

i get the following failures:


1. Message
The replication agent has been successfully started. See the Replication
Monitor for more information.


2. Message
2006-07-20 21:34:35.878 Agent message code 7601. Cannot use a CONTAINS or
FREETEXT predicate on table or indexed view 'dbo.arc_bodies' because it is
not full-text indexed.
2006-07-20 21:34:35.941 Category:COMMAND
Source: Failed Command
Number:
Message: CREATE FUNCTION "dbo"."SearchBodyFTI"
(@paramid bigint, @hash bigint)
RETURNS @result TABLE ("id" int)

AS
BEGIN
DECLARE @condition nvarchar(2000)
SELECT @condition = param from _queryParams where
pid=@paramid

INSERT @result
select "id" from dbo.arc_bodies WHERE
(

2006-07-20 21:34:35.941 Category:NULL
Source: Microsoft SQL Native Client
Number: 7601
Message: Cannot use a CONTAINS or FREETEXT predicate on table or indexed
view 'dbo.arc_bodies' because it is not full-text indexed.


3. Date 7/20/2006 10:34:19 PM
Log Job History (METHODOLOGY9-Mailarchive-MARC
Pub-METHODOLOGY-AKJ-MailReplica-A7383BB3-237A-434A-A722-7C934D3F64D7)

Step ID 1
Server METHODOLOGY-AKJ
Job Name METHODOLOGY9-Mailarchive-MARC
Pub-METHODOLOGY-AKJ-MailReplica-A7383BB3-237A-434A-A722-7C934D3F64D7
Step Name Run agent.
Duration 00:00:16
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: <DOMAINADMIN>. The replication agent encountered a
failure. See the previous job step history message or Replication Monitor
for
more information. The step failed.

4. Date 7/20/2006 10:34:19 PM
Log Job History (METHODOLOGY9-Mailarchive-MARC
Pub-METHODOLOGY-AKJ-MailReplica-A7383BB3-237A-434A-A722-7C934D3F64D7)

Step ID 0
Server METHODOLOGY-AKJ
Job Name METHODOLOGY9-Mailarchive-MARC
Pub-METHODOLOGY-AKJ-MailReplica-A7383BB3-237A-434A-A722-7C934D3F64D7
Step Name (Job outcome)
Duration 00:00:16
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
The job failed. The Job was invoked by User sa. The last step to run was
step 1 (Run agent.).




and i wasnt logged in as SA anyway.

this is catastrophically frustrating.

What am I doing wrong? the table is enabled for full text indexing on the
database, in the publication and the target replica databse has got the
box
ticked.

I have been stuck here for a week.

Alastair Jones

"Hilary Cotter" wrote:

that's another way, also sp_fulltext_database 'enable'

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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



"Methodology" <Methodology@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:96EF302B-9C51-4BC1-A181-78F0B38F599D@xxxxxxxxxxxxxxxx
Thanks Hilary

I should have come here in the first place as opposed to posting on
every
forum on the planet.

(one final thing - to enable Full text on the replica dbase is the tick
box
under properties>files : correct?)

"Hilary Cotter" wrote:

Yes you are correct.

The only additional thing you need to do on the subscriber is run
sp_fulltext_database 'enable'. Everything else is done on the
publisher.
You
should not need to run sp_replicationdboption once you have configured
your
publication.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business
intelligence.

This posting is my own and doesn't necessarily represent
RelevantNoise's
positions, strategies or opinions.

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



"Methodology" <Methodology@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EE19B5CB-B736-4FFE-A415-EAEC015D4810@xxxxxxxxxxxxxxxx
ok

So

I setup a local publication on the master, and set the full text rep
to
true

I create a database on the replica server and set the same value to
true.

then I create the subscription and point it at the new dbase on the
replica
that I have just created and configured.

correct?

(I was trying to run the command on the publisher dabse with the
correct
dbase name though I understand that I may need to use the
sp_replicationdboption command as well???)

Thanks for your response Hilary.

Alastair Jones



"Hilary Cotter" wrote:

To get this working you have to enable the subscription database
for
full-text in advance of creating your subscription. Ensure that you
have
selected to replicated the full-text indexes.

From you error message it seems to indicate that you are attempting
to
modify a publication which does not exist, or running this in a
database
which is not publisher. Make sure you run it in the publication
database.

You can also make this modification through the GUI.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business
intelligence.

This posting is my own and doesn't necessarily represent
RelevantNoise's
positions, strategies or opinions.

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



"Methodology" <Methodology@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:CF47CAB5-26FE-47F6-8821-80047FFA134C@xxxxxxxxxxxxxxxx
Hi

Im trying to set up SQL replication between two SQL 2005
Workgroup
ed
boxes
- Its a Mail archive (created by GFI mail Archiver Program) that
I
am
trying
to replicate for Disaster Recovery Purposes.

Im a newbie so Im taking a tiny step forward, hitting a wall,
Googling
for
half and hour and then progressing another miniscule amount. I
cannot
stress
enough how little I know about SQL and how fast im having to
learn..

however, I cant get past my latest error.

I have enabled full test replcation on the database that I want
to
replicate
- I set the value to 'true' when I set up the snapshot. Then
after
an
error
and some googling, I ran

use <database>
go
exec sp_fulltext_database 'enable'
go

to apparently tell the replica server that I had infact turned on
Full
Text
Searching on the master and to stop being a nobbo and to just get
on
with
it.

Now im getting

"Cannot use a CONTAINS or FREETEXT predicate on table or indexed
view
'dbo.arc_bodies' because it is not full-text indexed."

I have googled and found the following solution:

sp_changepublication @publication = 'yyy'
, @property = 'sync_method'
, @value = 'native'
, @force_invalidate_snapshot = 1

which I run with no success. I substitue 'yyy' for the name of
the
database
.

Relevant Pages

  • Re: Cannot use a CONTAINS or FREETEXT predicate on table...
    ... command from the context of the database I wish to publish. ... This database is not enabled for publication. ... The replication agent has been successfully started. ... Source: Microsoft SQL Native Client ...
    (microsoft.public.sqlserver.replication)
  • Re: Subscriber Transaction Logs Too Large
    ... Its not so critical to shrink the db files or transaction logs in SQL 2005. ... Director of Text Mining and Database Strategy ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: Subscriber Transaction Logs Too Large
    ... mentioned not shrinking the file too much on SQL 2000. ... SQL 2005 (Server B). ... alter database databaseName ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: Subscriber Transaction Logs Too Large
    ... mentioned not shrinking the file too much on SQL 2000. ... SQL 2005 (Server B). ... alter database databaseName ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: Subscriber Transaction Logs Too Large
    ... mentioned not shrinking the file too much on SQL 2000. ... SQL 2005 (Server B). ... alter database databaseName ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
Loading