Connect digest : 2009-08-09

Sorry, I've been trying to keep this to a regular Saturday installment, but it was a beautiful weekend here and I just couldn't pass up on non-computer-related activities.  I have a short list of Connect items to draw your attention to this week.


Setup messes with authentication mode

I was bitten earlier this year by a bug in SP3 (and potentially CU) setup code where, on a cluster, the authentication mode of the instance was changed from mixed to Windows only.  Why setup needs to mess with authentication mode, I have no idea; more importantly, if it needs to change authentication mode for some reason, it absolutely has to change it back when it is finished.  I filed an issue directly with Microsoft support and, since I was the only one who had reported the problem, and because I couldn't get it to happen again on demand, it was ultimately closed as "not reproducible."  Thankfully (and regrettably too, of course) this has happened to a few more users, and maybe this issue will finally gain enough steam to be investigated properly.

#480487 : Named Instances on Windows Cluster changed from Mixed Mode to Windows only on SP3 CU4 install

#450316 : Named instance changed to Windows-Only authentication on SP3 CU1 hotfix

The latter was closed as "Won't Fix"!  Until they decide this is worthy of spending any time on, please be sure to check that the authentication mode is still correct after applying SP3 or any post-SP3 CU to your 2005 mixed mode instances.  Better that you find it right away and correct it, than if a customer calls and complains about it.


Make it easier to turn local BOL links into MSDN URLs

Many of us support end users out in the wild, and often look up documents in our local Books Online reference.  When we want to show an end user a specific topic, it becomes very tedious to translate the local BOL link (e.g.) to its counterpart in the online version of Books Online (e.g. ).  This is because we cannot be sure that end users have Books Online installed at all, never mind the most recent version.  Fellow MVP Steve Kass created a bookmarklet earlier this week that makes this translation easier, however it would be very nice if this type of functionality (or even just referencing both links in every topic) were included and were more automated.

#481024 : DOC : please make local <-> online URLs easier to translate 


WHAT string or binary data would be truncated?

For years we have been plagued by this very generic error message:

Msg 8152, Level 16, State 6
String or binary data would be truncated.

David Walker is asking for more information to be given to the user to short circuit what often turns out to be very tedious troubleshooting.  Table name and column name would suffice for me.

#339410 : Please fix the "String or binary data would be truncated" message to give the column name 

At the time of writing, I hadn't noticed that Erland already had an existing feedback item with the same basic message.  (And in that item, Microsoft said, "Too late for SQL Server 2005; we'll fix it in 2008.")

#125347 : Improved error message when you try to put too large a value in a column 


sysprocesses is being deprecated?  Hold the phone!

And finally, one that should be getting more attention is the fact that sysprocesses is on the deprecation path, however it exposes exclusive information that is still to this day not available in any of the DMVs.  The most notable omissions are database_id (for idle processes) and open_tran_count.  These are only accessible through DMVs that are empty if the process is inactive, making it extremely difficult to troubleshoot; typically, most of us just continue to include sysprocesses in our monitoring / troubleshooting scripts.  At the point when sysprocesses is no longer offered as a backward compatibility view, we would hopefully be able to get our answers from DMVs.

#257502 : Deprecation of sysprocesses – DMV's doesn't fully replace all columns


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, speak frequently at conferences, and write at SQLPerformance and MSSQLTips. In real life I am a father of two, an architect at Wayfair, and my pronouns are he/him.

1 Response

  1. AaronBertrand says:

    Oops, looks like I talked about sysprocesses about a month ago: