October 15, 2001 at 1:43 am
Hi I accidentally dropped the stored procedure sp_fulltext_database. Could anyone help me with the process of recovering the same.
October 15, 2001 at 4:57 am
Just do a temp install on another server, script the procedure out, then run the script on your real server.
Andy
October 15, 2001 at 2:20 pm
You may need to allow updates
sp_configure 'allow updates', 1
reconfigure with override
then recompile the stored procedure.
Steve Jones
October 16, 2001 at 11:02 am
Hi Steve,
Thanks for your response. I could create the system stored procedure sp_fulltext_database and recompile. However when I try to execute the stored procedure with the following command
EXEC sp_fulltext_database @action = 'Enable'
I get the following error
-------
(1 row(s) affected)
Server: Msg 2526, Level 16, State 3, Procedure sp_fulltext_database1, Line 46
Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.
------
I dont know the reason for this. I copied the contents of this stored procedure from a new installation of SQL Server 2000, where this procedure runs smoothly.
Please find the code for the stored procedure
---------
CREATE proc sp_fulltext_database
@actionvarchar(20) -- 'enable' | 'disable'
as
declare @ftcat sysname,
@ftcatid smallint,
@path nvarchar(260),
@objid int,
@dbid smallint,
@objname sysname
-- VALIDATE PARAMS --
if @action is null OR @action not in ('enable','disable')
begin
raiserror(15600,-1,-1,'sp_fulltext_database')
return 1
end
-- DISALLOW USER TRANSACTION --
set implicit_transactions off
if @@trancount > 0
begin
raiserror(15002,-1,-1,'sp_fulltext_database')
return 1
end
-- CHECK PERMISSIONS (must be a dbowner) --
if (is_member('db_owner') = 0)
begin
raiserror(15247,-1,-1)
return 1
end
-- CHECK DATABASE MODE (must not be read-only) --
if (DATABASEPROPERTY(db_name(), 'IsReadOnly') = 1)
begin
raiserror(15635, -1, -1, 'sp_fulltext_database')
return 1
end
-- CLEAR SYSDATABASES BIT AND PROPAGATE W/ CHECKPOINT (for both enable & disable) --
select @dbid = db_id()
update master.dbo.sysdatabases set status2 = status2 & ~536870912 where dbid = @dbid
checkpoint
-- DROP ALL CATALOGS WITH THIS DATABASE (for both enable/disable) --
DBCC CALLFULLTEXT ( 7, @dbid )-- FTDropAllCatalogs ( "@dbid" )
if @@error <> 0
return 1
-- DELETE ALL THE CHANGE NOTIFICATIONS FROM SYSFULLTEXTNOTIFY --
delete sysfulltextnotify
if @action = 'enable'
begin
-- CREATE CATALOGS --
declare ms_crs_ftcat cursor static local for select name, path from sysfulltextcatalogs
open ms_crs_ftcat
fetch ms_crs_ftcat into @ftcat, @path
while @@fetch_status >= 0
begin
DBCC CALLFULLTEXT ( 16, @ftcat, @path )-- FTCreateCatalog( @ftcatid, @path )
if @@error <> 0
return 1
fetch ms_crs_ftcat into @ftcat, @path
end
deallocate ms_crs_ftcat
declare@vc1nvarchar(517)
-- BEGIN TRAN
begin tran
-- ACTIVATE TABLES/URLs --
declare ms_crs_ftind cursor static local for select ftcatid, id from sysobjects
where (ftcatid <> 0)
open ms_crs_ftind
fetch ms_crs_ftind into @ftcatid, @objid
while @@fetch_status >= 0
begin
DBCC CALLFULLTEXT ( 5, @ftcatid, @objid )-- FTAddURL( @ftcatid, db_id(), @objid )
if @@error <> 0
goto error_abort_exit
-- CHECK TABLE FOR NOTIFICATIONS --
if ObjectProperty(@objid, 'TableFulltextChangeTrackingOn') = 1
begin
-- ERROR IF DATABASE IS IN SINGLE USER MODE --
if DATABASEPROPERTY(db_name(), 'IsSingleUser') = 1
begin
select @objname = object_name(@objid)
raiserror(15638, -1, -1, @objname)
select @vc1 = quotename(user_name(OBJECTPROPERTY(@objid,'OwnerId'))) + '.'
+ quotename(@objname)
-- LOCK TABLE --
dbcc lockobjectschema(@vc1)
if @@error <> 0
goto error_abort_exit
-- TURN OFF CHANGE TRACKING ACTIVE BITS IN SYSOBJECTS --
update sysobjects set status = status & ~192 where id = @objid
fetch ms_crs_ftind into @ftcatid, @objid
continue
end
-- START A FULL CRAWL FOR THIS TABLE --
DBCC CALLFULLTEXT ( 12, @ftcatid, @objid, 0 )
if @@error <> 0
goto error_abort_exit
end
-- CHECK TABLE FOR AUTOPROPAGATION -
if ObjectProperty(@objid, 'TableFulltextBackgroundUpdateIndexOn') = 1
begin
DBCC CALLFULLTEXT ( 10, @ftcatid, @objid )-- FTEnableAutoProp( @ftcatid, db_id(), @objid )
if @@error <> 0
goto error_abort_exit
end
fetch ms_crs_ftind into @ftcatid, @objid
end
deallocate ms_crs_ftind
-- SET SYSDATABASES BIT --
update master.dbo.sysdatabases set status2 = status2 | 536870912 where dbid = @dbid
-- COMMIT TRAN --
commit tran
-- CHECKPOINT TO PUSH SYSDATABASES BIT TO MEMORY --
checkpoint
if @@error <> 0
goto error_abort_exit
end
-- SUCCESS --
return 0-- sp_fulltext_database
error_abort_exit:
rollback tran
return 1-- sp_fulltext_database
GO
-----------
Any help with this is greatly appreciated.
Thanks
Debasish
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply