Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Other Technologies / Full-Text Search / July 2005

Tip: Looking for answers? Try searching our database.

sp_fulltext_table with SQL Server Agent - permissions problem?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
edwaldo - 19 Jul 2005 01:49 GMT
Hi,

I have two databases on separate servers - db1 & db2.  

I use DTS to copy the tables from db1 to db2 overnight.  Then I want to
recreate the fulltext catalog on one of the tables in db2 using a SQL Server
Agent job to run sp_fulltext_table:

USE hm2
EXEC sp_fulltext_database  'enable'

USE hm2
EXEC sp_fulltext_catalog 'ft_item', 'drop'

USE hm2
EXEC sp_fulltext_catalog 'ft_item', 'create'

USE hm2
EXEC sp_fulltext_table 'item', 'create', 'ft_item', 'item_id'

This is where is fails (I think).

The table I'm fulltext indexing has a primary key column defined (NOT NULL).
When the overnight copy runs, the table is created under dbo, and the sql
agent job is running as dbo.  I have BUILTIN\Administrators setup with the
correct permissions.

Is this likely to be a permissions problem?

Many thanks,

ed
Hilary Cotter - 19 Jul 2005 02:19 GMT
This should work - what is the error message you are getting?

Also can you issue an sp_help 'item' and post the results back here?

Could you also try to create a full text index on the authors table in the
pubs database on db2 to see if SQL FTS works there?
Signature

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

> Hi,
>
[quoted text clipped - 30 lines]
>
> ed
edwaldo - 19 Jul 2005 11:57 GMT
Thanks for your help.

The error message I'm getting in the logs is:

SQL Server Scheduled Job 'hm_build_ft' (0xC0B499F8F5AD3047803EC1417DC293A7)
- Status: Failed - Invoked on: 2005-07-19 01:35:37 - Message: The job failed.
The Job was invoked by User CIHWEBSQL\Edward.  The last step to run was step
1 (drop_rebuild).

These are the (abbreviated) results of sp_help 'item':

Name  Owner  Type          Created_datetime
item    dbo      user talbe   2005-07-12 19:53

Column_name  Type      Computed  Length  Prec  Scale  Nullable  
item_id            decimal  no             9          18     0       no

TrimTrailingBlanks  FixedLenNullInSource  Collation
n/a                        n/a                            NULL

Identity  Seed  Increment  Not for Replication
item_id  1       1               0

RowGuidCol
No rowguidcol column defined

Data_located_on_filegroup
PRIMARY

index_name  index_description  
PK_item        clusered, unique, primary key located on PRIMARY

index_keys
item_id

constraint_type                contraint_name
PRIMARY KEY (clustered)  PK_item

delete_action, update_action, status_enabled, status_for_replication = n/a

constraint_keys
item_id

I have tried to create SQL FTS on the pubs database but the problem is the
same:

USE pubs
EXEC sp_fulltext_database  'enable'

USE pubs
EXEC sp_fulltext_catalog   'ft_temp','create'

USE pubs
EXEC sp_fulltext_table 'authors',
'drop'

USE pubs
EXEC sp_fulltext_table 'authors', 'create', 'ft_temp', 'PK_authors'

It creates the catalog but I don't get any further.  Any help would be
greatly appreciated.

Thanks again,

ed

> This should work - what is the error message you are getting?
>
[quoted text clipped - 36 lines]
> >
> > ed
Hilary Cotter - 19 Jul 2005 17:09 GMT
Connect to your SQL Server in EM, expand the Management folder, expand SQL
Server agent, expand jobs, right click on the job select properties and then
change the job owner to sa. Restart your job and see if this fixes the
problem.
Signature

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

> Thanks for your help.
>
[quoted text clipped - 102 lines]
> > >
> > > ed
edwaldo - 19 Jul 2005 17:51 GMT
Thanks Hilary.

I tried the following job running under sa:

USE pubs
EXEC sp_fulltext_database  'enable'

USE pubs
EXEC sp_fulltext_catalog   'ft_temp','create'

USE pubs
EXEC sp_fulltext_table 'authors',
'drop'

USE pubs
EXEC sp_fulltext_table 'authors', 'create', 'ft_temp', 'PK_authors'

Again, the catalog was created but it failed on the last line (I assume).  
Is there anything else I might be missing?

Many thanks,

Ed

> Connect to your SQL Server in EM, expand the Management folder, expand SQL
> Server agent, expand jobs, right click on the job select properties and then
[quoted text clipped - 113 lines]
> > > >
> > > > ed
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.