Double-Dash Comments, the Hidden Menace

While writing my last post on cleaning up old maintenance plans, I was reminded of something I heard in a presentation once (and unfortunately I can't remember who said it) that has stuck with me for the last several years:

Word Wrap is Your Enemy.

Why is this?  If you use double-dash inline comments:

--This is a comment, at least it is supposed to be.

SELECT COL01, COL02, from dbo.mytable

...and your code gets opened in Notepad/Wordpad or some other editor that utilizes Word Wrap, it could look like this:

--This is a comment, or at least it is sup

posed to be

SELECT COL01, COL02, from dbo.mytable

Good luck executing that code after it has been copy-pasted in this format into a query tool....something like "Improper syntax near 'posed'"

Even if you don't try to execute it, it can be difficult to read and troubleshoot when similar pieces of code are near each other and one line or the other is double-dash commented out.

So the hint to this is always, always, ALWAYS use star-slash comments /* */ even if your comment is short (even a single word) - it's not a bad habit to get into and doesn't cost you much (two extra characters) to protect you from this problem.

/*This is a comment, or at least it is sup

posed to be (and still is!)*/

SELECT COL01, COL02, from dbo.mytable 

Hope this helps!
 

Cleaning up Old Maintenance Plans

This is something I trip over every now and then on systems we inherit from our clients, and I thought I would write it up in case it helps someone else {-:

In the most recent case, I was looking at a SQL Server 2008 server that had been upgraded from SQL 2005 without installing Integration Services, so the maintenance plan jobs were failing with an error like this:

The SQL Server Execute Package Utility requires Integration Services to be installed by one of these editions of SQL Server 2008: Standard, Enterprise, Developer, or Evaluation. To install Integration Services, run SQL Server Setup and select Integration Services. The package execution failed. The step failed.

Rather than installing the relevant hotfix (http://support.microsoft.com/kb/961126) or upgrading to a service pack that they haven't tested, I took another step in my never-ending battle against SQL Server Maintenance Plans in favor of Ola Hallengren's Maintenance Solution.  If you don't already use it - check it out (all the cool kids are doing it {-:)

I installed MaintenanceSolution.sql and configured schedules comparable to the broken maintenance plans, and then went to delete the maintenance plans and their jobs:

The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.

Sigh.

I have seen this more than once for a variety of reasons, including plans that get deleted before the jobs and just flat out broken maintenance plans - but the fix is relatively easy (if you're OK deleting rows from tables in MSDB):

/*Look in the plans table to find the plan you wish to delete*/

select * from msdb..sysmaintplan_plans

/*Copy-Paste the Plan's ID (will be in GUID format) from the previous query into the where clause of the following statements and run them to clean up the foreign key relationships in the proper order*/

delete from msdb..sysmaintplan_log where plan_id = '11111111-AAAA-BBBB-CCCC-222222222222'

delete from msdb..sysmaintplan_subplans where plan_id = '11111111-AAAA-BBBB-CCCC-222222222222'

delete from msdb..sysmaintplan_plans where id = '11111111-AAAA-BBBB-CCCC-222222222222'

/*Check the plans table again to verify the plan id gone */

select * from msdb..sysmaintplan_plans

At this point the maintenance plan is gone and you can now safely (and successfully) delete the offending maintenance plan job(s) from Management Studio or from the command line.

Hope this helps!

A Little Feature Self-Deprecation

The buzz started yesterday, with the rumblings about Database Mirroring showing up on the "Features Not Supported in a Future Version of SQL Server" list for SQL Server 2012 in the wake of the release of AlwaysOn, with multiple blogs and tweets about it (including a blog from your truly here.)

...and then this morning I noticed this innocent tweet from Grant Fritchey (@GFritchey):




I went out and checked the list, and sure enough, Profiler is listed on the "Future Version" list as well, meaning it will be supported in 2012 and 2012+1, but may or may not be supported in 2012+2:




Ah, the continued rise of Extended Events (and by extension, Jonathan Kehayias (@SQLPoolBoy)).  Just another reason that learning about Extended Events should be way up there on everyone's list of things you *have* to learn.

This piqued my interest to continue examining the Deprecation lists, starting with the "Discontinued Database Engine Functionality in SQL Server 2012" list of features that are removed in SQL Server 2012.

The one that made me chuckle was this one:





All I could hear in my head was "Get a real O/S!"(Apologies to all of my brothers and sisters stuck with legacy 32-bit applications...)  {-:

Most of the other Discontinued Features seemed relatively innocuous - we do receive lots of notice these things are coming after all - although this one gets my award for the change most likely to break people's code:


No more *=/=* JOIN's - about time.

I moved on to the "Features Not Supported in the Next Version of SQL Server" list - features that are supported in 2012 but that will be removed in the next version after 2012 (2012+1).  The largest single batch of changes have to do with the long-anticipated drop of support for remote servers:




We have had linked servers since at least SQL Server 2000 and the noise that remote servers would go away "eventually" has been around for some time - and that time will be here....in 3 or 4 years.

Then there's the recommendation I dislike the most:




I don't mind the death of sqlmaint, but why recommend maintenance plans?  Why?  Why can't they do this:



Ah to dream (If you aren't familiar with the Hallengren SQL Server Maintenance Solution, you should be - numerous write-ups exist on the web, and Ola's documentation is thorough - check it out!)

The "Features Not Supported in a Future Version of SQL Server" list is significantly longer (as it always is) - these features are in 2012, and will be in 2012+1 as well (and potentially in many versions after that, but there are no guarantees); they are simply identified as features that will go away....someday.

This list is where database mirroring shows up:



Remember - being on the "Future Version" list means mirroring will exist at least in 2012 and 2012+1, but start considering now whether AlwaysOn will work for you instead.  The big catch in 2012 is that AlwaysOn is an Enterprise-Only feature (YUCK!) while mirroring works in Standard Edition.  There is significant online conversation about whether AlwaysOn will become a Standard Edition feature in 2012+1 to pave the way for the removal of mirroring, but we won't know that for another 3-4 years when the editions and feature lists for SQL 2012+1 are announced.

Another interesting entry on the "Future Version" list relates to backing up directly to Tape devices:



...but how may of us have local tape drives any more?  (Maybe more than Microsoft thinks.)

Many of the entries on the "Future Version" list relate to the apparent desire on the SQL Server team to remove the "classic" sp_xxxxx command formats in favor of extra load on the CREATE/ALTER/DROP commands, such as the following (not an exhaustive list):




...there are also several DBCC commands impacted in a similar way (at least they will be impacted someday):




...and of course Microsoft's ever present threat to force everyone to invest heavily in replacement semicolon keys for their keyboards (since we will all wear them out if they ever actually implement this):




What does all of this mean?


If you are still using non-ANSI (*=) join syntax, STOP.


Learn about Extended Events, DMV's, and although it wasn't mentioned above, Powershell.  All three of these have been Microsoft's general direction with SQL Server for some time, and it wouldn't surprise me if any or all of them are part of the next big paradigm shift with SQL Server (comparable to DTS >> SSIS.)


Look up the deprecation lists at the links I provided above and read them yourselves - they are each only a few pages - because you never know what change might be on the list that is seemingly minor to all of the bloggers like me and therefore not mentioned by us, but that will single-handedly break your production payroll, door access, or medical records system.  Knowledge is Power!