Package fails "Cannot use a CONTAINS or FREETEXT predicate"

I have a database (SQL Server 2000 Enterprise Ed.) with a single table
(Products), which has 3 columns set for Full-Text-Indexing. This
database is working fine. All queries are done through Stored Procs,
and works like a charm.
I tried to copy the database to another database (SQL Server 2000
Personal Ed.) which is on my PC. I have created a DTS package on the
Originating server. This DTS package used to work fine for a long
time, until sometime back few weeks ago I introduced FTI, and now it
does not work.
I get this error when executing DTS package:
"[ODBC SQL Server Driver][SQL Server]Cannot use a CONTAINS or FREETEXT
predicate on table 'Products' because it is not full-text indexed."
This table is full text on the originating and also on the destination
database. (I created one on the destination, thought perhaps that's why
it wouldn't work)
Does anyone know how to fix this? Thanks.
Is your DTS package dropping the table each time? It should merely delete
the data.
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
<bikmann@.gmail.com> wrote in message
news:1108690034.091208.194630@.g14g2000cwa.googlegr oups.com...
> I have a database (SQL Server 2000 Enterprise Ed.) with a single table
> (Products), which has 3 columns set for Full-Text-Indexing. This
> database is working fine. All queries are done through Stored Procs,
> and works like a charm.
> I tried to copy the database to another database (SQL Server 2000
> Personal Ed.) which is on my PC. I have created a DTS package on the
> Originating server. This DTS package used to work fine for a long
> time, until sometime back few weeks ago I introduced FTI, and now it
> does not work.
> I get this error when executing DTS package:
> "[ODBC SQL Server Driver][SQL Server]Cannot use a CONTAINS or FREETEXT
> predicate on table 'Products' because it is not full-text indexed."
> This table is full text on the originating and also on the destination
> database. (I created one on the destination, thought perhaps that's why
> it wouldn't work)
> Does anyone know how to fix this? Thanks.
>
|||DTS package is No dropping the table, and I do remember a while back
seeing data in the Products table. But I will check this again to make
sure and get back.
BTW, I couldn't get to nwsu.com's SQL Server replication book, perhaps
it is blocked on the corporate proxy.
|||It is also available on Amazon.
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
<bikmann@.gmail.com> wrote in message
news:1108749013.221494.201010@.g14g2000cwa.googlegr oups.com...
> DTS package is No dropping the table, and I do remember a while back
> seeing data in the Products table. But I will check this again to make
> sure and get back.
> BTW, I couldn't get to nwsu.com's SQL Server replication book, perhaps
> it is blocked on the corporate proxy.
>
|||Alright, I checked. The DTS is NOT dropping the table in the
destination nor originating. And it is also properly copying the
contents of the Products table into the destination database. But yet
it package will still fail with that message.
|||How are you transferring the data?
Are you using the Transform Data task or the Transfer Objects task?
If the latter then what options do you have set?
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
<bikmann@.gmail.com> wrote in message news:1108690034.091208.194630@.g14g2000cwa.googlegr oups.com...
>I have a database (SQL Server 2000 Enterprise Ed.) with a single table
> (Products), which has 3 columns set for Full-Text-Indexing. This
> database is working fine. All queries are done through Stored Procs,
> and works like a charm.
> I tried to copy the database to another database (SQL Server 2000
> Personal Ed.) which is on my PC. I have created a DTS package on the
> Originating server. This DTS package used to work fine for a long
> time, until sometime back few weeks ago I introduced FTI, and now it
> does not work.
> I get this error when executing DTS package:
> "[ODBC SQL Server Driver][SQL Server]Cannot use a CONTAINS or FREETEXT
> predicate on table 'Products' because it is not full-text indexed."
> This table is full text on the originating and also on the destination
> database. (I created one on the destination, thought perhaps that's why
> it wouldn't work)
> Does anyone know how to fix this? Thanks.
>
|||Allan,
I am using "Copy SQL Server Objects Task" to copy the database, with
all the defaults settings.
|||Personally If I was copying a database I would use BACKUP/RESTORE. I am
not a great fan of the Copy Objects task.
Allan
"Bik" <bikmann@.gmail.com> wrote in message news:bikmann@.gmail.com:
> Allan,
> I am using "Copy SQL Server Objects Task" to copy the database, with
> all the defaults settings.
|||DTS allows unattended copy to another running SQL Server. I have used
BACKUP/RESORE, but i wanted to use DTS so that I can put it on
schedule. Currently I have 3 other DBs being copied via DTS, this is
the only one that causes problem., yet it would still copy the table
contents.
|||You can set up a job to do the BACKUP and restore also unattended.
Allan
"Bik" <bikmann@.gmail.com> wrote in message news:bikmann@.gmail.com:
> DTS allows unattended copy to another running SQL Server. I have used
> BACKUP/RESORE, but i wanted to use DTS so that I can put it on
> schedule. Currently I have 3 other DBs being copied via DTS, this is
> the only one that causes problem., yet it would still copy the table
> contents.

Overlapping Permissions

I would think the following scenario should work, but it does not:
I have a table, Products, for which all users, via an NT domain group (e.g.
Domain Users) have only select permissions.
There is another group, ProductManagers, who are also members of the above
group, who need update, delete, and insert permissions. To accomplish this,
I
created a database role ProductMgmt, and added the ProductManagers to it.
This role has select, insert, update and delete permissions on the table.
The members of this group, however, get an error when attempting to delete
from the table. These members belong to both the Domain User and
ProductManagers groups.
I've also given the ProductManager group full permissions on the table. I'm
confused as to why all of this is not working, obviously I'm missing
something.
Thanks for any assistance,
TomtDoes the NT group which has only select permissions have a
deny on delete? Do any users or groups have deny set on the
table?
Permissions are cumulative but deny will take precedence.
-Sue
On Tue, 9 Nov 2004 14:51:03 -0800, "TomT" <tomt@.tomt.com>
wrote:

>I would think the following scenario should work, but it does not:
>I have a table, Products, for which all users, via an NT domain group (e.g.
>Domain Users) have only select permissions.
>There is another group, ProductManagers, who are also members of the above
>group, who need update, delete, and insert permissions. To accomplish this,
I
>created a database role ProductMgmt, and added the ProductManagers to it.
>This role has select, insert, update and delete permissions on the table.
>The members of this group, however, get an error when attempting to delete
>from the table. These members belong to both the Domain User and
>ProductManagers groups.
>I've also given the ProductManager group full permissions on the table. I'm
>confused as to why all of this is not working, obviously I'm missing
>something.
>Thanks for any assistance,
>Tomt
>|||Sue,
Thanks for your reply. No, there are no deny's on delete. I did know that
one, but am missing something...
Thanks
Tom
"Sue Hoegemeier" wrote:

> Does the NT group which has only select permissions have a
> deny on delete? Do any users or groups have deny set on the
> table?
> Permissions are cumulative but deny will take precedence.
> -Sue
> On Tue, 9 Nov 2004 14:51:03 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>|||And there are no other Windows groups and no other roles in
that database? Just the two roles and the two NT groups?
And members of the ProductMgmt role can select, insert and
update but not delete?
-Sue
On Tue, 9 Nov 2004 15:44:04 -0800, "TomT" <tomt@.tomt.com>
wrote:
[vbcol=seagreen]
>Sue,
>Thanks for your reply. No, there are no deny's on delete. I did know that
>one, but am missing something...
>Thanks
>Tom
>"Sue Hoegemeier" wrote:
>|||There's the Domain Users and two other non-NT, SQL Server accounts for web
access to the table.
There are no other roles other than the built-in roles. That group has
select, insert, update and delete permissions.
I'm going to have them try it again tomorrow, I might have overlooked
checking the delete permission, which is just due to trying to do too many
things at once...
I'll post the results tomorrow. Thanks for your assistance with this.
Tom
"Sue Hoegemeier" wrote:

> And there are no other Windows groups and no other roles in
> that database? Just the two roles and the two NT groups?
> And members of the ProductMgmt role can select, insert and
> update but not delete?
> -Sue
> On Tue, 9 Nov 2004 15:44:04 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>|||Tom
Grant them EXECUTE permission on SP that perform DELETE/INSERT/UPDATE on
this table.
"TomT" <tomt@.tomt.com> wrote in message
news:F6DB5A9A-6ADF-4F89-89E8-9656A8962BFF@.microsoft.com...[vbcol=seagreen]
> There's the Domain Users and two other non-NT, SQL Server accounts for web
> access to the table.
> There are no other roles other than the built-in roles. That group has
> select, insert, update and delete permissions.
> I'm going to have them try it again tomorrow, I might have overlooked
> checking the delete permission, which is just due to trying to do too many
> things at once...
> I'll post the results tomorrow. Thanks for your assistance with this.
> Tom
> "Sue Hoegemeier" wrote:
>
that[vbcol=seagreen]
group (e.g.[vbcol=seagreen]
above[vbcol=seagreen]
accomplish this, I[vbcol=seagreen]
to it.[vbcol=seagreen]
table.[vbcol=seagreen]
delete[vbcol=seagreen]
table. I'm[vbcol=seagreen]|||You really should check the other permissions as well as it
could make it easier for you to determine what has been
missed. Check the select, insert and update permissions as
well.
-Sue
On Tue, 9 Nov 2004 21:31:03 -0800, "TomT" <tomt@.tomt.com>
wrote:
[vbcol=seagreen]
>There's the Domain Users and two other non-NT, SQL Server accounts for web
>access to the table.
>There are no other roles other than the built-in roles. That group has
>select, insert, update and delete permissions.
>I'm going to have them try it again tomorrow, I might have overlooked
>checking the delete permission, which is just due to trying to do too many
>things at once...
>I'll post the results tomorrow. Thanks for your assistance with this.
>Tom
>"Sue Hoegemeier" wrote:
>|||I checked them all, for that particular group, and still no go. I have to
grant the permissions for the Domain Users group for insert, delete, etc.
otherwise the group I really need to have this access does not.
To summarize: Two groups (NT) Domain Users, to which all users belong,
member of the public role, and ProductManagers, member of public and
ProductMgmt roles.
A user, Rod, belongs to both Domain Users and ProductManagers groups.
ProductMangers have select, insert, delete and update permissions on table;
Domain Users have Select permission only, no other permissions granted or
denied.
Database role ProductMgmt has full permissions on the table.
With the scenario above, Rod cannot delete from the table. I have to grant
delete permissions to Domain Users in order for him to be able to delete row
s
from the table.
I gather from your replies that this should work, and I have set it up
correctly, is that right?
Thanks,
Tom
"Sue Hoegemeier" wrote:

> You really should check the other permissions as well as it
> could make it easier for you to determine what has been
> missed. Check the select, insert and update permissions as
> well.
> -Sue
> On Tue, 9 Nov 2004 21:31:03 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>|||Yes it will work so you are still missing something. I can't
reproduce the issue rebuilding with the same groups and
roles - it works fine on my end.
Try using xp_logininfo to determine the group membership and
dsiplay information on the Product Managers group at the
Windows level.
-Sue
On Wed, 10 Nov 2004 08:45:01 -0800, "TomT" <tomt@.tomt.com>
wrote:
[vbcol=seagreen]
>I checked them all, for that particular group, and still no go. I have to
>grant the permissions for the Domain Users group for insert, delete, etc.
>otherwise the group I really need to have this access does not.
>To summarize: Two groups (NT) Domain Users, to which all users belong,
>member of the public role, and ProductManagers, member of public and
>ProductMgmt roles.
>A user, Rod, belongs to both Domain Users and ProductManagers groups.
>ProductMangers have select, insert, delete and update permissions on table;
>Domain Users have Select permission only, no other permissions granted or
>denied.
>Database role ProductMgmt has full permissions on the table.
>With the scenario above, Rod cannot delete from the table. I have to grant
>delete permissions to Domain Users in order for him to be able to delete ro
ws
>from the table.
>I gather from your replies that this should work, and I have set it up
>correctly, is that right?
>Thanks,
>Tom
>"Sue Hoegemeier" wrote:
>|||Sue,
I found the problem, the person who set up the NT user group
ProductManagers, set it up as a distribution group, not a security group.
Once that was fixed, everything works correctly.
BTW, I assume this would still work without the database role, i.e., just
the ProductManager group having the delete, etc. permissions assigned,
correct?
thanks for your help and patience,
Tom
"Sue Hoegemeier" wrote:

> Yes it will work so you are still missing something. I can't
> reproduce the issue rebuilding with the same groups and
> roles - it works fine on my end.
> Try using xp_logininfo to determine the group membership and
> dsiplay information on the Product Managers group at the
> Windows level.
> -Sue
> On Wed, 10 Nov 2004 08:45:01 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>