NEWSEQUENTIALID() defect in SSMS. Wasn’t it fixed already in SQL 2012 RTM?

I have recently received this question from different sources, so I think it’s worth documenting why some people still see validation error for the default value of a column when it’s defined as a NEWSEQUENTIALID() built-in. The tracking defect on Connect ( keeps receiving complains about the observed, undesired behavior. It’s been fixed in Denali…


What OLEDB providers should you expect to show up under the Providers node in SSMS?

  The short answer is: Those returned by master.dbo.xp_enum_oledb_providers. But let’s dig deeper and try to give you the detailed description you deserve. The triggering reason why I’m writing about this is because a colleague described the following situation in one of our internal lists: I designed a OLE DB Provider, and want to register…


How to set the default transaction isolation level server wide?

Last week I was involved in a discussion about the default transaction isolation level used by the transaction objects in WCF’s services (Serializable) versus the one used by the transaction objects in Entity Framework for SQL Server (ReadCommitted) whose details I’m not going to cover in this post.   At a given point during that…


Inaccurate values for “Currently allocated space” and “Available free space” in the Shrink File dialog for TEMPDB only

Last week I went to a customer who showed me the following weird information.   He opened SSMS (SQL Server Management Studio) 2008 R2 and connected to one particular instance of SQL Server 2008 R2 in which he observed this behavior we wasn’t able to reproduce with any other.   From the Object Explorer window,…


Understanding when, in a PowerShell script, the call of a method produces output upon return from the call and when it doesn’t

A colleague asked the following a few days ago: <MESSAGE> With the following rows I’m trying to revoke “connect” permissions from database user. Unfortunately I always get the output from the red line (see script below) Output: I ‘d like to suppress the output but sadly without success. Redirection into nothing with “$null” and “out-null”…


Misconceptions around connection pooling

With this post I want to explain certain details about the way connection pooling works with SQL Server based applications. In the last months. it has been a recurrent topic I’ve had to discuss with some customers because of their misconceptions on the topic. For the purpose of illustrating my explanations here, I’ve built a…


ALTER TABLE SWITCH fails with 4947 even when all the publicly exposed metadata for the indexes in source and target tables perfectly match

A colleague who is a consultant in Chile, reported a weird scenario he was observing in one customer and for which he couldn’t articulate an explanation. I saw his question posted in one of our internal distribution lists but couldn’t put any work on it at the time because I didn’t have the time to….


Programmatically enabling/disabling SQL Native Client Protocols

SQL Server Configuration Manager uses the WMI provider for Configuration Management. More specifically, it uses the SetEnable/SetDisable methods of the ClientNetworkProtocol class. The following PowerShell script will leverage that class to enumerate the client protocols and whether they are enabled or not (ProtocolOrder == 0 means disabled): Get-WmiObject -namespace root\Microsoft\SqlServer\ComputerManagement -class ClientNetworkProtocol | Select-Object ProtocolName,…