New SSDT

A new version of SQL Server Data Tools ( SSDT) was released yesterday. You can read more here: http://blogs.msdn.com/b/ssdt/archive/2014/03/25/sql-server-data-tools-for-sql-server-2014-is-available.aspx. You can get the new version here: http://msdn.microsoft.com/en-us/data/hh297027

One of the coolest features in this version is the support for custom code analysis (yay finally). This version supports SQL Server 2014 also.

SQL Server Integration Services (SSIS) – Error Code Reference

Trying to find the meaning of error codes I find in SSIS logs led me to seek the codes from Microsoft. For example, I had an error code in a log, -1071607685, and when initially searching for error codes I didn’t find anything with that code. So I pulled up the trusty calculator, converted that code to Hex, which ended up looking like FFFFFFFFC020907B. This looked a bit familiar so I started searching for 0xC020907B. This led to eventually finding the Microsoft page, which has both codes.

Here’s the link (until it dies): http://technet.microsoft.com/en-us/library/ms345164.aspx

SSDT January 2014 Released

SSDT January 2014 was released today, main benefits being it supports SQL Server 2012 Parallel Data Warehouse and Parallel Data Warehouse Appliance Update 1. What this release loses is the support for Visual Studio 2010. According to the link here, “We will no longer update SSDT for Visual Studio 2010. Projects and DACPACs are fully compatible across shells. Please download the toolset for VS2012 or VS2013 using the links above for continued updates.” What it gains, it seems, is support for Visual Studio 2013 and an interesting new reference as there is a link to “Download Visual Studio 2013 with SQL Server Tooling

I Like SQL Server Data Tools

SQL Server Data Tools makes life much easier for me. I now can easily version control all of my SQL objects. The only thing right now I don’t like is the fact that it cannot perform data comparisons or migrations yet.

It’s easier to work with database projects with SQL Server Data Tools compared to the database projects in Visual Studio 2010.

SQL Developer Data Modeler 3.1 Connection to SQL Server 2008 using Microsoft JDBC Driver

First, download the Microsoft JDBC driver.

Once extracted, I copied the sqljdbc4.jar file into the lib folder under the jdbc folder of the SQL Developer Data Modeler but you just need to know where the jar file is located.
Go to Tools > Preferences > Data Modeler > Third Party JDBC Drivers and click the plus to add the location of the jar file. Then click the OK button.
If on a Windows machine and you want to use integrated security, edit your system path to include the location of the sqljdbc_auth.dll file or copy the sqljdbc_auth.dll to an existing location in your system path, like Windows\System32.
Then click File > Import > Data Dictionary.
Click the Add button.
Click the JDBC tab
Choose Other ThirdParty Driver
For JDBC URL, type jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value] – or look here for details about how to form the URL.
For Driver Class, type com.microsoft.sqlserver.jdbc.SQLServerDriver
For Database Type, choose SQL Server.

Assuming your URL is correct, click the test button and you should get success.

SQL Server CTE Performance

I had to review a stored procedure to figure out why it was causing the web application to timeout when being passed certain parameters. The web application was using this one stored procedure to not only show a search results page (with 9 columns) but also to show the detail for a particular row (showing all 39 columns in a separate window). The stored procedure was using a CTE to handle/enable paging. Reviewing the code showed at the end of the CTE, the code had the following:

  1.  SELECT * FROM CTE

This version of the code would take up to 4 minutes to display a result based upon the parameters used.

The fix was to change

  1.  SELECT *

to

  1.  SELECT ColumnName

and this improved the performance dramatically. Where the original code took up to 4 minutes to display a result, this change had the results display instantly (well, under a second).

The lesson, only use

  1.  SELECT * FROM OBJECT

when absolutely necessary (and when is that?).