Chain Linkage Mismatch Errors

Recently I have been getting a barrage of errors such as:

DESCRIPTION: Error: 8908, Severity: 22, State: 6
Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev =
(3:178).

and

DESCRIPTION: Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:164756) in database 'Clients' belongs
to object 'activities', not to object 'entity_address_check'.

The problem began to originally manifest itself with a couple errors
similar to:

DESCRIPTION: Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
object '1732152167', not to object
'#allrowstable____________________________________ __________________________________________________ _________________000100003472'.

So far all of the errors have been isolated to indexes and I have been
able to repair the problems with CHECKDB fast_rebuild or by dropping
the index that is causing the error and recreating it. At times the
errors will appear for a couple hours overnight and then resolve
themselves before the morning. Originally the problem began to appear
on a single SQL Servers and now appears daily on all three SQL
Servers.

We've investigated whether the NOLOCK optimizer was the culprit but
out of all of our views and procedures there was nothing compiled with
that optimizer in any of the databases, so this seems an unlikely
cause (SEE http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B308886
)

We've reviewed IO on the servers, and nothing appears out of the
ordinary. We have even checked into the possibility of unreport IO
errors (SEE http://support.microsoft.com/default.aspx?scid=kb;en-us;826433&Product=sql2k
) but this still is inconclusive. HP did admit that their disc array
is not fully compatible with W2K Adv Server SP3, but the problem only
recently appeared and the upgrade to SP3 was completed nearly 2 months
ago.

We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
Advanced Server. The server is setup as an A/P cluster on 2 HP
Proliant servers with a HP HTA200 disc array.

If anyone has any insight or suggestions, it would be great to hear
them.Did you do the whole database?

Fixing one doesn't guarentee all...

Did you do DBCC CHECKDB after the work was done to see if there are any other errors?

How big is the db?

So as to nor prevent an outage..take a dump, restore it to another box/instance, and do a full repair...see how long it takes...

Then schedule an outage...

MOO

and Good Luck...|||The largest of the affected databases is about 3 GB and the smallest 800 MB. I have done DBCC CHECKDB (with Fast_Rebuild) to fix some of the errors and have been running CHECKDB multiple times daily to watch for and catch the errors as they are showing up.

Also DBCC DBREINDEX has been running weekly to rebuild the indexes.

One thing I have been considering is rebuilding statistics and usage for all of the databases. On some previous projects I have seen statistics that are out of wack severly affect performance.

Jason Strate

Chain Linkage Mismatch Errors

Recently I have been getting a barrage of errors such as:
DESCRIPTION: Error: 8908, Severity: 22, State: 6
Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev =
(3:178).
and
DESCRIPTION: Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:164756) in database 'Clients' belongs
to object 'activities', not to object 'entity_address_check'.
The problem began to originally manifest itself with a couple errors
similar to:
DESCRIPTION: Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
object '1732152167', not to object
'#allrowstable__________________________
____________________________________
________________________________________
_000100003472'.
So far all of the errors have been isolated to indexes and I have been
able to repair the problems with CHECKDB fast_rebuild or by dropping
the index that is causing the error and recreating it. At times the
errors will appear for a couple hours overnight and then resolve
themselves before the morning. Originally the problem began to appear
on a single SQL Servers and now appears daily on all three SQL
Servers.
We've investigated whether the NOLOCK optimizer was the culprit but
out of all of our views and procedures there was nothing compiled with
that optimizer in any of the databases, so this seems an unlikely
cause (SEE http://support.microsoft.com/defaul...t
=sql2k
) but this still is inconclusive. HP did admit that their disc array
is not fully compatible with W2K Adv Server SP3, but the problem only
recently appeared and the upgrade to SP3 was completed nearly 2 months
ago.
We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
Advanced Server. The server is setup as an A/P cluster on 2 HP
Proliant servers with a HP HTA200 disc array.
If anyone has any insight or suggestions, it would be great to hear
them.>
> Recently I have been getting a barrage of errors such as:
> DESCRIPTION: Error: 8908, Severity: 22, State: 6
> Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
> linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev =
> (3:178).
> and
> DESCRIPTION: Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:164756) in database 'Clients' belongs
> to object 'activities', not to object 'entity_address_check'.
> The problem began to originally manifest itself with a couple errors
> similar to:
> DESCRIPTION: Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
> object '1732152167', not to object
>
'#allrowstable__________________________
____________________________________
________________________________________
_000100003472'.
> So far all of the errors have been isolated to indexes and I have been
> able to repair the problems with CHECKDB fast_rebuild or by dropping
> the index that is causing the error and recreating it. At times the
> errors will appear for a couple hours overnight and then resolve
> themselves before the morning. Originally the problem began to appear
> on a single SQL Servers and now appears daily on all three SQL
> Servers.
> We've investigated whether the NOLOCK optimizer was the culprit but
> out of all of our views and procedures there was nothing compiled with
> that optimizer in any of the databases, so this seems an unlikely
> cause (SEE
http://support.microsoft.com/defaul...Ben-us%3B308886
> )
> We've reviewed IO on the servers, and nothing appears out of the
> ordinary. We have even checked into the possibility of unreport IO
> errors (SEE
http://support.microsoft.com/defaul...3&Product=sql2k
> ) but this still is inconclusive. HP did admit that their disc array
> is not fully compatible with W2K Adv Server SP3, but the problem only
> recently appeared and the upgrade to SP3 was completed nearly 2 months
> ago.
> We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
> Advanced Server. The server is setup as an A/P cluster on 2 HP
> Proliant servers with a HP HTA200 disc array.
> If anyone has any insight or suggestions, it would be great to hear
> them.
>
--
Hi Jason,
Here is my recommended action plan:
1. Turn off write caching in you disk controllers. They have been known to
cause these 605 errors. Monitor your database for a few weeks and see if
turning off the write caching has made a difference.
2. Run SQLIOStress.exe on your system to check how it can handle a typical
SQL Server load. Information about SQLIOStress follows:
HOW TO: Use the SQLIOStress Utility to Stress a Disk Subsystem Such As SQL
Server
http://support.microsoft.com/?id=231619
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Chain Linkage Mismatch Errors

Recently I have been getting a barrage of errors such as:
DESCRIPTION:Error: 8908, Severity: 22, State: 6
Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev =
(3:178).
and
DESCRIPTION:Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:164756) in database 'Clients' belongs
to object 'activities', not to object 'entity_address_check'.
The problem began to originally manifest itself with a couple errors
similar to:
DESCRIPTION:Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
object '1732152167', not to object
'#allrowstable____________________________________ __________________________________________________ _________________000100003472'.
So far all of the errors have been isolated to indexes and I have been
able to repair the problems with CHECKDB fast_rebuild or by dropping
the index that is causing the error and recreating it. At times the
errors will appear for a couple hours overnight and then resolve
themselves before the morning. Originally the problem began to appear
on a single SQL Servers and now appears daily on all three SQL
Servers.
We've investigated whether the NOLOCK optimizer was the culprit but
out of all of our views and procedures there was nothing compiled with
that optimizer in any of the databases, so this seems an unlikely
cause (SEE http://support.microsoft.com/default...en-us%3B308886
)
We've reviewed IO on the servers, and nothing appears out of the
ordinary. We have even checked into the possibility of unreport IO
errors (SEE http://support.microsoft.com/default...&Product=sql2k
) but this still is inconclusive. HP did admit that their disc array
is not fully compatible with W2K Adv Server SP3, but the problem only
recently appeared and the upgrade to SP3 was completed nearly 2 months
ago.
We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
Advanced Server. The server is setup as an A/P cluster on 2 HP
Proliant servers with a HP HTA200 disc array.
If anyone has any insight or suggestions, it would be great to hear
them.
>
> Recently I have been getting a barrage of errors such as:
> DESCRIPTION:Error: 8908, Severity: 22, State: 6
> Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
> linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev =
> (3:178).
> and
> DESCRIPTION:Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:164756) in database 'Clients' belongs
> to object 'activities', not to object 'entity_address_check'.
> The problem began to originally manifest itself with a couple errors
> similar to:
> DESCRIPTION:Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
> object '1732152167', not to object
>
'#allrowstable____________________________________ __________________________
_________________________________________000100003 472'.
> So far all of the errors have been isolated to indexes and I have been
> able to repair the problems with CHECKDB fast_rebuild or by dropping
> the index that is causing the error and recreating it. At times the
> errors will appear for a couple hours overnight and then resolve
> themselves before the morning. Originally the problem began to appear
> on a single SQL Servers and now appears daily on all three SQL
> Servers.
> We've investigated whether the NOLOCK optimizer was the culprit but
> out of all of our views and procedures there was nothing compiled with
> that optimizer in any of the databases, so this seems an unlikely
> cause (SEE
http://support.microsoft.com/default...en-us%3B308886
> )
> We've reviewed IO on the servers, and nothing appears out of the
> ordinary. We have even checked into the possibility of unreport IO
> errors (SEE
http://support.microsoft.com/default...&Product=sql2k
> ) but this still is inconclusive. HP did admit that their disc array
> is not fully compatible with W2K Adv Server SP3, but the problem only
> recently appeared and the upgrade to SP3 was completed nearly 2 months
> ago.
> We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
> Advanced Server. The server is setup as an A/P cluster on 2 HP
> Proliant servers with a HP HTA200 disc array.
> If anyone has any insight or suggestions, it would be great to hear
> them.
>
Hi Jason,
Here is my recommended action plan:
1. Turn off write caching in you disk controllers. They have been known to
cause these 605 errors. Monitor your database for a few weeks and see if
turning off the write caching has made a difference.
2. Run SQLIOStress.exe on your system to check how it can handle a typical
SQL Server load. Information about SQLIOStress follows:
HOW TO: Use the SQLIOStress Utility to Stress a Disk Subsystem Such As SQL
Server
http://support.microsoft.com/?id=231619
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Chain Linkage Mismatch Errors

Recently I have been getting a barrage of errors such as:
DESCRIPTION: Error: 8908, Severity: 22, State: 6
Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev = (3:178).
and
DESCRIPTION: Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:164756) in database 'Clients' belongs
to object 'activities', not to object 'entity_address_check'.
The problem began to originally manifest itself with a couple errors
similar to:
DESCRIPTION: Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
object '1732152167', not to object
'#allrowstable_______________________________________________________________________________________________________000100003472'.
So far all of the errors have been isolated to indexes and I have been
able to repair the problems with CHECKDB fast_rebuild or by dropping
the index that is causing the error and recreating it. At times the
errors will appear for a couple hours overnight and then resolve
themselves before the morning. Originally the problem began to appear
on a single SQL Servers and now appears daily on all three SQL
Servers.
We've investigated whether the NOLOCK optimizer was the culprit but
out of all of our views and procedures there was nothing compiled with
that optimizer in any of the databases, so this seems an unlikely
cause (SEE http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B308886
)
We've reviewed IO on the servers, and nothing appears out of the
ordinary. We have even checked into the possibility of unreport IO
errors (SEE http://support.microsoft.com/default.aspx?scid=kb;en-us;826433&Product=sql2k
) but this still is inconclusive. HP did admit that their disc array
is not fully compatible with W2K Adv Server SP3, but the problem only
recently appeared and the upgrade to SP3 was completed nearly 2 months
ago.
We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
Advanced Server. The server is setup as an A/P cluster on 2 HP
Proliant servers with a HP HTA200 disc array.
If anyone has any insight or suggestions, it would be great to hear
them.>
> Recently I have been getting a barrage of errors such as:
> DESCRIPTION: Error: 8908, Severity: 22, State: 6
> Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
> linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev => (3:178).
> and
> DESCRIPTION: Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:164756) in database 'Clients' belongs
> to object 'activities', not to object 'entity_address_check'.
> The problem began to originally manifest itself with a couple errors
> similar to:
> DESCRIPTION: Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
> object '1732152167', not to object
>
'#allrowstable______________________________________________________________
_________________________________________000100003472'.
> So far all of the errors have been isolated to indexes and I have been
> able to repair the problems with CHECKDB fast_rebuild or by dropping
> the index that is causing the error and recreating it. At times the
> errors will appear for a couple hours overnight and then resolve
> themselves before the morning. Originally the problem began to appear
> on a single SQL Servers and now appears daily on all three SQL
> Servers.
> We've investigated whether the NOLOCK optimizer was the culprit but
> out of all of our views and procedures there was nothing compiled with
> that optimizer in any of the databases, so this seems an unlikely
> cause (SEE
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B308886
> )
> We've reviewed IO on the servers, and nothing appears out of the
> ordinary. We have even checked into the possibility of unreport IO
> errors (SEE
http://support.microsoft.com/default.aspx?scid=kb;en-us;826433&Product=sql2k
> ) but this still is inconclusive. HP did admit that their disc array
> is not fully compatible with W2K Adv Server SP3, but the problem only
> recently appeared and the upgrade to SP3 was completed nearly 2 months
> ago.
> We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
> Advanced Server. The server is setup as an A/P cluster on 2 HP
> Proliant servers with a HP HTA200 disc array.
> If anyone has any insight or suggestions, it would be great to hear
> them.
>
--
Hi Jason,
Here is my recommended action plan:
1. Turn off write caching in you disk controllers. They have been known to
cause these 605 errors. Monitor your database for a few weeks and see if
turning off the write caching has made a difference.
2. Run SQLIOStress.exe on your system to check how it can handle a typical
SQL Server load. Information about SQLIOStress follows:
HOW TO: Use the SQLIOStress Utility to Stress a Disk Subsystem Such As SQL
Server
http://support.microsoft.com/?id=231619
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Chain linkage mismatch

For the past few weeks I have been intermitently plagued with errors
similar to the one below. There is no pattern as it can occur on any
database and on any given table. We are running SQL SERVER 2000.
Error: 8908, Severity: 22, State: 6
Table error: Database ID 29, object ID 1220199397, index ID 0. Chain
linkage mismatch. (1:537042)->next = (1:895619), but (1:895619)->prev = (1:536384).
If I run DBCC CHECKDB (no problems are reported back) and the problem
has mysteriously cleared itself. I believe it may be a RAM issue, as I
believe the DBCC CHECKDB is probably flushing the RAM yet the RAM
diagnostics we run do not indicate a problem. I know we have a problem
with our drive cage which is about to be replaced. The problems caused
by it are identified with the CHECKDB and the table has to be REINDEXED.
I do not believe that this is the issue as with above error as now
reindedxing has to occur to correct the problem.
I am fairly new to the DBA world and any thoughts/ideas here would be
greatly appreciated.
Thanks in advance for any help that may be provided.
Dan
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B308886
Hope this helps.
Sal Terillo
"Dan Duncan" <dduncan@.snl.com> wrote in message
news:OXQrfiPXDHA.1384@.TK2MSFTNGP10.phx.gbl...
> For the past few weeks I have been intermitently plagued with errors
> similar to the one below. There is no pattern as it can occur on any
> database and on any given table. We are running SQL SERVER 2000.
> Error: 8908, Severity: 22, State: 6
> Table error: Database ID 29, object ID 1220199397, index ID 0. Chain
> linkage mismatch. (1:537042)->next = (1:895619), but (1:895619)->prev => (1:536384).
> If I run DBCC CHECKDB (no problems are reported back) and the problem
> has mysteriously cleared itself. I believe it may be a RAM issue, as I
> believe the DBCC CHECKDB is probably flushing the RAM yet the RAM
> diagnostics we run do not indicate a problem. I know we have a problem
> with our drive cage which is about to be replaced. The problems caused
> by it are identified with the CHECKDB and the table has to be REINDEXED.
> I do not believe that this is the issue as with above error as now
> reindedxing has to occur to correct the problem.
> I am fairly new to the DBA world and any thoughts/ideas here would be
> greatly appreciated.
> Thanks in advance for any help that may be provided.
> Dan
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||I am having a similar problem, the Microsoft website says that it's
something to do with the no lock hint or it may be to do with BCPing in
data to the table, my problem is that I do neither of these. I have
solved the problem periodically by dropping the primary key, doing a
dbcc dbreindex on the table and recreating the key. It reoccurs about
once a week. This is the error that comes up from checktable
Msg 8935, Sev 16: Table error: Object ID 7xxx6, index ID 1. The previous
link (1:250470) on page (1:250471) does not match the previous page
(1:709662) that the parent (1:674586), slot 79 expects for this page.
[SQLSTATE 42000]
Msg 8936, Sev 16: Table error: Object ID 7xxx6, index ID 1. B-tree chain
linkage mismatch. (1:709662)->next = (1:250471), but (1:250471)->Prev =(1:250470). [SQLSTATE 42000]
Msg 2536, Sev 16: DBCC results for 'APL'. [SQLSTATE 01000]
Msg 2593, Sev 16: There are 352795 rows in 18831 pages for object 'APL'.
[SQLSTATE 01000]
Msg 8990, Sev 16: CHECKTABLE found 0 allocation errors and 2 consistency
errors in table 'APL' (object ID 7xxx6). [SQLSTATE 01000]
Msg 8958, Sev 16: repair_rebuild is the minimum repair level for the
errors found by DBCC CHECKTABLE (Database.dbo.APL ). [SQLSTATE 01000]
and this is the error I get from users
Table error: Database ID 1, object ID 7xxx6, index ID 0. Chain linkage
mismatch. (1:709662)->next = (1:250471), but (1:250471)->prev =(1:250470)..
Error: 8908, Severity: 22, State: 6
If anyone can help, we are at a total loss at the moment.
Posted via http://dbforums.com|||In that case, it looks like you've got recurring hardware corruption. Is
there any correlation between the page Ids that are referenced in the weekly
error messages? Have you looked through the NT event log and SQL Server
error logs for messages indicating hardware problems. You should also run
hardware diagnostics on your IO subsystem.
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"blatchfordpeter" <member46949@.dbforums.com> wrote in message
news:3557576.1067956634@.dbforums.com...
> I am having a similar problem, the Microsoft website says that it's
> something to do with the no lock hint or it may be to do with BCPing in
> data to the table, my problem is that I do neither of these. I have
> solved the problem periodically by dropping the primary key, doing a
> dbcc dbreindex on the table and recreating the key. It reoccurs about
> once a week. This is the error that comes up from checktable
> Msg 8935, Sev 16: Table error: Object ID 7xxx6, index ID 1. The previous
> link (1:250470) on page (1:250471) does not match the previous page
> (1:709662) that the parent (1:674586), slot 79 expects for this page.
> [SQLSTATE 42000]
> Msg 8936, Sev 16: Table error: Object ID 7xxx6, index ID 1. B-tree chain
> linkage mismatch. (1:709662)->next = (1:250471), but (1:250471)->Prev => (1:250470). [SQLSTATE 42000]
> Msg 2536, Sev 16: DBCC results for 'APL'. [SQLSTATE 01000]
> Msg 2593, Sev 16: There are 352795 rows in 18831 pages for object 'APL'.
> [SQLSTATE 01000]
> Msg 8990, Sev 16: CHECKTABLE found 0 allocation errors and 2 consistency
> errors in table 'APL' (object ID 7xxx6). [SQLSTATE 01000]
> Msg 8958, Sev 16: repair_rebuild is the minimum repair level for the
> errors found by DBCC CHECKTABLE (Database.dbo.APL ). [SQLSTATE 01000]
>
> and this is the error I get from users
>
> Table error: Database ID 1, object ID 7xxx6, index ID 0. Chain linkage
> mismatch. (1:709662)->next = (1:250471), but (1:250471)->prev => (1:250470)..
> Error: 8908, Severity: 22, State: 6
>
> If anyone can help, we are at a total loss at the moment.
>
> --
> Posted via http://dbforums.com

Causes of allocation errors in tempdb

Hello,
Our company produces some software using SQL 7.0 as a back end, one function
of which is the running of several reports overnight. These reports make
use of temporary tables which can cause tempdb to grow to a few hundred
megabytes.
On a handful of our customers' servers, we have seen massive growth in
tempdb (usually consuming all available disk space - several Gb) when
running these reports. On running DBCC CHECKDB against tempdb we have
noticed thousands of allocation errors not associated with any particular
object. I have included details of some of these errors later on with some
commentary.
The first thought has been physical disk errors. All our customers' servers
run on either RAID1 or RAID5 arrays and we have run chkdsk with no errors
found. There have been no errors in the Windows event log suggesting
impending disk failure either.
The other databases (including system databases) have no errors when checked
with DBCC CHECKDB.
If there are any other factors that could lead to this situation I'd
appreciate it if you could let me know.
Many thanks,
John McLusky.
Could not allocate space for object '(SYSTEM table id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full.

> 1. When run on this system, the temporary database had grown so large that
> there was no room for further expansion.
Server: Msg 8999, Level 16, State 1, Line 1
Database tempdb allocation errors prevent further CHECKDB processing.

> 2. I suspect that this is related to the errors that follow shortly!
Server: Msg 1101, Level 17, State 1, Line 1
Could not allocate new page for database 'tempdb'. There are no more pages
available in filegroup DEFAULT. Space can be created by dropping objects,
adding additional files, or allowing file growth.

> 3. as number 1.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:116) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:197) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:198) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:208) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1

> 4. Several more otherwise identical errors with different page numbers
> follow. The SGAM and PFS IDs are identical.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:120) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:128) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:136) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:144) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.

> 5. Again, many more of these with only the extent number varying.
Server: Msg 8986, Level 16, State 1, Line 1
Too many errors found (201) for object ID 0. To see all error messages rerun
the statement using "WITH ALL_ERRORMSGS".
CHECKDB found 28208 allocation errors and 0 consistency errors not
associated with any single object.
CHECKDB found 28208 allocation errors and 0 consistency errors in database
'tempdb'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (tempdb ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Hi John ,
From your descriptions, I understood that your tempdb is growing unexpected
and allocation errors with DBCC CHECKDB. Have I understood you? Correct me
if I was wrong.
First of all, have you upgraded your SQL Server to latest update?
Then, you could perform stop SQL Server services, delete existing
TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is recreated
every time service is restarted.
Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it is
clean now.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael,
You're correct.
We have tried recreating tempdb by stopping SQL, deleting the files and then
restarting. Unfortunately once the nightly reports run again the same
problem recurs.
I am fairly sure that SQL 7 is on the latest service pack, but this will be
checked on Monday.
Before the reports run, DBCC CHECKDB comes back clean, but we haven't tried
DBCC NEWALLOC. Although I am out of the office next week my colleagues are
monitoring this thread and will try your advice.
Thanks,
John.
Michael Cheng [MSFT] wrote:
> Hi John ,
> From your descriptions, I understood that your tempdb is growing
> unexpected and allocation errors with DBCC CHECKDB. Have I understood
> you? Correct me if I was wrong.
> First of all, have you upgraded your SQL Server to latest update?
> Then, you could perform stop SQL Server services, delete existing
> TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is
> recreated every time service is restarted.
> Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it
> is clean now.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
|||Please increase the tempdb size so that you do not run out of space when
running the big reports. You can let tempdb auto grow or preallocate a large
size (the preferred method for high performance).
The error "Could not allocate space for object '(SYSTEM table
id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full." indicates that
you need to increase space in tempdb.
When you have enough space, the problem probably will go away. If not,
please contact microsoft tech support.
DBCC checkdb (tempdb) itself needs more space in tempdb. So you need to have
enough space as well.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:e7buAAWIFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi Michael,
> You're correct.
> We have tried recreating tempdb by stopping SQL, deleting the files and
> then restarting. Unfortunately once the nightly reports run again the
> same problem recurs.
> I am fairly sure that SQL 7 is on the latest service pack, but this will
> be checked on Monday.
> Before the reports run, DBCC CHECKDB comes back clean, but we haven't
> tried DBCC NEWALLOC. Although I am out of the office next week my
> colleagues are monitoring this thread and will try your advice.
> Thanks,
> John.
>
> Michael Cheng [MSFT] wrote:
>

Causes of allocation errors in tempdb

Hello,
Our company produces some software using SQL 7.0 as a back end, one function
of which is the running of several reports overnight. These reports make
use of temporary tables which can cause tempdb to grow to a few hundred
megabytes.
On a handful of our customers' servers, we have seen massive growth in
tempdb (usually consuming all available disk space - several Gb) when
running these reports. On running DBCC CHECKDB against tempdb we have
noticed thousands of allocation errors not associated with any particular
object. I have included details of some of these errors later on with some
commentary.
The first thought has been physical disk errors. All our customers' servers
run on either RAID1 or RAID5 arrays and we have run chkdsk with no errors
found. There have been no errors in the Windows event log suggesting
impending disk failure either.
The other databases (including system databases) have no errors when checked
with DBCC CHECKDB.
If there are any other factors that could lead to this situation I'd
appreciate it if you could let me know.
Many thanks,
John McLusky.
Could not allocate space for object '(SYSTEM table id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full.

> 1. When run on this system, the temporary database had grown so large that
> there was no room for further expansion.
Server: Msg 8999, Level 16, State 1, Line 1
Database tempdb allocation errors prevent further CHECKDB processing.

> 2. I suspect that this is related to the errors that follow shortly!
Server: Msg 1101, Level 17, State 1, Line 1
Could not allocate new page for database 'tempdb'. There are no more pages
available in filegroup DEFAULT. Space can be created by dropping objects,
adding additional files, or allowing file growth.

> 3. as number 1.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:116) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:197) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:198) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:208) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1

> 4. Several more otherwise identical errors with different page numbers
> follow. The SGAM and PFS IDs are identical.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:120) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:128) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:136) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:144) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.

> 5. Again, many more of these with only the extent number varying.
Server: Msg 8986, Level 16, State 1, Line 1
Too many errors found (201) for object ID 0. To see all error messages rerun
the statement using "WITH ALL_ERRORMSGS".
CHECKDB found 28208 allocation errors and 0 consistency errors not
associated with any single object.
CHECKDB found 28208 allocation errors and 0 consistency errors in database
'tempdb'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (tempdb ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.Hi John ,
From your descriptions, I understood that your tempdb is growing unexpected
and allocation errors with DBCC CHECKDB. Have I understood you? Correct me
if I was wrong.
First of all, have you upgraded your SQL Server to latest update?
Then, you could perform stop SQL Server services, delete existing
TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is recreated
every time service is restarted.
Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it is
clean now.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
You're correct.
We have tried recreating tempdb by stopping SQL, deleting the files and then
restarting. Unfortunately once the nightly reports run again the same
problem recurs.
I am fairly sure that SQL 7 is on the latest service pack, but this will be
checked on Monday.
Before the reports run, DBCC CHECKDB comes back clean, but we haven't tried
DBCC NEWALLOC. Although I am out of the office next week my colleagues are
monitoring this thread and will try your advice.
Thanks,
John.
Michael Cheng [MSFT] wrote:
> Hi John ,
> From your descriptions, I understood that your tempdb is growing
> unexpected and allocation errors with DBCC CHECKDB. Have I understood
> you? Correct me if I was wrong.
> First of all, have you upgraded your SQL Server to latest update?
> Then, you could perform stop SQL Server services, delete existing
> TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is
> recreated every time service is restarted.
> Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it
> is clean now.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.|||Please increase the tempdb size so that you do not run out of space when
running the big reports. You can let tempdb auto grow or preallocate a large
size (the preferred method for high performance).
The error "Could not allocate space for object '(SYSTEM table
id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full." indicates that
you need to increase space in tempdb.
When you have enough space, the problem probably will go away. If not,
please contact microsoft tech support.
DBCC checkdb (tempdb) itself needs more space in tempdb. So you need to have
enough space as well.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:e7buAAWIFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi Michael,
> You're correct.
> We have tried recreating tempdb by stopping SQL, deleting the files and
> then restarting. Unfortunately once the nightly reports run again the
> same problem recurs.
> I am fairly sure that SQL 7 is on the latest service pack, but this will
> be checked on Monday.
> Before the reports run, DBCC CHECKDB comes back clean, but we haven't
> tried DBCC NEWALLOC. Although I am out of the office next week my
> colleagues are monitoring this thread and will try your advice.
> Thanks,
> John.
>
> Michael Cheng [MSFT] wrote:
>

Causes of allocation errors in tempdb

Hello,
Our company produces some software using SQL 7.0 as a back end, one function
of which is the running of several reports overnight. These reports make
use of temporary tables which can cause tempdb to grow to a few hundred
megabytes.
On a handful of our customers' servers, we have seen massive growth in
tempdb (usually consuming all available disk space - several Gb) when
running these reports. On running DBCC CHECKDB against tempdb we have
noticed thousands of allocation errors not associated with any particular
object. I have included details of some of these errors later on with some
commentary.
The first thought has been physical disk errors. All our customers' servers
run on either RAID1 or RAID5 arrays and we have run chkdsk with no errors
found. There have been no errors in the Windows event log suggesting
impending disk failure either.
The other databases (including system databases) have no errors when checked
with DBCC CHECKDB.
If there are any other factors that could lead to this situation I'd
appreciate it if you could let me know.
Many thanks,
John McLusky.
Could not allocate space for object '(SYSTEM table id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full.
> 1. When run on this system, the temporary database had grown so large that
> there was no room for further expansion.
Server: Msg 8999, Level 16, State 1, Line 1
Database tempdb allocation errors prevent further CHECKDB processing.
> 2. I suspect that this is related to the errors that follow shortly!
Server: Msg 1101, Level 17, State 1, Line 1
Could not allocate new page for database 'tempdb'. There are no more pages
available in filegroup DEFAULT. Space can be created by dropping objects,
adding additional files, or allowing file growth.
> 3. as number 1.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:116) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:197) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:198) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:208) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
> 4. Several more otherwise identical errors with different page numbers
> follow. The SGAM and PFS IDs are identical.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:120) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:128) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:136) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:144) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
> 5. Again, many more of these with only the extent number varying.
Server: Msg 8986, Level 16, State 1, Line 1
Too many errors found (201) for object ID 0. To see all error messages rerun
the statement using "WITH ALL_ERRORMSGS".
CHECKDB found 28208 allocation errors and 0 consistency errors not
associated with any single object.
CHECKDB found 28208 allocation errors and 0 consistency errors in database
'tempdb'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (tempdb ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.Hi John ,
From your descriptions, I understood that your tempdb is growing unexpected
and allocation errors with DBCC CHECKDB. Have I understood you? Correct me
if I was wrong.
First of all, have you upgraded your SQL Server to latest update?
Then, you could perform stop SQL Server services, delete existing
TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is recreated
every time service is restarted.
Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it is
clean now.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
You're correct.
We have tried recreating tempdb by stopping SQL, deleting the files and then
restarting. Unfortunately once the nightly reports run again the same
problem recurs.
I am fairly sure that SQL 7 is on the latest service pack, but this will be
checked on Monday.
Before the reports run, DBCC CHECKDB comes back clean, but we haven't tried
DBCC NEWALLOC. Although I am out of the office next week my colleagues are
monitoring this thread and will try your advice.
Thanks,
John.
Michael Cheng [MSFT] wrote:
> Hi John ,
> From your descriptions, I understood that your tempdb is growing
> unexpected and allocation errors with DBCC CHECKDB. Have I understood
> you? Correct me if I was wrong.
> First of all, have you upgraded your SQL Server to latest update?
> Then, you could perform stop SQL Server services, delete existing
> TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is
> recreated every time service is restarted.
> Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it
> is clean now.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.|||Please increase the tempdb size so that you do not run out of space when
running the big reports. You can let tempdb auto grow or preallocate a large
size (the preferred method for high performance).
The error "Could not allocate space for object '(SYSTEM table
id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full." indicates that
you need to increase space in tempdb.
When you have enough space, the problem probably will go away. If not,
please contact microsoft tech support.
DBCC checkdb (tempdb) itself needs more space in tempdb. So you need to have
enough space as well.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:e7buAAWIFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi Michael,
> You're correct.
> We have tried recreating tempdb by stopping SQL, deleting the files and
> then restarting. Unfortunately once the nightly reports run again the
> same problem recurs.
> I am fairly sure that SQL 7 is on the latest service pack, but this will
> be checked on Monday.
> Before the reports run, DBCC CHECKDB comes back clean, but we haven't
> tried DBCC NEWALLOC. Although I am out of the office next week my
> colleagues are monitoring this thread and will try your advice.
> Thanks,
> John.
>
> Michael Cheng [MSFT] wrote:
>> Hi John ,
>> From your descriptions, I understood that your tempdb is growing
>> unexpected and allocation errors with DBCC CHECKDB. Have I understood
>> you? Correct me if I was wrong.
>> First of all, have you upgraded your SQL Server to latest update?
>> Then, you could perform stop SQL Server services, delete existing
>> TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is
>> recreated every time service is restarted.
>> Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it
>> is clean now.
>>
>> Sincerely yours,
>> Michael Cheng
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader
>> so that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>

Catching more then one Error

Hello all, not to sure if this can be done but I am attempting to catch a series of errors with in a single TRY CATCH statement.

For example, the following code triggers a single error ( which work fine)

SET NOCOUNT ON
declare @.errmsg nvarchar(4000)
begin try
select 0/0
end try
begin catch
SET @.errmsg = 'Msg ' +
cast(ERROR_NUMBER() as varchar(20)) + ', Level ' +
cast(ERROR_SEVERITY() as varchar(20)) + ', State ' +
cast(ERROR_STATE() as varchar(20)) + ', Line ' +
cast(ERROR_LINE() as varchar(20)) + ', ' + CHAR(13) + 'Procedure ' +
isnull(ERROR_PROCEDURE(),'') + CHAR(13) + isnull(ERROR_MESSAGE(),'')
end catch
select @.errmsg AS ERRMSG

RESULT:
Msg 8134, Level 16, State 1, Line 4, Procedure Divide by zero error encountered.

But when i execute the same TRY CATCH but use a backup routine that i purposly fail the query result shows the following ( which is what i want...)

Msg 4208, Level 16, State 0, Procedure usp_dbbackup2005_v1_5, Line 599
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

Msg 3013, Level 16, State 1, Procedure usp_dbbackup2005_v1_5, Line 599
BACKUP LOG is terminating abnormally.

Instead i only get the last error in the series.

Msg 3013, Level 16, State 1, Procedure usp_dbbackup2005_v1_5, Line 599
BACKUP LOG is terminating abnormally.

Any Thoughts?

Thanks

Anybody have any thoughts on this? i can't see why i couldnt grab all the errors within a transaction.

Thanks

DM

|||

With my understanding about Try..Catch block (any language); it always cath one exception object and transform the control to Catch block from the try block (even C#.NET or VB.NET does the same..); I dont think it is logically possible ..

Folks what about your opinion here..

|||You wont get the stack are any further executed command errors in the error functions.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Bummer, you would think you would be able to grab entire stack upon error. Obviously as far as a client is concerned the entire stack is pushed out.( which is what you would see if the statement was executed from a .net client or query analyzer.) so I guess that begs the question; how do you build a solid error handler if you are not provided all the tools to dissect the issue.

Thanks

DM

Catching errors within a stored procedure

Hi,
Is it possible to set error handling locally to a stored procedure, what
I am trying to do is insert data using a stored procedure and if the
insert fails due to a constraint between the FK of two tables I want to
pass back a friendly message to an ASP page, but what seems to happen is
that SQL handles the error before I can catch it displays the following
message:
INSERT statement conflicted with TABLE FOREIGN KEY constraint
'FK_Directory_sec_employee'. The conflict occurred in database 'CNMS',
table 'Employee'
So within my SP I am adding the following after my insert statement:
IF @.@.ERROR<>0
BEGIN
SELECT' WRONG SEC'
END
Then in my asp page I use the following after my execute command:
if objRS(0) = "WRONG SEC" Then
SEC_ERROR = 1
end if
I am no expert in SQL but my guess is SQL is taking control of the error
which is why this select doesn't work.
Any ideas?
May thanks
Peter
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi Peter.
You cannot suppress the errors being thrown from SQL Server. Although you
can sometimes handle them by checking @.@.error after statements, this doesn't
always work and the circumstances in which it does / doesn't is not clearly
covered in the SQL Server documentation.
TSQL error handling problems have been discussed extensively in these
newsgroups over the years but Erland Sommarskog took the time to write up a
good guide to this confusing topic a few months back. You can and should
read his article which you can find here:
http://www.sommarskog.se/
In short though - you'll need to catch and interpret the sql error in your
application layer. If you're using asp, how you code this will depend on
your scripting language. If Javascript, you'll use try / catch. If VBScript,
you'll use On Error Continue / Goto. If ASP.Net, try / catch in c#, vb, c.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Peter Rooney" <peter@.whoba.co.uk> wrote in message
news:uruFNv02DHA.2308@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Hi,
> Is it possible to set error handling locally to a stored procedure, what
> I am trying to do is insert data using a stored procedure and if the
> insert fails due to a constraint between the FK of two tables I want to
> pass back a friendly message to an ASP page, but what seems to happen is
> that SQL handles the error before I can catch it displays the following
> message:
> INSERT statement conflicted with TABLE FOREIGN KEY constraint
> 'FK_Directory_sec_employee'. The conflict occurred in database 'CNMS',
> table 'Employee'
> So within my SP I am adding the following after my insert statement:
> IF @.@.ERROR<>0
> BEGIN
> SELECT' WRONG SEC'
> END
>
> Then in my asp page I use the following after my execute command:
> if objRS(0) = "WRONG SEC" Then
> SEC_ERROR = 1
> end if
> I am no expert in SQL but my guess is SQL is taking control of the error
> which is why this select doesn't work.
> Any ideas?
> May thanks
> Peter
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

---

Catching errors within a stored procedure

Hi,
Is it possible to set error handling locally to a stored procedure, what
I am trying to do is insert data using a stored procedure and if the
insert fails due to a constraint between the FK of two tables I want to
pass back a friendly message to an ASP page, but what seems to happen is
that SQL handles the error before I can catch it displays the following
message:
INSERT statement conflicted with TABLE FOREIGN KEY constraint
'FK_Directory_sec_employee'. The conflict occurred in database 'CNMS',
table 'Employee'
So within my SP I am adding the following after my insert statement:
IF @.@.ERROR<>0
BEGIN
SELECT' WRONG SEC'
END
Then in my asp page I use the following after my execute command:
if objRS(0) = "WRONG SEC" Then
SEC_ERROR = 1
end if
I am no expert in SQL but my guess is SQL is taking control of the error
which is why this select doesn't work.
Any ideas?
May thanks
Peter
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hi Peter.
You cannot suppress the errors being thrown from SQL Server. Although you
can sometimes handle them by checking @.@.error after statements, this doesn't
always work and the circumstances in which it does / doesn't is not clearly
covered in the SQL Server documentation.
TSQL error handling problems have been discussed extensively in these
newsgroups over the years but Erland Sommarskog took the time to write up a
good guide to this confusing topic a few months back. You can and should
read his article which you can find here:
http://www.sommarskog.se/
In short though - you'll need to catch and interpret the sql error in your
application layer. If you're using asp, how you code this will depend on
your scripting language. If Javascript, you'll use try / catch. If VBScript,
you'll use On Error Continue / Goto. If ASP.Net, try / catch in c#, vb, c.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Peter Rooney" <peter@.whoba.co.uk> wrote in message
news:uruFNv02DHA.2308@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Is it possible to set error handling locally to a stored procedure, what
> I am trying to do is insert data using a stored procedure and if the
> insert fails due to a constraint between the FK of two tables I want to
> pass back a friendly message to an ASP page, but what seems to happen is
> that SQL handles the error before I can catch it displays the following
> message:
> INSERT statement conflicted with TABLE FOREIGN KEY constraint
> 'FK_Directory_sec_employee'. The conflict occurred in database 'CNMS',
> table 'Employee'
> So within my SP I am adding the following after my insert statement:
> IF @.@.ERROR<>0
> BEGIN
> SELECT' WRONG SEC'
> END
>
> Then in my asp page I use the following after my execute command:
> if objRS(0) = "WRONG SEC" Then
> SEC_ERROR = 1
> end if
> I am no expert in SQL but my guess is SQL is taking control of the error
> which is why this select doesn't work.
> Any ideas?
> May thanks
> Peter
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Catching errors in SSIS Backup Database Task

Hi,

In my SSIS package, I have a backup database task. When I run the package with DestinationAutoFolderPath set to a folder ("Network Service" account has full permission on this folder) and DestinationCreationType set to Auto, the task works just fine creating a backup with its own name. (similar to database_date<count>).

But what I want is in my front-end I am allowing the user to specify the name of the backup file. So I want the task to create the backup file in the name I supply. I set the DestinationCreationType to manual and in the application code added the DestinationManualList with the path from the UI.

Now the pacakge runs fine but does not take any backup. There is no errors as well. If I set the FailPackageOnFailure and FailParentOnFailure to true, then I am getting the DTSExecResult.Failure but I am not getting the actual error from the backup database task.

Am I missing anything here?

Thanks in advance,
Srikanth.

How are you executing the package?

What does the logfile (I assume that you have one) say?

-Jamie

|||

Thanks a lot for the reply.

I enabled the log in package and the backup database task. The following is the text appears in the log file:

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
OnPreValidate,SERVER211,SERVER211\Administrator,DICoreDataPackage,{8B470140-EB79-4A15-A2A7-10590065310C},{844D2D28-16D8-42C8-9E1F-3E1506544BE5},8/29/2006 11:31:28 AM,8/29/2006 11:31:28 AM,0,0x,(null)
PackageStart,SERVER211,SERVER211\Administrator,DICoreDataPackage,{8B470140-EB79-4A15-A2A7-10590065310C},{844D2D28-16D8-42C8-9E1F-3E1506544BE5},8/29/2006 11:31:28 AM,8/29/2006 11:31:28 AM,0,0x,Beginning of package execution.

Diagnostic,SERVER211,SERVER211\Administrator,DICoreDataPackage,{8B470140-EB79-4A15-A2A7-10590065310C},{844D2D28-16D8-42C8-9E1F-3E1506544BE5},8/29/2006 11:31:28 AM,8/29/2006 11:31:28 AM,0,0x,Based on the system configuration, the maximum concurrent executables are set to 3.

OnPreExecute,SERVER211,SERVER211\Administrator,DICoreDataPackage,{8B470140-EB79-4A15-A2A7-10590065310C},{844D2D28-16D8-42C8-9E1F-3E1506544BE5},8/29/2006 11:31:28 AM,8/29/2006 11:31:28 AM,0,0x,(null)
PackageEnd,SERVER211,SERVER211\Administrator,DICoreDataPackage,{8B470140-EB79-4A15-A2A7-10590065310C},{844D2D28-16D8-42C8-9E1F-3E1506544BE5},8/29/2006 11:31:29 AM,8/29/2006 11:31:29 AM,1,0x,End of package execution.

The thing is, I configured the DestinationCreationType to "manual" and added my backup file name through coding to DestinationManualList ArrayList:

if (backupTask.DestinationManualList == null)
backupTask.DestinationManualList = new ArrayList();

backupTask.DestinationManualList.Add(m_SSISArgs.DatabaseBackupPath);

The objective is to create backup of the database with the file name I am giving. Do I have to set anyother property for this...

Catching errors and row cnt from SQLdataSource

I'm new to using SQL Data Source, so bare with me on the newbie question.

Is there a way to do a Try...Catch type scenario on the SDS? I have a grid and a SDS that is mapped together but previously I use to use a Try...Catch and show any errors. What can I do to display a message if there is an error with the SDS?

Try
'Call to DB

Catch
label1.txt = "Error: " & ex.Message.ToString

End Try

And is the best way to determine if there are any records to display is to use the SDS_Selected event?

Dim Rec as Integer = e.AffectedRows
If Rec = 0 Then
label1.text = "No Records Found."
End If

To catch errors on a SQLDataSource I use the 'ed' events (ie Selected, Inserted, Deleted and Updated), and check that e.Exception is not null. You can returne.ExceptionHandled = True once you have handled the exception.

e.AffectedRows looks like it would be the best way to count the number of affected records (I can't say I've ever tried to catch that, so there may be other ways I don't know about)

HTH

|||

Hi,
I agree with drktrnq. Below there is a code snippet. I hope it helps you.

1Protected Sub SqlDataSource1_Selected(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles SqlDataSource1.Selected2If (e.Exception IsNotNothing)Then3 Me.Label1.Text = e.Exception.Message4 e.ExceptionHandled =True5 Return6 End If78 If (e.AffectedRows = 0)Then9 Me.Label1.Text ="No records found"10Else11 Me.Label1.Text = e.AffectedRows.ToString()12End If13 End Sub

Luis Ramirez.
www.sqlnetframework.com
The SQL framework for .NET.

catchin errors occured in sql server with DELPHI

hi
i want to know how can I catch an error occured in sql server with DELPHI
when an error occurs in sql server the number of error and description of th
e
error is sent to any programming language.
for example I want to change the message in my application when the error
occures?
but I don't knoe how?
is there any espesial EVENT HANDLER?Are you asking how to do this in Delphi? If so, you are probably better off
posting this in a Delphi forum.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"pooyan_pdm" <pooyanpdm@.discussions.microsoft.com> wrote in message
news:C64FB3F0-1327-4B22-AC22-7F367E9EEE33@.microsoft.com...
> hi
> i want to know how can I catch an error occured in sql server with DELPHI
> when an error occurs in sql server the number of error and description of
> the
> error is sent to any programming language.
> for example I want to change the message in my application when the error
> occures?
> but I don't knoe how?
> is there any espesial EVENT HANDLER?
>

CATCH block question

Hello,
With the new TRY - CATCH block feature in 2005, is there any way for
the calling application to know about errors unless I use RAISERROR?
The reason I ask is, I'd like to start using the new TRY - CATCH
syntax, but I don't know if our code is set up to receive the specific
error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
In fact, I don't even know if our apps are set up to handle return
codes. I think the way things have generally worked is - the stored
procedure is just allowed to break, with no error handling at all.
DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
using RAISERROR, but still have the calling app know about it?
If not, that means we have to rewrite a lot of code, which is not
desireable.
Thanks
Hi
You could return a status value, but your client code should always cater
for RAISERROR and other scenarios where the procedure does not return a value.
John
"tootsuite@.gmail.com" wrote:

> Hello,
> With the new TRY - CATCH block feature in 2005, is there any way for
> the calling application to know about errors unless I use RAISERROR?
> The reason I ask is, I'd like to start using the new TRY - CATCH
> syntax, but I don't know if our code is set up to receive the specific
> error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
> In fact, I don't even know if our apps are set up to handle return
> codes. I think the way things have generally worked is - the stored
> procedure is just allowed to break, with no error handling at all.
> DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
> SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
> RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
> So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
> using RAISERROR, but still have the calling app know about it?
> If not, that means we have to rewrite a lot of code, which is not
> desireable.
> Thanks
>

CATCH block question

Hello,
With the new TRY - CATCH block feature in 2005, is there any way for
the calling application to know about errors unless I use RAISERROR?
The reason I ask is, I'd like to start using the new TRY - CATCH
syntax, but I don't know if our code is set up to receive the specific
error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
In fact, I don't even know if our apps are set up to handle return
codes. I think the way things have generally worked is - the stored
procedure is just allowed to break, with no error handling at all.
DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
using RAISERROR, but still have the calling app know about it?
If not, that means we have to rewrite a lot of code, which is not
desireable.
Thanks
You have to use RAISERROR to pass a logic error back to the application.
Of course, any unhandled errors, or critical SQL Server errors 'may' send an
error back to the application without having to raise an error.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1160429120.981006.283160@.e3g2000cwe.googlegro ups.com...
> Hello,
> With the new TRY - CATCH block feature in 2005, is there any way for
> the calling application to know about errors unless I use RAISERROR?
> The reason I ask is, I'd like to start using the new TRY - CATCH
> syntax, but I don't know if our code is set up to receive the specific
> error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
> In fact, I don't even know if our apps are set up to handle return
> codes. I think the way things have generally worked is - the stored
> procedure is just allowed to break, with no error handling at all.
> DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
> SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
> RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
> So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
> using RAISERROR, but still have the calling app know about it?
> If not, that means we have to rewrite a lot of code, which is not
> desireable.
> Thanks
>
|||<tootsuite@.gmail.com> wrote in message
news:1160429120.981006.283160@.e3g2000cwe.googlegro ups.com...
> Hello,
> With the new TRY - CATCH block feature in 2005, is there any way for
> the calling application to know about errors unless I use RAISERROR?
> The reason I ask is, I'd like to start using the new TRY - CATCH
> syntax, but I don't know if our code is set up to receive the specific
> error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
> In fact, I don't even know if our apps are set up to handle return
> codes. I think the way things have generally worked is - the stored
> procedure is just allowed to break, with no error handling at all.
> DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
> SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
> RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
> So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
> using RAISERROR, but still have the calling app know about it?
>
No. Applications typically expect to receive an error message when
something goes wrong. For instance .NET clients will receive the error
message and raise a SqlException, thus invoking the client-side structured
exception handling. Conceptually this allows an error to propagate from
server code to client code.

> If not, that means we have to rewrite a lot of code, which is not
> desireable.
>
Probably not. For most clients, using RAISERROR to "re-raise" the error
"just works".
David

CATCH block question

Hello,
With the new TRY - CATCH block feature in 2005, is there any way for
the calling application to know about errors unless I use RAISERROR'
The reason I ask is, I'd like to start using the new TRY - CATCH
syntax, but I don't know if our code is set up to receive the specific
error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
In fact, I don't even know if our apps are set up to handle return
codes. I think the way things have generally worked is - the stored
procedure is just allowed to break, with no error handling at all.
DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
using RAISERROR, but still have the calling app know about it?
If not, that means we have to rewrite a lot of code, which is not
desireable.
ThanksYou have to use RAISERROR to pass a logic error back to the application.
Of course, any unhandled errors, or critical SQL Server errors 'may' send an
error back to the application without having to raise an error.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1160429120.981006.283160@.e3g2000cwe.googlegroups.com...
> Hello,
> With the new TRY - CATCH block feature in 2005, is there any way for
> the calling application to know about errors unless I use RAISERROR'
> The reason I ask is, I'd like to start using the new TRY - CATCH
> syntax, but I don't know if our code is set up to receive the specific
> error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
> In fact, I don't even know if our apps are set up to handle return
> codes. I think the way things have generally worked is - the stored
> procedure is just allowed to break, with no error handling at all.
> DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
> SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
> RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
> So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
> using RAISERROR, but still have the calling app know about it?
> If not, that means we have to rewrite a lot of code, which is not
> desireable.
> Thanks
>|||<tootsuite@.gmail.com> wrote in message
news:1160429120.981006.283160@.e3g2000cwe.googlegroups.com...
> Hello,
> With the new TRY - CATCH block feature in 2005, is there any way for
> the calling application to know about errors unless I use RAISERROR'
> The reason I ask is, I'd like to start using the new TRY - CATCH
> syntax, but I don't know if our code is set up to receive the specific
> error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
> In fact, I don't even know if our apps are set up to handle return
> codes. I think the way things have generally worked is - the stored
> procedure is just allowed to break, with no error handling at all.
> DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
> SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
> RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
> So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
> using RAISERROR, but still have the calling app know about it?
>
No. Applications typically expect to receive an error message when
something goes wrong. For instance .NET clients will receive the error
message and raise a SqlException, thus invoking the client-side structured
exception handling. Conceptually this allows an error to propagate from
server code to client code.

> If not, that means we have to rewrite a lot of code, which is not
> desireable.
>
Probably not. For most clients, using RAISERROR to "re-raise" the error
"just works".
David

CATCH block question

Hello,
With the new TRY - CATCH block feature in 2005, is there any way for
the calling application to know about errors unless I use RAISERROR'
The reason I ask is, I'd like to start using the new TRY - CATCH
syntax, but I don't know if our code is set up to receive the specific
error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
In fact, I don't even know if our apps are set up to handle return
codes. I think the way things have generally worked is - the stored
procedure is just allowed to break, with no error handling at all.
DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
using RAISERROR, but still have the calling app know about it?
If not, that means we have to rewrite a lot of code, which is not
desireable.
ThanksHi
You could return a status value, but your client code should always cater
for RAISERROR and other scenarios where the procedure does not return a valu
e.
John
"tootsuite@.gmail.com" wrote:

> Hello,
> With the new TRY - CATCH block feature in 2005, is there any way for
> the calling application to know about errors unless I use RAISERROR'
> The reason I ask is, I'd like to start using the new TRY - CATCH
> syntax, but I don't know if our code is set up to receive the specific
> error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
> In fact, I don't even know if our apps are set up to handle return
> codes. I think the way things have generally worked is - the stored
> procedure is just allowed to break, with no error handling at all.
> DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
> SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
> RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
> So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
> using RAISERROR, but still have the calling app know about it?
> If not, that means we have to rewrite a lot of code, which is not
> desireable.
> Thanks
>

CATCH block question

Hello,
With the new TRY - CATCH block feature in 2005, is there any way for
the calling application to know about errors unless I use RAISERROR'
The reason I ask is, I'd like to start using the new TRY - CATCH
syntax, but I don't know if our code is set up to receive the specific
error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
In fact, I don't even know if our apps are set up to handle return
codes. I think the way things have generally worked is - the stored
procedure is just allowed to break, with no error handling at all.
DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
using RAISERROR, but still have the calling app know about it?
If not, that means we have to rewrite a lot of code, which is not
desireable.
ThanksYou have to use RAISERROR to pass a logic error back to the application.
Of course, any unhandled errors, or critical SQL Server errors 'may' send an
error back to the application without having to raise an error.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1160429120.981006.283160@.e3g2000cwe.googlegroups.com...
> Hello,
> With the new TRY - CATCH block feature in 2005, is there any way for
> the calling application to know about errors unless I use RAISERROR'
> The reason I ask is, I'd like to start using the new TRY - CATCH
> syntax, but I don't know if our code is set up to receive the specific
> error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
> In fact, I don't even know if our apps are set up to handle return
> codes. I think the way things have generally worked is - the stored
> procedure is just allowed to break, with no error handling at all.
> DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
> SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
> RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
> So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
> using RAISERROR, but still have the calling app know about it?
> If not, that means we have to rewrite a lot of code, which is not
> desireable.
> Thanks
>|||<tootsuite@.gmail.com> wrote in message
news:1160429120.981006.283160@.e3g2000cwe.googlegroups.com...
> Hello,
> With the new TRY - CATCH block feature in 2005, is there any way for
> the calling application to know about errors unless I use RAISERROR'
> The reason I ask is, I'd like to start using the new TRY - CATCH
> syntax, but I don't know if our code is set up to receive the specific
> error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
> In fact, I don't even know if our apps are set up to handle return
> codes. I think the way things have generally worked is - the stored
> procedure is just allowed to break, with no error handling at all.
> DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
> SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
> RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
> So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
> using RAISERROR, but still have the calling app know about it?
>
No. Applications typically expect to receive an error message when
something goes wrong. For instance .NET clients will receive the error
message and raise a SqlException, thus invoking the client-side structured
exception handling. Conceptually this allows an error to propagate from
server code to client code.
> If not, that means we have to rewrite a lot of code, which is not
> desireable.
>
Probably not. For most clients, using RAISERROR to "re-raise" the error
"just works".
David

CATCH block question

Hello,
With the new TRY - CATCH block feature in 2005, is there any way for
the calling application to know about errors unless I use RAISERROR'
The reason I ask is, I'd like to start using the new TRY - CATCH
syntax, but I don't know if our code is set up to receive the specific
error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
In fact, I don't even know if our apps are set up to handle return
codes. I think the way things have generally worked is - the stored
procedure is just allowed to break, with no error handling at all.
DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
using RAISERROR, but still have the calling app know about it?
If not, that means we have to rewrite a lot of code, which is not
desireable.
ThanksHi
You could return a status value, but your client code should always cater
for RAISERROR and other scenarios where the procedure does not return a value.
John
"tootsuite@.gmail.com" wrote:
> Hello,
> With the new TRY - CATCH block feature in 2005, is there any way for
> the calling application to know about errors unless I use RAISERROR'
> The reason I ask is, I'd like to start using the new TRY - CATCH
> syntax, but I don't know if our code is set up to receive the specific
> error codes such as ERROR_MESSAGE or ERROR_SEVERITY (as shown below).
> In fact, I don't even know if our apps are set up to handle return
> codes. I think the way things have generally worked is - the stored
> procedure is just allowed to break, with no error handling at all.
> DECLARE @.ErrMsg nvarchar(4000), @.ErrSeverity int
> SELECT @.ErrMsg = ERROR_MESSAGE(), @.ErrSeverity = ERROR_SEVERITY()
> RAISERROR (@.ErrMsg, @.ErrSeverity, 1)
> So, I'm wondering, is it possible to use TRY - CATCH block WITHOUT
> using RAISERROR, but still have the calling app know about it?
> If not, that means we have to rewrite a lot of code, which is not
> desireable.
> Thanks
>