SQL Server 2012

You are currently browsing articles tagged SQL Server 2012.

The other day I needed to be able to study the SQL Server 2012 documentation, aka Books Online or BOL, to prepare for an exam while sitting on the train. So I was looking for the latest BOL download but that search wasn’t very successful. It took me a while to realize that what I was doing was no longer valid! Microsoft is no longer distributing the Books Online through the Downloads site!

Well, not entirely true, read on for details.

Because it took me some time to realize what was going on I decided to write a quick post on how to make the BOL 2012 available locally. Basically you’ve got two options.  Let’s start with the first one.

Method 1: Help Library Manager

The first – newly-introduced in SQL Server 2012 – method uses something called Help Library Manager.

This is a generic Microsoft product documentation management system, not exclusively for SQL Server.

Here are the steps to follow to get the SQL Server 2012 docs installed locally.  This method assumes that you’ve already got SQL Server 2012 installed and that your PC is connected to the internet.

To start, click the Manage Help Settings link located in the Documentation & Community subgroup of your SQL Server 2012 start menu shortcut group.

Manage Help Settings

This will open up the Help Library Manager mentioned earlier:

Help Library Manager: installing online content

Click the Install content from online link.  The Help Library Manager starts by fetching the list of available books.  Once finished, locate the SQL Server 2012 content:

Installing the SQL Server 2012 books

Click the Add link next to any book you’d like to install locally.  The link will change to Cancel as shown in following screenshot:

Selecting the books to be installed locally

When you’ve selected all books you’re interested in, click the Update button.  The Library Manager will now start downloading all the content you’ve requested.

Help Library Manager is downloading the requested packages

Now go and get a coffee.  When you’re back, you should be able to click the Finish button and the Books Online will be available locally:

SQL Server 2012 Books Online ready to be used

Method 2: Download from Download Center

It turns out Microsoft took into account that not all computers are able to access the internet, which is a requirement for method 1.  Microsoft does still have something available for download through the Download Center.  However, when I came across the link I hadn’t realized that this was actually the BOL download.  The reason that I didn’t realize this was because the stand-alone BOL download is now called Product Documentation for Microsoft SQL Server 2012 for firewall and proxy restricted environments. I thought this was documentation regarding firewall and other network-related issues but this is actually the full Books Online download!

So if you’re interested in the old-skool BOL download, check this out: [Download Center] Product Documentation for Microsoft SQL Server 2012 for firewall and proxy restricted environments

Have fun!


Add or Remove Product Documentation for SQL Server


Tags: , ,

Once again I’ve been wasting some time because of a silly bug.  This time it was due to the OLE DB Source component and the way it works with parameters.  If you are in a situation where you know your query is working fine and yet no records are going down the data flow, here’s a possible solution!

Disclaimer: this issue exists up until SQL Server 2008 R2.  Read on for details!

Update: after being advised to do so by several people, including Jamie Thomson, I’ve filed a bug at MS Connect: SSIS OLE DB Source incorrectly returns zero records in combination with parameter and comment

The Situation

I had a Data Flow with an OLE DB Source that uses one parameter, for instance:

select ProductAlternateKey, EnglishProductName
from dbo.DimProduct
--some really smart comment goes here
where Color = ?

I knew the query was working fine because when executed through SSMS and with the question mark replaced with ‘blue’, it would return 28 rows:

28 records in Management Studio

But when executed in BIDS, through either Execute Package or Execute Task, it would return zero records:

Zero records, zilch, nada, niente, none at all!

So I thought something must be going wrong with the package variable that gets passed into the source parameter, somehow.  I’m not going into details on what I tried out in my attempt to get this working, but I can tell you that I started to get really irritated.  My colleague Koen Verbeeck (b|t) can confirm this because I called him over to my desk to help me think! (thanks btw!) Smile

After some further tinkering with the data flow, we had our smart moment of the day and decided to launch SQL Server Profiler to see what BIDS was sending to the server!  I’m not sure if you’re aware of this but BIDS is doing some metadata-related stuff when preparing queries.  As far as I can tell, it also tries to determine the parameter type by running the following query:

 set fmtonly on select Color from  dbo.DimProduct
--some really smart comment goes here where 1=2 set fmtonly off

When creating this statement, it seems to use the whole FROM clause of the original query, including any trailing comments.  It combines that with a SELECT statement that contains the field that gets filtered and it appends " where 1=2 set fmtonly off".

But alas, apparently it’s not aware that lines can be commented out by using a double dash.  So part of its generated statement is commented out.  What it should have done is used some CRLFs, especially in front of the WHERE clause.  But it didn’t.

So, as a result of that, FMTONLY remains on while the SELECT statement gets executed, resulting in zero records!

For those unfamiliar with the FMTONLY setting:

Returns only metadata to the client. Can be used to test the format of the response without actually running the query.

And I can actually confirm what I’m stating here by changing the query to the following:

set fmtonly off;
select ProductAlternateKey, EnglishProductName
from dbo.DimProduct
--some really smart comment goes here
where Color = ?

28 records down the pipe!

We've got data!

But this hack is a little too dirty to put in production.  So what else can we do?  Well, use block-style comments instead and we won’t face the issue!

select ProductAlternateKey, EnglishProductName
from dbo.DimProduct
/* some even smarter comment goes here */
where Color = ?

So, as I mentioned at the start of the post, this behavior can be reproduced using SSIS versions prior to 2012.  What about 2012 then?  Here’s the result of the Data Flow using the first query mentioned above:

SSIS 2012: we've got data, even with the "faulty" query!

Alright, that works better!  Now let’s use Profiler to check what’s going on here.  This is the first statement that gets executed:

exec [sys].sp_describe_undeclared_parameters N'select ProductAlternateKey, EnglishProductName
from dbo.DimProduct
--some really smart comment goes here
where Color = @P1'

Further down, I also see this one:

exec [sys].sp_describe_first_result_set N'select ProductAlternateKey, EnglishProductName
from dbo.DimProduct
--some really smart comment goes here
where Color = @P1',N'@P1 nvarchar(15)',1

It is using an entirely different approach, no longer using the FMTONLY setting!  Hang on, this rings a bell!  Look what the BOL page for SET FMTONLY (2012 version) specifies:

Do not use this feature. This feature has been replaced by sp_describe_first_result_set (Transact-SQL), sp_describe_undeclared_parameters (Transact-SQL), sys.dm_exec_describe_first_result_set (Transact-SQL), and sys.dm_exec_describe_first_result_set_for_object (Transact-SQL).

Cool stuff!


If you’re not on SQL Server 2012 yet, be careful with comments in OLE DB Sources in the SSIS Data Flow!  Ow, and get the SQL Server Profiler off its dusty shelf now and then!

Have fun!



Tags: , , , ,

The other day I needed a counter in my SSIS Control Flow.  Using the Foreach Loop container I was looping over a bunch of files and needed to count the number of files that got imported successfully.  The counter had to comply with the following requirements:

  • Easy to implement
  • Fast

Let’s test some different methods on getting this implemented. 

Counting Possibilities

Two of the methods are possible as of SSIS 2005 while the third one is new since SSIS 2012. So yeah, the screenshots are taken using SQL Server 2012 RTM.

Setting The Scene

We’ll start from a new package and create a package variable of type Int32.  This variable will keep track of the count.

To be able to performance test the different possibilities, I’m using a For Loop container.  This loop is using another package variable called loop, type Int32, and performs the loop 10,000 times.  In case you don’t know how to set up such a loop, check out this screenshot:

Using a For Loop container to test task performance

So my package contains the following two variables:

Two package variables of type Int32

The cnt variable is the one to actually hold the count.

To test performance I’ll be using the command-line dtexec utility so that the debugging overhead does not mess up the numbers.   I’ve also executed each method at least three times to ensure a certain result was not “by accident”.

Using a Script Task

The most logical component that came to mind was a Script Task.

Using a couple lines of code, C# in this case, the counter value can be incremented with one:

int cnt = (int)Dts.Variables["cnt"].Value;
Dts.Variables["cnt"].Value = ++cnt;

The above code assumes that the cnt variable has been put in the ReadWriteVariables property:

The User::cnt variable is in the ReadWriteVariables for the script to use

Here’s what that looks like in the Control Flow:

For Loop container with a Script Task

So how fast does this execute?

Looping 10,000 times over the Script Task takes 8 seconds

About eight seconds, that’s acceptable.

However, what I don’t like about this method is how long it takes to implement.  I mean, it takes more than just a couple of seconds, right?  And the fact that you actually need to use custom .NET code to perform such a trivial task as adding one to a number.  Using .NET code is good for complex situations, when there’s no other option.  But the risk of a bug is always larger, imagine I wrote cnt++ instead of ++cnt, what do you think the results would be? (Hint: my laptop would crash before the counter reaches 10,000).

On to another option then!

Using a Execute SQL Task

Instead of resorting to .NET code, increasing a number by one is easy to achieve using a simple T-SQL statement, right?  So I thought, let’s try a Execute SQL Task!

Here’s the query:

select ? + 1 as cnt

What does the task look like?

Using Execute SQL Task to increase a package variable

ResultSet has been set to Single row.

The Parameter Mapping page has got the User::cnt variable specified:

Specifying the User::cnt package variable in the Parameter Mapping page

And the Result Set page has got the single value from the single row mapped to the User::cnt package variable:

Mapping the cnt result to the User::cnt variable

What do you say, easier to implement than the Script method?  I do think so!

This method has one limitation though: it needs a Connection Manager connecting to a SQL Server database.  However, in most ETL packages you’ll probably have that present already.  What I was a bit worried about though is the overhead of connecting to the server, how much will it be?

Let’s find out!

The Execute SQL Task needs almost a minute to increase the counter

That’s right, using the Execute SQL Task to increment the variable 10,000 times takes about a minute.  On my laptop.  Connecting to the tempdb on my local instance.  When testing this over a network, it even resulted in timings over four minutes.  So this solution is really unacceptable in my opinion.

However, we can give it one more try.  A Connection Manager has got a property called RetainSameConnection.  By default this is set to False which means that our test above has opened and closed ten thousand connections to my local tempdb.  Oh my, of course that takes a while!

Setting it to True gives us the following result:

Setting RetainSameConnection to True speeds up the process by three

About twenty seconds, which is about one third of the previous result.  That surely is better.  And what’s perhaps even more interesting is that a similar result is achieved when connecting to a database over the network: from over four minutes down to twenty seconds.  So yeah, this would work for me.

Sidenote: for other interesting use of the RetainSameConnection property, check out this post regarding transactions by Matt Masson.

But we’re not stopping just yet.  As of SQL Server 2012, we’ve got a third possibility!

Using an Expression Task (SQL2012!)

Instead of resorting to custom .NET code or (ab)using the Execute SQL Task, in SSIS 2012 we’ve got a new task: the Expression Task.

The new Expression Task in a For Loop ContainerThe Expression Task builds and evaluates SSIS expressions, nifty!

As you can read in the SSIS Toolbox, the Expression Task builds and evaluates SSIS Expressions that set variable values at runtime.  That surely sounds exactly like what we’d need, doesn’t it?

So how does that work?

Using the Expression Task to increase the counter

Really easy to set up, we just specify that the User::cnt variable should be set to itself plus one.  When put in a For Container, we’ve got a counter!

But how does it perform?

The Expression Task is the easiest and the fastest method, mission accomplished!

About seven seconds, which is even slightly faster than the Script Task method!

With that we’ve found the method that complies with both requirements: easy to implement and performs fast!  Now how am I going to convince my clients to upgrade to 2012, hmm, need to think a bit…


We found out that the new Expression Task is a very useful task for its purpose.  In our case we used it to create a counter.

If you’re not on SQL Server 2012 yet, better stick to either Script Task or Execute SQL Task with RetainSameConnection set to True.

Have fun!



Tags: , ,

If you have ever had the chance to see a presentation by Rafal Lukawiecki, I’m sure you’ll agree that he surely knows how to deliver his stuff, and on top of that he knows what he’s talking about.

In other words: Rafal is once more in our small country!  Next Thursday he’ll be talking about the present and future of the Microsoft Business Intelligence stack.

Location: Utopolis Mechelen, Spuibeekstraat 5, Mechelen, Belgium.

Date/Time: Thursday April 26th, 0900 – 1700.

Check out the Belgian SQLUG site for more details.

Ow, and it’s a full-day free event, so what are you waiting for?  If you’re into BI, you’d better have a real good excuse to miss this opportunity!  Register now!

See you there!



Tags: ,

You may or may not already have heard about it, but the next version of our favorite database, SQL Server 2012, ships with a client tool called SQL Server Data Tools (SSDT).  Just like the BIDS was actually Visual Studio 2008 (or 2005 if you’re long enough in the business) with BI-related project templates, SSDT is Visual Studio 2010.  But that’s not all: it contains additional functionality!  Do you remember the database projects you could create using the “data dude”, or officially known as Visual Studio 2008 with the GDR2 add-on?  Well, this is version-next of the data dude.

Let’s find out how such a database project can be created!

Getting Started With SSDT

Obviously the first step is installing SQL Server 2012 and making sure to select SQL Server Data Tools in the Feature Selection page:

SQL Server 2012 Feature Selection with the SQL Server Data Tools checked

With that up and running, launch the SQL Server Data Tools from the Microsoft SQL Server 2012 folder in the Start menu and go to File > New Project.

New Project - SQL Server Data Tools

One of the template categories is called SQL Server.  Under that you’ll find a template called SQL Server Data Tools – Database Projects (Web Install).

Erm, hang on, what do you mean, “web install”?  Does that mean it’s not installed yet?  The description on the right also gives some clue to what’s going on: “provides instructions for installing SQL Server Data Tools – Database Projects from the web”.  Okay, let’s get on with it then!

Click the OK button to proceed to the installer.

Installing the SSDT Database Projects template

SQL Server Data Tools - Database Projects (Web Install)

I think the message in that window above is a bit misleading.  It says that “Database Projects” is required, but isn’t that what we’re going to install now?  Sounds like chicken and egg to me!  Let’s just ignore the message then and click the Install button.

Data Developer Center - Download SQL Server Data Tools

We’re now presented with a web page in Internet Explorer.  In this Data Developer Center, click the blue Download SQL Server Data Tools link.

Next, when presented with the following pop-up, click Allow:

Do you want to allow this website to open a program on your computer? Yes we do!

The next window is the Web Platform Installer 3.0 that wants to install the Microsoft SQL Server Data Tools.  Click the Install button to get to the next step.

Web Platform Installer 3.0 - 1 items to be installed

As usual with any installer from Microsoft, we need to accept the license terms so click the Accept button.

Web Platform Installation - Accept License Terms

Finally, the installation begins!

Web Platform Installation - Progress Bar

To keep us busy, we’re presented with another pop-up with a progress bar:


Woah!  Is this thing installing SQL Server 2012 Express LocalDB like it says in the message?  I didn’t ask for that!  MS people, as I already have a version 2012 DB engine running on my machine, please make this optional…

Just a little later we’re presented with this:

Web Platform Installation - Congratulations!

Clicking the Finish button gives us yet one more pop-up:

Web Platform Installer 3.0 - Spotlight

The Web Platform Installer presents us with a list of applications we can install, including Microsoft SQL Server Data Tools.  Oh my, I thought we just finished installing it?  Let’s not be silly and click the Exit button

To conclude, we need to restart Visual Studio 2010, or aka SSDT.

Really Getting Started With SSDT

With everything up and running smoothly this time, open SSDT once more and in File > New Project you’ll now find the SQL Server Database Project under the SQL Server collection:

New Project - SQL Server Database Project

If you also have Visual Studio 2010 installed in Premium or Ultimate edition, you should take care to open up the correct SQL Server template collection.  The reason for that is because the data dude projects are included in those versions of Visual Studio, as shown in the screenshot below:

The SQL Server templates in Visual Studio 2010

So the templates under Database > SQL Server are not what you’re looking for!

That’s it for now, I’ll demonstrate my favorite SSDT feature in an upcoming post!

Have fun!



SSDT Main page on Data Developer Center

SQL Server Data Tools Team Blog


Tags: , ,

« Older entries

© 2008-2019 BI: Beer Intelligence? All Rights Reserved