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
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@.microsoft.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
|||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
