Problem with Agent on 2000 server - jobs hanging

I have a server that had SQL agent working on it, and running scheduled
jobs on set interval forever. Windows Server 2003, SQL 2000, most
current service packs on both.
I installed an instance of SQL 2005 on this server.
Now, when I look at Enterprise Manager & try to see what's happening
with the jobs, I see that the jobs are sitting out there & will kick off
when they are supposed to run, or I can kick them off manually.
However, now, they will run to certain steps in a job, and it will just
"hang". The jobs that hang are the ones that are running DTS jobs:
DTSRun /~Z0xC9C........
These particular jobs haven't been changed in months, and were running
fine until the install of SQL 2005 on this server.
Any idea what's happening or how to fix it?It may be a locking. You can really easy to detect in Current Activity
window, processes view, last two fields if you scroll to the right, 'blocked
by" and "blocking" columns.
"Blasting Cap" wrote:
> I have a server that had SQL agent working on it, and running scheduled
> jobs on set interval forever. Windows Server 2003, SQL 2000, most
> current service packs on both.
> I installed an instance of SQL 2005 on this server.
> Now, when I look at Enterprise Manager & try to see what's happening
> with the jobs, I see that the jobs are sitting out there & will kick off
> when they are supposed to run, or I can kick them off manually.
> However, now, they will run to certain steps in a job, and it will just
> "hang". The jobs that hang are the ones that are running DTS jobs:
> DTSRun /~Z0xC9C........
>
> These particular jobs haven't been changed in months, and were running
> fine until the install of SQL 2005 on this server.
> Any idea what's happening or how to fix it?
>

Problem with "Not in " operator

I have a table with 2 columns
The cloumn cp_lot_status can be null or 'PL','RE'
The following shows the current data

SQL> SELECT CP_LOT_NUMBER,CP_LOT_STATUS FROM CP_LOTS_GENERATED ;

CP_LOT_NUMBER CP
----- --
203750001
103750001

SQL> SELECT * FROM CP_LOTS_GENERATED WHERE CP_LOT_STATUS NOT IN ('PL','RE') AND
2 CP_LOT_STATUS IS NULL;

no rows selected

SQL> DESC CP_LOTS_GENERATED
Name Null? Type
---------------- --- -------
CP_LOT_NUMBER NOT NULL NUMBER(12)
CP_LOT_STATUS VARCHAR2(2)

So my question is does "Not in " operator not show the null values while doing the above query.IF so what could i do to show the null value rowsNULLs are tricky. They are never equal to, not equal to, IN or NOT IN anything. So this predicate:

WHERE CP_LOT_STATUS NOT IN ('PL','RE')

is neither true nor false when cp_lot_status is NULL - it evaluates to NULL. The query only returns rows where all predicates evaluate to TRUE.

As for what you could do - well, just remove that predicate, it was redundant anyway:

SELECT * FROM CP_LOTS_GENERATED
WHERE CP_LOT_STATUS IS NULL;|||But the values 'PL' and 'RE' may occur at some point of time.....

Originally posted by andrewst
NULLs are tricky. They are never equal to, not equal to, IN or NOT IN anything. So this predicate:

WHERE CP_LOT_STATUS NOT IN ('PL','RE')

is neither true nor false when cp_lot_status is NULL - it evaluates to NULL. The query only returns rows where all predicates evaluate to TRUE.

As for what you could do - well, just remove that predicate, it was redundant anyway:

SELECT * FROM CP_LOTS_GENERATED
WHERE CP_LOT_STATUS IS NULL;|||Well, if they did you would never see them with a query that contained the predicate "AND CP_LOT_STATUS IS NULL".

Maybe what you want is

WHERE (CP_LOT_STATUS NOT IN ('PL','RE')
OR CP_LOT_STATUS IS NULL);|||yeah this worked out

thanx.................

Originally posted by andrewst
Well, if they did you would never see them with a query that contained the predicate "AND CP_LOT_STATUS IS NULL".

Maybe what you want is

WHERE (CP_LOT_STATUS NOT IN ('PL','RE')
OR CP_LOT_STATUS IS NULL);