There are two issues with what that vendor wanted. The first is that SQL Server only supports Current Version + 2. Meaning SQL Server 2012 supports SQL Server 2005 and SQL Server 2008 (and 2008R2). SQL Server 2008 and 2008R2 have the same major release number (10). So what this vendor was asking for is not possible because compatibility levels in SQL Server 2012 only go back to 90 (SQL 2005).

The second (and in my opinion the bigger) issue is why does the vendor want the compatibility level set back at all?

I’ve seen many people who thing that if you set the compatibility level back to a previous version, then everything that worked in that previous version will work now. For example, they had a SQL 2005 Instance which they migrated to SQL 2012 and they think that setting the compatibility level at 90 means all the discontinued commands will go back to working.

This is completely incorrect and a dangerous trap! Compatibility levels are meant to partially run the database in the version you selected while you troubleshoot, fix and then change the compatibility level to the current version.

Doing a search on the internet, I cannot find anything directly from Microsoft about what the compatibility levels really do. However, I’ve seen and experienced firsthand that they do not allow discontinued code to suddenly work.

For example, in SQL 2005 you can use a command called “RAISEERROR” (note the use of two e’s in the middle). If you try that command on a database in SQL 2012 (no matter what compatibility level you have it set at), it will not work because it was discontinued.

What I’m trying to say is this, you cannot skip testing and removing discontinued code by using compatibility levels. You must test, removed discontinued code and work on migrate deprecated code to a supported solution. It like the old saying “If something appears too good to be true, it probably is too good to be true”.

This was happening on a table that was setup using 3rd party cross database platform replication software. The software used triggers to capture the changes to the table to send them over to an Oracle table and a DB2 table. The issue was caused by the trigger inserting records into another table that also had an identity column and that was being returned instead of the one from the original insert.

I used to also have a debate with a fellow DBA about @@ROWCOUNT. I use it all the time and haven’t had any issues. But I also wanted to know if triggers could affect it.
Here is some sample code that illustrates how triggers affect @@IDENTITY, @@ROWCOUNT and also demonstrates SCOPE_IDENTITY().

Here is the output from the above code:

So we can see that @@ROWCOUNT is not affected by the trigger. However we also see that @@IDENTITY can be affected by triggers and the way to get around it is to use SCOPE_IDENTITY() instead.

One place that DBAs use SELECT * is when they create views. The idea is that you do actually want every column available to the view because the query that hits that view should limit which columns it wants returned.

This works, but there is one big issue with it that most people learn the hard way. If the schema of the source table that the view is selecting from changes, the view will NOT automatically update to include those changes.

For example, let’s create a simple table with some data and a view over it (because we are using really simple code, I’m not going format visually the way I normally do).

I did break the rule of no SELECT * again but it’s to demonstrate the point of this article.
So far everything is being returned as we expect it. Now let’s change the schema of our table and see what happens.

Notice that col2 is missing even though the view is using SELECT *. Let’s see what happens if we try to use the view to insert a new record with data in col2.

To fix the “broken” view, we have to rebuild it.

Now let’s try the insert statement again and then the select statement.

And of course, let’s clean up after ourselves!

Fear not, you can add this keyboard short-cut!

1. In SSMS open the “Tools” menu and choose “Options…”.
2. On the left, expand “Environment”, then expand “Keyboard” and then click on “Keyboard”.
3. Choose “Window.ShowResultsPane” (you can type that into the “Show commands containing:” field to find it faster. Choose “SQL Query Editor” from the “Use new shortcut in:” drop down list.
4. Click into the “Press shortcut keys:” field and press Ctrl and R at the same time (the field should update to show “Ctrl +R”.
5. Review the “Shortcut currently used by:” dropdown list to ensure there are no other short cuts using Ctrl +R for SQL Query Editor.
6. Click the Assign button and then the OK button.

And presto, the Ctrl+R functionality to hide or display the query results/messages pane in SSMS!

I used to think that I had to either use my mouse to move it over or start playing with the position coordinates in the properties of the object.

NOT ANYMORE!

To make micro adjustments using the Arrow Keys, simply hold down the Ctrl Key at the same time.

It may seem odd to post such a simple and short article, but this little tip saves me a fair amount of time and swearing at my computer. I hope it does the same for you as well!

Before we go any further, if you are ever unsure about SQL Server Licensing, contact Microsoft (1-800-426-9400 open Monday through Friday 6:00 AM PST to 6:00 PM PST). What is valid for me at this point in time may not be valid for you or even for myself at a different point in time.

The Back Story

One of my most recent “egg on my face” moments came from a discussion on twitter about SQL Server licensing in non-production environments. A question came in on the #sqlhelp hashtag asking about a situation where the QA group at their company installed Enterprise Edition and the person wanted to know if they had to uninstall it and reinstall Developer Edition to be compliant with licensing.

At a previous company that I worked for, I was always told by the Database Manager that if the system was non-production, licensing was free. So I chimed into the twitter conversation and was then challenged by Robert Davis (T|B) that it should be verified by a licensing expert. He challenged it based on that Enterprise Agreements can override regular licensing. My previous employer had an Enterprise Agreement and my current one does not.

1st Assumption

My first assumption was that the licensing rules that applied to my previous employer would carry over to my current employer. The problem with that assumption is something I already pointed out, one had an Enterprise Agreement and one did not. So I really was not comparing apples to apples.

Now I too had a stake in finding the correct answer this licensing question. So I offered to the conversation to contact the licensing expert at the vendor from which my current employer purchases their licenses from. After a couple of e-mails back and forth we had a mostly clear answer that because the production workflows never touch the non-production SQL Instances, you were indeed covered by the production licenses and did not need extra ones. This turned out to NOT be true.

2nd Assumption

My second assumption was that someone who didn’t work directly from Microsoft can 100% be trusted with licensing questions. SQL Server Licensing is well known for being complex and very difficult to understand. I do not blame or think less of our software vendor for coming back with the wrong answer because I’m sure my preconceived thoughts on what the right answer is swayed them.

I replied back on the twitter conversation with what I talked about with my vendor. Fortunately Steinar Anderson (T|B) challenged me again to go straight to Microsoft to verify what the correct answer is.

The truth (for my situation)

I called Microsoft’s Licensing and was able to get ahold of someone with a couple minutes and they told me that unless you have an Enterprise Agreement, Microsoft does not look at production and non-production differently. You have to license each one. For the non-production SQL Instances you can use Developer Edition but must have a license of it purchased for each person who will use any of the Developer Edition SQL Instances.

So now I will leave you with two of the favorite DBA sayings about Licensing: “It depends” and “Always confirm with Microsoft about your licensing situation”.

What I have found works well is to add links in the summary report to the detail report. This approach allows us to pass in parameters to it and still limit the detail report to just the information that is needed.

To create a link in a report, you go to the Text Box Properties of the text box that you want to be the link, choose Action on the left side and then the “Go to report” radio button option.

If you are using Visual Studio, the dropdown list under “Specify a report” will be populated with included reports in your project. You can also use the expression builder to key in the name of the report you are linking to. The name you key in is the published report name and the .rdl file name. This screen also allows you to specify what parameters you want to pass into the detail report.

When you publish the summary and detail reports, if you publish them to the same directory then the summary report will automatically find the detail report when the link is clicked.

This past weekend, work was being done on our exchange server, so e-mail was down most of Saturday. What everyone (myself included) failed to realize was that this was going to impact scheduled reports from SSRS.

So Monday morning, I got an e-mail asking about which reports should have ran on the Saturday and who they would have gone to.

It sounds like such a simple request that I figured it couldn't be that hard to figure it out. I was dead wrong.

The Hurdles

After poking around in SSRS Web Interface, I quickly found that short of opening each and every subscription there was no way to find this information. Next I took a peek in the ReportServer database where SSRS is hosted and it looked pretty simple. I peeked in a few tables and the column names all lined up nice for joining to the other tables. But then I saw the first of two hurdles that wouldn't be so easy to get past.

Subscriptions.ExtensionSettings

I found the e-mail addresses that each Subscription is e-mailed to. The problem (or opportunity) is that it is buried in an XML field - Subscriptions.ExtensionSettings. This is the first opportunity that I've really had to mess with XML in SQL in a long time so I figured it wouldn't be too bad and I could figure out how to pluck what I needed.

However, my heart sank when I hit the second hurdle.

Schedule.RecurrenceType, Schedule.DaysOfWeek and Schedule.DaysOfMonth

The Schedule table has a series of fields where it stores the schedule - Schedule.RecurrenceType, Schedule.DaysOfWeek and Schedule.DaysOfMonth. This one wasn't going to be as straight forward to get past. I remember something in the back of my mind about taking a number have to square it or something or another but it was all really fuzzy.

This can't be the first time someone has had to solve this issue, who's got a blog or forum post about it?!?!

At this point, I figured it was time to turn to the web. This has to be an issue that others have come across, so how did they solve it? After searching around for a while, I wasn't able to find anyone who broke down the schedule fields. In fact, the only responses from Microsoft on their forums was that they don't support querying them! It was time for some bigger guns so I turned to twitter and posted on #SQLHELP and #SSRSHELP.

After a little while and a couple of conversations, people gave me the idea of linking the Schedules to their jobs and pulling the schedules that way. Linking the Schedules to their Jobs was easy. However, the job schedules are also difficult to figure out because they are done in a similar way.

Ah, there's light at the end of this tunnel!

The difference between job schedules and subscription schedules is that there ARE posts out there on how to decipher them! Between the little I was able to find about the schedules and the information about the job schedules, now I had what I needed to decipher the Subscription Schedules.

Remember, I needed to know what report subscriptions should have ran on a specific day.

Schedule.RecurrenceType had 3 values that I needed:

2 - The subscription runs multiple times during the day, every day. So I want all subscriptions that have that setting.
4 - The subscription runs one or more days a week. This is used in conjunction with Schedule.DaysOfWeek.
5 - The subscription runs on specific days of the month. This is used in conjunction with Schedule.DaysOfMonth.

I'm not going to explain bit wise, but here is a link to a BOL Article:

http://msdn.microsoft.com/en-us/library/ms174965.aspx

Schedule.DaysOfWeek and Schedule.DaysOfMonth work off a bit checker (bit wise). It'll help if you think of the day of the week in terms of what number it is. For example, Sunday = 1, Monday = 2, Tuesday = 3 etc.

The first value (Sunday and the first of the month) use a bit value of 1. After that you can figure out the bit value by taking 2 the power of the number you are looking for minus 1. For example, for the 3rd, it would be 2 to the power of (3 - 1) which is 2 to the power of 2 which equals 4.

So if we were looking for reports that ran on every Tuesday, the TSQL would be Schedule.RecurrenceType = 4 AND Schedule.DaysofWeek & 4 = 4.

The Query

I cleaned up the query a little bit by having a variable called @Date that you would set to the date you want to know which subscriptions would have been run on.

The query returns the Path to the Report, the Report Name, the owner of the Subscription and which E-Mail addresses it would have been sent to.

This by no means a finished and completely polished query, but rather a good starting point for others to use for their purposes.

Do you ever find yourself deploying the same code to the same 10 SQL Instances one at a time and wish there was a better way? Do you keep track of what SQL Instances you have by writing them on Post-It Notes in your cube? Are your developers always asking you what the names of the SQL Instances are?

If you answered yes to any of those questions, then you need to setup a Central Management Server! (CMS for short)

A Central Management Server is basically like a phone book (or a contact list on a phone for you kids too young to remember phone books!). The other thing CMS allows you to do, is to connect to multiple instances and run a query against all of them from a single SSMS window.

Setting up a CMS is really easy!

First thing to do is pick a SQL Instance that you want to host the CMS (you do not need a dedicated instance for it). A few things to consider is that the CMS is stored in the msdb, so db_reader (updated thanks to SQLArcher) or ServerGroupReaderRole access is needed for anyone you want to see it. The amount of disk space needed is very little (less than 100M. To plan ahead, choose an instance where the service account that runs the job agent can be given access to all the instances you will list in the CMS (for policy management). Next, click open SSMS and connect to the chosen instance.

From the View Menu, choose Registered Servers. Expand the Database Engine and then right click on Central Management Servers and choose the only option you get which is "Register Central Management Server...".

In the Server name, enter the SQL Instance you chose to host the CMS, set Authentication to whatever you shop uses (we use Windows Authentication), in Registered server name you can put in a nickname for the CMS and then you can fill out the description if you want.

Now if you right click on your CMS, you choose New Server Group to create folders to organize your instances. To add instances, you would choose New Server Registration and fill it out like you did when you added the CMS.

Here is a sample of how you could make it look:

If you right click on a folder you can choose New Query which will open a new window that is connected to all the instances in that folder including the children folders).

If you want to give Developers access to this feature, you will need to set them up with db_datareader (updated thanks to SQLArcher) or ServerGroupReaderRole access to the msdb database.

Maybe some of those alerts are not going to cause your system to crash right away, but you want to keep them in mind to show your boss that you need more memory or disk space.

So how do you know if something critical just happened that you really should look at and isn't part of the other 50 alerts that just noise for right now?

This is where I use the different alert statuses and have defined what each status means.

Critical (Red) status means I want our support center who is watching the alerts page 24x7 to call me anytime of the day or night about it. I keep things like job failures, disk space that is really low and custom alerts about backups being really behind for example in this spot. When a alert goes to critical and it is not something that I need to know about immediately, I will re-adjust to only go as far as a warning level.

Warning (Yellow) status means that this is something that I want to know about and could be become a serious issue, but I don't need a 3AM wake up call about it. For me it's almost as important as a critical alert and is something that I will look at, but I won't drop everything right now for it.

OK (Blue) status means it's something I need to be aware of, but could go for days or weeks before I do something about it. For example, our dev system is using 90% of the available memory. That's fine because it won't be growing larger than that and we are actually migrating SQL Instances over to a new 2012 Environment.

This allows me to quickly sort through the alerts to see which ones I should be working on and concerned about (aka, prioritizing my time!)