[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Why does this query fail only SOMETIMES

(Mortar)

3/20/2007 12:33:00 AM

DELETE
FROM PlayerRevisionScores
WHERE RevisionID IN (SELECT RevisionID
FROM Revision
WHERE RevisionDate < (SELECT RevisionDate FROM Revision WHERE
RevisionID = @nRev)
AND ClubID = @nClubID)

Revision Table - Primary Key is RevisionID, an Integer.

Player Revision Scores, Primary Key is PlayerID and RevisionID.

Subquery has no shot of returning more than 1 value, as RevisionID is the
PK...

PlayerRevisionScores has about 14000 rows in the 1 case where it fails, and
while this query doesn't HAVE TO remove all rows, it is, int his
installation, going to remove all 14000 rows most times... However, to
handle all scenarios, the logic in place is accurate.
10 Answers

Tom Moreau

3/20/2007 12:47:00 AM

0

When you say "fail", what exactly is the error message?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"AC" <a@b.com> wrote in message news:z_FLh.81$Nv4.49@newsfe12.lga...
DELETE
FROM PlayerRevisionScores
WHERE RevisionID IN (SELECT RevisionID
FROM Revision
WHERE RevisionDate < (SELECT RevisionDate FROM Revision WHERE
RevisionID = @nRev)
AND ClubID = @nClubID)

Revision Table - Primary Key is RevisionID, an Integer.

Player Revision Scores, Primary Key is PlayerID and RevisionID.

Subquery has no shot of returning more than 1 value, as RevisionID is the
PK...

PlayerRevisionScores has about 14000 rows in the 1 case where it fails, and
while this query doesn't HAVE TO remove all rows, it is, int his
installation, going to remove all 14000 rows most times... However, to
handle all scenarios, the logic in place is accurate.

(Mortar)

3/20/2007 12:56:00 AM

0

I wish I knew! It's failing at clients without error messages, or at least
they aren't reporting any.... Our app tracks them too and I see none.

The stored proc runs once, every month at midnight of the 1st. The top of
the proc is a cursor, and in this client's install, the cursor always
returns 2 rows, so the same stuff runs for both rows. The 1st row is run to
completion (about 6-7 calls) but the 2nd one stops here (this is the 3rd
call). I checked thre data and figured it's stopping here. We have about 5
clients with this... and it doesn;'t happen every time either. However,
when it fails here, 3-4 more SPs do not run...




"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:O8kN7koaHHA.348@TK2MSFTNGP02.phx.gbl...
> When you say "fail", what exactly is the error message?
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "AC" <a@b.com> wrote in message news:z_FLh.81$Nv4.49@newsfe12.lga...
> DELETE
> FROM PlayerRevisionScores
> WHERE RevisionID IN (SELECT RevisionID
> FROM Revision
> WHERE RevisionDate < (SELECT RevisionDate FROM Revision WHERE
> RevisionID = @nRev)
> AND ClubID = @nClubID)
>
> Revision Table - Primary Key is RevisionID, an Integer.
>
> Player Revision Scores, Primary Key is PlayerID and RevisionID.
>
> Subquery has no shot of returning more than 1 value, as RevisionID is the
> PK...
>
> PlayerRevisionScores has about 14000 rows in the 1 case where it fails,
> and
> while this query doesn't HAVE TO remove all rows, it is, int his
> installation, going to remove all 14000 rows most times... However, to
> handle all scenarios, the logic in place is accurate.
>
>


Tom Moreau

3/20/2007 1:05:00 AM

0

Is it possible to run just the SQL without any client code involved? Also,
do you know why they're using a cursor? Are there any blocking processes?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"AC" <a@b.com> wrote in message news:riGLh.84$Nv4.26@newsfe12.lga...
I wish I knew! It's failing at clients without error messages, or at least
they aren't reporting any.... Our app tracks them too and I see none.

The stored proc runs once, every month at midnight of the 1st. The top of
the proc is a cursor, and in this client's install, the cursor always
returns 2 rows, so the same stuff runs for both rows. The 1st row is run to
completion (about 6-7 calls) but the 2nd one stops here (this is the 3rd
call). I checked thre data and figured it's stopping here. We have about 5
clients with this... and it doesn;'t happen every time either. However,
when it fails here, 3-4 more SPs do not run...




"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:O8kN7koaHHA.348@TK2MSFTNGP02.phx.gbl...
> When you say "fail", what exactly is the error message?
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "AC" <a@b.com> wrote in message news:z_FLh.81$Nv4.49@newsfe12.lga...
> DELETE
> FROM PlayerRevisionScores
> WHERE RevisionID IN (SELECT RevisionID
> FROM Revision
> WHERE RevisionDate < (SELECT RevisionDate FROM Revision WHERE
> RevisionID = @nRev)
> AND ClubID = @nClubID)
>
> Revision Table - Primary Key is RevisionID, an Integer.
>
> Player Revision Scores, Primary Key is PlayerID and RevisionID.
>
> Subquery has no shot of returning more than 1 value, as RevisionID is the
> PK...
>
> PlayerRevisionScores has about 14000 rows in the 1 case where it fails,
> and
> while this query doesn't HAVE TO remove all rows, it is, int his
> installation, going to remove all 14000 rows most times... However, to
> handle all scenarios, the logic in place is accurate.
>
>

(Mortar)

3/20/2007 1:56:00 AM

0

The cursor is in play (I wrote the code) because was a series of actions
need to take place once a month (actually once a revision, but let's say, as
it is in this case, that is once a month). About 8 stored procs are run for
each record in the CLUB table. Some of them are doing calculations to keep
history (the players handicap when the revision was set), and the
PlayerRevisionScores is the list of scores to make the calculation, and they
are stored in an audit area as well. There is more to it than just this, so
it's not possible to simplify. The code is pretty optimized already. It's
not possible to do this without the cursor

There shouldn't be anything else hitting the database when this is running
at 12:00 (or shortly thereafter). It's run on Desktop Edition, which we
install for them, and only our app hits the DB. At midnight, users are not
using this (I can guarantee that), and even so, the table being deleted is
an audit table...

Basically, this is what happens

Club returns 2 rows:
1st row (ClubID = 1)
* RevisionID needed to be used is calculated (there is a series of 3)
* PlayerRevisionScores is cleaned for the given ClubID for OLDER
revisions (that's the query I showed
* Tables for audit history are populated
* Club table is updated with the revision ID so we do not run this
again until next revision (its possible the PC is turned off for days, so
we need to keep track of the revision we last run)
* Revision table is cleaned out for all revisions 13 months old or
older to keep DB size small

2nd row (ClubID =2)
* RevisionIDs are calculated
* PlayerRevisionScores are attempted to be cleaned, but this never
happens, and stored proc exits, as nothing else is run


As you can see, there are 5 steps, simpifying them of course, that need to
be run. ClubID=1 works perfectly, then it loops around to run the same code
for ClubID=2 and the 2nd step fails, and nothing else is run. The query for
the 2nd step is what I showed before.




"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:u8NibvoaHHA.1296@TK2MSFTNGP02.phx.gbl...
> Is it possible to run just the SQL without any client code involved?
> Also,
> do you know why they're using a cursor? Are there any blocking processes?
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "AC" <a@b.com> wrote in message news:riGLh.84$Nv4.26@newsfe12.lga...
> I wish I knew! It's failing at clients without error messages, or at
> least
> they aren't reporting any.... Our app tracks them too and I see none.
>
> The stored proc runs once, every month at midnight of the 1st. The top of
> the proc is a cursor, and in this client's install, the cursor always
> returns 2 rows, so the same stuff runs for both rows. The 1st row is run
> to
> completion (about 6-7 calls) but the 2nd one stops here (this is the 3rd
> call). I checked thre data and figured it's stopping here. We have about
> 5
> clients with this... and it doesn;'t happen every time either. However,
> when it fails here, 3-4 more SPs do not run...
>
>
>
>
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:O8kN7koaHHA.348@TK2MSFTNGP02.phx.gbl...
>> When you say "fail", what exactly is the error message?
>>
>> --
>> Tom
>>
>> ----------------------------------------------------
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "AC" <a@b.com> wrote in message news:z_FLh.81$Nv4.49@newsfe12.lga...
>> DELETE
>> FROM PlayerRevisionScores
>> WHERE RevisionID IN (SELECT RevisionID
>> FROM Revision
>> WHERE RevisionDate < (SELECT RevisionDate FROM Revision WHERE
>> RevisionID = @nRev)
>> AND ClubID = @nClubID)
>>
>> Revision Table - Primary Key is RevisionID, an Integer.
>>
>> Player Revision Scores, Primary Key is PlayerID and RevisionID.
>>
>> Subquery has no shot of returning more than 1 value, as RevisionID is the
>> PK...
>>
>> PlayerRevisionScores has about 14000 rows in the 1 case where it fails,
>> and
>> while this query doesn't HAVE TO remove all rows, it is, int his
>> installation, going to remove all 14000 rows most times... However, to
>> handle all scenarios, the logic in place is accurate.
>>
>>
>
>


Tom Moreau

3/20/2007 2:02:00 AM

0

I'd be tempted to run a profiler trace here, getting the statements that
begin and those that complete. We need to narrow down the exact piece of
code that's acting up. Pay attention also to the SPID that fails and any
other SPIDs that are running at the same time.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"AC" <a@b.com> wrote in message news:NaHLh.454$zo.143@newsfe12.lga...
The cursor is in play (I wrote the code) because was a series of actions
need to take place once a month (actually once a revision, but let's say, as
it is in this case, that is once a month). About 8 stored procs are run for
each record in the CLUB table. Some of them are doing calculations to keep
history (the players handicap when the revision was set), and the
PlayerRevisionScores is the list of scores to make the calculation, and they
are stored in an audit area as well. There is more to it than just this, so
it's not possible to simplify. The code is pretty optimized already. It's
not possible to do this without the cursor

There shouldn't be anything else hitting the database when this is running
at 12:00 (or shortly thereafter). It's run on Desktop Edition, which we
install for them, and only our app hits the DB. At midnight, users are not
using this (I can guarantee that), and even so, the table being deleted is
an audit table...

Basically, this is what happens

Club returns 2 rows:
1st row (ClubID = 1)
* RevisionID needed to be used is calculated (there is a series of 3)
* PlayerRevisionScores is cleaned for the given ClubID for OLDER
revisions (that's the query I showed
* Tables for audit history are populated
* Club table is updated with the revision ID so we do not run this
again until next revision (its possible the PC is turned off for days, so
we need to keep track of the revision we last run)
* Revision table is cleaned out for all revisions 13 months old or
older to keep DB size small

2nd row (ClubID =2)
* RevisionIDs are calculated
* PlayerRevisionScores are attempted to be cleaned, but this never
happens, and stored proc exits, as nothing else is run


As you can see, there are 5 steps, simpifying them of course, that need to
be run. ClubID=1 works perfectly, then it loops around to run the same code
for ClubID=2 and the 2nd step fails, and nothing else is run. The query for
the 2nd step is what I showed before.




"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:u8NibvoaHHA.1296@TK2MSFTNGP02.phx.gbl...
> Is it possible to run just the SQL without any client code involved?
> Also,
> do you know why they're using a cursor? Are there any blocking processes?
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "AC" <a@b.com> wrote in message news:riGLh.84$Nv4.26@newsfe12.lga...
> I wish I knew! It's failing at clients without error messages, or at
> least
> they aren't reporting any.... Our app tracks them too and I see none.
>
> The stored proc runs once, every month at midnight of the 1st. The top of
> the proc is a cursor, and in this client's install, the cursor always
> returns 2 rows, so the same stuff runs for both rows. The 1st row is run
> to
> completion (about 6-7 calls) but the 2nd one stops here (this is the 3rd
> call). I checked thre data and figured it's stopping here. We have about
> 5
> clients with this... and it doesn;'t happen every time either. However,
> when it fails here, 3-4 more SPs do not run...
>
>
>
>
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:O8kN7koaHHA.348@TK2MSFTNGP02.phx.gbl...
>> When you say "fail", what exactly is the error message?
>>
>> --
>> Tom
>>
>> ----------------------------------------------------
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "AC" <a@b.com> wrote in message news:z_FLh.81$Nv4.49@newsfe12.lga...
>> DELETE
>> FROM PlayerRevisionScores
>> WHERE RevisionID IN (SELECT RevisionID
>> FROM Revision
>> WHERE RevisionDate < (SELECT RevisionDate FROM Revision WHERE
>> RevisionID = @nRev)
>> AND ClubID = @nClubID)
>>
>> Revision Table - Primary Key is RevisionID, an Integer.
>>
>> Player Revision Scores, Primary Key is PlayerID and RevisionID.
>>
>> Subquery has no shot of returning more than 1 value, as RevisionID is the
>> PK...
>>
>> PlayerRevisionScores has about 14000 rows in the 1 case where it fails,
>> and
>> while this query doesn't HAVE TO remove all rows, it is, int his
>> installation, going to remove all 14000 rows most times... However, to
>> handle all scenarios, the logic in place is accurate.
>>
>>
>
>

(Mortar)

3/20/2007 2:25:00 AM

0

Thanks! I'm certain it fails on that DELETE query I initially posted.

No triggers are run on delete on the table being deleted.

Problem is our client has no tools to do any investigation, and they do not
have IT staffs. They are golf professionals that are 3000 miles away
unfortunately. We have a part of our app that backs up the DB and ftp's it
to our site so I can examine which is how I saw the data. They sent their
"error file" as well but there was no error in the file on the time it
happened. When the DB comes to us, it's truncated so it's small since most
users do not have broadband.

Our DB at 4am had a problem because of this routine not running fully which
ran at midnight, or close to it (our app is always up, and when the day
changes, it sees if it needs to run this).




"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:Ozcp1OpaHHA.2436@TK2MSFTNGP06.phx.gbl...
> I'd be tempted to run a profiler trace here, getting the statements that
> begin and those that complete. We need to narrow down the exact piece of
> code that's acting up. Pay attention also to the SPID that fails and any
> other SPIDs that are running at the same time.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "AC" <a@b.com> wrote in message news:NaHLh.454$zo.143@newsfe12.lga...
> The cursor is in play (I wrote the code) because was a series of actions
> need to take place once a month (actually once a revision, but let's say,
> as
> it is in this case, that is once a month). About 8 stored procs are run
> for
> each record in the CLUB table. Some of them are doing calculations to
> keep
> history (the players handicap when the revision was set), and the
> PlayerRevisionScores is the list of scores to make the calculation, and
> they
> are stored in an audit area as well. There is more to it than just this,
> so
> it's not possible to simplify. The code is pretty optimized already.
> It's
> not possible to do this without the cursor
>
> There shouldn't be anything else hitting the database when this is running
> at 12:00 (or shortly thereafter). It's run on Desktop Edition, which we
> install for them, and only our app hits the DB. At midnight, users are
> not
> using this (I can guarantee that), and even so, the table being deleted is
> an audit table...
>
> Basically, this is what happens
>
> Club returns 2 rows:
> 1st row (ClubID = 1)
> * RevisionID needed to be used is calculated (there is a series of 3)
> * PlayerRevisionScores is cleaned for the given ClubID for OLDER
> revisions (that's the query I showed
> * Tables for audit history are populated
> * Club table is updated with the revision ID so we do not run this
> again until next revision (its possible the PC is turned off for days, so
> we need to keep track of the revision we last run)
> * Revision table is cleaned out for all revisions 13 months old or
> older to keep DB size small
>
> 2nd row (ClubID =2)
> * RevisionIDs are calculated
> * PlayerRevisionScores are attempted to be cleaned, but this never
> happens, and stored proc exits, as nothing else is run
>
>
> As you can see, there are 5 steps, simpifying them of course, that need to
> be run. ClubID=1 works perfectly, then it loops around to run the same
> code
> for ClubID=2 and the 2nd step fails, and nothing else is run. The query
> for
> the 2nd step is what I showed before.
>
>
>
>
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:u8NibvoaHHA.1296@TK2MSFTNGP02.phx.gbl...
>> Is it possible to run just the SQL without any client code involved?
>> Also,
>> do you know why they're using a cursor? Are there any blocking
>> processes?
>>
>> --
>> Tom
>>
>> ----------------------------------------------------
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> "AC" <a@b.com> wrote in message news:riGLh.84$Nv4.26@newsfe12.lga...
>> I wish I knew! It's failing at clients without error messages, or at
>> least
>> they aren't reporting any.... Our app tracks them too and I see none.
>>
>> The stored proc runs once, every month at midnight of the 1st. The top
>> of
>> the proc is a cursor, and in this client's install, the cursor always
>> returns 2 rows, so the same stuff runs for both rows. The 1st row is run
>> to
>> completion (about 6-7 calls) but the 2nd one stops here (this is the 3rd
>> call). I checked thre data and figured it's stopping here. We have
>> about
>> 5
>> clients with this... and it doesn;'t happen every time either. However,
>> when it fails here, 3-4 more SPs do not run...
>>
>>
>>
>>
>> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
>> news:O8kN7koaHHA.348@TK2MSFTNGP02.phx.gbl...
>>> When you say "fail", what exactly is the error message?
>>>
>>> --
>>> Tom
>>>
>>> ----------------------------------------------------
>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>>> SQL Server MVP
>>> Toronto, ON Canada
>>> .
>>> "AC" <a@b.com> wrote in message news:z_FLh.81$Nv4.49@newsfe12.lga...
>>> DELETE
>>> FROM PlayerRevisionScores
>>> WHERE RevisionID IN (SELECT RevisionID
>>> FROM Revision
>>> WHERE RevisionDate < (SELECT RevisionDate FROM Revision WHERE
>>> RevisionID = @nRev)
>>> AND ClubID = @nClubID)
>>>
>>> Revision Table - Primary Key is RevisionID, an Integer.
>>>
>>> Player Revision Scores, Primary Key is PlayerID and RevisionID.
>>>
>>> Subquery has no shot of returning more than 1 value, as RevisionID is
>>> the
>>> PK...
>>>
>>> PlayerRevisionScores has about 14000 rows in the 1 case where it fails,
>>> and
>>> while this query doesn't HAVE TO remove all rows, it is, int his
>>> installation, going to remove all 14000 rows most times... However, to
>>> handle all scenarios, the logic in place is accurate.
>>>
>>>
>>
>>
>
>


Someone Else

3/20/2007 8:55:00 AM

0

The only way I can see that could fail is a foreign key constraint
being violated - any of those exist?

On 20 Mar, 02:25, "AC" <a...@b.com> wrote:
> Thanks! I'm certain it fails on that DELETE query I initially posted.
>
> No triggers are run on delete on the table being deleted.
>
> Problem is our client has no tools to do any investigation, and they do not
> have IT staffs. They are golf professionals that are 3000 miles away
> unfortunately. We have a part of our app that backs up the DB and ftp's it
> to our site so I can examine which is how I saw the data. They sent their
> "error file" as well but there was no error in the file on the time it
> happened. When the DB comes to us, it's truncated so it's small since most
> users do not have broadband.
>
> Our DB at 4am had a problem because of this routine not running fully which
> ran at midnight, or close to it (our app is always up, and when the day
> changes, it sees if it needs to run this).
>
> "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote in message
>
> news:Ozcp1OpaHHA.2436@TK2MSFTNGP06.phx.gbl...
>
>
>
> > I'd be tempted to run a profiler trace here, getting the statements that
> > begin and those that complete. We need to narrow down the exact piece of
> > code that's acting up. Pay attention also to the SPID that fails and any
> > other SPIDs that are running at the same time.
>
> > --
> > Tom
>
> > ----------------------------------------------------
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > .
> > "AC" <a...@b.com> wrote in messagenews:NaHLh.454$zo.143@newsfe12.lga...
> > The cursor is in play (I wrote the code) because was a series of actions
> > need to take place once a month (actually once a revision, but let's say,
> > as
> > it is in this case, that is once a month). About 8 stored procs are run
> > for
> > each record in the CLUB table. Some of them are doing calculations to
> > keep
> > history (the players handicap when the revision was set), and the
> > PlayerRevisionScores is the list of scores to make the calculation, and
> > they
> > are stored in an audit area as well. There is more to it than just this,
> > so
> > it's not possible to simplify. The code is pretty optimized already.
> > It's
> > not possible to do this without the cursor
>
> > There shouldn't be anything else hitting the database when this is running
> > at 12:00 (or shortly thereafter). It's run on Desktop Edition, which we
> > install for them, and only our app hits the DB. At midnight, users are
> > not
> > using this (I can guarantee that), and even so, the table being deleted is
> > an audit table...
>
> > Basically, this is what happens
>
> > Club returns 2 rows:
> > 1st row (ClubID = 1)
> > * RevisionID needed to be used is calculated (there is a series of 3)
> > * PlayerRevisionScores is cleaned for the given ClubID for OLDER
> > revisions (that's the query I showed
> > * Tables for audit history are populated
> > * Club table is updated with the revision ID so we do not run this
> > again until next revision (its possible the PC is turned off for days, so
> > we need to keep track of the revision we last run)
> > * Revision table is cleaned out for all revisions 13 months old or
> > older to keep DB size small
>
> > 2nd row (ClubID =2)
> > * RevisionIDs are calculated
> > * PlayerRevisionScores are attempted to be cleaned, but this never
> > happens, and stored proc exits, as nothing else is run
>
> > As you can see, there are 5 steps, simpifying them of course, that need to
> > be run. ClubID=1 works perfectly, then it loops around to run the same
> > code
> > for ClubID=2 and the 2nd step fails, and nothing else is run. The query
> > for
> > the 2nd step is what I showed before.
>
> > "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote in message
> >news:u8NibvoaHHA.1296@TK2MSFTNGP02.phx.gbl...
> >> Is it possible to run just the SQL without any client code involved?
> >> Also,
> >> do you know why they're using a cursor? Are there any blocking
> >> processes?
>
> >> --
> >> Tom
>
> >> ----------------------------------------------------
> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> >> SQL Server MVP
> >> Toronto, ON Canada
> >> .
> >> "AC" <a...@b.com> wrote in messagenews:riGLh.84$Nv4.26@newsfe12.lga...
> >> I wish I knew! It's failing at clients without error messages, or at
> >> least
> >> they aren't reporting any.... Our app tracks them too and I see none.
>
> >> The stored proc runs once, every month at midnight of the 1st. The top
> >> of
> >> the proc is a cursor, and in this client's install, the cursor always
> >> returns 2 rows, so the same stuff runs for both rows. The 1st row is run
> >> to
> >> completion (about 6-7 calls) but the 2nd one stops here (this is the 3rd
> >> call). I checked thre data and figured it's stopping here. We have
> >> about
> >> 5
> >> clients with this... and it doesn;'t happen every time either. However,
> >> when it fails here, 3-4 more SPs do not run...
>
> >> "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote in message
> >>news:O8kN7koaHHA.348@TK2MSFTNGP02.phx.gbl...
> >>> When you say "fail", what exactly is the error message?
>
> >>> --
> >>> Tom
>
> >>> ----------------------------------------------------
> >>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> >>> SQL Server MVP
> >>> Toronto, ON Canada
> >>> .
> >>> "AC" <a...@b.com> wrote in messagenews:z_FLh.81$Nv4.49@newsfe12.lga...
> >>> DELETE
> >>> FROM PlayerRevisionScores
> >>> WHERE RevisionID IN (SELECT RevisionID
> >>> FROM Revision
> >>> WHERE RevisionDate < (SELECT RevisionDate FROM Revision WHERE
> >>> RevisionID = @nRev)
> >>> AND ClubID = @nClubID)
>
> >>> Revision Table - Primary Key is RevisionID, an Integer.
>
> >>> Player Revision Scores, Primary Key is PlayerID and RevisionID.
>
> >>> Subquery has no shot of returning more than 1 value, as RevisionID is
> >>> the
> >>> PK...
>
> >>> PlayerRevisionScores has about 14000 rows in the 1 case where it fails,
> >>> and
> >>> while this query doesn't HAVE TO remove all rows, it is, int his
> >>> installation, going to remove all 14000 rows most times... However, to
> >>> handle all scenarios, the logic in place is accurate.- Hide quoted text -
>
> - Show quoted text -

(Mortar)

3/20/2007 11:51:00 AM

0

No, the table I am deleting from is not referenced in a relationship by
another table.


"Someone Else" <someone_else_of_course@hotmail.co.uk> wrote in message
news:1174380921.756798.78970@b75g2000hsg.googlegroups.com...
> The only way I can see that could fail is a foreign key constraint
> being violated - any of those exist?
>
> On 20 Mar, 02:25, "AC" <a...@b.com> wrote:
>> Thanks! I'm certain it fails on that DELETE query I initially posted.
>>
>> No triggers are run on delete on the table being deleted.
>>
>> Problem is our client has no tools to do any investigation, and they do
>> not
>> have IT staffs. They are golf professionals that are 3000 miles away
>> unfortunately. We have a part of our app that backs up the DB and ftp's
>> it
>> to our site so I can examine which is how I saw the data. They sent
>> their
>> "error file" as well but there was no error in the file on the time it
>> happened. When the DB comes to us, it's truncated so it's small since
>> most
>> users do not have broadband.
>>
>> Our DB at 4am had a problem because of this routine not running fully
>> which
>> ran at midnight, or close to it (our app is always up, and when the day
>> changes, it sees if it needs to run this).
>>
>> "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote in message
>>
>> news:Ozcp1OpaHHA.2436@TK2MSFTNGP06.phx.gbl...
>>
>>
>>
>> > I'd be tempted to run a profiler trace here, getting the statements
>> > that
>> > begin and those that complete. We need to narrow down the exact piece
>> > of
>> > code that's acting up. Pay attention also to the SPID that fails and
>> > any
>> > other SPIDs that are running at the same time.
>>
>> > --
>> > Tom
>>
>> > ----------------------------------------------------
>> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> > SQL Server MVP
>> > Toronto, ON Canada
>> > .
>> > "AC" <a...@b.com> wrote in messagenews:NaHLh.454$zo.143@newsfe12.lga...
>> > The cursor is in play (I wrote the code) because was a series of
>> > actions
>> > need to take place once a month (actually once a revision, but let's
>> > say,
>> > as
>> > it is in this case, that is once a month). About 8 stored procs are
>> > run
>> > for
>> > each record in the CLUB table. Some of them are doing calculations to
>> > keep
>> > history (the players handicap when the revision was set), and the
>> > PlayerRevisionScores is the list of scores to make the calculation, and
>> > they
>> > are stored in an audit area as well. There is more to it than just
>> > this,
>> > so
>> > it's not possible to simplify. The code is pretty optimized already.
>> > It's
>> > not possible to do this without the cursor
>>
>> > There shouldn't be anything else hitting the database when this is
>> > running
>> > at 12:00 (or shortly thereafter). It's run on Desktop Edition, which
>> > we
>> > install for them, and only our app hits the DB. At midnight, users are
>> > not
>> > using this (I can guarantee that), and even so, the table being deleted
>> > is
>> > an audit table...
>>
>> > Basically, this is what happens
>>
>> > Club returns 2 rows:
>> > 1st row (ClubID = 1)
>> > * RevisionID needed to be used is calculated (there is a series of
>> > 3)
>> > * PlayerRevisionScores is cleaned for the given ClubID for OLDER
>> > revisions (that's the query I showed
>> > * Tables for audit history are populated
>> > * Club table is updated with the revision ID so we do not run this
>> > again until next revision (its possible the PC is turned off for days,
>> > so
>> > we need to keep track of the revision we last run)
>> > * Revision table is cleaned out for all revisions 13 months old or
>> > older to keep DB size small
>>
>> > 2nd row (ClubID =2)
>> > * RevisionIDs are calculated
>> > * PlayerRevisionScores are attempted to be cleaned, but this never
>> > happens, and stored proc exits, as nothing else is run
>>
>> > As you can see, there are 5 steps, simpifying them of course, that need
>> > to
>> > be run. ClubID=1 works perfectly, then it loops around to run the same
>> > code
>> > for ClubID=2 and the 2nd step fails, and nothing else is run. The
>> > query
>> > for
>> > the 2nd step is what I showed before.
>>
>> > "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote in message
>> >news:u8NibvoaHHA.1296@TK2MSFTNGP02.phx.gbl...
>> >> Is it possible to run just the SQL without any client code involved?
>> >> Also,
>> >> do you know why they're using a cursor? Are there any blocking
>> >> processes?
>>
>> >> --
>> >> Tom
>>
>> >> ----------------------------------------------------
>> >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> >> SQL Server MVP
>> >> Toronto, ON Canada
>> >> .
>> >> "AC" <a...@b.com> wrote in messagenews:riGLh.84$Nv4.26@newsfe12.lga...
>> >> I wish I knew! It's failing at clients without error messages, or at
>> >> least
>> >> they aren't reporting any.... Our app tracks them too and I see none.
>>
>> >> The stored proc runs once, every month at midnight of the 1st. The
>> >> top
>> >> of
>> >> the proc is a cursor, and in this client's install, the cursor always
>> >> returns 2 rows, so the same stuff runs for both rows. The 1st row is
>> >> run
>> >> to
>> >> completion (about 6-7 calls) but the 2nd one stops here (this is the
>> >> 3rd
>> >> call). I checked thre data and figured it's stopping here. We have
>> >> about
>> >> 5
>> >> clients with this... and it doesn;'t happen every time either.
>> >> However,
>> >> when it fails here, 3-4 more SPs do not run...
>>
>> >> "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote in message
>> >>news:O8kN7koaHHA.348@TK2MSFTNGP02.phx.gbl...
>> >>> When you say "fail", what exactly is the error message?
>>
>> >>> --
>> >>> Tom
>>
>> >>> ----------------------------------------------------
>> >>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> >>> SQL Server MVP
>> >>> Toronto, ON Canada
>> >>> .
>> >>> "AC" <a...@b.com> wrote in
>> >>> messagenews:z_FLh.81$Nv4.49@newsfe12.lga...
>> >>> DELETE
>> >>> FROM PlayerRevisionScores
>> >>> WHERE RevisionID IN (SELECT RevisionID
>> >>> FROM Revision
>> >>> WHERE RevisionDate < (SELECT RevisionDate FROM Revision WHERE
>> >>> RevisionID = @nRev)
>> >>> AND ClubID = @nClubID)
>>
>> >>> Revision Table - Primary Key is RevisionID, an Integer.
>>
>> >>> Player Revision Scores, Primary Key is PlayerID and RevisionID.
>>
>> >>> Subquery has no shot of returning more than 1 value, as RevisionID is
>> >>> the
>> >>> PK...
>>
>> >>> PlayerRevisionScores has about 14000 rows in the 1 case where it
>> >>> fails,
>> >>> and
>> >>> while this query doesn't HAVE TO remove all rows, it is, int his
>> >>> installation, going to remove all 14000 rows most times... However,
>> >>> to
>> >>> handle all scenarios, the logic in place is accurate.- Hide quoted
>> >>> text -
>>
>> - Show quoted text -
>
>


(Mortar)

3/22/2007 1:24:00 AM

0

Is it possible an antivirus program is causing this to happen? We have
reports of all clients with this had the same antivirus running, and 0
reports of it happening for clients without it.

"AC" <a@b.com> wrote in message news:z_FLh.81$Nv4.49@newsfe12.lga...
> DELETE
> FROM PlayerRevisionScores
> WHERE RevisionID IN (SELECT RevisionID
> FROM Revision
> WHERE RevisionDate < (SELECT RevisionDate FROM Revision WHERE
> RevisionID = @nRev)
> AND ClubID = @nClubID)
>
> Revision Table - Primary Key is RevisionID, an Integer.
>
> Player Revision Scores, Primary Key is PlayerID and RevisionID.
>
> Subquery has no shot of returning more than 1 value, as RevisionID is the
> PK...
>
> PlayerRevisionScores has about 14000 rows in the 1 case where it fails,
> and while this query doesn't HAVE TO remove all rows, it is, int his
> installation, going to remove all 14000 rows most times... However, to
> handle all scenarios, the logic in place is accurate.
>
>


Hugo Kornelis

3/25/2007 11:43:00 AM

0

On Wed, 21 Mar 2007 21:24:29 -0400, AC wrote:

>Is it possible an antivirus program is causing this to happen? We have
>reports of all clients with this had the same antivirus running, and 0
>reports of it happening for clients without it.

Hi AC,

Nothing is impossible, of course, but I've never heard or witnessed
queries running foul because of antivirus programs, and it would
definitely qualify as a bug. And a weird one too - I can imagine
antivirus programs messing up the isntallation of SQL Server, but once
installed, there shouldn't be any interaction between antivirus and SQL
Server.

I'm 99.999% sure it is NOT the antivirus program. To investigiate what
other cause there might be, more information is needed. At least the
table stuctures (post as CREATE TABLE statements, please) and the error
message; if possible (I understand that it's hard to do this with
intermittent errors) also sample data (as INSERT statements) that cause
the error to happen.

BTW, you state in another message that this query is part of a larger
process, but that you are sure that the problem is in this query - how
do you know that? What have you done to confirm this theory?

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...