server set bup for subquery

[ravi]

Hello,
Here is the below query I am running at server A and getting this below error.
If I am running the same query in Server B and running fine without any error.
My question is do I need to do any server configuration?. Or any solution for this issue.
I really appreciate your help.
Thanks,
Ravii

UPDATE Leap.dbo.CIB
SET credit_status = c.credit_status
FROM DNT02.oesd.dbo.credit_line c
WHERE c.effective_date = (SELECT MAX(c1.effective_date)
FROM DNT02.oesd.dbo.credit_line c1
WHERE c.account_num = c1.account_num
)
AND Leap.dbo.CIB.account_num = c.account_num

Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'c' does not match with a table name or alias name used in the query.

[MAK]

Are both servers are SQL 2000 and with same SP on it?

[ravi]

Mak,
Yes, both are same version and SP.

[ravi]

Here is the infor in @@version
Server A
Microsoft SQL Server 2000 - 8.00.679 (Intel X86)
Aug 26 2002 15:09:48
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)

Server B
Microsoft SQL Server 2000 - 8.00.679 (Intel X86)
Aug 26 2002 15:09:48
Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: )

[JBane]

Ravi,
Is the linked server configured the same on both servers?
Does this produce the same output from server A and server B?

select * FROM DNT02.oesd.dbo.credit_line

Jeff

[ravi]

Jeff,
Yes, Both are configured as linked server.
See the below query with same query with isnull. It is not giving any error and executing.
Why the previous query is not executing?.



SELECT c.credit_status
FROM DNTDOES02.oesd.dbo.credit_line c
WHERE isnull(c.effective_date,0) = (SELECT MAX(c1.effective_date)
FROM DNTDOES02.oesd.dbo.credit_line c1
WHERE isnull(c.account_num,0) = isnull(c1.account_num,0)
)

[JBane]

Rav,
Can you post some DDL, I'd like to see if I can reproduce the behavior.


Jeff

[MAK]

Look into BOOKS online for

SET ANSI_NULLS {ON | OFF}
SET ANSI_PADDING { ON | OFF }
SET ANSI_WARNINGS { ON | OFF }

check the status in both servers

[ravi]

Jeff,
Are you asking this same or different one?.
T'x
Ravi

CREATE TABLE dbo.credit_line
( account_num int NOT NULL ,
effective_date smalldatetime NOT NULL )

[JBane]

The local credit_status table as well, unless MAK's suggestion has solved your problem.

Jeff

[ravi]

Jeff/MAK,
I used all those SET option and that didn't help. Here is another column too.
Any other possible solution Please.
T'x
Ravi


CREATE TABLE dbo.credit_line
( account_num int NOT NULL ,
effective_date smalldatetime NOT NULL
credit_status char (3) COLLATE Latin1_General_BIN NULL )

[JBane]

Ravi,
Well it seems to be SP3 that has caused the change. I had an instance of SP2 and your query worked fine but when I upgraded to SP3, I got the same error as you. I have rewritten the query to function:

UPDATE Leap.dbo.CIB
SET credit_status = c.credit_status
FROM DNT02.oesd.dbo.credit_line c
WHERE c.effective_date = (SELECT MAX(c1.effective_date)
FROM DNT02.oesd.dbo.credit_line c1
WHERE Leap.dbo.CIB.account_num = c1.account_num
)
AND Leap.dbo.CIB.account_num = c.account_num


This makes a bit more sense as the subquery is now referencing the the table in the update statement. I can't explain the failure other than MS must have changed the optimizer in SP3 to prohibit your syntax. Also, it only seemed to exihbit this behavior when using a linked server, when both tables were local the old syntax worked fine.


Jeff

[ravi]

Jeff,
You Query working for me too.
If you see my other post that If use isnull for that column and that also work fine.
I have another question regarding
sql server version. In the below result
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)

Is it pointing to sql server edition or OS edition?
T'x,
Ravi


SELECT @@version

Microsoft SQL Server 2000 - 8.00.679 (Intel X86)
Aug 26 2002 15:09:48
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)

[JBane]

That final bit is your OS service pack level. To get the sql server service pack level in english, just use:

SELECT SERVERPROPERTY('productlevel')


Jeff

[ravi]

Yes Jeff,
I also just found this SP info and my both server has SP2 only.
Do you think this SP make sense?
T'x,
Ravi