• Categories

  • Recent Posts

  • Archives

  • Copyright Notice

    Copyright © Nancy Hidy Wilson, 2010-2013. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Nancy Hidy Wilson and nancyhidywilson.wordpress.com with appropriate and specific direction to the original content.

SQLSaturday #308 Houston – Selected Sessions Final

Thanks to everyone who submitted sessions for SQLSaturday #308 in Houston on May 10th. I think this was definitely the most difficult selection process of the 3 SQLSaturdays that we have held. Lots of great sessions were submitted from a really large pool of speakers. For those who were not selected – PLEASE do not take this personally! It primarily came down to a balance of session topics spread across as many speakers as possible – we have 48 speakers for 60 sessions.

If you only submitted one session and we had multiple similar sessions to choose from then if your abstract didn’t really stand out in that category, it may not have been selected. If you only submitted one session on a topic that no one else submitted and we did not select it, it may be because we thought the topic was too narrow or the abstract didn’t clearly express what you planned to present. If you think it may be your abstract keeping you from being selected, check out any of several posts that Brent Ozar has done over the years on Abstract writing for tips to improve. And, in the future I would recommend submitting at least 2 topics for program committees to choose from to improve your chances!

So, with that all said, here are the selected sessions alphabetized by speaker last name.

Last Name First Name Session Title
Adams Ryan SQL 2012 AlwaysOn Quickstart
Adams Ryan How Active Directory affects SQL Server
Bansal Amit R S Mind your IO: Resource Governor shows you How
Barnes Bill Fill Factor: Performance or Nuisance?
Bell Chris The Spy Who Loathed Me – An Intro to SQL Security
Bertrand Aaron T-SQL : Bad Habits & Best Practices
Bertrand Aaron Top 5 Ways to Write Effective triggers
Bourgon Michael SQL Watchdog – find out instantly when SQL changes in production
Brown Wesley Fundamentals of SAN, NAS and IP Storage
Chaves Warner The Super Trace: Introduction to Extended Events
Cherry Denny Storage For the DBA
Clark Tamera 45 min to build your first SSRS report
Cook John SQL Server 2012 Analytic Functions
Costello Tim Windowing Functions
Costello Tim Pro Tips: Tuning the data flow in SSIS
Curnutt Mindy SQL Server Bingo – Install, Migration & Config
Cutshall Aaron The Lost Science of Set Theory and Relational Algebra
D’Antoni Joseph SQL Server–All About HA and DR
D’Antoni Joseph In-Memory Columnstore Indexes–Make Your Data Warehouse Fly
Edmondson Garret Measuring Data Warehouse Performance
Edmondson Garret Data Warehouse ETL
Edwards Lori SQL Server Statistics – What Are The Chances?
Gable Glenda Data Warehouse Indexes
Harp Vicky Edge Case Testing for the Database Professional
Harp Vicky Care and Feeding of Your System Databases
Hays Mike Troubleshooting Your Network Connections
Herold Amy Making the Leap from Developer to DBA
Hokanson Kris Beyond the Where: Full Text Search tips and tricks
LeBlanc Thomas Attributes & Hierarchies in Analysis Services 2012
LeBlanc Thomas Execution Plan Basics – Beginners
Lopez Carlos The Transaction Log Internals
Lopez Karen Database Design Contentious Issues
Lopez Karen Windows Azure SQL Database Design: Concepts and Trade-offs
Loski Russ Introduction to BIML and BIMLScript
Loski Russ SSIS project deployments with multiple developers
McCown Jennifer T-SQL Code Sins
McCown Sean DIY Performance Reporting
McCown Sean & Jennifer DBA Career Roadmap
Mendo AJ DBA Survival Guide: Daily Checklist and Server Monitoring
Mitchell Tim Scripting in SSIS
Mitchell Tim 15 Quick Tips for SSIS Performance
Moreign Valentino and Anthony {Tex} The Cloud and I
Murphy Jim Mission Possible: Interactive Performance Troubleshooting – Indexes
Norman Tom Converting Unreliable Deployments Into Consistent Releases
Parekh Aashish Speed, Depth & Flexibility – Having it all with real-time SSAS
Rodarte Rudy Expand your TSQL: Intersect, Except, and Apply
Schilling Dane Taming the T-Log
Serra James Best Practices to Deliver BI Solutions
Serra James Enhancing your career: Building your personal brand
Speshock Carl Microsoft Predictive Analytics/Data Mining Overview
Stein David Writing Your First BimlScript
Sterrett John Table Partitioning: the Secret Weapon for your Big Data Problems.
Sterrett John Proactive Monitoring with PBM and CMS
Stewart Robert SSAS – An Introduction to Multidimensional Cubes
Tidwell Lance SQL Server Agent: The life preserver for the drowning DBA
Wilson Derek Tabular Data Visualized by PowerView
Wong Jason Ah, ha, how do you automate database administration?

If you are thinking there are some pretty big topics missing from the above list, you are correct! We will be having 3 additional sessions presented by TechEd speakers on PowerBI, Hybrid Cloud, and In-Memory.

The final schedule will be posted on the SQLSaturday #308 website in the next few days! So stay tuned!


SQLSaturday #308 Houston – Speaker Selections #3

Today (March 21, 2014) is the final day for speakers to submit their sessions for SQLSaturday #308 in Houston on May 10.  We are already building up a great slate of speakers (announced here and here) and today we are pre-announcing that the following MVP speakers will also be presenting:

  • John Paul Cook (website)   – SQL Server 2012 Analytic Functions
  • Tim Mitchell (twitter | website) – to be determined SSIS topic
  • James Serra (twitter | website) – BI/DW or Professional Development session topic still to be determined

Stay tuned for the complete slate and schedule to be finalized soon!

Spread the news to your colleagues and register to attend this free training event now as space will be limited!

SQLSaturday #308 Houston – Speaker Selections #2

Last night I revealed the names of three speakers selected for our free training event on May 10 at San Jacinto College – South.  Today, I’m glad to announce that my fellow Texans Sean McCown (website) and Jen McCown (twitter | website) the husband and wife team also known collectively as the MidnightDBAs will also be speaking.  We haven’t finalized which of their submitted sessions will be on the schedule, but you can probably expect something related to Backups, PowerShell, Code Sins, or Career Paths!

If you plan to submit, do it quickly as the call for speakers to submit their sessions for SQLSaturday #308 ends on Friday, March 21, 2014.

Otherwise, spread the news to your colleagues and register to attend this free training event now as space will be limited!

SQLSaturday #308 Houston – Speaker Selections #1

Although there are a couple of more days for speakers to submit their sessions for SQLSaturday #308 in Houston on May 10, we are excited to start announcing over the next several days who is confirmed to present so that both presenters and attendees can start making their plans.

We hope to have the final schedule determined by the end of next week around March 28.

So without further ado… the following Microsoft SQL Server MVPs are planning to present the session titles shown.

In alphabetical order:

  • Aaron Bertrand (twitter | website) – Top 5 Ways to Write Effective Triggers
  • Denny Cherry (twitter | website) – Storage For the DBA
  • Karen Lopez (twitter | website) – Windows Azure SQL Database Design: Concepts and Trade-offs

We are looking forward to hosting these speakers and announcing more speakers over the next few days.

Spread the news to your colleagues and register to attend this free training event now as space will be limited!

SQL Server 2008 R2 Security Benchmark Released

The Center for Internet Security (CIS) Security Benchmarks Division released “CIS Microsoft SQL Server 2008 R2 Database Engine Benchmark V1.0.0” on November 16, 2012. The best I can tell, this benchmark can also be used with SQL Server 2008.

CISSQL2008R2This is a consensus-based development of security best practices which have become the de facto security configuration standards.  If you are in charge of your SQL Server security configuration, you need a copy of this document – it is what your auditors will be using soon!  



SQLSaturday #150 – Baton Rouge – Signup Now!

There’s an awesome FREE technical training event coming to Baton Rouge on August 4, 2012. That’s right; SQLSaturday and Tech Day 2012 will be held at LSU’s new College of Business facility.  This is the fourth year that the Baton Rouge technical community has held this event and they expect around 400 people – if you live anywhere close by, then you should be there!  William Assaf (blog | twitter) even got some local TV exposure for the event this year.  

This event is bigger than your normal SQLSaturday. In addition to tracks for the SQL Server professional, there are also tracks for .NET developers, Windows Phone developers, SharePoint, and general professional development. Check out the full schedule here, and then sign up here.

Why am I plugging this event? Well, for one thing the Baton Rouge SQL Server community has always come west across the state line to support our SQLSaturdays in Houston. Secondly, I’ll be speaking at their event this year on “Managing SQL Server in the Enteprise with TLAs”.  TLA is “Three-Letter Acronym” for those unsure. We have lots of those in techno-speak. I’ll be covering CMS, PBM, EPM, MDW, and more…. If you work with SQL Server and don’t know what those are or how they can help you, then register today for SQLSaturday #150 and come to my session at 8:20am in Room 1700! 

Addendum: I’ll now also be presenting a second session “SQL Server 2012 Database Engine – Why Upgrade?” in the 2:45pm slot in Room 1700.


If you can’t attend this event, then check here for all the currently scheduled SQLSaturdays in the US and around the world! 


SQL Server “Agent XPs” Behavior

Ever realize you’ve been doing something a certain way for so long and you don’t remember why? Or what the consequences would be if you changed how you did things?

I was testing our new SQL Server installation process this week. We create a configuration file programmatically from user input and some set defaults and then run the install unattended followed by executing a series of T-SQL and PowerShell scripts to make sure certain configurations are done.

Vicky Harp (blog | twitter) has a presentation on edge case testing which really caught my attention a few months back. For years, I’ve always made sure all parameters, required and optional, were explicitly defined for our installs. But, what if someone else runs this and isn’t as precise as I tend to be?(I know some of you are thinking of another word.) For the current test I was doing with our new process, I was only providing the absolute minimum required parameters and letting everything else default. The install ran great, and then the configuration files started executing. Surprisingly, I had two scripts report failure. I had successfully run the same set of scripts on a prior install earlier in the day. What changed?  You’ve probably already guessed, but bear with me through my analysis.

My first thought was a SQL Server 2012 difference from SQL Server 2008 R2. The current install I was performing was 2012 while the earlier one was for 2008 R2. So, that seemed like the logical place to start – a difference between versions. But, I was confident that these scripts worked for both versions and had previously tested them independently of the install process. Then I looked a little more carefully at the error. The failing scripts were calling system stored procedures to modify SQLAgent’s job history settings. The error being reported was that the Agent XPs weren’t enabled.  I was confused – why did my SQL 2008 R2 install work fine with the same scripts and SQL 2012 fail?

While I’m pondering the mystery of what was different I remembered that the SQL 2012 Setup had returned error 3010 which means that a reboot is needed following the install; so I rebooted that box. In the meantime, I searched and verified that I indeed did not have a script which was explicitly enabling the Agent XPs, but somehow they were enabled on the SQL 2008 R2 system.  The SQL 2012 system came back up and I logged back on to continue my investigation. That was when I noticed that SQLAgent had not started following the reboot. Its start mode was set to “manual” which is the default if you do not specify the AGTSVCSTARTUPTYPE=”AUTOMATIC” in your configuration file. I opened SQL Server Configuration Manager and changed the SQLAgent service’s start mode to “Automatic” and started the service.  Then, I checked the value for sp_configure ‘Agent XPs’ – and sure enough it was now 1 (enabled) and I could execute the scripts which previously failed.

At first I thought it was just changing the start mode to Automatic triggered the enabling of the Agent XPs, but I finally verified after testing several scenarios that whenever the SQLAgent service is started or stopped regardless of the start mode, it will enable or disable the Agent XPs respectively.  This is really confusing if you know that you have explicitly run:

sp_configure ‘Agent XPs’, 1;


Then you stop SQLAgent and don’t restart – it is now disabled as if you’d never run the above command. If you want to modify some aspect of SQLAgent while it is now stopped, you’ll have to run the above command again.

sp_configure ‘Agent XPs’, 1;


EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=150000;

So, you can modify the properties while SQLAgent is not running, you just have to remember to always enable the Agent XPs immediately beforehand. Since this behavior is by design for security purposes, then you’ll probably want to get the current setting before enabling it and if it was disabled, then set it back to disabled when you’ve finished running your command(s). 

It’s pretty obvious when you use SSMS and the Agent XPs are disabled. You cannot access any SQLAgent nodes or even its properties.

You can leave the SQLAgent service stopped and run the sp_configure command to enable the XPs, and then you can access the SQLAgent properties in SSMS. Most of the time this shouldn’t be an issue, you’ll have SQLAgent running and the XPs will have been automatically enabled. But, just in case you ever run into an unexpected error with the Agent XPs like I did, hopefully, you’ll remember this behavior.

TSQL2sday #32 – A Day in the Life

TSQL2sday is a monthly SQL Server blogger event started back in late 2009 by Adam Machanic (blog | twitter). For more info on its beginning and purpose see the origin of TSQL2sday. Each month a different SQL Server blogger is the host (announces the theme and compiles a recap). This month’s event is hosted by Erin Stellato (blog | twitter) and the selected theme for this month is “A Day in the Life”.

Erin challenged us to track what we did in our jobs for a specific day and write about it. This is great because I often have trouble explaining to others (especially non-IT folk) what my title of SQL Server Service Engineer really means. However, as this exercise is just supposed to cover a single day, this is just a small sample of what I do. There is no such thing as a “normal” day for me. Sometimes my tasks are based on the “crisis du jour” prioritization method, and sometimes I can actually follow the team work plan. The variety in my job is one of the things I like about it. So here goes…

Unless, I have an early morning meeting with global colleagues, my day nearly always begins with processing email. Since I work in a global organization in the region whose workday is last to begin, even if I’d cleared my Inbox the day before, I always open my mailbox to encounter new emails from European and Asia-Pacific colleagues who have already completed or are wrapping up their workday. In that sense, this day starts out as just a normal day (no early meetings!).

Unfortunately for this write-up, it appears that summer time may be impacting my email load in a positive sense as I have only a handful of emails and only as a cc on a couple of subjects which one of my teammates is handling.  One of the issues has to do with deploying SQL Server Enterprise Edition versus Standard Edition and licensing implications for the customer. My team is comprised of technical experts – we can tell the customer if what they are trying to do requires a specific edition of SQL Server to use the requested feature, but we are not involved in the licensing agreements between Microsoft and each customer.  That is for others to figure out! 

Email done and no looming crisis for today, I can get back to the task I’ve been working on previously – writing an automated process to rollout multiple T-SQL Scripts to multiple instances using PowerShell. These are the scripts which update the standard tables and stored procedures in the admin database we install on all instances along with a set of SQLAgent jobs which the operational DBAs use for system maintenance. Every so often, we need to rollout updates to these objects. Our current automated process for doing this (which was developed for SQL 2005) isn’t as automated as we’d like it to be. We have since created a CMS and are utilizing registered groups to run various processes (like EPM) and now want to extend that concept to this activity as well. I’m thinking within a couple of hours I can write a script to save our operational DBAs literally hundreds of man-hours. Easy, right?

If you’ve worked with PowerShell any at all – or any programming language for that matter – you know there is always more than one way to write a process to accomplish the task at hand. The challenge is in finding the most efficient way that gives you what you want.  Our old script to run a set of .sql files was written in VBScript and called the sqlcmd utility. I figured I’m writing this in PowerShell, I’m using Invoke-Sqlcmd to get the list of instances from the CMS, I can use the Invoke-Sqlcmd cmdlet as shown in BOL in the second example and it will work just like sqlcmd. Wrong! It seems that example only works if you are running a SELECT statement in your InputFile.  This particular set of .sql files should have no output unless it is an error and in my test I have a script which I know produces an error – but my output file is empty.

I try various parameters such as -ErrorLevel and -SeverityLevel and I even use -Verbose to no avail – still nothing is piped to my output file.  I consult with my team mates to see if they tried this before; I search for examples on the Internet and the best I can find in one of the forums was someone else encountering the same thing, but with no solution for me. I can be stubborn some times and I’m not about to give up yet – after a couple of hours of struggling – I fire off an email to my SQL PowerShell buddy Allen White (blog | twitter) asking for his input – can I do what I’m trying to do with Invoke-Sqlcmd or should I revert to calling sqlcmd?

While waiting for Allen to respond, a couple of more emails have hit my Inbox.  Yea! It appears that our request to rebuild one of our team’s test servers has been completed.  We try not to do this too often, but part of engineering is writing scripts \ installing \ testing \ uninstalling \ enhancing scripts…repeat; over the course of time sometimes things get so messed up from all the testing (and occasional bad script) you just have to start over with a clean image.  This is now a box we plan to use for testing our processes on SQL Server 2012.

It doesn’t take long before I have a reply from Allen – I hope he doesn’t mind if I quote him:

I honestly believe that it’s best to use the tool that best suits the task, so I’d use sqlcmd here, because it works the way you want it to. 

Thanks Allen for the reminder not to use a hammer when a screwdriver is what you need! Sometimes, a hammer is all you have, but not in this case. 

Now, it’s time for lunch. I head down to the cafeteria with my team mates and join other colleagues at our usual table. I don’t hang around too long chit-chatting as I want to get back to my desk and switch out my code and test so I can announce success at our afternoon team meeting.

Remember earlier what I said about more than one way to do something? Now, I have to decide how to go about calling sqlcmd.exe from PowerShell. I need to specify variables to all the parms based on the target instance and input file to execute – and the output filename and location is dynamically determined as well based on the target instance and input filename.  I start with looking at Invoke-Command, then move to Invoke-Expression, but I’m still not getting my output file like I want it and I’m not able to detect if sqlcmd experienced an error to report in my general execution log. I have an example using [diagnostics.process]::start($exe,$args).WaitForExit() that seems to be getting me close to what I want, but now it is time to break for my afternoon meeting.

I’m the Technical Team Lead for a team of three. We each have our areas of specialization within the overall work plan, but try to keep each other in the loop so we can back each other up at any time. As needed (usually every 1-2 weeks), we meet formally to update the work plan, assign/reassign new/old tasks if needed, catch each other up on what we’ve each been working on and brainstorm areas for improvement. This is one of those meetings and since last week was a holiday week and we didn’t meet, we have a lot to catch up on.  The nice thing about a team is having others to bounce ideas off of and this is what I do with my frustration in finding the exact syntax I need to be using to get the results I want from calling sqlcmd inside PowerShell.  The next thing I know, one of my colleagues has done their own search and found a code example – I look and express skepticism as it is very much like what I’m already doing, but with one key difference that might make a difference; what can it hurt to try?

We continue to discuss how far we want to take this initial rewrite of our update process.  We are also in progress of redesigning our whole automated install process and ultimately we want the update process to utilize what we are putting into place there.  However, we have a more immediate need to have the operations team rollout some updates and decide that version 1 of the update process will do no more than we have already in place today (in terms of reporting), but it will be automated such that the DBAs only need to review the central output file for any problems. Selection of the systems requiring an update into a special CMS group can be done in an automated fashion as well as scheduling the update itself in SQLAgent. We decide to make further enhancements for logging the process’s results into a central table in a future version.

Our meeting continues with more brainstorming about the consequences of developing an install and configuration solution for SQL Server which can account for multiple versions and differing customer standards (e.g. install locations). We plot out on the whiteboard differing ways we can handle this – probably the umpteenth discussion like this that we’ve had; but each time we come in with new experiences and thoughts from what we decided previously and in some cases started trying to implement and we are therefore continually refining the solution.  We are becoming more confident that we are developing a standardized, but flexible solution which is also more sustainable across multiple versions of SQL Server than our existing process.

The meeting concludes and although I’m anxious to try the code snippet my colleague found, it is really time for me to head home. I arrived at the office much earlier this morning than my normal start time trying to beat the rain and now I need to try to get home before the next round hits. There is some flooding already occurring around town. Working further on this script can wait until later. I know that once I do get started back on it, I won’t stop until I have it totally finished. That’s my life!

I probably learned more today in trying all the ways that didn’t work the way I thought they would than if the first code I tried had worked. This experience will pay off later, I know.

Today was an “Edison day”:

I am not discouraged, because every wrong attempt discarded is another step forward.

I have not failed. I’ve just found 10,000 ways that didn’t work.


P.S. I did finally get the script functioning the way I wanted the following day and it will save our operations team hundreds and maybe even thousands of hours. This is what I do!

Database Security Roles in msdb

All databases have a standard set of “fixed” database security roles which have been available since SQL Server 2000 and hopefully, even if you are new to SQL Server, you are familiar with this set:

  • db_accessadmin
  • db_backupoperator
  • db_datareader
  • db_datawriter
  • db_ddladmin
  • db_denydatareader
  • db_denydatawriter
  • db_owner
  • db_securityadmin

Back in the day, the msdb database was primarily used for managing backups, SQLAgent jobs, and DTS packages. And, other than the TargetServersRole role for multi-server job management via a master server and target servers, there were no other database roles defined for helping establish a separation of duties security model for tasks controlled via msdb

In SQL Server 2005 some long awaited new roles were added to msdb (http://msdn.microsoft.com/en-us/library/ms188283(SQL.90).aspx) which help with segregating permissions for managing SQLAgent Jobs, DTS\SSIS, Database Mirroring, and Database Mail (the replacement for SQL Mail):

  • DatabaseMailUserRole
  • db_dtsadmin – renamed in later versions as db_ssisadmin
  • db_dtsltduser -renamed in later versions as db_ssisltduser
  • db_dtsoperator – renamed in later versions as db_ssisoperator
  • dbm_monitor – does not appear until database mirroring is implemented
  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole

Then, in SQL Server 2008 even more roles appeared as msdb took on increased importance in the management of new features such as the CMS (Central Management Server), PBM (Policy Based Management), Data Collection, and MDW (Management Data Warehouse).

Thus, the following new roles may be found in SQL Server 2008+:

In SQL 2008 R2 one more set of roles was added for the Server Utility (UCP) feature:

  • UtilityCMRReader
  • UtilityIMRReader
  • UtilityIMRWriter

The above UCP roles are primarily assigned via the Server Utility configuration wizard and Utility Explorer Security tab. The “Utility Reader role” referenced in the Security tab is equivalent to the UtilityCMRReader role. As you may notice, certain service and administrator accounts are automatically in this role and cannot be changed (grayed out).

And, now in SQL Server 2012, believe it or not, there are no new roles in msdb!  So, if you haven’t kept up with all the changes in msdb in the past few versions, this is your chance to catch up and make sure that you are fully utilizing these features and roles as needed in your environment. For a recap of all the features which utilize msdb, you can also refer to an earlier post I wrote – “What’s in Your msdb?”.

Discontinued Features in SQL Server 2012 – Part 4

In the previous installment on this topic, I looked at discontinued features in SQL Server 2012 which may mostly impact your administration scripts.  Today, we are widening the net and looking at discontinued features which may impact either the application layer or administration programs and scripts. Most of these items have been deprecated as far back as SQL Server 2000, so it isn’t like they have gone away overnight. However, if you are still running on SQL Server 2000, then you really haven’t had a need to go replace them…until now.

SQL Mail is finally gone! It would have been totally fine with me if SQL Mail had been discontinued as soon as Database Mail was added in SQL Server 2005.  However, you do lose functionality (receive mail processing) with Database Mail, but the trade-off is much improved reliability, security, supportability, and scalability. For example, you no longer need an Extended MAPI client (e.g. Outlook) installed; you don’t run the risk of attacks via reading mail; and Database Mail is supported on clusters. So, if you are still using the read\process mail functionality of SQL Mail, you’ll need to find another method for your application to use before upgrading to SQL Server 2012.  If all you are doing are send mail tasks, including notifications from SQLAgent, then the switch to Database Mail is a no-brainer and you can do it now in SQL Server 2005/2008/2008R2. However, this is more than just a configuration change; it does require usage coding changes for example from xp_sendmail to sp_send_dbmail.  

Unless you’ve been stuck on SQL Server 2000, you have probably already started converting applications using SQL-DMO (Database Management Objects) to use SQL-SMO (SQL Server Management Objects) when you found you wanted to access newer features in SQL Server 2005+ that were not available via SQL-DMO.  If you want to use your SQL-DMO scripts against SQL Server 2012 – you must convert to SQL-SMO; SQL-DMO is discontinued.

If you have databases in compatibility mode 80, it might be because the code using them is still using the old non-ANSI outer join syntax “*=” or “=*”.  This syntax is not supported in later compatibility modes and because compatibility mode 80 is not supported in SQL Server 2012, this syntax is no longer recognized in SQL Server 2012 in any scenario.  You’ll need to get all of this code up-to-date with current ANSI join syntax used in the FROM clause.

Another T-SQL syntax which is disappearing is the COMPUTE / COMPUTE BY clause in SELECT statements; you will need to convert these statements to use the ROLLUP option of the GROUP BY clause.

If you tried to speed up the results returned to your client or force a particular query plan by using the FASTFIRSTROW query hint, you’ll need to switch to the OPTION (FAST n) syntax as FASTFIRSTROW is no longer available.  For more info on the overall impact of using this option, see this blog from the SQL Server Query Optimization team and this one from SQL MVP Grant Fritchey. As with all query hints, you should re-evaluate these periodically as they may no longer be as beneficial as when originally added.

In SQL Server 2012, you can no longer invoke an ad-hoc error message number using RAISERROR integer ‘message’ syntax as in: RAISERROR 50111 ‘Sample error message’.  You’ll need to convert to the RAISERROR() syntax:  RAISERROR (‘Sample error message’, 10, 1). But, if you want to really get your code current, convert to TRY…CATCH (available since SQL Server 2005) and use THROW (new in SQL Server 2012). There are some differences in behavior between RAISERROR and THROW and these are documented in BOL’s THROW entry.

I’ve only highlighted a sample of discontinued features in this set of articles; for the complete list of discontinued database engine features in SQL Server 2012, please reference BOL. And for help finding out if you are using these features (which were deprecated in earlier releases), I covered that here.

Happy upgrading!


Get every new post delivered to your Inbox.

Join 415 other followers