Cannot use a CONTAINS or FREETEXT predicate.......

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

High Availability (2005)

Cannot use a CONTAINS or FREETEXT predicate.......

Author

Topic

methodology
Starting Member

31 Posts

Posted - 2006-07-20 : 08:36:54

Hi

Im trying to set up SQL replication between two sql 2005 boxes. Im a newbie so Im taking a tiny step forwsard, hitting a wall, Googling for half and hour and then progressing another miniscule amount.

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:

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 AND the name of the publication on BOTH the Master and Replica servers but no matter what I do or where I run it, I get:

Msg 14013, Level 16, State 1, Procedure sp_MSrepl_changepublication, Line 94

This database is not enabled for publication.

when surely it is???? - publication is setup and the snapshot is created....

Can someone dig me out of my pit of frustration? or point me in the direction of a step by step guide for complete dumba**es?...

Thanks is advance

Alastair Jones

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-20 : 08:40:57

Are you try to install replication or full text search?
It looks like a bit of both.

Is the replication working?
Have you populated full text indexes?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

methodology
Starting Member

31 Posts

Posted - 2006-07-20 : 08:50:38

to be completely blunt, I have no idea what Im doing! - my GOAL is to replicate a database on a server to another server for Disaster recovery compliance. if it looks like im doing both rep and ful text search, thats only because this is what Im working out from some serious googling...

I create a new publication on my Primary server, and it creates a snapshot, and all is fine and dandy. I create a subscription on my replica server which is also fine and dandy until such time as it tries to pull the snapshot over ie immediately after I finish setting up the subscription.

How do I populate the full text indexes? what will this do for me?

Excuse my complete ignorance

Thanks for your reply

Alastair

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-20 : 09:49:29

Which version are you on? I'm assuming v2000.

Ignore full text indexes - that's to do with full text search not replication.
Also forget about the contains and freetext clauses - that's to do with full text search too.

You need to set up a subscriber, distributer and publisher, define publications and subscriptions and set the thing running.
First of all the source database must be bulk logged or full - i.e. not use the simple recovery model.

After that you are probably better off using enterprise manager to set things up.
Have a look in bol (books online) for replcation. It explains how to set things up and what to consider (start with the section on architecture).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

methodology
Starting Member

31 Posts

Posted - 2006-07-20 : 09:57:49

Hi

Im on sql 2005 workgroup

I have setup a subscriber, distributer and publisher, I have defined publications and subscriptions and I have set the thing running - only it doesnt work for the errors I mention below. I use enterprise manager to set things up.

the online help doesnt really address the specific errors that I am getting

Thanks though.

"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.

methodology
Starting Member

31 Posts

Posted - 2006-07-20 : 10:16:20

Specifically I currently need to know how to get

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

Working without giving the error:

Msg 14013, Level 16, State 1, Procedure sp_MSrepl_changepublication, Line 94

This database is not enabled for publication.

so that I can take the next tiny little step towards my goal.

"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-20 : 10:28:26

You need to enable the database for publication - i.e. define it as a publisher.
Have a look at sp_replicationdboption

I have some scripts to set up replication somewhere but can't find them at the moment unfortunately.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

methodology
Starting Member

31 Posts

Posted - 2006-07-20 : 10:36:16

so even though I have setup a publication of that database in 'local publications', i STILL need to use ANOTHER command ro enable it for replication?

"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources