Compatibility problem SQL 2005 <-> SQL 2000

I use the following sql 2000 query:
Delete otherdatabase..table
From table Join otherdatabase..table on table.DSN =
otherdatabase..table.DSN
Where otherdatabase..table.TOUCH <= table.TOUCH
The table structure of "otherdatabase..table" and "Table" are the same.
The query works fine under sql 2000.
With sql 2005 I get an error:
The objects "otherdatabase..table" and "table" in the FROM clause have
the same exposed names. Use correlation names to distinguish them.
When I replace table with table as table_a it works.
I use the syntax above at many places.
Any hints to run the query above without changes under sql 2005?try setting database compatibility to 8.0
Farmer
<Klaus8812@.community.nospam> wrote in message
news:OuyJTR83FHA.252@.TK2MSFTNGP15.phx.gbl...
>I use the following sql 2000 query:
>
> Delete otherdatabase..table
> From table Join otherdatabase..table on table.DSN =
> otherdatabase..table.DSN
> Where otherdatabase..table.TOUCH <= table.TOUCH
> The table structure of "otherdatabase..table" and "Table" are the same.
>
> The query works fine under sql 2000.
>
> With sql 2005 I get an error:
>
> The objects "otherdatabase..table" and "table" in the FROM clause have
> the same exposed names. Use correlation names to distinguish them.
> When I replace table with table as table_a it works.
> I use the syntax above at many places.
> Any hints to run the query above without changes under sql 2005?
>|||Hello,
I tested the issue on my side and I received the same error in SQL server
2000. However, add a table alias can resolve the error. For your
reference, I tested it by performing the following steps:
1. Run the following code in SQL server 2000:
select * into test..authors from pubs..authors
go
Delete test..authors
From authors Join test..authors on authors.au_id =
test..authors.au_id
Where test..authors.au_lname='white'
I received the same error message you mentioned. However, the following
works fine:
Delete test..authors
From authors a Join test..authors on a.au_id =test..authors.au_id
Where test..authors.au_lname='white'
2. Run the following code in SQL server 2005, I receive the same error:
select * into test..address from person.address
Delete test..address
From person.address Join test..address on person.address.addressid =
test..address.addressid
Where test..address.addressID=1
The the following works fine:
Delete test..address
From person.address a Join test..address on a.addressid =
test..address.addressid
Where test..address.addressID=1
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||You should add aliases to Remote tables.
In general, 3dot notation is used for cross server queries, eg.
server.database.owner.object
Using 3dot notation in join clauses is just confusing.
eg.
DELETE FROM
myDatabase..MyTable
FROM
myDatabase..MyTable remoteMyTable
JOIN
MyTable localMyTable
ON
remoteMyTable.id = localMyTable.id
WHERE
remoteMyTable.Filter <= localMyTable.Filter
In addition to making it work in 2k5, it also makes it more readable IMO.
<Klaus8812@.community.nospam> wrote in message
news:OuyJTR83FHA.252@.TK2MSFTNGP15.phx.gbl...
> I use the following sql 2000 query:
>
> Delete otherdatabase..table
> From table Join otherdatabase..table on table.DSN =
> otherdatabase..table.DSN
> Where otherdatabase..table.TOUCH <= table.TOUCH
> The table structure of "otherdatabase..table" and "Table" are the same.
>
> The query works fine under sql 2000.
>
> With sql 2005 I get an error:
>
> The objects "otherdatabase..table" and "table" in the FROM clause have
> the same exposed names. Use correlation names to distinguish them.
> When I replace table with table as table_a it works.
> I use the syntax above at many places.
> Any hints to run the query above without changes under sql 2005?
>