Chain transact sql scripts

I have two Transact SQL scripts and I want to call the second script from the first –
is this possible in SQL server 2005?
I am trying to do a port from Oracle (where this is possible) but cannot find the mechanism to do so in SQL Server.

Eg if Script1.sql is
BEGIN
PRINT (‘Inside Script1’)
//Invoke Script2.sql – how do I do this?
END

where Script2.sql is
BEGIN
PRINT (‘Inside Script2’)
END

When I execute Script1.sql - I need it to print both 'Inside Script2' and 'Inside Script1'

You can do this using the :r command if you invoke the script using the new SQLCMD command-line utility. It also has other script pre-processing features. You can take a look at the Books Online topic at link below:

http://msdn2.microsoft.com/en-us/library/ms162773(SQL.90).aspx

|||We can use the :r option inside sqlcmd - but was looking for Transact SQL support (without using xp_cmdshell to spawn "sqlcmd" inside a larger script) - Inside a Oracle pl/sql script, we use "@.<scriptname> from a enclosing script" - was looking for similar functionality in Transact SQL|||

I would suggest the best way would be to place the code inside two stored procedures, and call one from the other.

HTH

|||

shuges is right

store procedure can call another sp

using the exec "spname" syntax

also sql queries can be nested so it is possible to

rewrite two sps into one.

moreover you can also make use of functions

|||The :r SQLCMD command is the equivalent of @. command in SQLPLUS. These are client-side features not part of the TSQL or PL/SQL language.

Chain of triggers - how to break it?

I have an application to capture and process timesheet information.
Put simply, employees clock in and out at various locations. Business
logic determines how to process these events.

The system allows an administrator (though it's not strictly relevant,
this is through an ASP.NET front end) to determine "rules" for
employees. A rule will consist of a Rostered Start Time (the time that
an employee or group of employees is expected to "clock on"), a
threshold allowance for early and late starts, and a Minimum and a
Maximum number of minutes that an employee is expected to work. This
table is called tblTSRule (see DDL below).

Another part of the system assigns a rule to one or more employees.
The table that stores this data is called tblTSEmpRules. The columns
in this table contain links to the tblTSRule table, the tblEmployee
table, the date for which this rule applies, and the two fields that
are at the heart of this question. These are fldLowerBound and
fldUpperBound.

All employee timesheet entries (which are via a barcode scan on their
id badge) are simply raw data - the system captures the EmployeeID, the
location and the time of the scan. It doesn't differentiate between a
"clock on", a "clock off" or a "sub duty" (where an employee has left
their normal place of work to do a rostered duty at another location).
These raw "scans" are processed en bloc at a later date. To
illustrate, the UI presents the user with a range of dates, and the
user can then "apply the rules" to a date or range of dates.

At this point the system needs to collate all the raw scans according
to the date that they wish to process. However, let's say that the
date is the 31st January, but some of the employees for that date are
working a late shift. Logically, therefore, some of the relevant scans
will actually occur on 1 February.

In order to get all the relevant scans for each employee, therefore,
the "rule" for an employee for any particular date will contain the
Lower and Upper bounds between which all raw scans should be processed.
We found that with a typical data load of around 1,200,000 records
that processing was very slow if the lower and upper bounds were
calculated "on the fly" in the SQL, so instead we decided to store this
calculated data in the table itself. Codd may not like it, but it's
expedient.

The two fields fldLowerBound and fldUpperBound are calculated or
recalculated EITHER when a row in the tblTSRule table is updated or
when rows in the tblTSEmpRules table are either inserted or updated.
This is done via INSERT and UPDATE triggers. There is no requirement
to have an INSERT trigger on the tblTSRule, since creating a new rule
automatically removes the possibility that there are any related rows
in the tblTSEmpRules table. The triggers are scripted below.

What we are finding is that if an update is made on the tblTSRule
table, it fires TWO triggers - first the UPDATE trigger on the
tblTSRule table (which updates the tblTSEmpRules table) and then the
UPDATE trigger on the tblTSEmpRules table. The two triggers are
virtually identical, but what can we do in terms of design to get
around this? I should point out that the ability exists in the
application to amend both rules and the assignment of rules to
employees.

Thank you for reading this far. DDL below.

Edward

========================

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblTSEmpRules_tblTSRules]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblTSEmpRules] DROP CONSTRAINT
FK_tblTSEmpRules_tblTSRules
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CreateEmpCaptureBound]') and OBJECTPROPERTY(id,
N'IsTrigger') = 1)
drop trigger [dbo].[CreateEmpCaptureBound]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CreateCaptureBound]') and OBJECTPROPERTY(id,
N'IsTrigger') = 1)
drop trigger [dbo].[CreateCaptureBound]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[UpdateCaptureBound]') and OBJECTPROPERTY(id,
N'IsTrigger') = 1)
drop trigger [dbo].[UpdateCaptureBound]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblTSEmpRules]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblTSEmpRules]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblTSRule]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblTSRule]
GO

CREATE TABLE [dbo].[tblTSEmpRules] (
[fldEmpRuleID] [int] IDENTITY (1, 1) NOT NULL ,
[fldDate] [datetime] NULL ,
[fldEmployeeID] [int] NULL ,
[fldRuleID] [int] NULL ,
[fldLowerBound] [datetime] NOT NULL ,
[fldUpperBound] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTSRule] (
[fldRuleID] [int] IDENTITY (1, 1) NOT NULL ,
[fldCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[fldDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldRosteredStart] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[fldEarlyStartArb] [int] NULL ,
[fldLateStartArb] [int] NULL ,
[fldMinMins] [int] NULL ,
[fldMaxMins] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSEmpRules] WITH NOCHECK ADD
CONSTRAINT [PK_tblTSEmpRules] PRIMARY KEY CLUSTERED
(
[fldEmpRuleID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSRule] WITH NOCHECK ADD
CONSTRAINT [PK_tblTimesheetRules] PRIMARY KEY CLUSTERED
(
[fldRuleID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSEmpRules] WITH NOCHECK ADD
CONSTRAINT [DF_tblTSEmpRules_fldLowerBound] DEFAULT (getdate()) FOR
[fldLowerBound],
CONSTRAINT [DF_tblTSEmpRules_fldUpperBound] DEFAULT (getdate()) FOR
[fldUpperBound]
GO

ALTER TABLE [dbo].[tblTSRule] WITH NOCHECK ADD
CONSTRAINT [DF_tblTSRule_fldRosteredStart] DEFAULT ('00:00') FOR
[fldRosteredStart],
CONSTRAINT [DF_tblTSRule_fldMaxMins] DEFAULT (0) FOR [fldMaxMins],
CONSTRAINT [IX_tblTSRules_1] UNIQUE NONCLUSTERED
(
[fldCode],
[fldSubAreaCode]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE UNIQUE INDEX [IX_tblTSEmpRules] ON
[dbo].[tblTSEmpRules]([fldEmployeeID], [fldDate]) WITH FILLFACTOR = 90
ON [PRIMARY]
GO

CREATE INDEX [IX_tblTSEmpRules_1] ON [dbo].[tblTSEmpRules]([fldDate])
WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_tblTSRules] ON [dbo].[tblTSRule]([fldCode]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSEmpRules] ADD
CONSTRAINT [FK_tblTSEmpRules_tblEmployee1] FOREIGN KEY
(
[fldEmployeeID]
) REFERENCES [dbo].[tblEmployee] (
[fldEmployeeID]
),
CONSTRAINT [FK_tblTSEmpRules_tblTSRules] FOREIGN KEY
(
[fldRuleID]
) REFERENCES [dbo].[tblTSRule] (
[fldRuleID]
) NOT FOR REPLICATION
GO

ALTER TABLE [dbo].[tblTSRule] ADD
CONSTRAINT [FK_tblTSRules_tblSubArea] FOREIGN KEY
(
[fldSubAreaCode]
) REFERENCES [dbo].[tblSubArea] (
[fldSubAreaCode]
) NOT FOR REPLICATION
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER CreateEmpCaptureBound ON dbo.tblTSEmpRules

FOR INSERT,UPDATE

AS

-- Calculate the EARLIEST POSSIBLE clock in time for this
employee/rule/date record and write it to fldLowerBound.
-- Calculate the LATEST POSSIBLE clock out time for this employee/rule
record/date and write it to fldUpperBound.

-- Write data to ALL affected rows.

update tblTSEmpRules

set
fldLowerBound =
dateadd(
mi,
( tblTSRule.fldMaxMins - 1440 ) * 0.5,
dbo.fnGetDateFromDateAndVarCharTimeParts(-- Rostered start date &
time
inserted.fldDate, -- Date on which rule is to be applied
tblTSRule.fldRosteredStart)-- Time
),

fldUpperBound =
dateadd(mi,
( tblTSRule.fldMaxMins + 1440 ) * 0.5,
dbo.fnGetDateFromDateAndVarCharTimeParts(-- Rostered start date &
time
inserted.fldDate,-- Date on which rule is to be applied
tblTSRule.fldRosteredStart)-- Time
)

FROM inserted INNER JOIN
tblTSRule ON inserted.fldRuleID =
tblTSRule.fldRuleID

WHERE inserted.fldEmpRuleID=tblTSEmpRules.fldEmpRuleID

print 'Trigger for TSEmpRules fired...'

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER UpdateCaptureBound ON dbo.tblTSRule

FOR UPDATE

AS

update tblTSEmpRules

set
fldLowerBound =
dateadd(
mi,
( inserted.fldMaxMins - 1440 ) * 0.5,
dbo.fnGetDateFromDateAndVarCharTimeParts(-- Rostered start date &
time
tblTSEmpRules.fldDate, -- Date on which rule is to be applied
inserted.fldRosteredStart)-- Time
),

fldUpperBound =
dateadd(mi,
( inserted.fldMaxMins + 1440 ) * 0.5,
dbo.fnGetDateFromDateAndVarCharTimeParts(-- Rostered start date &
time
tblTSEmpRules.fldDate,-- Date on which rule is to be applied
inserted.fldRosteredStart)-- Time
)

FROM inserted

WHERE inserted.fldRuleID = tblTSEmpRules.fldRuleID

print 'update trigger for TSRule fired.....'

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO(teddysnips@.hotmail.com) writes:
> What we are finding is that if an update is made on the tblTSRule
> table, it fires TWO triggers - first the UPDATE trigger on the
> tblTSRule table (which updates the tblTSEmpRules table) and then the
> UPDATE trigger on the tblTSEmpRules table. The two triggers are
> virtually identical, but what can we do in terms of design to get
> around this? I should point out that the ability exists in the
> application to amend both rules and the assignment of rules to
> employees.

There are a couple of options.

1) Set the configuration option "nested triggers" to 0. But this is a
server-wide option. I recommend that you leave it on.

2) In the trigger tblTSEmpRules add:

IF NOT EXISTS (SELECT * FROM inserted WHERE
fldLowerBound IS NULL OR fldUpperBound IS NULL)
RETURN

3) In the trigger TSRules add:

CREATE TABLE #no$cascade(a int NOT NULL)

And in the other trigger add:

IF object_id('tempdb..#no$cascade') IS NOT NULL
RETURN

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> I have an application to capture and process timesheet information. Put simply, employees clock in and out at various locations. Business logic determines how to process these events... <<

Take a look at the job clock at http://www.exaktime.com. I designed
their database, and I did not need triggers.

I am writing questions for two exams do not have time to go thru your
code right (and clean out all the "tbl-" and 'fld-" prefixes, bring it
up to ISO-11179, remvoe IDENTITY columns, etc.) But based on a quick
scan, it looks like it could made much easier.

Chain Multiplication on a column

Can someone point me out a function that return the multiplication of all row contents from a column?

It should work like the sum() function, but instead of the summary.. it will return the chain multiplication result. Here is what I need it to do.

x
---
4
2
3
7

I need a function "multiply" so that when I issued "Select multiply(x) ...group by..." It would return 168 which is 4*2*3*7

I need to do this in DB2, I checked the DB2 UDF, but looks like there is no simple way to create a customized column function like this.

I'll be really appreciated if someone can help.

Quote:

Originally Posted by janet04

Can someone point me out a function that return the multiplication of all row contents from a column?

It should work like the sum() function, but instead of the summary.. it will return the chain multiplication result. Here is what I need it to do.

x
---
4
2
3
7

I need a function "multiply" so that when I issued "Select multiply(x) ...group by..." It would return 168 which is 4*2*3*7

I need to do this in DB2, I checked the DB2 UDF, but looks like there is no simple way to create a customized column function like this.

I'll be really appreciated if someone can help.

--------------
Reply : select exp(sum(ln(val_num))) from test_multiply;|||select exp(sum(ln(val_num))) from test_multiply;

It will solve your purpose

Chain Linkage Problem

Hi All,
I am facing this error continously:
Error: 8908, Severity: 22, State: 6
Table error: Database ID 7, object ID 477400920, index ID 0. Chain
linkage
mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev =
(1:736799)..
And when I run DBCC CHECKTABLE it shows consistency errors.
It get fixed using DBCC DBReIndex but re-occur after sometimes.
I have tried to find in newsgroup, some of them says it can happen due to
NOLOCK hint.
Is there any solution for this problem or please help me to identify the
root cause of the problem.
Thanks
Ritesh
Hi
KB article 308886 describes the issue, butthose errors occur in that query
only and do not affect data. In you case, you are showing data issues.
Have you run DBCC CheckDB and what version of SQL are you on?
Regards
Mike
"Ritesh" wrote:

> Hi All,
> I am facing this error continously:
> Error: 8908, Severity: 22, State: 6
> Table error: Database ID 7, object ID 477400920, index ID 0. Chain
> linkage
> mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev =
> (1:736799)..
> And when I run DBCC CHECKTABLE it shows consistency errors.
> It get fixed using DBCC DBReIndex but re-occur after sometimes.
> I have tried to find in newsgroup, some of them says it can happen due to
> NOLOCK hint.
> Is there any solution for this problem or please help me to identify the
> root cause of the problem.
> Thanks
> Ritesh
>
>
|||Hi Mike,
Thanks for your response.
We are using Enterprise version of SQL 2000. Consistency errors get resolved
using DBCC DBReIndex but it is a re-occuring problem. Presently our server
is handling heavy traffic and according business logic needs we need to
access just inserted data, say Top 50 records desc by CreatedDateTime for
avoiding delay due to locks we use NOLock hints.
Will also like to know from you, though out of context, is there any way to
compute Fillfactor for indexes or just hit and trial is the only way. I have
a table which is more than 750 MB in size and in every 1 second atleast 10
records are getting inserted ( with updates also). Putting 90 or 85 can have
its effects but want to know exact FACTORS to be used, if any?
Will appreciate your help in this regard and think it may also help in
resolving the original issue (which may have this as its root cause)
Thanks & Regards,
Ritesh Khanna
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> KB article 308886 describes the issue, butthose errors occur in that query
> only and do not affect data. In you case, you are showing data issues.
> Have you run DBCC CheckDB and what version of SQL are you on?
> Regards
> Mike
> "Ritesh" wrote:

Chain Linkage Problem

Hi All,
I am facing this error continously:
Error: 8908, Severity: 22, State: 6
Table error: Database ID 7, object ID 477400920, index ID 0. Chain
linkage
mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev = (1:736799)..
And when I run DBCC CHECKTABLE it shows consistency errors.
It get fixed using DBCC DBReIndex but re-occur after sometimes.
I have tried to find in newsgroup, some of them says it can happen due to
NOLOCK hint.
Is there any solution for this problem or please help me to identify the
root cause of the problem.
Thanks
RiteshHi
KB article 308886 describes the issue, butthose errors occur in that query
only and do not affect data. In you case, you are showing data issues.
Have you run DBCC CheckDB and what version of SQL are you on?
Regards
Mike
"Ritesh" wrote:
> Hi All,
> I am facing this error continously:
> Error: 8908, Severity: 22, State: 6
> Table error: Database ID 7, object ID 477400920, index ID 0. Chain
> linkage
> mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev => (1:736799)..
> And when I run DBCC CHECKTABLE it shows consistency errors.
> It get fixed using DBCC DBReIndex but re-occur after sometimes.
> I have tried to find in newsgroup, some of them says it can happen due to
> NOLOCK hint.
> Is there any solution for this problem or please help me to identify the
> root cause of the problem.
> Thanks
> Ritesh
>
>|||Hi Mike,
Thanks for your response.
We are using Enterprise version of SQL 2000. Consistency errors get resolved
using DBCC DBReIndex but it is a re-occuring problem. Presently our server
is handling heavy traffic and according business logic needs we need to
access just inserted data, say Top 50 records desc by CreatedDateTime for
avoiding delay due to locks we use NOLock hints.
Will also like to know from you, though out of context, is there any way to
compute Fillfactor for indexes or just hit and trial is the only way. I have
a table which is more than 750 MB in size and in every 1 second atleast 10
records are getting inserted ( with updates also). Putting 90 or 85 can have
its effects but want to know exact FACTORS to be used, if any?
Will appreciate your help in this regard and think it may also help in
resolving the original issue (which may have this as its root cause)
Thanks & Regards,
Ritesh Khanna
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> KB article 308886 describes the issue, butthose errors occur in that query
> only and do not affect data. In you case, you are showing data issues.
> Have you run DBCC CheckDB and what version of SQL are you on?
> Regards
> Mike
> "Ritesh" wrote:
> > Hi All,
> >
> > I am facing this error continously:
> >
> > Error: 8908, Severity: 22, State: 6
> >
> > Table error: Database ID 7, object ID 477400920, index ID 0. Chain
> > linkage
> > mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev => > (1:736799)..
> >
> > And when I run DBCC CHECKTABLE it shows consistency errors.
> > It get fixed using DBCC DBReIndex but re-occur after sometimes.
> >
> > I have tried to find in newsgroup, some of them says it can happen due to
> > NOLOCK hint.
> >
> > Is there any solution for this problem or please help me to identify the
> > root cause of the problem.
> >
> > Thanks
> > Ritesh
> >
> >
> >

Chain Linkage Problem

Hi All,
I am facing this error continously:
Error: 8908, Severity: 22, State: 6
Table error: Database ID 7, object ID 477400920, index ID 0. Chain
linkage
mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev =
(1:736799)..
And when I run DBCC CHECKTABLE it shows consistency errors.
It get fixed using DBCC DBReIndex but re-occur after sometimes.
I have tried to find in newsgroup, some of them says it can happen due to
NOLOCK hint.
Is there any solution for this problem or please help me to identify the
root cause of the problem.
Thanks
RiteshHi
KB article 308886 describes the issue, butthose errors occur in that query
only and do not affect data. In you case, you are showing data issues.
Have you run DBCC CheckDB and what version of SQL are you on?
Regards
Mike
"Ritesh" wrote:

> Hi All,
> I am facing this error continously:
> Error: 8908, Severity: 22, State: 6
> Table error: Database ID 7, object ID 477400920, index ID 0. Chain
> linkage
> mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev =
> (1:736799)..
> And when I run DBCC CHECKTABLE it shows consistency errors.
> It get fixed using DBCC DBReIndex but re-occur after sometimes.
> I have tried to find in newsgroup, some of them says it can happen due to
> NOLOCK hint.
> Is there any solution for this problem or please help me to identify the
> root cause of the problem.
> Thanks
> Ritesh
>
>|||Hi Mike,
Thanks for your response.
We are using Enterprise version of SQL 2000. Consistency errors get resolved
using DBCC DBReIndex but it is a re-occuring problem. Presently our server
is handling heavy traffic and according business logic needs we need to
access just inserted data, say Top 50 records desc by CreatedDateTime for
avoiding delay due to locks we use NOLock hints.
Will also like to know from you, though out of context, is there any way to
compute Fillfactor for indexes or just hit and trial is the only way. I have
a table which is more than 750 MB in size and in every 1 second atleast 10
records are getting inserted ( with updates also). Putting 90 or 85 can have
its effects but want to know exact FACTORS to be used, if any?
Will appreciate your help in this regard and think it may also help in
resolving the original issue (which may have this as its root cause)
Thanks & Regards,
Ritesh Khanna
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> KB article 308886 describes the issue, butthose errors occur in that query
> only and do not affect data. In you case, you are showing data issues.
> Have you run DBCC CheckDB and what version of SQL are you on?
> Regards
> Mike
> "Ritesh" wrote:
>

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