Error when running optimizer maintenance plan

Hi
I have 140GB data base with maintenance plan schedule
every sunday. when it run Optimizer plan
I get the error and the job failed if I rerun the job
some time it will completed but 1 out of 4 time it will
failed on
middle of reindex with the error [Microsoft SQL-DMO (ODBC
SQLState: 01000)] Error 0: This server has been
disconnected.
Have any one known what caused this problem?
Thank in advanceThe message indicates that SQL Server became unavailable to the client
connection. Check your SQL error log for any exceptions of access
violations. Also check to see if someone manually shutdown SQL Server.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Error using UPDATE statement

Hi,

I am relatively new to SQL. I am using SQL 2000. I am trying to
Update a field base in a criteria in a scond table.

UPDATE Tbl1
SET Tbl1.Row2 = '1'
WHERE Tbl1.Row1 =
(SELECT Tbl1.Row1
FROM Tbl2, Tbl1
WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fn
CURRENT_TIMESTAMP () } >= Tbl2.Row3))

Row 1 is the key between the two table. If I am doing only the select
below, I am getting the right value.

SELECT Tbl1.Row1
FROM Tbl2, Tbl1
WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fn
CURRENT_TIMESTAMP () } >= Tbl2.Row3)

When I am running the entire querry, I am getting this error:

Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
The statement has been terminated.

What I am trying to do is to update a field in Tbl1 base on a date in
Tbl2. If the date is expire, I want to raise a flag, in Tbl1.

Thank you

PhilippeOn 27 Feb 2006 12:22:26 -0800, solidsna2@.gmail.com wrote:

>Hi,
>I am relatively new to SQL. I am using SQL 2000. I am trying to
>Update a field base in a criteria in a scond table.
>UPDATE Tbl1
>SET Tbl1.Row2 = '1'
>WHERE Tbl1.Row1 =
> (SELECT Tbl1.Row1
> FROM Tbl2, Tbl1
> WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fn
>CURRENT_TIMESTAMP () } >= Tbl2.Row3))
>Row 1 is the key between the two table. If I am doing only the select
>below, I am getting the right value.
>SELECT Tbl1.Row1
> FROM Tbl2, Tbl1
> WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fn
>CURRENT_TIMESTAMP () } >= Tbl2.Row3)
>When I am running the entire querry, I am getting this error:
>Subquery returned more than 1 value. This is not permitted when the
>subquery follows =, !=, <, <= , >, >= or when the subquery is used as
>an expression.
>The statement has been terminated.
>What I am trying to do is to update a field in Tbl1 base on a date in
>Tbl2. If the date is expire, I want to raise a flag, in Tbl1.
>Thank you
>Philippe

Hi Philippe,

Hard to be sure without CREATE TABLE statements, INSERT statements and
expected output (see www.aspfaq.com/5006), but I guess that you need
something like this:

UPDATE Tbl1
SET Row2 = '1'
WHERE EXISTS
(SELECT *
FROM Tbl2
WHERE Tbl2.Row3 >= CURRENT_TIMESTAMP
AND Tbl2.Row1 = Tbl1.Row1)

--
Hugo Kornelis, SQL Server MVP|||(solidsna2@.gmail.com) writes:
> I am relatively new to SQL. I am using SQL 2000. I am trying to
> Update a field base in a criteria in a scond table.
> UPDATE Tbl1
> SET Tbl1.Row2 = '1'
> WHERE Tbl1.Row1 =
> (SELECT Tbl1.Row1
> FROM Tbl2, Tbl1
> WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fn
> CURRENT_TIMESTAMP () } >= Tbl2.Row3))

This does not look right. You have Tbl1 once extra in the subquery,
making it entirely uncorrelated with the outer Tbl1. Try chaning the
query to:

UPDATE Tbl1
SET Row2 = '1'
WHERE Tbl1.Row1 = (SELECT Tbl2.Row1
FROM Tbl2
WHERE Tbl2.Row1 = Tbl1.Row1
AND CURRENT_TIMESTAMP >= Tbl2.Row3)

I also changed {fn current_timestamp() } as there is no reason to
call an ODBC function to get the current date.

--
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|||Thank you for the reply. Sadly, I am getting the same error with your
solution. If I run only the SELECT subquery alone, I am getting
mutiple result, and the result are what I am expecting. In Tbl2, Row1
can be the same for up to 8 lines. Row1 is the key between the 2
tables. In Tbl1, Row1 is unique.

Ex:
ROW1 ROW2 ROW3
1 xxx zzz
1 aaa bbb
1 ccc ddd
1 eee fff
1 ggg hhh

Maybe this is why it is giving me the error that I have mutiple value.
Like I sayd, I am a new to doing SQL programming. Maybe I am missing
something or I do not approach the problem properly.

Thank you again|||Well I figure out the problem. I had a GROUP BY at the end of the
SELECT subquery.

UPDATE Tbl1
SET Row2 = '1'
WHERE Tbl1.Row1 = (SELECT Tbl2.Row1
FROM Tbl2
WHERE Tbl2.Row1 = Tbl1.Row1
AND CURRENT_TIMESTAMP >= Tbl2.Row3 GROUP BY
Tbl2.Row1)

Thank you again for your help.