Link two tables in UPDATE - ERR:single-row subquery returns more than one row

1 UPDATE PM_ASSIGNMENTS
2 SET PM_ASSIGNMENTS.JOB_TYPE_CODE = (SELECT PM_TASK_DETAILS.JOB_TYPE_CODE
3 FROM PM_ASSIGNMENTS, PM_TASK_DETAILS
4 WHERE ((PM_ASSIGNMENTS.TASK_NUMBER) = (PM_TASK_DETAILS.TASK_NUMBER))
5* AND (PM_ASSIGNMENTS.TASK_NUMBER = '1000'))
SQL> /
SET PM_ASSIGNMENTS.JOB_TYPE_CODE = (SELECT PM_TASK_DETAILS.JOB_TYPE_CODE
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one rowthe sub-query is returning more than one row for the given condition.

either group-by them, or give all user-key conditions.

regards,
Raghu.sql

Link Server from SQL2005 to SQL2000

I have the follwoing scenario, where the query returns an error, when we tried to upgrade oru production SQL2000 enviroment to SQL2005.

SQL2000 Env:

Microsoft SQL Server Developer Edition

Microsoft Windows NT 5.2 (3790)

8.00.2175 SP4

SQL2005 Env:

Microsoft SQL Server Developer Edition

Microsoft Windows NT 5.2 (3790)

9.00.3054.00

Notice the removal of the two RTRIMs.

Any help welcome

Gertus

Current Connection was on the SQL2005 machine:

Remote Link Server SQL2000 SP4

Working:

SELECT * FROM Entity -- on SQL2005

WHERE

(EntityRef) + Source NOT IN

(SELECT (ENTITYID) + 'EU' FROM MRIEU.MRIOB.dbo.ENTITY) --this is on sql2000

AND Source = 'EU'

Not-working:

SELECT * FROM Entity-- on SQL2005

WHERE

RTRIM(EntityRef) + Source NOT IN

(SELECT RTRIM(ENTITYID) + 'EU' FROM MRIEU.MRIOB.dbo.ENTITY)

AND Source = 'EU'

Error :: >>

OLE DB provider "SQLNCLI" for linked server "MRIEU" returned message "Cannot create new connection because in manual or distributed transaction mode.".

Msg 7320, Level 16, State 2, Line 1

Cannot execute the query "SELECT TOP 1 1 FROM "MRIOB"."dbo"."ENTITY" "Tbl1005"" against OLE DB provider "SQLNCLI" for linked server "MRIEU".

Did you try searching the MS knowledge base?

http://support.microsoft.com/kb/272358/en-us

|||

Sorry that is not the problem, I am runnign this query in sql storedproc, it this is an error on the link server code internal of Microsoft.

Can any Microsoft person help here please?

Regards

Gert