I run two batches at night time:
1) A batch that updates all the records one by one to
calculate special interest on accounts.
2) The databas optimization batch that cleans up pages,
free space, rebuilds indexes, etc.
My batch 1 is failing with this error: "Key column
information is insufficient or incorrect. Too many rows
were affected by update." I do know that batch 2 is
running in tbe background still. They both take around 3-4
hours each. It's hard to schedual them apart enough to
keep them from running at the same time because their run
lengths are so unpredictable.
Any ideas?
My updates are using ado 2.7
Jason RoozeeThis is a multi-part message in MIME format.
--=_NextPart_000_012C_01C37398.83003D00
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
My guess is that batch 1 is using a cursor or raw ADO to do updates on a =table that does not have a primary key. Ensure that all tables have =primary keys and that you make every effort to replace row-by-row =processing with set-level processing.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jason Roozee" <jason@.camcoinc.net> wrote in message =news:2bb701c373b9$2999dfc0$a401280a@.phx.gbl...
I run two batches at night time:
1) A batch that updates all the records one by one to calculate special interest on accounts.
2) The databas optimization batch that cleans up pages, free space, rebuilds indexes, etc.
My batch 1 is failing with this error: "Key column information is insufficient or incorrect. Too many rows were affected by update." I do know that batch 2 is running in tbe background still. They both take around 3-4 hours each. It's hard to schedual them apart enough to keep them from running at the same time because their run lengths are so unpredictable.
Any ideas?
My updates are using ado 2.7
Jason Roozee
--=_NextPart_000_012C_01C37398.83003D00
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
My guess is that batch 1 is using a =cursor or raw ADO to do updates on a table that does not have a primary key. =Ensure that all tables have primary keys and that you make every effort to replace row-by-row processing with set-level processing.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jason Roozee"
wrote in =message news:2bb701c373b9$29=99dfc0$a401280a@.phx.gbl...I run two batches at night time:1) A batch that updates all the =records one by one to calculate special interest on accounts.2) The =databas optimization batch that cleans up pages, free space, rebuilds =indexes, etc.My batch 1 is failing with this error: "Key column =information is insufficient or incorrect. Too many rows were affected by =update." I do know that batch 2 is running in tbe background still. They both take =around 3-4 hours each. It's hard to schedual them apart enough to keep =them from running at the same time because their run lengths are so unpredictable. Any ideas?My updates are using ado 2.7Jason Roozee
--=_NextPart_000_012C_01C37398.83003D00--|||This is a multi-part message in MIME format.
--=_NextPart_000_0166_01C3739A.E6D14CA0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Ste-level process avoids the use of cursors and loops. Here, you would =use, say, a single UPDATE statement and apply the calculation across all =of the target rows.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jason Roozee" <jason@.camcoinc.net> wrote in message =news:2c0e01c373bb$e9832330$a401280a@.phx.gbl...
Yes, all the tables have a primary key and it's included in the select list of fields in my ADO record set...
"SET LEVEL" processing' Explain.
Jason
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Jason Roozee" <jason@.camcoinc.net> wrote in message news:2bb701c373b9$2999dfc0$a401280a@.phx.gbl...
>I run two batches at night time:
>1) A batch that updates all the records one by one to >calculate special interest on accounts.
>2) The databas optimization batch that cleans up pages, >free space, rebuilds indexes, etc.
>My batch 1 is failing with this error: "Key column >information is insufficient or incorrect. Too many rows >were affected by update." I do know that batch 2 is >running in tbe background still. They both take around 3-
4 >hours each. It's hard to schedual them apart enough to >keep them from running at the same time because their run >lengths are so unpredictable. >
>Any ideas?
>My updates are using ado 2.7
>Jason Roozee
>
--=_NextPart_000_0166_01C3739A.E6D14CA0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Ste-level process avoids the use of =cursors and loops. Here, you would use, say, a single UPDATE statement and =apply the calculation across all of the target rows.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jason Roozee" wrote in =message news:2c0e01c373bb$e9=832330$a401280a@.phx.gbl...Yes, all the tables have a primary key and it's included in the select =list of fields in my ADO record set..."SET LEVEL" processing' Explain.Jason>Columnist, SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Jaso=n Roozee" wrote in message news:2bb701c373b9$29=99dfc0$a401280a@.phx.gbl...>>I run two batches at night time:>>1) A batch that updates =all the records one by one to >calculate special interest on accounts.>>2) The databas optimization batch that cleans =up pages, >free space, rebuilds indexes, etc.>>My batch 1 is =failing with this error: "Key column >information is insufficient or =incorrect. Too many rows >were affected by update." I do know that batch 2 =is >running in tbe background still. They both take around 3-4 >hours each. It's hard to schedual them apart enough to =>keep them from running at the same time because their run >lengths are so unpredictable. >>>Any ideas?>>My =updates are using ado 2.7>>Jason Roozee>
--=_NextPart_000_0166_01C3739A.E6D14CA0--
