One of the servers can’t see it’s own cubes. As a work-around you can Right-Click on the SSMS icon and select > Run as Administrator to get SSMS to see it’s own cubes: If you skip step in the setup where it asked you to add current user to AS server admin role, then you would need to run SSMS as administrator.
Adam Machanic told us all about a new craze sweeping the SQL Blogosphere Nation last week and that is T-SQL Tuesdays. Real quick, the way it works is that on Patch-Tuesday everyone who’s got something to say about the topic of the month releases a blog post about it and the “Host” of the topic recaps all of the various blog posts in a blog at their site. OK now onto the topic. Because I think there might be a few people bolgging about this today I will keep min short-and-sweet and go over 5 main points of the Old-School DateTime data type. Before I start I want to remind you that DBAs Data Professionals are a little different, a lot of us think in terms of Largest to Smallest unit when we think of DateTime; which makes it into the way that you think of a standard number. Right now it is “200912051505” or “2009-12-05 15:05” Selecting and aggregating using Styles all of the rows in a table can be a little problematic if you are selecting a date that looks something like this one “2009-12-05 15:05”. One of my favorite ways to get around this is with the CONVERT function and changing it to a VARCHAR and then using a style. Here’s what I usually do. Run this query in AdventureWorks and you’ll notice that it also pulls in time which might be something that we want to avoid: SELECT COUNT(*) AS ‘# of Orders’ , OrderDate AS ‘Order Date’ FROM Sales.SalesOrderHeader GROUP BY OrderDate ORDER BY OrderDate # of Orders Order Date 32 2008-07-28 00:00:00.000 31 2008-07-29 00:00:00.000 23 2008-07-30 00:00:00.000 40 2008-07-31 00:00:00.000 So what I do is CONVERT it to VARCHAR and then add Style 112 to it SELECT COUNT(*) AS ‘# of Orders’ , CONVERT(VARCHAR, OrderDate, 112) AS ‘Order Date’ FROM Sales.SalesOrderHeader GROUP BY CONVERT(VARCHAR, OrderDate, 112) ORDER BY CONVERT(VARCHAR, OrderDate, 112) # of Orders Order Date 32 20080728 31 20080729 23 20080730 40 20080731 Now this isn’t overly readable but it sorts great. Another problem with it is that it doesn’t paste into Excel real well. For pasting into Excel and having it quickly recognize it as a DateTime field I use Style 110 but there’s a catch. Style 110 pastes into Excel fine but it doesn’t sort properly so I end up having to keep my Style 112 column so that I can sort on it. SELECT COUNT(*) AS ‘# of Orders’ , CONVERT(VARCHAR, OrderDate, 110) AS ‘Order Date’ , CONVERT(VARCHAR, OrderDate, 112) AS ‘Order Sort’ FROM Sales.SalesOrderHeader GROUP BY CONVERT(VARCHAR, OrderDate, 110) , CONVERT(VARCHAR, OrderDate, 112) ORDER BY CONVERT(VARCHAR, OrderDate, 112) # of Orders Order Date 32 7/28/2008 31 7/29/2008 23 7/30/2008 40 7/31/2008 Selecting just the time with Style 108 is another trick I have used in the past. As a quick and easy way to extract just the time out of a DateTime field is to CONVERT it to VARCHAR making sure to specify a length of 5. (Now if you also want the seconds you need to make the length 8.) SELECT GETDATE() AS ‘GetDate’, CONVERT(VARCHAR(5) , GETDATE(), 108) AS ‘GetTime’ GetDate GetTime 2009-12-05 17:10:54.430 17:10 And now for my final trick Selecting the date of the Sunday of the Week (or any recurring day of the week) Sometimes – especially in Business Intelligence – you want to group data by week but you don’t want to use DATEPART (WK, OrderDate) because that produces a number and you might want something more like an actual date. In this case what I typically do is just use this: DATEADD(DAY, 1-DATEPART(WEEKDAY, OrderDate), OrderDate) (Now you can pick the Saturday of that week just as easily by swapping out that 1 for a 7.) SELECT COUNT(*) AS ‘# of Orders’ , DATEPART (WK, OrderDate) , DATEADD(DAY, 1-DATEPART(WEEKDAY, OrderDate), OrderDate) AS ‘Sunday of the Week’ , DATEPART(WEEKDAY, OrderDate) AS ‘Day# of Week’ , CONVERT(VARCHAR, OrderDate, 112) AS ‘Order Date’ FROM Sales.SalesOrderHeader GROUP BY DATEPART (WK, OrderDate) , CONVERT(VARCHAR, OrderDate, 112) , DATEADD(DAY, 1-DATEPART(WEEKDAY, OrderDate), OrderDate) , DATEPART(WEEKDAY, OrderDate) ORDER BY CONVERT(VARCHAR, OrderDate, 112) # of Orders Sunday of the Week Day# of Week Order Date 24 2008-07-20 00:00:00.000 6 20080725 32 2008-07-20 00:00:00.000 7 20080726 29 2008-07-27 00:00:00.000 1 20080727 32 2008-07-27 00:00:00.000 2 20080728 31 2008-07-27 00:00:00.000 3 20080729 23 2008-07-27 00:00:00.000 4 20080730 40 2008-07-27 00:00:00.000 5 20080731 You can check out the rest of the blogs for this T-SQL Tuesday topic right here: http://sqlblog.com/blogs/adam_machanic/archive/2009/12/09/t-sql-tuesday-001-the-roundup.aspx or do a search on Twitter and look for the hash #TSQL2sDay. I hope this helps a few people out, as always if you have any questions please comment. TSQL2sDay