Greyed out View Designer and Query Designer

Jarryd (jarryd@nodomain.com) writes:
> OK View Designer was just me being stupid. I opened a view and it didn't
> enable the toolbar. Tried it again and it is fine now so I must have done
> something silly, but I still can't figure out what the point of the Query
> Designer toolbar is.

Well, the entire Query Designer is pointless in my opinion. I strongly
recommend from any usage of it. The Query Designer is only able to
deal with a fairly limited subset of SQL and it also has the habit of
adding things to do not belong in a query, even less a view.


> And what gives with there being no more Open Table option? The closest
> thing to it is the SELECT TOP 1000 option. Is this a configuration /
> customisation step that I am being lazy not to do?

That or Edit TOP 200 Rows. The point is that dragging all rows in a
ten-million row table in Mgmt Studio is not good for the health of the
database server, nor your machine.

You can edit the limits under Tools->Options->Object Explorer->Commands.



--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Greyed out View Designer and Query Designer 

Hi Erland,

I have read this issue with Query Designer not being the best tool. Still
seems odd that there is a redundant toolbar in there. Would've been less
confusing to leave it out all together.

Anyhoo, what do we use as a substitute then? I want to open a table, see
the design pane, criteria pane, SQL pane, and the results pane... all in
one.

I realise that I can highlight all or part a script, right click, and...
boring. I just want to open a tool, with all the panes, and run the SQL and
see the results.

TIA,

Jarryd
 >> Stay informed about: Greyed out View Designer and Query Designer 

This feature of SQL Server Management Studio is part of a package called
Visual Database Tools (VDT) that SSMS takes from a central team at
Microsoft. The VDT is also used by Visual Studio and Microsoft Access. What
Access calls a query is roughly analogous to a view in SQL Server. The VDT
has toolbar for each. You may have noticed they share all but one button.
The Change Type on the Query toolbar lets Access users create "make table"
queries and "update queries". SSMS sucks up VDT without changing the bits.
Just trying to save you money! Alright. It's more about saving Microsoft
money, but it also reduces the chance of introducing bugs by disabling stuff
in an already tested tool.
--
Rick Byham (MSFT), SQL Server Books Online
This posting is provided "AS IS" with no warranties, and confers no rights.

"Jarryd" wrote in message

> Hi Erland,
>
> I have read this issue with Query Designer not being the best tool. Still
> seems odd that there is a redundant toolbar in there. Would've been less
> confusing to leave it out all together.
>
> Anyhoo, what do we use as a substitute then? I want to open a table, see
> the design pane, criteria pane, SQL pane, and the results pane... all in
> one.
>
> I realise that I can highlight all or part a script, right click, and...
> boring. I just want to open a tool, with all the panes, and run the SQL
> and see the results.
>
> TIA,
>
> Jarryd
 >> Stay informed about: Greyed out View Designer and Query Designer 

Jarryd (jarryd@nodomain.com) writes:
> Anyhoo, what do we use as a substitute then? I want to open a table, see
> the design pane, criteria pane, SQL pane, and the results pane... all in
> one.
>
> I realise that I can highlight all or part a script, right click, and...
> boring. I just want to open a tool, with all the panes, and run the SQL
> and see the results.

If you are looking for entertainment, maybe you should be doing SQL
at all?

I think the Query Editor is the best choice. While simple queries
lends themselves to graphic representation, more complex queries don't.
It may be boring to just select a query and run it, but somewhat naïvely
I assume that people who ask in these newsgroups are interested in their
professional development. But it's difficult to evolve as an SQL
programmer when you stay in the Query Designer.

And anyway, when you run a query in the query editor, you do get to see
the results.

--
Erland Sommarskog, SQL Server MVP, esquel.RemoveThis@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Greyed out View Designer and Query Designer 

A bit of an elitist attitude you seem to have there Erland. I see the
smiley; it doesn't really do much to change that.

What's the big point you are making here? Don't use graphical tools to
create queries? At all? If you don't want to work towards being a human
coding machine then you shouldn't be allowed to retrieve data from a
database EVER. Instead, pay someone $1,000 a day to do it for you. Come
on. I don't do SQL statements all the time. I can get data out, create
functions, stored procedures, etc. Nothing too high-brow, but good enough
for what I need. Not doing it often and not being an accomplished typist I
find graphical aids helpful. Sure, doing more complex queries requires you
to get stuck in there, and I do on the rare occasion, butter fingers and
all. But often I am doing simple queries to check / fix up data integrity
issues. E.g. Navision is not displaying certain Sales Orders and some
simple querying reveals that this is because the Global Dimension fields are
blank and so these records have been filtered out. Rather than typing out:
USE NAVDB
GO

SELECT No_, [Sell-to Customer No_], [Posting Date], [Shortcut Dimension
1 Code], [Shortcut Dimension 2 Code]
FROM [Toray$Sales Header]
WHERE ([Posting Date] BETWEEN '20070401' AND '20080331')

I could simply right-click a table to open it, get rid of what I don't want
using the design pane and put in a few filters, and I'm there. I don't have
to run it four times to fix up typos. Brilliant!

We had something in SSMS2005 that worked better for me. It seems that has
been pulled for good reason as it tends to do things that are potentially
problematic. That is all you have to say. Having a go at my competency is
unwelcomed and more relevant to the purposes of these newsgroups, unhelpful.

Query Designer pretty much does what I need it to but I CANNOT run the query
from there. I have to click OK and run it from the query editor. It aint a
big deal, but why the extra step? Honestly, I don't see what Query Designer
without a results pane is doing better to stave off gremlins than having one
that does. There must be something and anyway, its gone. No problem, it
seems a little unnecessary but it saves on production costs for MS which may
or may not feed through to us customers, and ultimately I can manage without
it. I was only asking if there was a way to do things like we all used to.
The answer appears to be no and some reasons have been put forward that are
"fair enough", but making jibes offers little in the form of anything
constructive.

"If you haven't got anything nice to say, don't saying anything" is what my
dear old dad used to say .

Regards,

Jarryd
 >> Stay informed about: Greyed out View Designer and Query Designer 

Hi Rick,

Thanks for the info. It isn't a big deal. I guess what you say makes sense
and surely there were issues with the older SSMS and the resolution was to
simply strip out some of the gooey bits. Fine. I can manage without it.

Thanks again,

Jarryd
 >> Stay informed about: Greyed out View Designer and Query Designer 

Jarryd (jarryd@nodomain.com) writes:
> What's the big point you are making here? Don't use graphical tools to
> create queries? At all?

If you use a graphical tool, you should use one that

1) Generates good SQL
2) Does not prevent you from using essential SQL constructs.

I opened the View Designer, selected some tables and checked a few boxes,
and I got this query:

SELECT TOP (100) PERCENT dbo.Customers.CustomerID, dbo.Customers.CompanyName, dbo.Employees.EmployeeID, dbo.Employees.LastName, dbo.Employees.FirstName,
dbo.Orders.OrderID, dbo.Orders.OrderDate
FROM dbo.Customers INNER JOIN
dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID INNER JOIN
dbo.Employees ON dbo.Orders.EmployeeID = dbo.Employees.EmployeeID
WHERE (dbo.Orders.OrderDate > CONVERT(DATETIME, '1997-01-01 00:00:00', 102))
ORDER BY dbo.Orders.OrderDate


What's bad with this?

1) There are no table aliases, and nor can I find a way to add table
aliases that the designer picks up. Repeating tables names over and
over again in a query, makes it difficult to see the forest for all
the trees.
2) The placement of the JOIN operator is poor in my opinion, but admittedly
this is more an issue of style.
3) The handling of the datetime parameter is clunky. I entered
"> '19970101'", in return I got the clunky format with datetime.
Date literals is a difficult terrain, because they may be interpreted
differently because of settings, but the format YYYYMMDD is unambiguous.

But the real killer is the TOP (100) PERCENT + ORDER BY clause. While
syntactically permitted, a designer intended to help beginners, should
not produce anything which is completely meaningless. A view is an
unordered object, and thus you normally cannot have an ORDER BY clause
in a view. You can, if you also have a TOP clause, which would make
sense if you for instance want a view that show the 100 most recent
orders or the top five percent customers by order amount. But TOP 100
PERCENT is of course, entirely redundant.

It happened to be the case in SQL 2000 that if you said "SELECT * FROM
myview", you would often get the rows in the order implied by the ORDER BY
clause in the view, but this happens far less often in SQL 2005 and
SQL 2008.

Now over to the second part. Let's say you want to show details from
the most recent order for each customer. This is the query:

WITH numbered_orders AS (
SELECT C.CustomerID, C.CompanyName, E.EmployeeID, E.LastName,
O.OrderID, O.OrderDate,
rowno = row_number() OVER(PARTITION BY C.CustomerID
ORDER BY O.OrderID DESC)
FROM Customers C
JOIN Orders O ON C.CustomerID = O.CustomerID
JOIN Employees E ON O.EmployeeID = E.EmployeeID
)
SELECT CustomerID, CompanyName, EmployeeID, LastName, OrderID, OrderDate
FROM numbered_orders
WHERE rowno = 1

If I select this query, and then select "Design Query in Editor", I get
this message:

The text that you have selected cannot be represented graphically using
the Query Designer. Please select just the text necessary to design a
SELECT, DELETE, INSERT or UPDATE statement.

This is not a very advanced query; it is quite common to ask for this
information. What is new to SQL 2005 is the row_number function. (This
function is extremely powerful.)

> USE NAVDB
> GO
>
> SELECT No_, [Sell-to Customer No_], [Posting Date],
> [Shortcut Dimension 1 Code], [Shortcut Dimension 2 Code]
> FROM [Toray$Sales Header]
> WHERE ([Posting Date] BETWEEN '20070401' AND '20080331')
>
> I could simply right-click a table to open it, get rid of what I don't
> want using the design pane and put in a few filters, and I'm there. I
> don't have to run it four times to fix up typos. Brilliant!

I can appreciate that those table and column names do not lend themselves
for memorising and typing. But SSMS introduces a new feature that you
may find useful in this regard: intellisense. True, intellisense is not
for everyone; I hate it myself.

> We had something in SSMS2005 that worked better for me.

Exactly what? As far as I can make out, neither in SQL 2005 the query
designer permitted you to run the query. You could only do that when
running it as the view designer.

> It seems that has been pulled for good reason as it tends to do things
> that are potentially problematic. That is all you have to say. Having
> a go at my competency is unwelcomed and more relevant to the purposes of
> these newsgroups, unhelpful.

I did not make a comment on your competence, but well on your attitude
("boring").

> Query Designer pretty much does what I need it to but I CANNOT run the
> query from there. I have to click OK and run it from the query editor.
> It aint a big deal, but why the extra step? Honestly, I don't see what
> Query Designer without a results pane is doing better to stave off
> gremlins than having one that does.

Well, find the View node in Object Explorer, select "New View", and
there you go.





--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Greyed out View Designer and Query Designer 

Without arguing any of the other points, you can use table aliases in the
View Designer.
Open the designer and add a table.
With the table selected, press F4 to open the Properties window. Now you can
see an Alias box.
--
Rick Byham (MSFT), SQL Server Books Online
This posting is provided "AS IS" with no warranties, and confers no rights.

"Erland Sommarskog" wrote in message

> Jarryd (jarryd@nodomain.com) writes:
>> What's the big point you are making here? Don't use graphical tools to
>> create queries? At all?
>
> If you use a graphical tool, you should use one that
>
> 1) Generates good SQL
> 2) Does not prevent you from using essential SQL constructs.
>
> I opened the View Designer, selected some tables and checked a few boxes,
> and I got this query:
>
> SELECT TOP (100) PERCENT dbo.Customers.CustomerID,
> dbo.Customers.CompanyName, dbo.Employees.EmployeeID,
> dbo.Employees.LastName, dbo.Employees.FirstName,
> dbo.Orders.OrderID, dbo.Orders.OrderDate
> FROM dbo.Customers INNER JOIN
> dbo.Orders ON dbo.Customers.CustomerID =
> dbo.Orders.CustomerID INNER JOIN
> dbo.Employees ON dbo.Orders.EmployeeID =
> dbo.Employees.EmployeeID
> WHERE (dbo.Orders.OrderDate > CONVERT(DATETIME, '1997-01-01
> 00:00:00', 102))
> ORDER BY dbo.Orders.OrderDate
>
>
> What's bad with this?
>
> 1) There are no table aliases, and nor can I find a way to add table
> aliases that the designer picks up. Repeating tables names over and
> over again in a query, makes it difficult to see the forest for all
> the trees.
> 2) The placement of the JOIN operator is poor in my opinion, but
> admittedly
> this is more an issue of style.
> 3) The handling of the datetime parameter is clunky. I entered
> "> '19970101'", in return I got the clunky format with datetime.
> Date literals is a difficult terrain, because they may be interpreted
> differently because of settings, but the format YYYYMMDD is unambiguous.
>
> But the real killer is the TOP (100) PERCENT + ORDER BY clause. While
> syntactically permitted, a designer intended to help beginners, should
> not produce anything which is completely meaningless. A view is an
> unordered object, and thus you normally cannot have an ORDER BY clause
> in a view. You can, if you also have a TOP clause, which would make
> sense if you for instance want a view that show the 100 most recent
> orders or the top five percent customers by order amount. But TOP 100
> PERCENT is of course, entirely redundant.
>
> It happened to be the case in SQL 2000 that if you said "SELECT * FROM
> myview", you would often get the rows in the order implied by the ORDER BY
> clause in the view, but this happens far less often in SQL 2005 and
> SQL 2008.
>
> Now over to the second part. Let's say you want to show details from
> the most recent order for each customer. This is the query:
>
> WITH numbered_orders AS (
> SELECT C.CustomerID, C.CompanyName, E.EmployeeID, E.LastName,
> O.OrderID, O.OrderDate,
> rowno = row_number() OVER(PARTITION BY C.CustomerID
> ORDER BY O.OrderID DESC)
> FROM Customers C
> JOIN Orders O ON C.CustomerID = O.CustomerID
> JOIN Employees E ON O.EmployeeID = E.EmployeeID
> )
> SELECT CustomerID, CompanyName, EmployeeID, LastName, OrderID, OrderDate
> FROM numbered_orders
> WHERE rowno = 1
>
> If I select this query, and then select "Design Query in Editor", I get
> this message:
>
> The text that you have selected cannot be represented graphically using
> the Query Designer. Please select just the text necessary to design a
> SELECT, DELETE, INSERT or UPDATE statement.
>
> This is not a very advanced query; it is quite common to ask for this
> information. What is new to SQL 2005 is the row_number function. (This
> function is extremely powerful.)
>
>> USE NAVDB
>> GO
>>
>> SELECT No_, [Sell-to Customer No_], [Posting Date],
>> [Shortcut Dimension 1 Code], [Shortcut Dimension 2 Code]
>> FROM [Toray$Sales Header]
>> WHERE ([Posting Date] BETWEEN '20070401' AND '20080331')
>>
>> I could simply right-click a table to open it, get rid of what I don't
>> want using the design pane and put in a few filters, and I'm there. I
>> don't have to run it four times to fix up typos. Brilliant!
>
> I can appreciate that those table and column names do not lend themselves
> for memorising and typing. But SSMS introduces a new feature that you
> may find useful in this regard: intellisense. True, intellisense is not
> for everyone; I hate it myself.
>
>> We had something in SSMS2005 that worked better for me.
>
> Exactly what? As far as I can make out, neither in SQL 2005 the query
> designer permitted you to run the query. You could only do that when
> running it as the view designer.
>
>> It seems that has been pulled for good reason as it tends to do things
>> that are potentially problematic. That is all you have to say. Having
>> a go at my competency is unwelcomed and more relevant to the purposes of
>> these newsgroups, unhelpful.
>
> I did not make a comment on your competence, but well on your attitude
> ("boring").
>
>> Query Designer pretty much does what I need it to but I CANNOT run the
>> query from there. I have to click OK and run it from the query editor.
>> It aint a big deal, but why the extra step? Honestly, I don't see what
>> Query Designer without a results pane is doing better to stave off
>> gremlins than having one that does.
>
> Well, find the View node in Object Explorer, select "New View", and
> there you go.
>
>
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel.TakeThisOut@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
 >> Stay informed about: Greyed out View Designer and Query Designer 

Rick Byham, (MSFT) (rickbyh@REDMOND.CORP.MICROSOFT.COM) writes:
> Without arguing any of the other points, you can use table aliases in
> the View Designer. Open the designer and add a table. With the table
> selected, press F4 to open the Properties window. Now you can see an
> Alias box.

Now, that was well hidden! Does not show up in a context menu or anything.
Thanks for the enlightment, Rick!



--
Erland Sommarskog, SQL Server MVP, esquel.TakeThisOut@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Greyed out View Designer and Query Designer 

Yeah, there are all kinds of little corners in VS/SSMS where neat stuff is
hidden.
--
Rick Byham (MSFT), SQL Server Books Online
This posting is provided "AS IS" with no warranties, and confers no rights.

"Erland Sommarskog" wrote in message

> Rick Byham, (MSFT) (rickbyh@REDMOND.CORP.MICROSOFT.COM) writes:
>> Without arguing any of the other points, you can use table aliases in
>> the View Designer. Open the designer and add a table. With the table
>> selected, press F4 to open the Properties window. Now you can see an
>> Alias box.
>
> Now, that was well hidden! Does not show up in a context menu or anything.
> Thanks for the enlightment, Rick!
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel.TakeThisOut@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
 >> Stay informed about: Greyed out View Designer and Query Designer