Using IIF instead of CASE

For years the primary means of expressing conditional logic in SQL Server has been the CASE statement, which allows for powerful set-based logic and I personally love.  However, sometimes you just have a quick two-way decision that you need to make in your data set or just with variables, and CASE syntax feels a bit overkill.  Especially if you also code in a programming language that gives you the IIF function.

Thank you SQL Server 2012.  The IIF has arrived.

Now you can (and I already have, extensively) deal with situations where if the Boolean answer to a formula is true, do this, else do that using IIF.  Here is a quick, simple example.

Declare @Value1 Int = 1,

@Value2 Int = 2

Print '@Value1=' + Convert(varchar(1),@Value1)

Print '@Value2=' + Convert(varchar(1),@Value2)

Print IIF(@Value1 = @Value2,'They Equal','They Do Not Equal')

Set @Value2 = 1

Print '@Value1=' + Convert(varchar(1),@Value1)

Print '@Value2=' + Convert(varchar(1),@Value2)

Print IIF(@Value1 = @Value2,'They Equal','They Do Not Equal')

This is not revolutionary, but it does make for a nice shortcut in true/false scenarios, and it is another step toward shared syntax with external programs.  A small but good addition to SQL 2012.

Until next time, may well defined requirements be at your back and realistic timelines lie before you.

SQL Saturday #112 - Birmingham 2012

Today was another great SQL Saturday in Birmingham.  A day packed full of meeting other SQL professionals, good food, and great sessions of information.  If you have never been to a SQL Saturday and you are reading this blog, you should go.  Here is a quick run down of the sessions that I attended today.

Time Saving Tips & Tricks for SSMS 2012 - Aaron Nelson

What's New in SSIS 2012 - Christopher Price

SQL Server 2012: What's New - Bob Cheatham

Data Quality Services 2012 - Chris Price

Introduction to FileTables - Sven Aelterman

Windowing Functions: The Reason to Upgrade to 2012 - Kevin Boles

As you can tell, it was a great day for learning more about SQL 2012.  

http://www.sqlsaturday.com/default.aspx

Check the site and find a session near you.  You will not regret it.

SQL Saturday #81 Birmingham

Today I had the great pleasure of meeting other SQL Server professionals from all over the Southeast, and listening to some great presentations on everything from design and statistics to the upcoming SQL "Denali".

We had five different sessions to choose from in each of the six time slots. My choices today were

Database Design
SQL "Denali" High Availability
SQL Statistics
Building a Professional Development Plan
Table Partitioning
Advanced DW Scenarios in SSIS

It was not just informative. These events are about networking and being a part of a community centered around the SQL Server product. Plus, on top of great sessions and door-prizes, we were provided with coffee, doughnuts, sodas, and a nice box lunch. If you have not been to one, I highly recommend it.

Upcoming SQL Saturday in Birmingham, AL

It is time for year three of the incredible FREE training day on SQL Server in Birmingham, AL.  This event has grown each year across the country, and this year will be no exception.  If you are anywhere in the Southeast region, I recommend you make the trip to Birmingham on July 30th.  Otherwise, please go to the main site and find a SQL Saturday closer to you.  You owe it to yourself to take advantage of these free events to continue your professional development and take advantage of networking with other SQL Server professionals.

http://sqlsaturday.com/81/eventhome.aspx

Using Extended Properties for Documentation

This is a nice article on using extended properties to document your database. Much easier than maintaining a Word document.

Create a SQL Server Data Dictionary in Seconds using Extended Properties

SQL Saturday Free Training

I just attended my first free SQL Saturday training day (#29 in Birmingham, AL) and it was an amazing day. The training was of the highest quality and as I went through the day with free coffee/soda and free lunch provided, I could not believe all of the value that was being provided for free. Of course it is not free, just free to we the trainees.

A big thank you to all the sponsors and the volunteers that made the day possible.

I want to encourage all of you reading this to go to the SQL Saturday website and find SQL Saturday's in your region. There were many people that I met in sessions who were from Tennessee and Georgia, and some had been to as many as three SQL Saturday's in different locations. It is worth a little driving time.

The hardest part was choosing which sessions to attend, since it was pretty common to have two or more sessions that I was interested in going at the same time. Here is the list that I finally went with.

Parallel Query Execution Dive Deep presented by Kevin Boles.
Database Design Patterns presented by Louis Davidson.
Tuna Helper for SQL Server DBAs presented by Janis Griffin.
Tricks & Tips for Writing Better Queries presented by Joe Webb.
SQL Server Locking and Blocking Made Simple presented by Joe Webb.

In each session, I already knew some of the material and I learned some new things, and most importantly gained some insight from people who approached problems from different angles. SQL Server is so robust, there are hundreds of ways to carve the turkey.

I hope if you have never attended SQL Saturday that you will make it a priority. All it costs you is one Saturday.

Conditionally Blocking Scheduled Reporting

As is true for many DBAs, I have developed a custom set of SSIS packages along with a database that manages scheduled Reporting Services reports. It delivers to a mixture of email recipients and FTP locations after generating the output PDFs and Excel results.

Then came the inevitable weekend of trouble. The set of SQL Server 2005 databases, which provide the data for the reports, failed to load due to trouble with the source legacy systems. However, since the data in the SQL Server databases still contained yesterday's data, the report schedule had no problem completing the scheduled reporting.

Therein lay the problem. I did not want any reports to generate, or worse yet email to recipients, if the recent day of data was not included.

The solution is fairly simple. The SSIS packages that load the SQL Server databases were logging to a table, and included a special status column. When each database was finished loading and had completed any subsequent updates required, a log entry with a status of DONE and UPDATED were entered. I just created a stored procedure to scan the log for databases that were scheduled to be loaded, filtered by UPDATED, and with a log time that fit into the correct date/time range for the current day's data load.

If the stored procedure found any records that did not meet the above criteria, it returned a result reporting accordingly. Then I just added a call to this stored procedure in my primary scheduled reporting stored procedure, where the list of scheduled reports to process is generated. If the data load check reported problems, I prevent the scheduled reports from processing. Then once the problem with the data load is resolved and all data is available, all I have to do it run the SQL Agent job that runs the scheduled reports and this time it will succeed.

I'm always a fan of a little more work in the back end to make a little less work for the routine. Not to mention, you don't have to answer Fred's email asking, "Why did I get two emails of the same report, and which one is right?"

Domain Move Part 2

After the first attempt at moving a SQL 2005 Server from one domain to another, I learned several lessons to help me make the permanent transition to the new domain. Here are a few of the key things I checked and/or changed.

- Verify that the new DNS server has entries for the short names of all servers that SQL Server needs to access. I found that several servers would resolve to [servername].[full domain name] but not to simply [servername]. I had to request CNAME entries for all the resources that required (due to extensive existing code) the ability to access the server through the short name.
- Make sure all services are running under local accounts.
- Make sure the Reporting Services execution account is running under a local account.
- If any objects are owned by domain level accounts, change ownership to local accounts.
- If any SQL Agent Jobs are scheduled to run under domain accounts, change them to local accounts.
- Check SSIS packages for any domain account ownership and change to local account.

I still had a few issues, but for the most part I was able to change one SQL 2005 Server from one domain to another and add a SQL 2005 and a SQL 2000 server to the domain from a workgroup with little trouble after checking the previous list and making changes as needed.

If you have any more tips, please feel free to leave comments to flesh out the topic.

FILESTREAM Article on SQLServerCentral.com

One week ago, an article I wrote on FILESTREAM in SQL 2008 was published on SQLServerCentral.com. This was the first time I had written for a professional publication, online or in print, and I want to thank Steve Jones for giving me the opportunity.

In case you missed it, here is a link to my article.
http://www.sqlservercentral.com/articles/FILESTREAM/67668/

I highly recommend that those of you with any inclination toward documentation or training start writing articles. It not only forces you to dig deeper on a subject than you may have done otherwise, but it also helps to establish you as a professional in the field. I know we all have busy lives, but I think it is worth it. Only time will tell.

And speaking of Steve Jones, check out his recent blog entry on Technical Article Writing where he lists several good places to submit your work.

SSIS Packages fail on ODBC connections after .NET Framework 3.5 SP1 or 2.0 SP2

If you work with ODBC connections through third-party ODBC drivers such as MySQL, you may have already experienced the pain of this Windows Critical Update. This is my tale of woe.

I was innocently applying critical Windows Updates to one of my SQL Servers, one is the .NET Framework 3.5 SP1. I read the details and saw that it also includes SP2 for both .NET 2.0 and 3.0, but still it is a critical update. As always, I there may be some bumps in the road but I figure I will just work through them. Updates applied. Basic database and reporting all test fine. So far so good.

The next morning, all of my SSIS packages that pull data through ODBC connections have failed, and as a result all of my subsequent scheduled reporting has also failed. That is what I call having Monday on Wednesday.

I know what happened due to my daily email report of all jobs from the last 24 hours, sorted by the errors first. In checking my SSIS log files, I find this error:

OnError,[Server],[UserAccount],[TableName],{GUID1},{GUID2},9/1/2009 11:15:48
PM,9/1/2009 11:15:48 PM,-1071607767,0x,SSIS Error Code
DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "DataReader Source"
(31)" failed because error code 0x80131937 occurred, and the error row
disposition on "output column "[ColumnName]" (1848)" specifies failure on error.
An error occurred on the specified object of the specified component.
There may be error messages posted before this with more information about the
failure.

After some research, I find that this is a known issue on an msdn blog. It seems that .NET Framework 2.0 SP2 changes the way that the OdbcDataReader checks for Nulls, resulting in compatibility issues with some ODBC drivers. It impacted both the MySQL and CVIEW (for ISAM connections) ODBC drivers that I was using to pull in all my data.

As explained in the excellent blog post, the preferred option is to add error output to your SSIS packages, thereby handling the bogus errors. Unfortunately I have six or seven packages with twenty or more DataReaders in each package, so it would not be a quick fix. The blog mentions option #2 is to roll back your updates, which for me was the only option. I had production people waiting.

Due to the 3.5 constraints, I had to remove .Net 3.5 SP1, then .Net 3.0 SP2, and finally .Net 2.0 SP2. Then, because 3.0 and 2.0 updates are slipstreamed into the 3.5 SP1 update, at that point I had zero .Net frameworks installed. I had to reinstall .Net 2.0 and SP1, which is all I need for my SQL 2005 and related functionality. It took roughly 20-30 minutes to uninstall and reinstall along with reboots, not including any download requirements.

Afterward, I started playing with the website and realized the IIS default page was not even available. I looked at properties in the default website, looked at the ASP.NET tab... bingo, the ASP.NET version is set to... blank. I click the drop-down and see .Net 2.0 is available, but knowing that the scope of the settings goes far beyond this setting, I went back to the command line.

I knew I needed to run regiis, but I don't do this frequently so I searched, found a refresher, and ran the following command:

C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\Aspnet_Regiis -i

Because I am on a 64-bit server, I was wroking in the Frameword64 subdirectory, but if you are on 32-bit, it is the same but you browse to the Framework subdirectory.

Now my baseline ASP.NET is configured in IIS. I browse to the "/reportserver" directory in IE8 and get a (403) Forbidden error. That's an improvement.

I looked around in various places at various settings for a bit, just trying to help me think. In the Reporting Services Configuration Manager, it hit me that I had just made a mess of IIS and in the process the virtual directories had been through the ringer. So under Report Server Virtual Directory and Report Manager Virtual Directory, I checked the "Apply default settings" checkbox and applied the changes.

Boom, back in business. Reports were up and running and I was able to run my SSIS package to generate all my scheduled reports. Then I remembered one other little ASP.NET application that I wrote for managing the report schedules. Tried to pull it up in the browser and it didn't open.

After looking at IIS settings for a while, I finally found the problem. So I right-click on the Virtual Directory, go to Properties-->Documents tab and default.aspx is not in the list. In the process of resets, apparently the default content page list had been reset on my virtual directory. I added default.aspx to the list and boom, everything is back up and running.

I hope that at some point Microsoft makes allowances for this issue in their .Net updates but in the meantime, doing without .Net 2.0 SP2 is fine by me.

Preparing To Join A Domain and Change Domains

Currently I am managing three production SQL Servers:
SQL 2000 on Windows 2003 with Workgroup security.
SQL 2005 on Windows 2003 with Workgroup security.
SQL 2005 on Windows 2003 with Domain/AD security.

We just spent the weekend moving all the PC's to a brand new Domain controller with a new Active Directory. The servers are next.

I have been surprised at how little is on the web about joining an existing SQL Server installation to a domain, presumably because most people start on one or never move to one. There is a little more on changing domains, but it still seems a little sketchy.

So sometime in the next week, all three servers will be members of the new domain. These servers use IIS, heavy Reporting Services, heavy Integration Services, and extensive cross server connectivity.

Hopefully I will have a good report, but either way I will let you know the good, bad, and ugly.

Books on SQL 2008

From what I have seen there are are two basic types of tech learners: the book learners and the "learn as you go" crowd. I lean toward the learn as you go crowd in that I enjoy jumping in with both feet and playing around with a product, then reading about it later. The playing around gives me more context for the books.

Anyway, I picked up a few books to start reading up on SQL 2008 to augment my playing around. At the office I have Microsoft SQL Server 2008 Management and Administration since initially management and administration will be my focus for work. I have barely started it, but the chapter overviews look promising.

At home I have two more books. I started reading Microsoft SQL Server 2008 Internals because from what I have read online, it is an excellent book for really understanding the guts of how SQL Server works. I'm only in Chapter 1, but my quick overview of the book looks promising.

The third book, which I have not started, is Microsoft SQL Server 2008-Database Development which is preparation for the 70-433 MCTS exam. I have never received a Microsoft certification, but I think it is time that I start down that road. Due to my current title of Database Developer this seemed like a good MCTS to start with. Since this one is the least important for me ramping up the installation, setup, and maintenance of SQL 2008, it will probably be the last book I read.

I'll post my impressions of the books as I finish them. And if you have any good book recommendations, feel free to post them in the comments.

Fixing a Reporting Services Report that belongs to someone else

It is a common occurance in a shop with multiple report developers and without version control. Someone goes on vacation, you get a call that the report is giving an error, and then what? You don't have the local Visual Studio project or rdl file to edit. No Source Safe to pull the file from. What do you do?

Login to your reporting services host at http:\\[hostname]\Reports to get a list of folders and reports. Navigate the directory structure to find the offending report. Once you find it, you need to copy it to the local PC.

If the "Show Details" button appears on the right hand side of the sub-toolbar, click it. When it reads "Hide Details" you are ready to move on. Now click on the icon in the "Edit" column (just after the check-box). Now under the "Report Definition" section you will see an "Edit" link. When you click the "Edit" link, you will get a save dialog box and can save the report rdl file to your PC. My recomendation is to save it first to your desktop.

Then open SQL Server Business Intelligence Studio (i.e. Visual Studio) and either open a project that you use for researching that person's reports or start a new project to use for researching other people's reports. In the Solution Explorer, right-click on the Reports folder and select Add-->Existing Item...

You navigate to your Desktop (or wherever you saved the rdl) and select the rdl you just downloaded. Now it is a part of your project and you can look at data sources, evaluate issues, and exercise your genius to solve the problem.

And if the solution does not lie in the data sources, you can always setup the Target report folder, server, etc. to post changes to the actual rdl file. If you do this, I recommend holding onto that copy of the rdl that is still sitting on your desktop because it makes for a good backup in case you make things worse and need to back track.

Introduction

My name is Hugh Thomas and I finally decided to cut my teeth on my first professional blog about SQL Server. I have been developing databases for about fifteen years going back to Access version 2.0. In 2000 I started developing with SQL 2000 and I've never looked back.

As a database engine and feature-rich tool set, I consider the SQL Server platform to be the best bang for your buck and the easiest platform to have multiple tiers of support, from the very basic support user up to the Super DBA.

I have been using SQL 2005 in production since three months after it was released and am currently testing SQL 2008 for future development. Although my primary roll is that of a Database Developer, I have played the roll of our primary (well, really our only) DBA since 2000. That means I have installed SQL 2000-2008 multiple times, dealt with the configuration side of the server and services, and banged my head against the wall when things went South.

I plan to blog about what I learn, mistakes I make, and link to interesting resources that I find along the way. If my successes and failures can help someone else, all the better.

Until next time,

Hugh