SQL 2012 Power View Reporting Services Addin

I have to say, I was pretty blown away by a recent demo of the new Power View product (codename: CRESCENT) released with SQL Server 2012 Reporting Services. The demo shows some really cool visualization features. Power View is only available on Enterprise or Business Intelligence versions of SQL Server 2012 and it is also only accessible through Sharepoint 2010 Enterprise Edition or through Excel 2013. It’s a reporting services add-in written in Silverlight. If you are eager you can download the Excel 2013 preview here. And here is the link for the SQL Server 2012 Eval. The demo seems to run off of a cube, however they do not specify. I’m not sure, but I assume you could also run this off of data source views exposed by reporting services however it probably would not have the cross-reference capability. Power View Youtube Demo. Here are some more interesting links: TechNet – Power View Overview Visualizing the Summer Games with Power View in Excel 2013! Deployment Checklist

Continue reading ...

SQL Server 2012 – Features Update

After attending a meeting with a Microsoft engineer I have some more good news on Denali. Unfortunately the meeting ran short and we didn’t get through all the content so below I could not extrapolate on some points. I still listed them all below. Performance Datawarehouse This has existed in SQL Server 2008, however now it comes with a wizard and is much easier to setup. Basically you install a new SQL Server and run through a wizard (using a domain account) to implement the Performance Data warehouse simply by selecting the remote boxes you want to monitor. The ETL is automatically done for you and collects data on a 15 minute interval. Very cool. CodeName “Juneau” New SQL Server Developer tools integrated with Visual Studio. At the time of this writing, it’s on version CTP3. This tool looks excellent. It promises to make database development much easier and wrapped in source control. It’s easy integration with Azure seems like it will make Azure more attractive. You can view videos here: http://msdn.microsoft.com/en-us/data/hh297028. Download here. Filetable Store files (ppt, word) and link them into sql server. Get transactional support for accessing files. You can Drag drop files into a directory, and then dynamically access them via SQL Server. When files are added, searches can be run against the documents using Full Text Search. Semantic search within FT An optional parameter that captures the top 100 statistically relevant words from a document. Also captures the top 10 similar documents based on the […]

Continue reading ...

SQL Server Denali – New Features

I’m hoping this is a collaborative post, because I do not know a lot about the capabilities of Denali, and the documentation looks pretty sparse. What I do know however is that everything I have seen looks promising.  The SQL Server development team is top notch in my opinion.  The direction they are travelling is very promising for us as SQL Server professionals. Onto the post.  So I downloaded the CTP version.  Which can be downloaded here Installation One note on the install.  You cannot install this on Windows XP or on Server 2003.  This is a little surprising because I can only imagine how many clients will not be able to upgrade.  The other surprising thing is that because many companies have not adopted Windows 7 yet on their desktops (and will likely never move to Vista), the developers will not be able to install SSMS (SQL Server Management Studio).  Because of this limitation, many people may find themselves calling this version SQL Server 2013.  As for the actual installation, it’s getting more cumbersome, and complicated, however the new features and added security are worth it. SQL Server Management Studio The first cool part is SSMS.  SSMS now boasts a “Powered by Visual Studio” text on the splash screen.  Digging into it deeper, you can see the changes.  The borders of the interface are a different color and the tabs that appear when you create a new query look different.  The color coding is nicer looking (table names are […]

Continue reading ...

SQL Server Denali – Pagination using ORDER BY

SQL Server Denali (2012) now has additional features built into the ORDER BY Clause. This features makes pagination simpler and more efficient. Here is an example: [cc lang=”sql”] SELECT * FROM HumanResources.Employee E ORDER BY E.EmployeeID ASC OFFSET 55 ROWS FETCH NEXT 30 ROWS ONLY; [/cc] Offset determines the start row to begin on, while the fetch next defines how many rows should be returned. Both parameters above can be variable driven. An example of this would be the following: [cc lang=”sql”] CREATE PROCEDURE dbo.spGetEmployees ( @StartRow int = 1, @RowCount int = 10 ) AS BEGIN SELECT * FROM HumanResources.Employee E ORDER BY E.EmployeeID ASC OFFSET @StartRow ROWS FETCH NEXT @RowCount ROWS ONLY; END [/cc] As you can see, this new syntax makes pagination much simpler than having to roll your own in previous versions.

Continue reading ...

SQL Server Denali – EXECUTE Statement WITH RESULT SETS

When I had originally written this article, I was very excited that this would be an answer to a long-standing wish that I have hope SQL Server would support. I wished I could call a stored procedure without knowing what was in the result set and dump it to a table (or temp table). Currently in order to trap the result set from executing a procedure we need to perform work arounds – all because we do not dynamically know the columns and data types the procedure is returning. If there was a way SQL could facilitate this without a work-around, it would take SQL to a new level of abstraction and modularity. The WITH RESULT SETS feature does allow us to change the column names and datatypes of the result set a stored procedure returns, however it does not allow us to define a subset of columns to return. Therefore, if a new column is added to the result set of a procedure that is called using WITH RESULT SETS, an error will occur. The SQL Server Denali CTP can be downloaded here. Let’s take a look at this new feature, first we’ll create a sample stored procedure: [cc lang=”sql”] CREATE PROCEDURE spSalesOverAmt ( @Amount money ) AS BEGIN SELECT SalesOrderID, OrderQty, UnitPrice FROM Sales.SalesOrderDetail WHERE UnitPrice > @Amount END [/cc] Now let’s change the column names and the data types: [cc lang=”sql”] — Execute the procedure with a parameter EXEC spSalesOverAmt @Amount = 1200 WITH RESULT SETS ( […]

Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!