Advice on handling a client's full-text index loss

Hi,
The problem started because the client's hardware malfunctioned and he
needed to move things to a new computer. So, we installed SQL 2000 on the
new computer, ran the SP3 updates, and installed the specific initial
database using our installer.
After that, we restored the database to the data that they had backed up
a while ago. Everything looked good and they ran without full-text searches
for a while. They then decided that they were ready to do the full-text
index populations, so we ran a script designed to get that all squared away.
The problem apparently started when this script attempted to drop the
full-text indexes and relocate them in the appropriate place. I thought I
could work through this, but couldn't find appropriate information for quite
a while. Later, I decided to attempt a manual removal of the full-text
indexes from the sysfulltextindex (sp?) table. There were two rows, with
the first columns having the values of 5 and 6. I removed them, and tried
to clean things up. In that process, I disabled the full-text indexing, and
now it simply will not re-enable.
Reading this newsgroup, I noticed the existence of a script that could
possibly help by manually deleting the traces of the full-text catalogs.
My question is: What is the best next step?
1) Try to make use of the script I just mentioned.
2) Restore the database, copy in the previous full-text data files
to the appropriate directory, and then try to run our script that moves and
re-populates the catalogs?
3) Something else entirely?
Thanks for your help.
John
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.744 / Virus Database: 496 - Release Date: 8/24/2004
John,
I've emailed you charter account directly as resolving this type of a
situation is better handled via direct email. I don't publicly post these
scripts because they require SysAdmin permission level of access and modify
both Registry keys & values as well as SQL Server system tables.
Regards,
John
"John" <jsfishr@.charter.net> wrote in message
news:eMb$LO6jEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi,
> The problem started because the client's hardware malfunctioned and he
> needed to move things to a new computer. So, we installed SQL 2000 on the
> new computer, ran the SP3 updates, and installed the specific initial
> database using our installer.
> After that, we restored the database to the data that they had backed
up
> a while ago. Everything looked good and they ran without full-text
searches
> for a while. They then decided that they were ready to do the full-text
> index populations, so we ran a script designed to get that all squared
away.
> The problem apparently started when this script attempted to drop the
> full-text indexes and relocate them in the appropriate place. I thought I
> could work through this, but couldn't find appropriate information for
quite
> a while. Later, I decided to attempt a manual removal of the full-text
> indexes from the sysfulltextindex (sp?) table. There were two rows, with
> the first columns having the values of 5 and 6. I removed them, and
tried
> to clean things up. In that process, I disabled the full-text indexing,
and
> now it simply will not re-enable.
> Reading this newsgroup, I noticed the existence of a script that could
> possibly help by manually deleting the traces of the full-text catalogs.
> My question is: What is the best next step?
> 1) Try to make use of the script I just mentioned.
> 2) Restore the database, copy in the previous full-text data files
> to the appropriate directory, and then try to run our script that moves
and
> re-populates the catalogs?
> 3) Something else entirely?
> Thanks for your help.
> John
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.744 / Virus Database: 496 - Release Date: 8/24/2004
>

ADO error handling when connecting to SQL Server 2000

I use Delphi to connect to SQL Server 2000 using the ADO components. I'm
executing stored procedure that makes some calls to another stored procs. In
one of the sub called stored procs foreign key is violated and error message
is generated ('INSERT statement conflicted with COLUMN FOREIGN KEY
constraint ..' i see it in the SQL Query analyser).
When i use this connection string:
Provider=SQLOLEDB.1;Password="";Persist Security Info=True;User
ID=sa;Initial Catalog=TechDB;Data Source=.
the error is not reported in my application, it seems that the driver
ignores the error message. I have no Exception generated. So i tried with
another driver for SQL Server (ODBC). The ADO connection string was:
Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data
Source=LOCA_RMP;Initial Catalog=TechDB
This one is reporting error in the above situation (i get Exception with
'INSERT statement conflicted with COLUMN FOREIGN KEY constraint ...' ). But
the problem is that it generates exception when 'print' command is executed,
so it thinks that every message is an error. Besides the connecting thru
ODBC DSN seems to be slower and i think it's not native for ADO .
I know that @.@.ERROR is used to detect errors in SQL Server 2000 but it's
messy to use "if @.@.error<>0 set @.local_error=1" after every line of code. Is
there another way to do this? Am i supposed to raiserror at the end of the
stored proc?
Any suggestions are appreciated,
Georgi PeshterskiFor a complete discussion on Error handling in Stored Procs, see
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"George Peshterski" <unseen@.mail.bg> wrote in message
news:eF%234vneDFHA.2216@.TK2MSFTNGP09.phx.gbl...
> I use Delphi to connect to SQL Server 2000 using the ADO components.
> I'm
> executing stored procedure that makes some calls to another stored procs.
> In
> one of the sub called stored procs foreign key is violated and error
> message
> is generated ('INSERT statement conflicted with COLUMN FOREIGN KEY
> constraint ..' i see it in the SQL Query analyser).
> When i use this connection string:
> Provider=SQLOLEDB.1;Password="";Persist Security Info=True;User
> ID=sa;Initial Catalog=TechDB;Data Source=.
> the error is not reported in my application, it seems that the driver
> ignores the error message. I have no Exception generated. So i tried with
> another driver for SQL Server (ODBC). The ADO connection string was:
> Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data
> Source=LOCA_RMP;Initial Catalog=TechDB
> This one is reporting error in the above situation (i get Exception
> with
> 'INSERT statement conflicted with COLUMN FOREIGN KEY constraint ...' ).
> But
> the problem is that it generates exception when 'print' command is
> executed,
> so it thinks that every message is an error. Besides the connecting thru
> ODBC DSN seems to be slower and i think it's not native for ADO .
> I know that @.@.ERROR is used to detect errors in SQL Server 2000 but it's
> messy to use "if @.@.error<>0 set @.local_error=1" after every line of code.
> Is
> there another way to do this? Am i supposed to raiserror at the end of the
> stored proc?
> Any suggestions are appreciated,
> Georgi Peshterski
>|||Thank you for the useful links, i'm reading the articles now and there are
many things i didn't knew.
As for my problem i found that the foreign key error that ADO hides happens
in INSTEAD OF trigger in the subprocedures. I read that error in trigger
cancels the batch but i'm still curious why ADO isn't reporting it to me,
i'm going to examine carefully the articles about this problem ...
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:%23B7yxteDFHA.3688@.TK2MSFTNGP14.phx.gbl...
> For a complete discussion on Error handling in Stored Procs, see
> http://www.sommarskog.se/error-handling-I.html
> http://www.sommarskog.se/error-handling-II.html
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "George Peshterski" <unseen@.mail.bg> wrote in message
> news:eF%234vneDFHA.2216@.TK2MSFTNGP09.phx.gbl...
procs.
with
it's
code.
the
>|||Make sure you have SET NOCOUNT ON at the beginning of your procs and
triggers. This will suppress DONE_IN_PROC messages that are returned as
empty closed recordsets in ADO.
Hope this helps.
Dan Guzman
SQL Server MVP
"George Peshterski" <unseen@.mail.bg> wrote in message
news:e6k39MgDFHA.392@.TK2MSFTNGP14.phx.gbl...
> Thank you for the useful links, i'm reading the articles now and there are
> many things i didn't knew.
> As for my problem i found that the foreign key error that ADO hides
> happens
> in INSTEAD OF trigger in the subprocedures. I read that error in trigger
> cancels the batch but i'm still curious why ADO isn't reporting it to me,
> i'm going to examine carefully the articles about this problem ...
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:%23B7yxteDFHA.3688@.TK2MSFTNGP14.phx.gbl...
> procs.
> with
> it's
> code.
> the
>