accidentally dropped sp_fulltext_database

  • debasishjava

    SSC Journeyman

    Points: 85

    Hi I accidentally dropped the stored procedure sp_fulltext_database. Could anyone help me with the process of recovering the same.

  • Andy Warren

    SSC Guru

    Points: 119655

    Just do a temp install on another server, script the procedure out, then run the script on your real server.

    Andy

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714698

    You may need to allow updates

    sp_configure 'allow updates', 1

    reconfigure with override

    then recompile the stored procedure.

    Steve Jones

    steve@dkranch.net

  • debasishjava

    SSC Journeyman

    Points: 85

    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

    @action varchar(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 @vc1 nvarchar(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 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply