Prev: Zip SQL Job query
Next: log shipping file size
From: Bev Kaufman on 19 May 2010 14:05
We have two databases on the same Sql 2000 server. They have the same
tables, views, procs and indexes. This statement works in one database, but
returns an internal Sql error in another:
UPDATE Ard
SET ard.Applbatnbr ='',
ard.ApplAmt=0,
ard.CuryApplamt=0
FROM wrkrelease w INNER JOIN batch b
ON b.batnbr = w.batnbr
INNER JOIN ardoc ard
ON ard.Applbatnbr = b.batnbr
WHERE b.Module='AR' AND w.Module='AR'
AND w.useraddress ='WSPPFXPPConsole'
AND b.status ='I'
AND b.editscrnnbr ='08030'

If I replace the UPDATE-SET syntax with SELECT *, the statement run
correctly in both databases, so I know Sql is having problem with doing the
update, not in returning the recordset.
I examined the properties of both databases and found no differences.
I generated scripts of the 3 tables, with all options checked, and I saw no
difference in table structure, indexes or constraints.
What else should I be checking for?
Upgrading to Sql 2005 or 2008 is not an option with this client.
From: John Bell on 19 May 2010 17:31
On Wed, 19 May 2010 11:05:01 -0700, Bev Kaufman
<BevKaufman(a)discussions.microsoft.com> wrote:

>We have two databases on the same Sql 2000 server. They have the same
>tables, views, procs and indexes. This statement works in one database, but
>returns an internal Sql error in another:
> UPDATE Ard
> SET ard.Applbatnbr ='',
> ard.ApplAmt=0,
> ard.CuryApplamt=0
> FROM wrkrelease w INNER JOIN batch b
> ON b.batnbr = w.batnbr
> INNER JOIN ardoc ard
> ON ard.Applbatnbr = b.batnbr
> WHERE b.Module='AR' AND w.Module='AR'
> AND w.useraddress ='WSPPFXPPConsole'
> AND b.status ='I'
> AND b.editscrnnbr ='08030'
>
>If I replace the UPDATE-SET syntax with SELECT *, the statement run
>correctly in both databases, so I know Sql is having problem with doing the
>update, not in returning the recordset.
>I examined the properties of both databases and found no differences.
>I generated scripts of the 3 tables, with all options checked, and I saw no
>difference in table structure, indexes or constraints.
>What else should I be checking for?
>Upgrading to Sql 2005 or 2008 is not an option with this client.

I think it may be the aliases in the set clause. Try:

UPDATE Ard
SET Applbatnbr ='',
ApplAmt=0,
CuryApplamt=0
FROM ardoc ard
INNER JOIN batch b
ON ard.Applbatnbr = b.batnbr
INNER JOIN wrkrelease w ON b.batnbr = w.batnbr
WHERE b.Module='AR' AND w.Module='AR'
AND w.useraddress ='WSPPFXPPConsole'
AND b.status ='I'
AND b.editscrnnbr ='08030'

John
From: Erland Sommarskog on 19 May 2010 17:49
Bev Kaufman (BevKaufman(a)discussions.microsoft.com) writes:
> We have two databases on the same Sql 2000 server. They have the same
> tables, views, procs and indexes. This statement works in one database,
> but returns an internal Sql error in another:
> UPDATE Ard
> SET ard.Applbatnbr ='',
> ard.ApplAmt=0,
> ard.CuryApplamt=0
> FROM wrkrelease w INNER JOIN batch b
> ON b.batnbr = w.batnbr
> INNER JOIN ardoc ard
> ON ard.Applbatnbr = b.batnbr
> WHERE b.Module='AR' AND w.Module='AR'
> AND w.useraddress ='WSPPFXPPConsole'
> AND b.status ='I'
> AND b.editscrnnbr ='08030'
>
> If I replace the UPDATE-SET syntax with SELECT *, the statement run
> correctly in both databases, so I know Sql is having problem with doing
> the update, not in returning the recordset.

There have been some rollup of hotfixes after SP4. You could consider
to apply any of these.

Else I cannot but suggest that you try to pick it apart. First
check for triggers and cacscading constraints. Then remove conditions
and tables from the query. Maybe using a temp table could help. Also
rebuilding indexes may help. I'm afraid that it's a bit of fumbling in
the dark.



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: SQLSQUIRREL on 23 May 2010 23:24
Have you thought about perhaps corruption in the database that it fails in.
If it works in one database but not the other with the UPDATE statement I
would run DBCC CHECKDB or at a minimum DBCC CHECKTABLE to see if there are
any problems with the underlying rows \ indexes in that database.

Let us know what the issue was...

SQLSquirrel
http://www.lockergnome.com/sqlsquirrel/


"Bev Kaufman" wrote:

> We have two databases on the same Sql 2000 server. They have the same
> tables, views, procs and indexes. This statement works in one database, but
> returns an internal Sql error in another:
> UPDATE Ard
> SET ard.Applbatnbr ='',
> ard.ApplAmt=0,
> ard.CuryApplamt=0
> FROM wrkrelease w INNER JOIN batch b
> ON b.batnbr = w.batnbr
> INNER JOIN ardoc ard
> ON ard.Applbatnbr = b.batnbr
> WHERE b.Module='AR' AND w.Module='AR'
> AND w.useraddress ='WSPPFXPPConsole'
> AND b.status ='I'
> AND b.editscrnnbr ='08030'
>
> If I replace the UPDATE-SET syntax with SELECT *, the statement run
> correctly in both databases, so I know Sql is having problem with doing the
> update, not in returning the recordset.
> I examined the properties of both databases and found no differences.
> I generated scripts of the 3 tables, with all options checked, and I saw no
> difference in table structure, indexes or constraints.
> What else should I be checking for?
> Upgrading to Sql 2005 or 2008 is not an option with this client.
 | 
Pages: 1
Prev: Zip SQL Job query
Next: log shipping file size