-2

I have seen this question before, but aside from it being a few years old and the answer not really being SQL Server related (looks like MySQL) Im hoping someone can provide perhaps a database-option that allows it as it runs perfectly fine on SQL 2000.

So I have 2 databases, Im doing a join between the two databases with a table name that is the same. Normally, the SQL code looks like :

select Category.Category 
FROM
Category INNER JOIN TEST123.dbo.Category ON Category.CategoryRef=TEST123.dbo.Category.CategoryRef

I get the dreaded: The objects "TEST123.dbo.Category" and "Category" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

but this all runs fine in SQL 2000 - it quite happily works along, knowing that its 2 different databases. Is there a Database Option that I can enable/disable that will allow this, it just means a LOT of changes in an application that has possibly thousands of SQL statements - thanks!

Carl Harrison
  • 145
  • 13
  • 1
    Three part column names are deprecated anyway. Just give each table a different alias, for example `AS cc` and `AS tc` – Charlieface May 16 '23 at 11:00
  • 1
    You can set various compatibility options for modern SQL Server, but I don't think any of them will fix this issue. Ultimately, however, you should be fixing your code. To expect it to work in a system that is 20 years newer is wishful thinking at best. – DavidG May 16 '23 at 11:04
  • 2
    The application has probably had two decades now to get this change done so I doubt there is any option apart from sucking it up and fixing it – Martin Smith May 16 '23 at 11:05
  • 1
    if youre not using too new server version, it might help to alter database yourdb set compatibility_level = 80 – siggemannen May 16 '23 at 12:03

1 Answers1

0

Cheers everyone - yep, compat level wont go below 100 (SQL Server 2008) so Im up the creek without a paddle, so will as suggested - suck it and go through it. The issue is I have to keep it running with SQL 2k as well, while client dbs are moved over to 2019 so lots of IF THEN statements with alternating SQL INNER JOINs with and without aliases to get it working. Thanks anyway - it was, as I thought so at least I have my answer to present to the boss. Ta !

Carl Harrison
  • 145
  • 13
  • 3
    You don't need the `IF` ... `THEN`. When you fix it so it works in 2019 the same code should also work in 2000. The aliasing isn't something that was disallowed in 2000. It just didn't enforce that you had to do it. `select c.Category FROM Category c INNER JOIN TEST123.dbo.Category tc ON c.CategoryRef=tc.CategoryRef` should work in both – Martin Smith May 16 '23 at 15:54
  • I'll try that, it didnt work for me but will give it a go and see how it goes, that would be great, thanks! – Carl Harrison May 19 '23 at 09:56
  • IT WORKED. I must have gotten something wrong in my initials tests, but the alias worked - that'll save me a load of work - thanks Martin – Carl Harrison May 19 '23 at 10:39