MS SQL Server Tools - Intellisense in Views corrupts SQL Syntax

Author

Intellisense in Views corrupts SQL Syntax

Sparky

2005-04-30, 11:25 am

When selecting "Design mode" from "Views", the syntax I enter is
automatically changed on-the-fly and corrupts the syntax.

For example from pubs:

SELECT TOP 100 PERCENT *
FROM dbo.authors a INNER JOIN
dbo.publishers p ON a.city = p.city
ORDER BY a.au_lname DESC

Problems:
1. I can't remove "TOP 100 PERCENT" which is valid SQL.
2. "a INNER JOIN" should be "AS a INNER JOIN"

So, how can I code valid SQL without the code being changed?

Sparky
and the phrase "a INNER JOIN" should be:
"AS a INNER JOIN"which would be valid

Tibor Karaszi

2005-05-01, 3:25 am

EM has a lot of strange things in the query builder. I recommend developing your views in Query
Analyzer. As for TOP, you can't have ORDER BY without TOP in a view. A view is supposed to behave as
a table and a table is not ordered. A view allow ORDER BY if you have TOP as this will influence
which rows are returned, the ordering is possibly still not guaranteed. You might want to look at
this:
http://www.aspfaq.com/show.asp?id=2455

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"Sparky" <sparky@boing.com> wrote in message
news:7F7B6BCF-CD0C-405B-8A11- 7AC6E268E80F@microso
ft.com...
> When selecting "Design mode" from "Views", the syntax I enter is
> automatically changed on-the-fly and corrupts the syntax.
>
> For example from pubs:
>
> SELECT TOP 100 PERCENT *
> FROM dbo.authors a INNER JOIN
> dbo.publishers p ON a.city = p.city
> ORDER BY a.au_lname DESC
>
> Problems:
> 1. I can't remove "TOP 100 PERCENT" which is valid SQL.
> 2. "a INNER JOIN" should be "AS a INNER JOIN"
>
> So, how can I code valid SQL without the code being changed?
>
> Sparky
> and the phrase "a INNER JOIN" should be:
> "AS a INNER JOIN"which would be valid

Sparky

2005-05-01, 1:25 pm

The problem arises when I use Visual Studio 2003's Server Explorer tool to
explore a selected "View" from the sqlserver's treeview. I was not using EM
or QA to edit the view.

Tibor: Thnaks sooo much for the reply - saw your web site -- Great!

Sparky

====================
====================
========

"Sparky" wrote:

> When selecting "Design mode" from "Views", the syntax I enter is
> automatically changed on-the-fly and corrupts the syntax.
>
> For example from pubs:
>
> SELECT TOP 100 PERCENT *
> FROM dbo.authors a INNER JOIN
> dbo.publishers p ON a.city = p.city
> ORDER BY a.au_lname DESC
>
> Problems:
> 1. I can't remove "TOP 100 PERCENT" which is valid SQL.
> 2. "a INNER JOIN" should be "AS a INNER JOIN"
>
> So, how can I code valid SQL without the code being changed?
>
> Sparky
> and the phrase "a INNER JOIN" should be:
> "AS a INNER JOIN"which would be valid

Sponsored Links