Views with Order By not in Select List

I noticed that if you have a View which has an item in the Order by list, but not in the select list the script will error out. For example:
CREATE VIEW user.view
AS
SELECT TOP 3 Id, Naam, Host, Woonplaats, IdAbon, WidthH, HeightH, YesCount, Datum
FROM user.table
WHERE (IdAbon = 3) AND (Foto = 1)
ORDER BY (YesCount+Datum)

Will give you the following error:

"Error 4511: Could not perform CREATE VIEW because no column name was specified for column X"

It is possible to make these views, however it needs to be created first and then Altered to the final select statement (the check only happens on create). I know it might be difficult to know in advance whether a statement would produce this error, but it would be really nice if SQL Compare would create a dummy view first, then alter it with the correct view statement in these situations.

Anyways, now I have to manually edit the scripts to make it work.

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Mike,

    We can have a look into this. It would seem SQL Server's syntax checker has a bug if it's fickle and changes its mind to allow something when you alter a view that it wouldn't allow on create view!
  • Agreed, it probably could be considered a bug, but it is all these small issues that I've encountered that has made me have to spend lots of time diagnosing the errors and rewriting the scripts I get from SQL Compare.
  • It appears it is not a bug, but rather a limitation of SQL Server 7. SQL Server 2000 allows this view creation, and SQL Server 7 does not. So this type of error only happens when sync'ing from 2000 to 7. I'm not sure how you address other incompatabilities, but perhaps raising a warning would help.
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Mike,

    There is a section in the help about making scripts SQL 7 compatible. I don't believe this is addressed in there so we'll have to look into adding it for sure!
Sign In or Register to comment.