error handling flow: index

Showing posts with label index. Show all posts

Monday, March 26, 2012

error index data base

Hi all,
I'm Jose and first of all excuse my english:
I have many problems with "Plan de mantenimiento de SQL" often when the
"Plan de mantenimiento" begin, have an errors during the reindex how:
Reconstruyendo los Â¡ndices de la tabla 'ws_Albaranes_Lineas'
[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3624: [Microsoft][ODBC SQL
Server Driver][SQL Server]
[Microsoft][ODBC SQL Server Driver][SQL Server]Location: page.cpp:2831
Expression: spaceContig >= Align (spaceNeeded)
SPID: 54
Process ID: 1284
then marked how suspicous and can repair automatically, but when erros like
that occur for 4 or 5 times the database can't repair automatically and the
client lost data.
W2K SP4 and SQL SP3
Thanks a lotOK, I understand a little 'Spanglish'. If I read correctly, you have chosen
the 'Repair minor problems Automatically' option in your Maintenance plans
("Plan de mantenimiento de SQL" ). The repair tried and failed and marked
the database suspect.
First, this repair automatically option a bad idea and I think it should not
be in the product. I never check this option. I want to know if there is a
problem so I can schedule a fix during non-peak times. I also want to know
so I can find what is causing the error.
The good news is you may be OK. the ws_indexes are actually statistics and
do not directly impact the data. Look at 'Resetting Suspect Status' in BOL
(Books On-Line). This may let you extract your data and recreate the
database.
This KB Article may help if nothing else works.
INF: Bypass (Emergency) Mode and DUMP TRANSACTION WITH NO_LOG
http://support.microsoft.com/default.aspx?scid=kb;en-us;165918
Good Luck.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jose Rojas" <JoseRojas@.discussions.microsoft.com> wrote in message
news:7592A326-4D93-4439-8BAB-1E95056C4809@.microsoft.com...
> Hi all,
> I'm Jose and first of all excuse my english:
> I have many problems with "Plan de mantenimiento de SQL" often when the
> "Plan de mantenimiento" begin, have an errors during the reindex how:
> Reconstruyendo los ¡ndices de la tabla 'ws_Albaranes_Lineas'
> [Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3624: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]
> [Microsoft][ODBC SQL Server Driver][SQL Server]Location: page.cpp:2831
> Expression: spaceContig >= Align (spaceNeeded)
> SPID: 54
> Process ID: 1284
> then marked how suspicous and can repair automatically, but when erros
like
> that occur for 4 or 5 times the database can't repair automatically and
the
> client lost data.
> W2K SP4 and SQL SP3
> Thanks a lot
>|||Hi again:
If I undestand, the correct option is: no marked the option "Repair minor
problems Automatically" but when the maintenance plans fail, always fail
during the reindex, The question is: Why reindex fail? For example the last
error that I wrote you:
> > Reconstruyendo los Â¡ndices de la tabla 'ws_Albaranes_Lineas'
> > [Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3624: [Microsoft][ODBC
> SQL
> > Server Driver][SQL Server]
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Location: page.cpp:2831
> > Expression: spaceContig >= Align (spaceNeeded)
> > SPID: 54
> > Process ID: 1284
Do you think I needn't reindex database?
"Geoff N. Hiten" wrote:
> OK, I understand a little 'Spanglish'. If I read correctly, you have chosen
> the 'Repair minor problems Automatically' option in your Maintenance plans
> ("Plan de mantenimiento de SQL" ). The repair tried and failed and marked
> the database suspect.
> First, this repair automatically option a bad idea and I think it should not
> be in the product. I never check this option. I want to know if there is a
> problem so I can schedule a fix during non-peak times. I also want to know
> so I can find what is causing the error.
> The good news is you may be OK. the ws_indexes are actually statistics and
> do not directly impact the data. Look at 'Resetting Suspect Status' in BOL
> (Books On-Line). This may let you extract your data and recreate the
> database.
> This KB Article may help if nothing else works.
> INF: Bypass (Emergency) Mode and DUMP TRANSACTION WITH NO_LOG
> http://support.microsoft.com/default.aspx?scid=kb;en-us;165918
> Good Luck.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jose Rojas" <JoseRojas@.discussions.microsoft.com> wrote in message
> news:7592A326-4D93-4439-8BAB-1E95056C4809@.microsoft.com...
> > Hi all,
> > I'm Jose and first of all excuse my english:
> >
> > I have many problems with "Plan de mantenimiento de SQL" often when the
> > "Plan de mantenimiento" begin, have an errors during the reindex how:
> > Reconstruyendo los Â¡ndices de la tabla 'ws_Albaranes_Lineas'
> > [Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3624: [Microsoft][ODBC
> SQL
> > Server Driver][SQL Server]
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Location: page.cpp:2831
> > Expression: spaceContig >= Align (spaceNeeded)
> > SPID: 54
> > Process ID: 1284
> >
> > then marked how suspicous and can repair automatically, but when erros
> like
> > that occur for 4 or 5 times the database can't repair automatically and
> the
> > client lost data.
> >
> > W2K SP4 and SQL SP3
> >
> > Thanks a lot
> >
>
>|||I understand. It failed during a reindex. Reindexing is a good thing.
Continue to reindex.
Make sure your database has enough free space to reindex. Do not
auto-shrink the database. Manually add the space rather than relying on
auto-grow. Run DBCC CHECKDB to make sure your database is error free. You
may have to run further DBCC commands to fix it. Always make sure you have
a backup before attempting repairs.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jose Rojas" <JoseRojas@.discussions.microsoft.com> wrote in message
news:2E29E2B7-0A30-4CFD-937F-CCAABEDDBDB0@.microsoft.com...
> Hi again:
> If I undestand, the correct option is: no marked the option "Repair minor
> problems Automatically" but when the maintenance plans fail, always fail
> during the reindex, The question is: Why reindex fail? For example the
last
> error that I wrote you:
> > > Reconstruyendo los ¡ndices de la tabla 'ws_Albaranes_Lineas'
> > > [Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3624:
[Microsoft][ODBC
> > SQL
> > > Server Driver][SQL Server]
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]Location: page.cpp:2831
> > > Expression: spaceContig >= Align (spaceNeeded)
> > > SPID: 54
> > > Process ID: 1284
> Do you think I needn't reindex database?
> "Geoff N. Hiten" wrote:
> > OK, I understand a little 'Spanglish'. If I read correctly, you have
chosen
> > the 'Repair minor problems Automatically' option in your Maintenance
plans
> > ("Plan de mantenimiento de SQL" ). The repair tried and failed and
marked
> > the database suspect.
> >
> > First, this repair automatically option a bad idea and I think it should
not
> > be in the product. I never check this option. I want to know if there
is a
> > problem so I can schedule a fix during non-peak times. I also want to
know
> > so I can find what is causing the error.
> >
> > The good news is you may be OK. the ws_indexes are actually statistics
and
> > do not directly impact the data. Look at 'Resetting Suspect Status' in
BOL
> > (Books On-Line). This may let you extract your data and recreate the
> > database.
> >
> > This KB Article may help if nothing else works.
> >
> > INF: Bypass (Emergency) Mode and DUMP TRANSACTION WITH NO_LOG
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;165918
> >
> > Good Luck.
> >
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "Jose Rojas" <JoseRojas@.discussions.microsoft.com> wrote in message
> > news:7592A326-4D93-4439-8BAB-1E95056C4809@.microsoft.com...
> > > Hi all,
> > > I'm Jose and first of all excuse my english:
> > >
> > > I have many problems with "Plan de mantenimiento de SQL" often when
the
> > > "Plan de mantenimiento" begin, have an errors during the reindex how:
> > > Reconstruyendo los ¡ndices de la tabla 'ws_Albaranes_Lineas'
> > > [Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3624:
[Microsoft][ODBC
> > SQL
> > > Server Driver][SQL Server]
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]Location: page.cpp:2831
> > > Expression: spaceContig >= Align (spaceNeeded)
> > > SPID: 54
> > > Process ID: 1284
> > >
> > > then marked how suspicous and can repair automatically, but when erros
> > like
> > > that occur for 4 or 5 times the database can't repair automatically
and
> > the
> > > client lost data.
> > >
> > > W2K SP4 and SQL SP3
> > >
> > > Thanks a lot
> > >
> >
> >
> >sql

error index data base

Friday, March 9, 2012

Error in master.dbo.sysindexes

Hi!
This command:
DBCC CHECKTABLE (sysindexes) WITH NO_INFOMSGS
go
yields:
Page (1:396), object ID 2, index ID 0 has been modified but is not marked
modified in the differential backup bitmap.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'sysindexes' (object ID 2).
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKTABLE (master.dbo.sysindexes ).
I would like to identify the troubled index and perhaps delete it and
recreate it to see if this will go away.
--returns 43 rows
select * from sysindexes where indid = 0
--returns 0 rows
select * from sysindexes where indid = 0 and id=2
--returns 1 row where name='sysindexes'
select * from sysobjects where id=2
What exactly do "object ID 2" and "index ID 0" in the error message refer
to?
Thanks,
JimHave you considered contacting Microsoft for this? As this sounds like a
possible corruption issue, I would contact PSS.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jim" <please.reply@.group> wrote in message
news:OmP9xkDpGHA.4408@.TK2MSFTNGP04.phx.gbl...
Hi!
This command:
DBCC CHECKTABLE (sysindexes) WITH NO_INFOMSGS
go
yields:
Page (1:396), object ID 2, index ID 0 has been modified but is not marked
modified in the differential backup bitmap.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table
'sysindexes' (object ID 2).
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKTABLE (master.dbo.sysindexes ).
I would like to identify the troubled index and perhaps delete it and
recreate it to see if this will go away.
--returns 43 rows
select * from sysindexes where indid = 0
--returns 0 rows
select * from sysindexes where indid = 0 and id=2
--returns 1 row where name='sysindexes'
select * from sysobjects where id=2
What exactly do "object ID 2" and "index ID 0" in the error message refer
to?
Thanks,
Jim|||Hi Jim
Object ID 2 is the sysindexes table, and index ID 0 means the table itself,
not an index on sysindexes.
So there is no 'troubled index' that you can just drop. The problem is in
the sysindexes table itself, which keeps track of all your indexes in a
database, all the statistics, and points to the structures that keep track
of space used for all tables and indexes, in other words, this is a very
critical table and any corruption in it can impact everything else in the
database.
If you have a good recent backup of the database, you can try running DBCC
CHECKTABLE with repair_allow_data_loss, but read all about it before you run
it.
Or you might just decide to pay the fee and call Microsoft support.
--
HTH
Kalen Delaney, SQL Server MVP
"Jim" <please.reply@.group> wrote in message
news:OmP9xkDpGHA.4408@.TK2MSFTNGP04.phx.gbl...
> Hi!
> This command:
> DBCC CHECKTABLE (sysindexes) WITH NO_INFOMSGS
> go
> yields:
> Page (1:396), object ID 2, index ID 0 has been modified but is not marked
> modified in the differential backup bitmap.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'sysindexes' (object ID 2).
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKTABLE (master.dbo.sysindexes ).
> I would like to identify the troubled index and perhaps delete it and
> recreate it to see if this will go away.
> --returns 43 rows
> select * from sysindexes where indid = 0
> --returns 0 rows
> select * from sysindexes where indid = 0 and id=2
> --returns 1 row where name='sysindexes'
> select * from sysobjects where id=2
> What exactly do "object ID 2" and "index ID 0" in the error message refer
> to?
> Thanks,
> Jim
>|||Vyas,
Thank you for the reply.
Jim
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:eUMcopDpGHA.1548@.TK2MSFTNGP04.phx.gbl...
> Have you considered contacting Microsoft for this? As this sounds like a
> possible corruption issue, I would contact PSS.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Jim" <please.reply@.group> wrote in message
> news:OmP9xkDpGHA.4408@.TK2MSFTNGP04.phx.gbl...
> Hi!
> This command:
> DBCC CHECKTABLE (sysindexes) WITH NO_INFOMSGS
> go
> yields:
> Page (1:396), object ID 2, index ID 0 has been modified but is not marked
> modified in the differential backup bitmap.
> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
> 'sysindexes' (object ID 2).
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKTABLE (master.dbo.sysindexes ).
> I would like to identify the troubled index and perhaps delete it and
> recreate it to see if this will go away.
> --returns 43 rows
> select * from sysindexes where indid = 0
> --returns 0 rows
> select * from sysindexes where indid = 0 and id=2
> --returns 1 row where name='sysindexes'
> select * from sysobjects where id=2
> What exactly do "object ID 2" and "index ID 0" in the error message refer
> to?
> Thanks,
> Jim
>
>|||Kalen,
I have taken your advice:
DBCC CHECKTABLE (master, repair_allow_data_loss)
The table now shows no errors. I have backed up the repaired table and will
monitor it.
Thanks!
Jim
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eMedHwDpGHA.4848@.TK2MSFTNGP03.phx.gbl...
> Hi Jim
> Object ID 2 is the sysindexes table, and index ID 0 means the table
> itself, not an index on sysindexes.
> So there is no 'troubled index' that you can just drop. The problem is in
> the sysindexes table itself, which keeps track of all your indexes in a
> database, all the statistics, and points to the structures that keep track
> of space used for all tables and indexes, in other words, this is a very
> critical table and any corruption in it can impact everything else in the
> database.
> If you have a good recent backup of the database, you can try running DBCC
> CHECKTABLE with repair_allow_data_loss, but read all about it before you
> run it.
> Or you might just decide to pay the fee and call Microsoft support.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Jim" <please.reply@.group> wrote in message
> news:OmP9xkDpGHA.4408@.TK2MSFTNGP04.phx.gbl...
>> Hi!
>> This command:
>> DBCC CHECKTABLE (sysindexes) WITH NO_INFOMSGS
>> go
>> yields:
>> Page (1:396), object ID 2, index ID 0 has been modified but is not marked
>> modified in the differential backup bitmap.
>> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
>> 'sysindexes' (object ID 2).
>> repair_allow_data_loss is the minimum repair level for the errors found
>> by DBCC CHECKTABLE (master.dbo.sysindexes ).
>> I would like to identify the troubled index and perhaps delete it and
>> recreate it to see if this will go away.
>> --returns 43 rows
>> select * from sysindexes where indid = 0
>> --returns 0 rows
>> select * from sysindexes where indid = 0 and id=2
>> --returns 1 row where name='sysindexes'
>> select * from sysobjects where id=2
>> What exactly do "object ID 2" and "index ID 0" in the error message refer
>> to?
>> Thanks,
>> Jim
>|||There was nothing wrong with the sysindexes table per se - the problem was
with the diff map (the bitmap the shows which extents need to be included in
the next differential backup). A page in sysindexes had been altered since
the last diff backup but the corresponding bit for the extent containing the
page wasn't set in the diff map.
If you lookup the error number (its error 2515) in MSDN
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_3qwp.asp)
you'll see an explanation and what you need to be aware of if you've run
repair (that you need to take a full backup before diff backups are possible
again)
Thanks
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim" <please.reply@.group> wrote in message
news:OcDK4wEpGHA.1440@.TK2MSFTNGP03.phx.gbl...
> Kalen,
> I have taken your advice:
> DBCC CHECKTABLE (master, repair_allow_data_loss)
> The table now shows no errors. I have backed up the repaired table and
> will monitor it.
> Thanks!
> Jim
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eMedHwDpGHA.4848@.TK2MSFTNGP03.phx.gbl...
>> Hi Jim
>> Object ID 2 is the sysindexes table, and index ID 0 means the table
>> itself, not an index on sysindexes.
>> So there is no 'troubled index' that you can just drop. The problem is in
>> the sysindexes table itself, which keeps track of all your indexes in a
>> database, all the statistics, and points to the structures that keep
>> track of space used for all tables and indexes, in other words, this is a
>> very critical table and any corruption in it can impact everything else
>> in the database.
>> If you have a good recent backup of the database, you can try running
>> DBCC CHECKTABLE with repair_allow_data_loss, but read all about it before
>> you run it.
>> Or you might just decide to pay the fee and call Microsoft support.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Jim" <please.reply@.group> wrote in message
>> news:OmP9xkDpGHA.4408@.TK2MSFTNGP04.phx.gbl...
>> Hi!
>> This command:
>> DBCC CHECKTABLE (sysindexes) WITH NO_INFOMSGS
>> go
>> yields:
>> Page (1:396), object ID 2, index ID 0 has been modified but is not
>> marked modified in the differential backup bitmap.
>> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
>> 'sysindexes' (object ID 2).
>> repair_allow_data_loss is the minimum repair level for the errors found
>> by DBCC CHECKTABLE (master.dbo.sysindexes ).
>> I would like to identify the troubled index and perhaps delete it and
>> recreate it to see if this will go away.
>> --returns 43 rows
>> select * from sysindexes where indid = 0
>> --returns 0 rows
>> select * from sysindexes where indid = 0 and id=2
>> --returns 1 row where name='sysindexes'
>> select * from sysobjects where id=2
>> What exactly do "object ID 2" and "index ID 0" in the error message
>> refer to?
>> Thanks,
>> Jim
>>
>|||Paul,
Thank you for the pointer!
Jim
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:ugOopBSpGHA.756@.TK2MSFTNGP05.phx.gbl...
> There was nothing wrong with the sysindexes table per se - the problem was
> with the diff map (the bitmap the shows which extents need to be included
> in the next differential backup). A page in sysindexes had been altered
> since the last diff backup but the corresponding bit for the extent
> containing the page wasn't set in the diff map.
> If you lookup the error number (its error 2515) in MSDN
> (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_reslsyserr_1_3qwp.asp)
> you'll see an explanation and what you need to be aware of if you've run
> repair (that you need to take a full backup before diff backups are
> possible again)
> Thanks
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Jim" <please.reply@.group> wrote in message
> news:OcDK4wEpGHA.1440@.TK2MSFTNGP03.phx.gbl...
>> Kalen,
>> I have taken your advice:
>> DBCC CHECKTABLE (master, repair_allow_data_loss)
>> The table now shows no errors. I have backed up the repaired table and
>> will monitor it.
>> Thanks!
>> Jim
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:eMedHwDpGHA.4848@.TK2MSFTNGP03.phx.gbl...
>> Hi Jim
>> Object ID 2 is the sysindexes table, and index ID 0 means the table
>> itself, not an index on sysindexes.
>> So there is no 'troubled index' that you can just drop. The problem is
>> in the sysindexes table itself, which keeps track of all your indexes in
>> a database, all the statistics, and points to the structures that keep
>> track of space used for all tables and indexes, in other words, this is
>> a very critical table and any corruption in it can impact everything
>> else in the database.
>> If you have a good recent backup of the database, you can try running
>> DBCC CHECKTABLE with repair_allow_data_loss, but read all about it
>> before you run it.
>> Or you might just decide to pay the fee and call Microsoft support.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Jim" <please.reply@.group> wrote in message
>> news:OmP9xkDpGHA.4408@.TK2MSFTNGP04.phx.gbl...
>> Hi!
>> This command:
>> DBCC CHECKTABLE (sysindexes) WITH NO_INFOMSGS
>> go
>> yields:
>> Page (1:396), object ID 2, index ID 0 has been modified but is not
>> marked modified in the differential backup bitmap.
>> CHECKTABLE found 0 allocation errors and 1 consistency errors in table
>> 'sysindexes' (object ID 2).
>> repair_allow_data_loss is the minimum repair level for the errors found
>> by DBCC CHECKTABLE (master.dbo.sysindexes ).
>> I would like to identify the troubled index and perhaps delete it and
>> recreate it to see if this will go away.
>> --returns 43 rows
>> select * from sysindexes where indid = 0
>> --returns 0 rows
>> select * from sysindexes where indid = 0 and id=2
>> --returns 1 row where name='sysindexes'
>> select * from sysobjects where id=2
>> What exactly do "object ID 2" and "index ID 0" in the error message
>> refer to?
>> Thanks,
>> Jim
>>
>>
>

Error in master.dbo.sysindexes

Wednesday, February 15, 2012

Error handling with CREATE INDEX

I'm receiving a "severe" error that I can't seem to trap and evaluate in SQL Server 2000 (and no, I can't switch to 2005, this is on our customers' machines). And I've not been able to find info on this specific problem.

This is essentially the statement I'd like to catch and gracefully quit if it occurs:

CREATE UNIQUE NONCLUSTERED INDEX UQ_First_Key_SecondField_ThirdField
ON [dbo].[DetailTable] ( Prime_Key, SecondField, ThirdField ) ON [PRIMARY]
SET @.ErrorNumber = @.@.ERROR

Execution never gets to the "SET @.ErrorNumber = @.@.ERROR" statement so I can't act on it. Instead it bombs right away and gives me this error message:

Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 5. Most significant primary key is '706'.
The statement has been terminated.

When that CREATE statement is executed I'd like to gracefully exit the stored procedure (sproc) and report the error to the operator. It's not that I don't understand the error - I fully expect it with SOME of our customers - the problem is that I want to report the REASON for the error to our customers of various expertise.

I created a series of sprocs to re-create indexes in our customers' databases when we define them. Indexes were not defined before, or were defined with random names, so now I'm trying to manage index names and designs explicitly with a series of sprocs I create in SQL Server 2000 to search for, and/or delete, and/or recreate if necessary after verifying the suitability of the index names and syntax as defined by our developers.

Note that specically I'm using either EXEC( ) or EXEC @.ErrorNumber = sp_executesql @.SQLString and I get the same results as if I just use the CREATE statement described above - it bombs out on me and I can't handle the error gracefully.So should I take the lack of response to mean that there is no way to trap this kind of error?|||You will not be able to trap "fatal errors" in SQL, you should build in error handling at the application level to take care of such situations.|||

Quote:

Originally Posted by Motoma

You will not be able to trap "fatal errors" in SQL, you should build in error handling at the application level to take care of such situations.

Thanks, Motoma, I was afraid that might be the case. I was hoping to avoid having to build a new tool to take care of this "simple" task outside the normal procedures we use to upgrade our customers...

Monday, March 26, 2012

error index data base

error index data base

error index data base

Friday, March 9, 2012

Error in master.dbo.sysindexes

Error in master.dbo.sysindexes

Wednesday, February 15, 2012

Error handling with CREATE INDEX

error handling flow

Blog Archive

About Me