Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm migrating a SQL Server 6.5 database to SQL Server 2012. Migration went well but one of the software using this database performs a strange query:

UPDATE myTable SET Field1='',Field2=0, a.Field3=14 WHERE Field10=1

The problem lies of course with the identifier ('a') before Field3. There are no views, aliases or join with that name but for SQL Server 6,5 that is a valid query.

Since I'm not familiar with SQL Server 6.5, do any of you remember the meaning of this syntax?

The software using this query is an old one of which I don't have any sources (and the company that made it is now closed...), so changing the query is not an option.

The full query is

UPDATE SituazioneMacchina 
SET DatiSetPointSpediti=''
,   SpedMappa=0
,   NrRichMappa=0
,   DatiSetPoint=''
,   a.IDProduzione=14
WHERE NumMacchina=1 

If I change "a.IDProduzione" with something like "fasdffas.IDProduzione", the query still works in SQL Server 6.5

share|improve this question
    
The complete query is as follow: UPDATE SituazioneMacchina SET DatiSetPointSpediti='',SpedMappa=0,NrRichMappa=0,DatiSetPoint='', a.IDProduzione=14 WHERE NumMacchina=1 If I change "a.IDProduzione" with something like "fasdffas.IDProduzione", the query still works in SQL 6.5 –  Nicola Cassolato Dec 6 '12 at 17:06
    
If the query became UPDATE a SET ... FROM myTable AS a WHERE ... that should make it valid, yes? –  billinkc Dec 6 '12 at 17:11
    
Looks like this was fixed by 7.0. I can't get it to accept this syntax. 4.21 is fine with it, though, essentially ignoring the table alias. –  db2 Dec 6 '12 at 19:05
    
@db2 - You still have 4.21 running? –  Martin Smith Dec 6 '12 at 19:11
1  
@MartinSmith Doesn't everybody have a 4.21 VM sitting around? :) –  db2 Dec 6 '12 at 19:15
show 1 more comment

1 Answer 1

up vote 6 down vote accepted

As you say the query doesn't make any sense.

Probably just a parser bug in 6.5. There have been a few similar other issues ORDER BY nonsensealias.somecolumn worked until 2005 with the SQL Server 2000 behavior being

Column names in the ORDER BY clause are resolved to columns listed in the select list, regardless if they are qualified. For example, the following query executes without error:

USE pubs
SELECT au_fname AS 'FName',
  au_lname AS 'LName'
FROM authors a
ORDER BY a.LName

SQL Server ignores the qualifier a in the ORDER BY clause and resolves the column name LName to the select list.

and there is still a similar issue for INSERT statements where the following syntax is accepted.

CREATE TABLE nisse(hult int NOT NULL)
go
INSERT nisse(ab.xx.hult)
VALUES(12)
go
DROP TABLE nisse
share|improve this answer
    
Thanks Martin. I'v tried the same query in SQL 2008 with SQL 2000 compatibility level and it doesn't work. Maybe the bug was resolved in SQL 2000. I'm gonna investigate for one more day and then I'll mark your answer as accepted. –  Nicola Cassolato Dec 6 '12 at 18:07
    
@Nicola query in SQL 2008 with SQL 2000 compatibility level This changes some things around parsing and tokenizing, but there is still only ONE underlying engine, i.e. it doesn't magically run the SQL Server 2000 engine! –  孔夫子 Dec 6 '12 at 20:30
    
Yes Richard, I know that, I was just hoping :) –  Nicola Cassolato Dec 7 '12 at 8:46
    
@Martin: you were right. It's a SQL 6.5 bug, solved in SQL Server 7.0. I can make that query work on SQL 2000 with compatibility level up to 65. –  Nicola Cassolato Dec 7 '12 at 8:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.