Month / November 2012

by Arvind Shyamsundar

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!

by Arvind Shyamsundar

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.