|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Greyed out View Designer and Query DesignerHi all,
If I add the View Designer or Query Designer toolbars they remain greyed out no matter what I do. What is the point of this. If this functionality is only available using tools other than SSMS-2008 then why is it available to add this toolbar in SSMS-2008? If it is possible to get these toolbars livened up then please someone tell me what I am doing wrong. 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? TIA, Jarryd Hi,
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. Thanks again. Jarryd Show quoteHide quote "Jarryd" <jar***@nodomain.com> wrote in message news:O0owDmeWJHA.1532@TK2MSFTNGP03.phx.gbl... > Hi all, > > If I add the View Designer or Query Designer toolbars they remain greyed > out no matter what I do. What is the point of this. If this > functionality is only available using tools other than SSMS-2008 then why > is it available to add this toolbar in SSMS-2008? If it is possible to > get these toolbars livened up then please someone tell me what I am doing > wrong. > > 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? > > TIA, > > Jarryd Jarryd (jar***@nodomain.com) writes:
> OK View Designer was just me being stupid. I opened a view and it didn't Well, the entire Query Designer is pointless in my opinion. I strongly> 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. 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 That or Edit TOP 200 Rows. The point is that dragging all rows in a> thing to it is the SELECT TOP 1000 option. Is this a configuration / > customisation step that I am being lazy not to do? 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, esq***@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 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 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. -- Show quoteHide quoteRick Byham (MSFT), SQL Server Books Online This posting is provided "AS IS" with no warranties, and confers no rights. "Jarryd" <jar***@nodomain.com> wrote in message news:uriMkQsWJHA.5064@TK2MSFTNGP02.phx.gbl... > 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 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 Jarryd (jar***@nodomain.com) writes:
> Anyhoo, what do we use as a substitute then? I want to open a table, see If you are looking for entertainment, maybe you should be doing SQL > 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. 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, esq***@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 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 Jarryd (jar***@nodomain.com) writes:
> What's the big point you are making here? Don't use graphical tools to If you use a graphical tool, you should use one that> create queries? At all? 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 I can appreciate that those table and column names do not lend themselves> 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! 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 I did not make a comment on your competence, but well on your attitude> 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. ("boring"). > Query Designer pretty much does what I need it to but I CANNOT run the Well, find the View node in Object Explorer, select "New View", and> 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 you go. -- Erland Sommarskog, SQL Server MVP, esq***@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 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. -- Show quoteHide quoteRick Byham (MSFT), SQL Server Books Online This posting is provided "AS IS" with no warranties, and confers no rights. "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns9B73B80CA4223Yazorman@127.0.0.1... > Jarryd (jar***@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, esq***@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 > Rick Byham, (MSFT) (rick***@REDMOND.CORP.MICROSOFT.COM) writes:
> Without arguing any of the other points, you can use table aliases in Now, that was well hidden! Does not show up in a context menu or anything.> 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. Thanks for the enlightment, Rick! -- Erland Sommarskog, SQL Server MVP, esq***@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 Yeah, there are all kinds of little corners in VS/SSMS where neat stuff is
hidden. -- Show quoteHide quoteRick Byham (MSFT), SQL Server Books Online This posting is provided "AS IS" with no warranties, and confers no rights. "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns9B84F3B633A1EYazorman@127.0.0.1... > Rick Byham, (MSFT) (rick***@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, esq***@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 >  
Other interesting topics
Script to delete backup files which are 7 days old
[reporting services 2005] excel export doesn't honor numeric format 2nd Post: MSDN Subscriber - Sql Server 2005 Restore from tape fails ... SQLCMD on SQL cluster Quest vs Idera Backup completion Notification using smtp MS SQL Server Management Studio Using Profiler to capture database name and object script to delete old backup jobs Using a trigger on sysjobhistory to send mail via cdosysmail |
|||||||||||||||||||||||
