“The operation has timed out” while using Power View in Excel 2013

This one is a quick tip. I recently upgraded to Office 2013 RTM and wanted to use the excellent Power View feature which is now an integral part of Excel! However, after I setup my tables (actually I used the Power Pivot data model) I was consistently presented with the below error message whenever I tried to open the Power View add-in: “The operation has timed out” with a title of “Power View error”.

If you encounter this, my tip to you is to reinstall the Silverlight runtime on your PC. I don’t know exactly why, but that was in some way causing the above issue. I hope it will save you a few hours of troubleshooting!

Advertisements

Microsoft Report Viewer 2012 update: a ‘gotcha’ to be aware of

Something which I have been waiting for a long time has finally been released! The ReportViewer 2012 redistributable RTM package is available for download now!

Deployment notes

FYI, ReportViewer is used by Management Studio (SSMS), other utilities and also by any custom application which uses this to render local RDLC reports, or within a web application to view remotely rendered reports.

SQL 2012 installation deploys ReportViewer if the Management Tools are selected for installation. The other shipping vehicle for the ReportViewer control is Visual Studio 2012. This blog post pertains more to the case where we installed ReportViewer through the normal SQL 2012 installer.

FYI, you can view the ReportViewer 2012 assembly version at C:windowsassemblyGAC_MSILMicrosoft.ReportViewer.WinForms11.0.0.0__….. Right clicking on the assembly, and viewing the Details tab will give you the version of the DLL.

Note the ‘gotcha’

Now, the updated runtime release will deploy the equivalent of SQL 2012 SP1 binaries, so you get the latest and greatest bits! These should deploy a 11.0.3010 version for the Microsoft.ReportViewer.WinForms.dll file (and other files as well.)

Now, here’s the ‘note from the field’ thing which you can only get from me Smile If you just install SQL 2012 SP1 (without later running the above download) it does not seem to update the ReportViewer control. Normally this may not have much visible impact, but if you are like me, you may want to keep the runtime up to date due to the number of important fixes in such updated versions.

Test case

In my tests, just applying SQL 2012 SP1 installation did NOT upgrade the runtime to 11.0.3000. It was still at 11.0.2100. However, applying the above updated runtime MSI will upgrade the runtime to 11.0.3010.

Your checkpoint is that the version of ReportViewer 2012 assembly under C:windowsassemblyGAC_MSILMicrosoft.ReportViewer.WinForms11.0.0.0__….. should finally be 11.0.3010 or higher. (repeat this check for the other controls such as Microsoft.ReportViewer.WebForms as well.

Conclusion

So in short, if you use ReportViewer – either indirectly (like in SSMS) or directly (through custom applications developed using Visual Studio 2012) it is highly recommended to update your RTM ReportViewer 11.0 runtime to the latest version using the MSI from the download link.

Extra T-SQL checks to complement SQL Upgrade Advisor

Background

More and more customers are looking to upgrade to SQL Server 2012, which offers some exciting features like AlwaysOn Availability Groups, ColumnStore Indexes and much more. As part of my job in the Microsoft Premier Field Engineering team, we are called upon by customers to assess their readiness to upgrade to the new version.

Normally, when we perform an upgrade readiness assessment, we also review the T-SQL code that the customer is running. This is done to uncover breaking changes and other patterns which might cause poor performance or such behavioral change post the upgrade. For breaking changes, we normally recommend customers to use the publicly available SQL Server Upgrade Advisor (SSUA) tool. However, not all breaking changes can be detected by the current SSUA toolset.

Stuff outside of SSUA scope

Here are some examples of T-SQL breaking changes which cannot be uncovered by SSUA. Some of these are boundary scenarios, but I feel it is important to list them so that you are aware of the issues. Also, some of these are known to the SSUA team but they would not incorporate checks for them in SSUA due to their ship timeline and priority ratings.

Issue Conflicting locking hints
Description A specific issue in SQL Server 2000 caused locking hints in a FROM clause for an UPDATE statement, to be ignored. While this bug was fixed in SQL 2005, it potentially gave rise to upgrade issues if the locking hint in the FROM clause was different from the locking hint for the UPDATE source table.
Example See this Connect bug for an example.
Recommendation If at all used, locking hints need to be consistent between the table source and the FROM clause of the UPDATE.

 

Issue Objects in the FROM clause must have distinct exposed names
Description In SQL 2000, identically named tables from different databases did not require an alias
Example This code will work in compatibility mode 80 while it will break on compatibility 90 and above:

select *
from DB1.dbo.sometab
join DB2.dbo.sometab
on DB1.dbo.sometab.name = DB2.dbo.name

Recommendation It is generally a good practice in any case to alias all tables in a JOIN. That is the recommended solution in this case as well.
Additional Reading You can read more about this at Nacho’s blog.

 

Issue Subqueries are not supported in GROUP BY clause
Description The GROUP BY clause cannot contain a subquery in an expression that is used for the group by list. In earlier versions of SQL Server, this was allowed. In SQL Server 2008, error 144 is returned.
Recommendation Be aware of this breaking change and revise the query logic accordingly.
Additional Reading There are some Connect articles which describe this. Read them here, here and here.

 

Issue SETUSER statement usage
Description SETUSER may not be supported in a future release of SQL Server.
Recommendation We recommend that you use EXECUTE AS instead.

 

Issue ORDER BY clauses in views
Description The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless the TOP or OFFSET and FETCH clauses are also specified. When ORDER BY is used in these objects, the clause is used only to determine the rows returned by the TOP clause or OFFSET and FETCH clauses. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.
Recommendation Specify the ORDER BY clause only in the outermost query and not inside views.

 

Issue FASTFIRSTROW table hint usage
Description The usage of FASTFIRSTROW as a table hint has been disallowed in SQL 2012. We recommend that hints be used only as a last resort by experienced developers and database administrators.
Recommendation You can evaluate the query hint OPTION (FAST 1) instead.

 

Issue COMPUTE clause is not allowed in database compatibility 110
Description The COMPUTE clause generates totals that appear as additional summary columns at the end of the result set. However, this clause is no longer supported in SQL Server 2012.
Recommendation Use the ROLLUP operator instead.
Additional Info A Connect request has been logged to include this check in SSUA. Vote for it!

 

Conclusion

So, now you know the most common patterns which you need to check for in addition to what SSUA already uncovers. However let’s say you have a huge T-SQL code base and cannot do this manually. Our PFE team are working on some offerings: SQL Server Upgrade Assessment and also a T-SQL Patterns and Practices Review, which among other things will also cover the above deprecation / upgrade checks. If you need more information, you can contact me for more details.

Also if you have any comments, additional cases which are not detected by SSUA, please leave a feedback item in the Comments section below. I read each one of them!

Getting TaskUnzip to work with SQL Server Integration Services (SSIS) 2012

Background

It is a very common requirement in SSIS packages to extract contents of a compressed ZIP file, and then process that file in a data flow. From what I can see, it appears that TaskUnzip is quite a popular way to achieve this. So as part of the BI Aviation project I decided to use this for extracting the data from the ZIP files available in those datasets.

Using TaskUnzip

In my testing I used build 1.3.0.1 available for SQL 2008 (SSIS_TaskUnZip_1.3.0.1_SQL_2008.zip). I suspected it may not work in SQL 2012 as-is, but decided to give it a try. The setup instructions are included in the ZIP file, so I ran the Install_SQL_2008.cmd to install the library. That failed, because the references are to SQL 2008. So here is the corrected installation script:

net stop "SQL Server Integration Services 11.0"
gacutil.exe /u "ICSharpCode.SharpZipLib"
gacutil.exe /u TaskUnZip
copy "ICSharpCode.SharpZipLib.dll" "%ProgramFiles%Microsoft SQL Server110DTSTasks" /Y
copy "TaskUnZip.*" "%ProgramFiles%Microsoft SQL Server110DTSTasks" /Y
gacutil.exe /i "%ProgramFiles%Microsoft SQL Server110DTSTasksICSharpCode.SharpZipLib.dll"
gacutil.exe /i "%ProgramFiles%Microsoft SQL Server110DTSTasksTaskUnZip.dll"
net start "SQL Server Integration Services 11.0"
pause

The fixes are to change the references of 10.0 to 11.0 and 100 to 110, to suit the SQL 2012 version. You have to do similar corrections in the x86 version of the CMD script, because designers like the VS2010 based SSIS package editor are still 32-bit:

net stop "SQL Server Integration Services 11.0"
gacutil.exe /u "ICSharpCode.SharpZipLib"
gacutil.exe /u TaskUnZip
copy "ICSharpCode.SharpZipLib.dll" "%ProgramFiles(x86)%Microsoft SQL Server110DTSTasks" /Y
copy "TaskUnZip.*" "%ProgramFiles(x86)%Microsoft SQL Server110DTSTasks" /Y
gacutil.exe /i "%ProgramFiles(x86)%Microsoft SQL Server110DTSTasksICSharpCode.SharpZipLib.dll"
gacutil.exe /i "%ProgramFiles(x86)%Microsoft SQL Server110DTSTasksTaskUnZip.dll"
net start "SQL Server Integration Services 11.0"
pause

At this stage, you would expect the TaskUnzip task should show up in the SSIS Toolbox, but if you have a fresh installation of SQL 2012, it will not show up!

Rebuilding TaskUnzip for SQL 2012

Start by downloading the sources (I used this link and clicked the ‘download’ link on the top) and opening it in Visual Studio 2010 SP1. When you rebuild this project, you get the following errors:

The type or namespace name ‘Dts’ does not exist in the namespace ‘Microsoft.SqlServer’ (are you missing an assembly reference?)
The type or namespace name ‘Task’ could not be found (are you missing a using directive or an assembly reference?)

(errors truncated for brevity). This is due to the namespace changes in DTS in SQL 2012. You will also find that the references to the older DLLs are broken:

image

We also need to change the .NET Framework version to 4.0 runtime (read more about this here):

image

Then remove the old references and add Microsoft.SqlServer.Dts.Design and Microsoft.SQLServer.ManagedDTS (though the namespace names are the same, the DLLs they reference are different, hence you need to add them again.)

After this is done, the DLL build should succeed.

Deploying the recompiled DLL

After this is done, if you uninstall and reinstall the newer DLL you might get the following error:

C:2008>gacutil.exe /i "C:Program FilesMicrosoft SQL Server110DTSTasksTaskUnZip.dll"
Microsoft (R) .NET Global Assembly Cache Utility.  Version 3.5.21022.8
Copyright (c) Microsoft Corporation.  All rights reserved.

Failure adding assembly to the cache:   This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.

This is because the gacutil.exe which ships with the TaskUnzip library ZIP file is actually for the framework version 3.5. To fix this you need a gacutil for v4.0. You can find an updated gacutil at “c:Program Files (x86)Microsoft SDKsWindowsv7.0ABinNETFX 4.0 Tools” (this path is on your VS.NET box). Copy these to your deployment server and overwrite the version which shipped with the TaskUnzip version. More details on this step are here.

Conclusion

That’s it! By recompiling the DLL you can add it to the toolbox and then into your Control Flows in SSIS. We will be using this component to import our BI Aviation data, so stay tuned for more details on how that is done!

And in closing, please take a minute to leave your questions or comments in the space provided at the end of this post. I do appreciate your feedback!


Disclaimer: the mention of 3rd party or community products in these blog posts in no way constitutes a recommendation or advice. These are my personal opinion and do not reflect any of my employer’s opinions.

Aviation BI Project – Part 1: Requirements

Background

One of my hobbies is following the commercial aviation scene. My job requires me to fly around a lot, and that only sparks my curiosity further around how the commercial aviation business works. What better then, that to use this hobby as a motivation to test new features in SQL Server 2012? This hybrid interest, which I call Aviation BI, is the driver behind a series of blog posts, where I will take you through a typical lifecycle of a BI project:

  • Defining requirements
  • Solution design and technology selection
  • Cleansing and importing data into the data warehouse
  • Querying the data warehouse
  • Using a multidimensional / tabular model to obtain slice / dice insights
  • Visualizing those insights in eye-catching fashion

In the first blog post of this series, I will take you through what I (as an end user) see as possible requirements. We will also review the datasets available for consumption.

Requirements

There are some categories of requirements that we have, depending on the perspective we are talking about. A typical retail consumer would want to check some statistics:

  1. Given a particular sector, which airlines serve that sector?
  2. How is the on-time performance of each airline on the sector?
  3. Which airline has been providing the cheapest tickets on the sector?
  4. What is the average arrival delay for a particular airport? Is there a pattern based on time of day?
  5. What is the average load factor (how full is the aircraft) on the sector?

As an airline executive, I might want to look at:

  1. Which are the routes with the highest traffic?
  2. Which aircraft am I routinely having service difficulties with?

As an airport administrator, I might want to look at:

  1. For my airport, is the ramp time (the time taken to taxi from gate to runway) very high compared to other airports?
  2. What are the most common locations from which international passengers arrive to my airport?
  3. Which is the busiest time for arrivals and departures for my airport?

As an aviation analyst I may have the following queries:

  1. Which type of aircraft is used to make trans-Atlantic journeys?
  2. Which operators are still using DC-10 aircraft (DC-10s were largely phased out by major operators in the late 1990s)
  3. Did the Antonov 225 make any appearances in the US recently?
  4. What are the longest non-stop flights to the USA?

And so on… these perspectives are imaginary, but I suppose they are very typical of what people in those roles would be asking to see.

Data sets available

There are some amazing datasets available in the public domain. For our purposes, we will be using the following:

Data set Data Points Row count
BTS On-time Performance Origin, Destination airports
Departure delay
Arrival delay
Taxi times
Flight distance
Flight time

Data available: 1987 onwards
Granularity: flight

146,090,545
Airline Origin and Destination Survey (DB1B) Market Number of coupons for this market1
Origin, destination airports
Ticketing and operating carrier         
Market Fare         
Number of passengers
         
Data available: 1993 onwards
Granularity: individual ‘market’
346,513,372
Airline Origin and Destination Survey (DB1B) Ticket Number of coupons in itinerary
Origin airport
Round trip indicator
Miles flown
Ticket fare per person
Reporting carrier

Data available: 1993 onwards
Granularity: individual ticket

190,783,532
Airline Origin and Destination Survey (DB1B) Coupon Origin, destination airports
Number of passengers included
Fare class

Data available: 1993 onwards
Granularity: individual coupon

584,874,480
T-100 Segment Unique carrier code
Origin, destination airport
Aircraft type
Departures scheduled and performed
Load factor
Seats vs. passengers
Freight and mail
Distance
Ramp to ramp time
Air time

Data available: 1990 onwards
Granularity: aggregated flights (monthly)

7,347,563
T-100 Market 2 Unique carrier code
Origin, destination airport
Service class
Domestic / International indicator
Passengers
Freight and mail
Distance

Data available: 1990 onwards
Granularity:

5,994,306
FAA Service Difficulty Reporting (SDR) Data available: 1994 onwards
Granularity: each service difficulty report
 

1 A ‘market’ in DB1B terminology is a break in journey other than for changing planes.

2 The difference between ‘market’ and ‘segment’ in T-100 is explained at this link. The T-100 reporting guide is also useful to understand this dataset.

   

Next Steps

Over a series of blog posts, I will take you through how we:

  1. Cleanse and import this data into a data warehouse.
  2. Build some analysis services databases on top of the relational data. We will explore both multidimensional and tabular mode databases for this step and see where each fits in.
  3. We will then consume this data – through Reporting Services, Excel and PowerView clients.
  4. We will finally do an incremental loading of ‘new’ data into the warehouse, and follow the incremental processing of the Analysis Services databases and confirm if the reporting pieces refresh the new data.

All of this, towards answering the ‘customer’ requirements which we laid out earlier!

References

For those interested in reading further about airline statistics, here are some references:

So if you have some comments on the scope of this ‘project’, I would LOVE to hear from you! Do leave a comment below if you liked this idea and would like to see more.

Where can I download SQL 2012 Upgrade Advisor?

This post is a really quick one… Let’s say you are planning to check your SQL Server and T-SQL code for any potential breaking changes in SQL 2012. So you use Upgrade Advisor. Let’s say you want to download it, rather than get it from the installation media (the MSI can be found under ServersredistUpgrade Advisor path.)

If you monkey around with your favorite search engine (mine is Bing!) you may not find a download link which directly says ‘SQL 2012 Upgrade Advisor’. That is because the link to download SQL UA is actually under the SQL 2012 Feature Pack. Once you get to that page, look for SQLUA.MSI. Select the one as per the architecture of the machine where you are going to execute the tool (x86 or x64.) 

You may also need to install the Windows Installer 4.5 and the .NET Framework 4.0 if they are not already installed. Then install the SQLUA.MSI file… and you are done!

Virtual PC / Virtual Server 2005 to Hyper-V: VM Additions issues

Background

I have some old VPCs which I used to run on Virtual PC 2007 SP1. In Virtual PC, we used ‘VM Additions’ which would install some drivers to make the VPC run smoothly in the virtualized environment. For example these additions would enable smooth usage of the mouse across host and guest. There are multiple versions of these additions, some dating back to the old Virtual Server 2005 days.

Problem

Now, I have Windows 8, which brings along with it Hyper-V. So when I moved my (rather old) Windows 2003 VPC image to the Hyper-V platform, and I tried to use my mouse to click within the guest OS, this is what I got:

Mouse not captured in Remote Desktop session

Generally, this is due to the lack of the right type of additions for the virtualization platform. Now, Hyper-V has its own Integration Services which perform a role similar to that played by the previous VPC additions (which used to be installed from VMadditions.ISO). So when I moved my old VPC’s VHD file and created a VM under Hyper-V, I expected that a simple ‘Install Integration Services’ click would suffice. Normally, this works for VHDs previously used inside of Virtual PC, but in my case, when I did this, it failed with the following ‘Virtual machine additions detected’ error:

Virtual Machine Additions Detected

OK, so I had to uninstall the Virtual PC additions first. Let’s try to Add/Remove them. But this also failed with the following cryptic error ‘Tahoma8 You can install Virtual Machine Additions only on a virtual machine that is running a supported guest operating system’. Well, at that time, Windows 2003 was indeed supported so I wonder why this error is reported when I try to uninstall Smile

image

The existing VM additions version I have on the guest is 13.552:

Virtual Machine Additions version

Ideal solution

The ideal case is, you still have Virtual PC somewhere, using which you can uninstall the VM additions and then move the VHD back to Hyper-V. But in my case, I no longer have Virtual PC anywhere on my computers.

Workaround

After a bit of digging around, I found the workaround. You can use this at your OWN RISK. It worked for me, but please do not blame me if it does not work for you Smile

Locating the MSI

First, we will locate the MSI installer which is used to install the VM Additions. To do this, firstly you have to be pretty good with your keyboard skills Smile 

  • First, put the guest into ‘Full Screen’ mode. This will allow you to use the Windows keyboard shortcuts on the guest.
  • Start up Windows Explorer on the guest (Windows key + E)
  • Do a Alt-D to go to the location bar. Type in “C:windowsinstaller” (you cannot see this folder normally)

Now, there are 2 ways to get to know which MSI is the VM additions MSI. Here is the first way:

  • In the list that you get, look at each MSI file and check its properties. To do that use the properties key (right side of the spacebar normally)
  • Use Tab and shift-Tab to navigate to the Summary tab of the properties window. Look at the ‘Subject’ field.
  • Quick tip: the VM additions MSI file for 13.552 is around 821KB in the Installer folder.

Looking at Summary tab in MSI properties

The second way is to use the Details screen, and use the ‘Choose Details’ Windows Explorer option to select the Subject field.

Customizing your Explorer view of MSI

Once you press enter, you can choose the Subject column. Make sure you use the ‘Move Up’ button to move this Subject next to the Name.

Customizing your Explorer view of MSI

Next, use your top notch keyboard skills Smile, use the Tab key, navigate to the details window and locate the file which has the subject of Virtual Machine Additions:

Viewing the MSI product name

Obtaining the ORCA tool

Next, we will edit that MSI file to remove the validations used to check for valid guest OS. To do this, we will use the ORCA utility which ships with the Platform SDK. Please see this KB article for more background. In my case I used the Windows 2003 Platform SDK for simplicity.

(note: you can also consider third party MSI editor like InstEd to edit the MSI).

Platform SDK installation to get the ORCA tool

 

  • Let’s say you installed the Platform SDK 2003 to a standalone folder like C:win2k3sdkBin
  • You will find the Orca.MSI file there
  • Create an ISO image containing this ORCA MSI file. You can use any freely available ISO image creator to do this.
Editing the MSI
  • Mount the ISO image on the guest OS.
  • Then install the Orca tool inside the guest OS.
  • Then load the VM Addition MSI file (which we identified previously) into the ORCA tool.
  • Using your top-notch keyboard skills, locate the CustomActions Node, and tab to the right pane. There locate the custom action called ‘Error_CheckForRunning…’. Delete it.

Using ORCA to delete the failing custom actions

  • Locate the other CustomAction called ‘CA_CheckForRunningIns…’. Delete it as well.
  • Similarly, locate the InstallExecuteSequence on the left pane, and delete the 2 entries there as well: ‘CA_CheckForRunningIns’ and ‘Error_CheckForRunning’.
  • Save the MSI (Ctrl-S).
Removing the old VM additions

Finally, we will replace that MSI file and then uninstall the old VM Additions.

Removing the VM additions

Finally… success!

Success! VM Additions removed!

 

Once this step is done (and you follow it with a reboot) you are ready to install the Hyper-V integration services!

I hope you found this workaround useful – please leave a comment if you found it saved you!