SQL Server 2005 Service Pack 2 is here!

Since I probably won't be the "first post!" winner, I'll go over the highlights of what you're getting (some of which are not mentioned in the What's New document). Before I do that I'll post the links:

SQL Server 2005 SP2

KB 921896: A list of the bugs that are fixed in SQL Server 2005 Service Pack 2

What's New Document

February 2007 Books Online


  • User-defined reports in Management Studio
  • New VARDECIMAL storage type
  • Various fixes to maintenance plans, including multiple schedules, a maintenance cleanup task, and they no longer require Integration Services;
  • Better backup/attach/restore dialog features;
  • Better scripting options;
  • Context menu for views is more clear (added Edit and Design, removed Modify);
  • Better error reporting for Database Mail configuration problems;
  • More reliable copy database wizard with better logging;
  • Better permissions viewing experience;
  • Logon triggers, and a CCC option in sp_configure;
  • Object Explorer Details tab replaces Summary Tab;
  • New SMO methods Table.CheckIdentityValue() and Column.AddDefaultConstraint();
  • Reporting Services can now be integrated with SharePoint Services 3.0 or Office 2007 SharePoint Server;
  • Much of the color coding omissions have been cleaned up;
  • Plan cache improvements;
  • New table-valued function sys.dm_exec_text_query_plan;
  • Better layout in graphical showplans;
  • OBJECT_NAME() now accepts an optional DB_ID parameter;
  • New function OBJECT_SCHEMA_NAME() allows you to get the schema for an object by name, without having to join multiple catalog views;
  • New procedure sp_refreshsqlmodule
  • Disk Usage report has been overhauled, and in general, in-built reports perform better; and,
  • Vista support — allegedly. I was unable to upgrade my client tools on this machine.

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a father of two, a huge hockey and football fan, and my pronouns are he/him. If I've helped you out, consider thanking me with a coffee. :-)

15 Responses

  1. DBA says:

    Hi Guys,
    i found this option but my issue is how i can take advantage of this features in SSIS. i am trying to export data into csv and having lots of issues to deal with , in between data. please advice.

  2. AaronBertrand says:

    No problem David.
    BTW, I updated the original issue with a comment and a request to re-visit.  I also opened two more issues as a result of my investigation.  I encourage you to consider Connect an ally and not a waste of time.

  3. David Markle says:

    It helps immensely.  I've been searching for this checkbox ever since I read that this issue had been closed.  And you're right, there it is.  Eating crow tastes good when you realize that most of your issues have been taken care of.  Maybe I've just been reading too many Celko posts lately and it's rubbed off.  I think perhaps it's time for a vacation, or investigating this whole "woman" concept I've been hearing a lot about lately.
    The moral of the story: Sometimes there really IS a good answer to the question, "wtf?"  🙂

  4. AaronBertrand says:

    I updated the client tools on my workstation here at the office to SP2, and reviewed the issue you pointed to earlier, 132806, to which you said you couldn't see a resolution in SP2.
    In Management Studio I found the following option:
    Tools | Options | Query Results | SQL Server | Results to Grid |
    Quote strings containing list separators when saving .csv results
    After enabling this checkbox and opening a new tab, I ran this query:
    And the output CSV file (File | Save Results As | Export Files (*.csv)) looked like this:
    (It handles doubling up of double quotes within a quoted string, also, which I thought for sure would cause trouble).
    I also tested the original query that you posted in your first comment above, and the output CSV file looks correct in Notepad:
    "this should return two column, not three, four or one.",column two
    However, when I double-clicked it to open it in Excel 2007, it all showed as one column.  This is because the default output format for files in Management Studio is Unicode.  To avoid this problem, you need to click the little arrow on the Save button, choose "Save with encoding", switch from Unicode to ANSI, and then the new file will open correctly in Excel.  (You can also change it from Unicode to ANSI within Notepad or other text editors.)  There may be an easier path using File | Open manually from within Excel, but I didn't investigate that.  Note though, that if you output really does have Unicode data, this is not necessarily a desirable output either.
    I will be filing a suggestion on Connect to provide an option for default encoding when saving files.  We don't all have Unicode data and we don't always want to jump through these extra steps just to produce a file we can open in Excel.
    Hope this information helps.

  5. AaronBertrand says:

    Yes, I perform data extracts to CSV all the time.  Like Jeremy, I have written a few different EXEs to handle this.  Obviously, this is something that could be fixed.  But just because something is embarrassing and obvious does not automatically make it to the top of the pile.  If you want Microsoft to push this omission to the top of their priority stack, you're going to have to convince them, not me.  I am virtually powerless to do anything about this.  I have my extract tools in place, and I don't use SSMS to save CSV output, so I wouldn't even know what to ask for.
    You've highlighted a few bigger problems in SSMS, in my opinion.  For scripting stored procedures, for example, I use tools from other vendors such as Red-Gate.  And there are so many issues with the visual designers that I warn others to stay away from them completely.
    SSMS is decent at some things, and horrible at others.  But you seem to be contradicting yourself… you are saying that this CSV extract problem should be their #1 priority and needs to be fixed, yet you're complaining about how crippling just using SSMS is?
    I am not patting anyone on the back.  Take a look at my bug reports and suggestions on Connect; and keep in mind these do not include any that I made when we were still submitting issues at beta.microsoft.com and/or ladybug.  I have been very vocal in my complaints about SSMS.  One of the nice things in SP2 that I believe was originally initiated by me was the change to the context menu when right-clicking a view.  It used to just have "Modify" and I would swear about 40 times every time I chose that and got the brain-dead visual editor.  Now it has "Edit" which brings up the code (thank you!) and "Design" which brings up the dumb drag and drop point and click nonsense.
    As far as SP2 goes, I think they delivered a lot, but they obviously can't please everyone.  Not *all* of the problems were in SSMS, and to be honest, I'd rather they kept fixes to the engine and fixes to an optional client tool separate anyway.  I don't use maintenance plans except at one consulting site, but for some people the changes here have been great (especially the elimination of the dependency on SSIS).  I am using several custom reports in SSMS that would otherwise have to be developed as a .NET application and hosted on a web server somewhere.  I have one system where there are 500 databases with identical schema, so the simple addition of a DB_ID() parameter to OBJECT_NAME(), and the new function OBJECT_SCHEMA_NAME(), have helped in several of my admin tasks.
    So yes, they have fixed some of the things we have complained about.  But like I said, you need to convince /them/ that this should be a priority.

  6. Jeremy Freedman says:

    I would have to agree with David that SSMS seems lacking in features.  I too have had problems with the CSV issue, and I ended up writing my own exe to go through sql projects and sort the queries based on name.  If I can find an hour to write that tool, the folks at MS surely could do the same.

  7. David Markle says:

    I love it.  The "workaround" is to pick a language of choice and not use the product.  That's no workaround to me — it's an admission of failure.  
    Of course there are "bigger" problems in SSMS.  But there's no more embarrassingly obvious one.  And of course I don't do production data extracts in SSMS, so don't accuse me of being a point & click developer.  
    So, has nobody in a business ever asked you for results to and ad-hoc query that they can browse in, say, Excel?  Do you not think this is a common usage pattern?  
    I have a big problem when the tools are considered third-class citizens in a product.  Tools are what make you productive.  Productivity is what saves us and our clients money.  At the end of the day, it comes down to productivity, because it all comes down to money.  I love working on this platform as much as you do, but I'm not willing to let how much I like my work affect my judgement of the tools I use.
    A good tool is one that makes both simple and complicated things easy (Take Red Gate's SQL Compare, or maybe even SSIS).  An "OK" tool makes simple things easy, and complicated things complicated (Query Analyzer?  Maybe that's a "good" tool…).   A poor tool is one that makes simple things complicated (SSMS, not because of its design, but because of its bugs).  
    So I'm not willing to give the SQL Server team a pat on the back for SP2.  No pats on the back, no "thanks" until the glaring deficiencies are taken care of.  Ever tried to script a bunch of stored procedures out to a new query window (100% CPU for a few minutes)?  Ever seen the god-awful (and wrong!) SQL that the SQL Designer generates?  (No, I don't use the designer, but I sometimes have to clean up after those that do.)  Ever had to add a new SQL file to a SSMS project, only to find that the files you add aren't shown in the Solution Explorer sorted in alpha order — they're all thrown together in an unordered "pile" of functions?  
    These are all issues totally ignored since SSMS was released.  What was the last REAL fix to the toolset that you can remember?  I'm THROUGH being "satisfied" with workarounds to my toolset.  I'd like people to start DEMANDING quality, and I would like the MVPs to stand up for it.  Maybe then someone will listen.

  8. AaronBertrand says:

    I didn't vote for it, because it has been closed.  And because it didn't affect me.
    As far as pride in what you do, there is a big difference between you or I writing a stored procedure, and a mammoth product like SQL Server.  They have to cut their losses at some point, or else they will never ship.  I'm going to go out on a limb here and guess that more people would be angry if they delayed shipping by a month to make you happy, than the current state of affairs.
    If you think this is the biggest problem in SSMS, that's fine.  The problem is that I know there are still many others that I, personally, deem more important.  I'd have to imagine that the team's priority list is different from yours, also, or this issue would have been fixed.
    Your issue has a workaround… pick your language of choice, and don't use SSMS directly to export data.  Data extracts should not be dependent upon pointing and clicking in a GUI anyhow, IMHO.

  9. David Markle says:

    Well, first off, did you vote for it? 😉
    I understand how valuable the voting thing can be.  But at some point, it all comes down to pride in what you do.  It's *personally* embarrassing to me when I forget something obvious like not properly dealing with delimiters as data in an export.  
    It's the first thing I test for when doing file exports, and it's among the most basic questions that I ask when I get a feed from someone.  So it should be a point of pride to the management of the SSMS team that they not have bugs that might show up on thedailywtf.com.  I mean, other parts of the SQL Server team are working on truly difficult problems — query optimization, parallel algorithms, predictive data mining algorithms, you name it.  And to not be able to save a CSV file from a grid?  To not even make it a priority?  Doesn't this stir up the bile in your belly at least a tiny bit as much as it does mine?
    As Forrest Gump said, "and that's all I have to say about that."

  10. AaronBertrand says:

    I actually think the voting helps give them guidance as to which bugs affect the most people, and how badly.  With a product this complex and with this many potential bugs/enhancements filed, they have to prioritize somehow.  If they fixed everything and could not rank the things they should fix in chunks, we would never see a service pack, never mind a new release.

  11. David Markle says:

    Aaron.  Thanks, I think I found the "real" issue:
    Issue 132806.  This bug has been closed, but there's no resolution that I can see in SP2.
    I hate asking people to "vote" for bugs like this.  There should be no reason to "vote" for something like this to get fixed.  Their "conflict" for SQL 2000 means absolutely nothing to me.  Nothing.  Read what the MS commenter says: "SQL 2000 QA also has the same problem of not quoting strings, so now it's almost a feature".  
    Since when was that an excuse not to fix an obvious bug that wastes as much time and effort as this one?

  12. AaronBertrand says:

    What do you mean, "can not"? Do you have a passport?  If so, click "Sign In" in the top right corner of the interface.  Kind of an annoying requirement, but I guess they want you to feel some accountability for what you post.  🙂
    I looked at 233903 and this looks specific to reporting services (and not being overly familiar with RS, the workaround suggested by MS does not make much sense to me).
    I think this issue may be related, but I think it will get more visibility as a separate issue not chunked under reporting services.

  13. David Markle says:

    Well, there's related Bug ID 233903 on Connect.  MS's response is that the export behavior is by design.  
    You've got to be ***** kidding me.  
    Oh well, it's not a part of the engine, so it's not worth paying any attention to.  I guess they figure by ignoring obvious bugs that could be fixed in a matter of a few hours' work, they can finally catch up to the quality (blech) of Oracle's tools.  
    Of course, I can not file any feedback on Connect.  No idea why.  Perhaps I need "permission".  Connect looks like it was put together by monkeys.

  14. AaronBertrand says:

    David, is this issue posted on Connect?  If they aren't aware of it, they can't fix it.  Don't get me wrong, I'm not saying it's okay that this problem existed in the first place; I know that they took many shortcuts to deliver the product on time.  But unless you bring these issues front and center, they won't really know they exist.  Thanks for pointing it out here, but if you want them to fix the issue, you really should file an issue on Connect.  And if you want community support, post the link here so we can all weigh in with our votes.

  15. David Markle says:

    This seems small, but it's absolutely ridiculous that this bug is still in the SQL Server tools.  Try this with SP2, or SP1, or RTM.  Go into SSMS and run this query:
    select 'this should return two column, not three, four or one.' as test1, 'column two' as test2
    Save the file to CSV.  Attempt to open it in anything that can read a CSV file.  Most people use MS Excel for this.  You'll notice that it doesn't work.  If you save the file to an ANSI encoding, you can at least see it show up as separate columns, but SSMS is too stupid to text-qualify the output into the proper number of columns.  I'm sorry, but you have to be kidding me.  When will this be fixed?  SP5?  SQL 2007?  Just because it's not an engine bug doesn't mean it's not important.  Wrong output is wrong output.
    I'm sorry for calling them out on this public forum, but you've GOT to be kidding me here.  How did this fix not make it to SP2?  SP1?  RTM?!  Even the most junior programmer knows how to text-qualify a CSV file.