Tone Poem

Why Can't I Log Into SQL Server?

Top things I always forget when logging into SQL Server:

1. SSL Self-Signed Cert Does Not Work When Forcing Encryption From Client

Today I forgot that I checked the "Encrypt Connection" checkbox in SQL Server Management Studio while trying to verify that an SSL certificate I installed on a SQL 2000 box was working.  Then I attempted to connect to my SQL 2008 server that is using self-signed certificate (which is default behavior).  When you do this, you get the following error:

"A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server, Error: -2146893019)

This error makes sense if you realize a self-signed certificate does not have a trusted root (everyone knows this, right?).  Anyway, if you see this one, now you will remember.  This can occur with SQL 2005 and SQL 2008, both of these will load a self-signed certificate during startup time if an appropriate SSL certificate is not setup on the machine.

Thing to remember:  If you want full SSL encryption to work properly with SQL Server, you need to configure an SSL certificate that the client trusts.   The self-signed cert is only used for encrypting login packets and is not fully trusted by client since it does not have a trusted root authority.

2. Vista Admin Cannot Login To local SQL Server

Another good one:  Attempt to login to SQL Server on Vista from non-elevated command prompt or non-elevated SSMS.  Login failed for user domain\username.  But I'm logged in as an admin!  You are not admin if you don't elevate.  This one get's me every so often.

I'll add more later.

Sharing IE Links With Live Mesh

In case you have not heard the buzz, we have this new thing called Live Mesh that is pretty awesome.  I found out about it whist trying to figure out how to share my Links (c:\users\...\Favorites\Links) folders across multiple machines.  I talked to one of my IE buds and he indicated Live Mesh might be the ticket.

With Live Mesh you can add each computer as a node in your mesh.  Then you can share folders from your computer into the mesh (and it shares these across all your computers).

To share my Links, I had to play some tricks with Live Mesh, but nothing too serious.

I setup Live Mesh on multiple computers, then on primary computer right clicked on C:\Users\MattN\Favorites\Links and shared it to the Mesh (note these computers are Vista, Live Mesh works on Vista and XP for now).  On XP this would be C:\Documents And Settings\MattN\Favorites\Links.  I shared my Links folder on one and only one machine (and also at this point consolidated all my actual links on the one primary machine).

Next on secondary machines what will happen is a new shortcut named Links will appear on your desktop once Live Mesh syncs up.

I then closed all IE instances on secondary machines, renamed existing Links to Links1.

Then go to link on desktop, right click and select Live Mesh Options... Change Sync Settings...

This will give you an option on where you want the link to reside, change it to C:\Users\Mattn\Favorites\Links, the folder will move automatically and now you have a shared Links folder, pretty cool!

Going to let this run for a few weeks and see how it works out.

Finding Locals In CLR

Today I'm looking at a few Watson dumps trying to determine why SqlConnection is failing to connect to SQL Server and hanging.

I see stack like below:

0:000> k
0021dc5c 79f40778 kernel32!SleepEx+0x62
0021dc90 7a0561d7 mscorwks!EESleepEx+0xa3
0021dc9c 7a12fc71 mscorwks!CExecutionEngine::ClrSleepEx+0xd
0021dcac 7a0e4d7c mscorwks!ClrSleepEx+0x13
0021dce8 7a082a5f mscorwks!Thread::UserSleep+0x63
0021dd88 652e94cd mscorwks!ThreadNative::Sleep+0xce
0021ddc8 652e9283 System_Data_ni!System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover
0021de20 652e83ff System_Data_ni!System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist

I need to look at CLR locals in SqlClient.SqlInternalConnectionTds.LoginNoFailover, and these locals are intrinsic CLR type long (Int64).

Where does CLR hide these frame local long values?

Using the sos extension I see !dso does not dump intrinsic locals.

!clrstack, wow the output is completely wrong and bizarre.

!dumpstack, !dumpstack -EE, broken.

kP works, somewhat reasonably.

Time to get ugly.

Ok, I locate start of assembly for SqlInternalConnectionTds.LoginNoFailover in debugger and just dump the raw assembly.

I know my variable is passed into function ADP.TimerRemainingMilliseconds, so my plan is to locate this call in unassembly and see how the assembly code packs the value passed to this function, this will give me a clue on where the value is held.

I find it here:

652e942f ff75d8          push    dword ptr [ebp-28h]
652e9432 ff75d4          push    dword ptr [ebp-2Ch]
652e9435 e8d2c3f3ff      call    System_Data_ni!System.Data.Common.ADP.TimerRemainingMilliseconds(Int64) (6522580c)

Good, I see ebp-offset, this looks like stack local.  I use kvn to get frame number.

0:000> kvn 10
# ChildEBP RetAddr  Args to Child             
...
07 0021dd88 652e94cd 06bf6a58 06bf3f90 06bf67cc mscorwks!ThreadNative::Sleep+0xce (FPO: [Non-Fpo]) (CONV: fastcall) [f:\whidbeyrtmlhs\ndp\clr\src\vm\comsynchronizable.cpp @ 964]
08 0021ddc8 652e9283 00000000 00000000 00000000 System_Data_ni!System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(System.String, System.String, Boolean, System.Data.SqlClient.SqlConnection, System.Data.SqlClient.SqlConnectionString, Int64)+0x215 (Managed) [f:\WhidbeyRTMlhs\ndp\fx\src\Data\System\Data\SqlClient\SqlInternalConnectionTds.cs @ 862]

Set the frame:

0:000> .frame 8
08 0021ddc8 652e9283 System_Data_ni!System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(System.String, System.String, Boolean, System.Data.SqlClient.SqlConnection, System.Data.SqlClient.SqlConnectionString, Int64)+0x215 [f:\WhidbeyRTMlhs\ndp\fx\src\Data\System\Data\SqlClient\SqlInternalConnectionTds.cs @ 862]

I then look at registers for frame 8:

0:000> r
Last set context:
eax=00000000 ebx=00000000 ecx=00000000 edx=00000000 esi=0021dc38 edi=00000000
eip=77520f34 esp=0021dbf4 ebp=0021dc5c iopl=0         nv up ei pl nz na po nc
cs=001b  ss=0023  ds=0023  es=0023  fs=003b  gs=0000             efl=00200202
ntdll!KiFastSystemCallRet:
77520f34 c3              ret

Ok, then 0021dc5c -28 and 0021dc5c -2c should be the 2 DWORDs that compose my Int64 value:

0:000> dd 0021dc5c-2c
0021dc30  76f16110 00000000

For real?  I am really not sure.  I'll try it on a live debug to be sure.

Connection Pooling, ADO.NET, SPIDs

If you have pooling issues with ADO.NET (leaking connections, pool size exceeded, etc...) one way to slice and dice these with SQL 2005 is to use the SQL 2005 system DMVs.

You can locate leaked connections like so:

select 
session_id, 
datediff(s,last_read,getdate()) as SecondsSinceLastRead,
datediff(s,last_write,getdate()) as SecondsSinceLastWrite,
(select text from sys.dm_exec_sql_text(dec.most_recent_sql_handle)) as LastSqlStatment
from sys.dm_exec_connections dec

The idea being if the connection is properly pooled then last_read and last_write should always be active.  Leaked connections will go into garbage collector and sit there for a while still open but not reading and writing.

Next suppose you find leaked ones, how can you correlate these with client code?  Here is one way:

select 
session_id as Spid, 
(select login_name from sys.dm_exec_sessions where session_id=dec.session_id) as UserName,
datediff(s,last_read,getdate()) as SecondsSinceLastRead,
datediff(s,last_write,getdate()) as SecondsSinceLastWrite,
text as LastSqlStatment
from sys.dm_exec_connections dec
cross apply sys.dm_exec_sql_text(dec.most_recent_sql_handle)

This will dump out each SPID and also the last TSQL statement to run on the SPID.  Then search your source code for the TSQL to locate which part of your code leaked the connection.  Not a perfect solution but should get you pretty close.

Suppose you are really stumped and cannot correlate the TSQL.   You can get a full memory dump of the client process (I'm assuming like an ASP.NET application here) and then dig through this using the sos debugger extension.  I'll post on this later.

Debugging .NET Code

Some days I wonder how customers debug .NET code.

I've been debugging CLR for many years using a mix of techniques (VS.NET debugger and Windbg and logging of course).  To be honest I hate debugging in VS.NET, the debugger just annoys me to no end except for very simple debugging scenarios.  Once you go Windbg it is hard to go back.

But Windbg and CLR debugging are not a match made in heaven.  They work together like Frankenstein's monster and the angry mob work together.

Today I am debugging System.Data.SqlClient and transaction behavior inside CLR stored procedures and TDS protocol.

I know that transaction state flows over TDS protocol using a combination of TDS ENVCHANGE tokens (server->client) plus TDS mars header (client -> server).

What I see is proper setup of transaction state here:

- Tds: Response from server, Version=7.2
  + TDSHeaderFirst: 0x1
  - TDSServerResponseData:
     TokenType: ENVCHANGE
   - TokenData:
      Length: 11 (0xB)
      EnvChangeType: Begin Transaction
      NewValueLength: 8 (0x8)
      NewValue: 223338299393 (0x3400000001)
      OldValueLength: 0 (0x0)
     TokenType: DONE
   + TokenData:

0x3400000001 is my transaction id (descriptor).  I see it going out on the next request:

- Tds: Query, Version=7.2
  + TDSHeaderFirst: 0x1
  - SqlBatchData:
   - AllHeadersData:
      TotalLength: 22 (0x16)
      HeaderLength: 18 (0x12)
      HeaderType: MARS Header
      TransactionDescriptor: 223338299393 (0x3400000001)
      OutstandingRequestCount: 1 (0x1)
     SQLText: exec ClrProc2 N'123'

Next my CLR sproc inserts data into a table which in turn fires a trigger which in turn rolls back the transaction ->

create trigger trig1 on t1 for insert, update as
begin
    insert t2 (f1) values (1)
    select * from t2
    rollback tran
end

It is not recommended to do this in a trigger of course!  This is only my little test to see TDS protocol behavior.  The CLR sproc is supposed to surpress TDS tokens for internally run commands, which is a nice feature.  But how does it do this and keep transaction state consistent with client?  I expected it to send back a rollback transaction state ENVCHANGE.  I don't see it over the wire.

I do see an ERROR token of state 16 coming back and I suspect this is telling SqlClient to reset transaction state.

Ok, back to debugging CLR.  I need to know when this TDS response comes back, how it resets the transaction internal state of SqlClient.  I know little to nothing about the internals of how it works, but need to figure it out and debug it quickly without wasting time.  Via some digging I found this _currentTransaction was getting set and reset when the ENVCHANGEs come in.

Now, how do I set a breakpoint on the change of _currentTransaction in VS.NET debugger?

Let the fun begin.

First, note that I have a debug version of SqlClient that I just freshly built out of my enlistment and shoved into the GAC.  This is one important way to please the temperamental gremlin living inside VS.NET debugger.  Ok, crank up debugger.

Ok, get to my code, try to step into SqlCommand.ExecuteNonQuery.   Debugger does not step into it.

Tools | Options... Debugging | General-> "Enable Just My Code" checkbox is clear.  Why does it not step in?

Ok, the gremlin does not like my System.Data.dll.  VS.NET cannot find the symbols for a dll I just build on my local machine.  The trick to fix this is to place the symbols right next to the dll so that the debugger does not have to look too hard for symbols. Or you can modify symbol path in VS.NET.  I choose to place symbols next to dll, the reload symbols from module window.

Cool, got it working.  Now how do I set a bp on change of _currentTransaction?  This seems to be the tricky part in CLR.  In Windbg I would locate address of _currentTransaction and set a ba w4 <address> on it and call it a day.  In CLR there is no equivalent as far as I can tell.  Yes, I suppose I can manually set breakpoints on every method of the class that _currentTransaction uses in hopes that the methods are used but this is difficult to do in CLR as well (in Windbg I can set breakpoint on all class methods using a wildcard in one command).  It is time to go beg the VS.NET debugger gurus to see if they can help me.

Not yet.  Let me try this VS.NET 2008 immediate window, I have been meaning to give it a try.

!help
'!help': not available while Managed only debugging.

Arg.  I found a few basic commands that work, but most Windbg commands don't work:

?_currentTransaction
null
?poi(_currentTransaction)
The name 'poi' does not exist in the current context

Ok, I give up for now. I talked to CLR folks and they indicated VS.NET does not have data breakpoints for managed code yet.  Bummer.

Writing CLR Stored Procedures

I'm trying to write some CLR sprocs to test out the TDS behavior of CLR sprocs versus T-SQL sprocs for TDS protocol documentation purposes.  If you are not aware, we now have the TDS protocol spec on MSDN for your bedtime reading: 

http://msdn2.microsoft.com/en-us/library/cc448435.aspx

Note I'm also finishing up a full blown netmon parser for TDS as part of the protocol documentation effort.  To ensure this works well I'm investigating the dusty corners of TDS protocol stream behavior to ensure we have it properly spec' d and my parser works too.

On to CLR sprocs topic.  First thing I did was crank up VS.NET 2008 and try to create a database project so I can upload some test CLR sprocs.  It doesn't work because VS.NET 2008 is smart and detects the target SQL Server is 2008 and not 2005 so it refuses to co-operate and create a nice little CLR project for me.  I suppose I could use a 2005 server but I'm lazy.

No worries, I know how to manually upload CLR sprocs with all the magic T-SQL incantations.

Next I do this:

create assembly ClrTestJunkAssembly from N'C:\Project\ADONet\TranStress\bin\Debug\TranStress.exe' WITH PERMISSION_SET=SAFE
go

Msg 10301, Level 16, State 1, Line 1
Assembly 'TranStress' references assembly 'system.enterpriseservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.

Note yes I am really really lazy and don't want to create a separate clean library project with my CLR stored proc in it.  I just want to upload my test client exe that also just happens to have the sproc class in it and get on with my life.  My other non-sproc test code happens to ref System.EnterpriseServices.  Arg.

Can I just shovel EnterpriseServices in?

create assembly EnterpriseServicesAssemblyForSQLServerWhoIsTooLazyToLookInGac from N'e:\WINNT\Microsoft.NET\Framework\v2.0.50727\System.EnterpriseServices.dll' WITH PERMISSION_SET=EXTERNAL_ACCESS
go

Warning: The Microsoft .Net frameworks assembly 'system.enterpriseservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.directoryservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Msg 6218, Level 16, State 2, Line 1
CREATE ASSEMBLY for assembly 'System.EnterpriseServices' failed because assembly 'System.DirectoryServices' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : System.DirectoryServices.DirectorySearcher::SetSearchPreferences][mdToken=0x60000f8][offset 0x0000050E][found address of value 'System.DirectoryServices.Interop.AdsSortKey'] Expected numeric type on the stack.
[ : System.DirectoryServices.SearchResultCollection::RetrieveDirectorySynchronizationCookie][mdToken=0x60001c6][offset 0x00000023][found address of value 'System.DirectoryServices.Interop.AdsSearchColumn'] Expected numeric type on the stack.
[ : System.DirectoryServices.SearchResultCollection::RetrieveVLVResponse][mdToken=0x60001c7][offset 0x00000023][found address of value 'System.DirectoryServices.Interop.AdsSearchColumn'] Expected numeric type on the stack.
[ : System.DirectoryServices.SearchResultCollection+ResultsEnumerator::GetCurrentResult][mdToken=0x60001d4][offset 0x00000033][found address of Native Int] Expected numeric type on the stack.

Arg,  you don't have to tell me everything twice, work with me brother.  Ok, I didn't like System.EnterpriseServices anyway, it was highly over-rated, so I removed it from my other code (I wasn't using it anymore anyway).

create assembly ClrTestJunkAssembly from N'C:\Project\ADONet\TranStress\bin\Debug\TranStress.exe' WITH PERMISSION_SET=SAFE
go

Msg 6212, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because method 'Main' on type 'TranStress.Program'  in safe assembly 'TranStress' is storing to a static field. Storing to a static field is not allowed in safe assemblies.

Ok, ok, SQL, I know this game.  I'm starting to get offended.

create assembly ClrTestJunkAssembly from N'C:\Project\ADONet\TranStress\bin\Debug\TranStress.exe' WITH PERMISSION_SET=UNSAFE
go

Msg 10327, Level 14, State 1, Line 1
CREATE ASSEMBLY for assembly 'TranStress' failed because assembly 'TranStress' is not authorized for PERMISSION_SET = UNSAFE.  The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.

Ok, let me try this but it makes me feel strange:

alter database transtress set trustworthy on
go
create assembly ClrTestJunkAssembly from N'C:\Project\ADONet\TranStress\bin\Debug\TranStress.exe' WITH PERMISSION_SET=UNSAFE
go

Ahh!  Now that was easy.  Next, I have to expose my function as a stored proc.  I created my CLR proc like so:

class CLRTestJunk
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void ClrProc1(string param1)
    {
        using(SqlConnection conn = new SqlConnection("Context Connection=true;"))
        {
            conn.Open();
            using (SqlCommand cmd = conn.CreateCommand())
            {
                int v = -1;
                int.TryParse(param1, out v);
                cmd.Parameters.Add("@p1", SqlDbType.Int);
                cmd.Parameters[0].Value = v;
                cmd.CommandText = "insert t1 (f1) values (@p1)";
                cmd.ExecuteNonQuery();
            }
        }
    }
}

Next I run this:

create procedure ClrProc1 as external name ClrTestJunkAssembly.CLRTestJunk.ClrProc1
go

Msg 6505, Level 16, State 2, Procedure ClrProc1, Line 1
Could not find Type 'CLRTestJunk' in assembly 'TranStress'.

Ok, now what?  I crank up reflector to see if it's there, yes it's there.

Maybe because I forgot to add the param:

create procedure ClrProc1(@param1 varchar(max)) as external name ClrTestJunkAssembly.CLRTestJunk.ClrProc1
go

Msg 6505, Level 16, State 2, Procedure ClrProc1, Line 1
Could not find Type 'CLRTestJunk' in assembly 'TranStress'.

No. no. no.  I'm not beaten yet.  Go search around web for 6505, try this:

No dice.  Ok SQL CLR, you win, I strip out a separate library class with my code.  Same problem:

create assembly ClrClassLibraryAssembly from N'C:\Project\ADONet\ClrClassLibrary\bin\Debug\ClrClassLibrary.dll' WITH PERMISSION_SET=SAFE
go
create procedure ClrProc1 as EXTERNAL NAME ClrClassLibraryAssembly.ClrClassLibrary.ClrProc1
go

Msg 6505, Level 16, State 2, Procedure ClrProc1, Line 1
Could not find Type 'ClrClassLibrary' in assembly 'ClrClassLibrary'.

Ok, SQL CLR you are making me feel stupid, I thought I was a kick butt dba until today, now you are just mocking me, thanks a ton.  I found another newsgroup entry that gave me an idea:

create procedure ClrProc1 as EXTERNAL NAME ClrClassLibraryAssembly.[ClrClassLibrary.ClrClassLibrary].ClrProc1
go

Msg 6576, Level 16, State 4, Procedure ClrProc1, Line 1
Type 'ClrClassLibrary.ClrClassLibrary' in assembly 'ClrClassLibrary' is not public.

Ohhh!  I am getting closer.  Here is my class now for reference:

namespace ClrClassLibrary
{
    class ClrClassLibrary
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void ClrProc1(string param1)
        {

To reference this in SQL Server I have to say ClrClassLibraryAssembly.[ClrClassLibrary.ClrClassLibrary].ClrProc1, not bloody intuitive but ok, I can guess why we did it this way (SQL Server's 4 part naming convention probably won out over CLR).

Ok, make my class public and away I go:

create procedure ClrProc1 as EXTERNAL NAME ClrClassLibraryAssembly.[ClrClassLibrary.ClrClassLibrary].ClrProc1
go

Msg 6550, Level 16, State 2, Procedure ClrProc1, Line 1
CREATE PROCEDURE failed because parameter counts do not match.

Ok, I forgot to add back my param:

create procedure ClrProc1(@param1 varchar(max)) as EXTERNAL NAME ClrClassLibraryAssembly.[ClrClassLibrary.ClrClassLibrary].ClrProc1
go

Msg 6552, Level 16, State 3, Procedure ClrProc1, Line 1
CREATE PROCEDURE for "ClrProc1" failed because T-SQL and CLR types for parameter "@param1" do not match.

Ok, I think I have performed full code coverage for all CLR error messages by now, one more try, the winner is:

create procedure ClrProc1(@param1 nvarchar(max)) as EXTERNAL NAME ClrClassLibraryAssembly.[ClrClassLibrary.ClrClassLibrary].ClrProc1
go

Yes!  Now to call my CLR stored procedure:

exec ClrProc1 N'123'

Msg 6263, Level 16, State 1, Line 1
Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

Whoops.  Ok, need to enable CLR:

sp_configure 'clr enabled',1
go
reconfigure with override
go

Ok, one more time:

exec ClrProc1 N'123'

Works!  Looks like VS.NET does quite a bit of nasty CLR grunt work for me.  But overall what have we learned about CLR stored procedures?

1. Make sure your class is public.
2. Avoid references to CLR modules like System.EnterpriseServices, try to keep your code as reference free as possible.
3. Avoid namespaces.
4. Ensure you enable CLR on the server.
5. Probably best to have it in it's own class library to keep things as simple as possible.
6. Don't change versions of SQL Server or VS.NET will no longer work.
7. Always learn how to do it using T-SQL versus rely on VS.NET if you want it to work anyway.

You can always sniff the TSQL that VS.NET uses to configure your CLR sproc using SQL Profiler and save this.

Why does Windows Always Want To Hide Things From Me?

Over the years, I find more and more of the Windows UI is designed to hide things like where the files are coming from, file extensions, etc... from me. Why is this?  I want to see this stuff. 

For example, why can't I get the full path to a file easily?  I have to open Run box, then drag and drop file into the Run box, then select file name, then copy it, then go paste it where I need it.   I've probably done this a million times in my life.

I found in Vista recently that drag+drop to a console window no longer works, I was heart broken, this saves me tons of time.  But I found a cool feature in Vista that makes up for it.

Hold down shift key and right-click on file and you will see a few new options on the menu bar.  See the Copy as Path below, this is the one you want.  Saves you having to pop the Run menu, very nice!

Hey, I was excited. :)

I found on Vista that whenever I had a networking problem, the Vista UI was trying very hard to hide all networking information from me.  The best it can muster is this insult to my networking abilities:

Why? This is all you have for me Vista, I ask?  I want to see random Matrix like diagnostic spew and perl scripts scrolling across the screen like in the Hollywood movies.  This will tell me Vista is doing something purposeful.  Maybe next version.

High Performance Help In VS.NET 2008

Don't know about you, but some days I'm so busy I don't have time to wait while VS.NET spawns dexplore.exe over DCOM and makes bunch of SOAP calls to MSDN to then find the MSDN url to pop context sensitive help for a keyword (yes I debugged it).  Don't get me started about updating help topics please wait 5 minutes.

I don't need more proof VS.NET help thinks Moore's Law is a challenge one should take very seriously.

Hence I wrote this VS.NET macro to speed things up for me.  Assigned keyboard Alt-F1 to fire off this ReallyFastHelp macro below.

Ok, agreed it's a bit primitive, I haven't written any VB.NET code in years, but it get's the job done.  Feel free to spruce it up.  The cool thing is it automagically opens a new tab in IE7 for me each time I shell it, leaving a nice tab for each search.  Now if I can just figure out how to make VS.NET 2008 remember my keyboard shortcuts after I close and re-open it.  Grrrr.

Imports System
Imports EnvDTE
Imports EnvDTE80
Imports EnvDTE90
Imports System.Diagnostics

Public Module Module1
    Sub ReallyFastHelp()
        Dim selection As String
        Dim shellObject As Object
        Dim shellUrl As String

        selection = DTE.ActiveDocument.Selection.Text
        If String.IsNullOrEmpty(selection) Then
            shellUrl = "http://msdn.microsoft.com"
        Else
            selection = Uri.EscapeUriString(selection)
            shellUrl = "http://search.live.com/results.aspx?q=" & selection
        End If

        Dim psi As ProcessStartInfo
        Dim startInfo As New System.Diagnostics.ProcessStartInfo(shellUrl)
        startInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Maximized

        System.Diagnostics.Process.Start(startInfo)

    End Sub
End Module

MacGruber Transactions

Found an interesting bit of code on the newsgroups the other day .... what I like to call MacGruber transactions.  If you are a fan of SNL you know the story.

With System.Data.SqlClient.SqlConnection in .NET 2.0 we added a new connection string attribute named "Transaction Binding" to alter the behavior of how SqlConnection interacts with System.Transaction.

One might believe a transaction always spans a TransactionScope block, this is technically true but the transaction can rollback at any point inside the block.  The transaction is still there but it's state has changed.

With SqlClient our default behavior is to use Transaction Binding=Implicit Unbind which means we auto unbind from the transaction scope if the transaction rolls back and switch back to standard SQL auto-commit mode.

Hence work inside the TransactionScope could be half rolled back and half committed if for example the transaction times out half way through the code block.  I recommend using Transaction Binding=Explicit Unbind since this gives you deterministic behavior.  We are thinking about changing the default to this in future releases of System.Data.SqlClient driver let me know what you think.

Easy Way To Debug SSL Issues With Web Services (Including SQL Server 2005 SOAP Endpoints)

Debugging SQL Connectivity 101

Note to self:  Things to remember to check next time I can't connect to SQL...

1. Is SQL started?  Yes.  Is the instance I'm trying to connect to started?  Yes.

2. Run netstat -aon on sql box, do I see something bound to 1433 or say my instance port 5555?  For example do I see 0.0.0.0:1433 bound? Does the pid match with pid for SQL Server?

3. If I run > telnet servername 1433 <, does this connect?  I usually try this one first it is easy and tells you if you have a firewall problem right away.  Oh Windows Firewall, you have made my life in SQL Protocols such fun.

4. Try the osql connection test using hard coded protocol and port:

      osql -E -Stcp:mysql,5555

5. Hey, try the admin port, does this work?   Why does admin port not work with osql?  We call this progress my friend.

   sqlcmd -E -Sadmin:127.0.0.1

6. Maybe I have a machine with IPv6?

      sqlcmd -E -Sadmin:::1

Not even sure if this works, but ::1 is loopback for ipv6, good to know.

7. Did I check SQL SAC?   The SQL Server Surface Area Configuration tool can totally block remote connectivity, go check it.

8. Is the SQL Browser service started?  Run -> net start sqlbrowser.  This service helps the client locate the proper port for named instances.

9. Did I change the port for my default instance from 1433 to something else?  Client is not smart enough to figure this out sometimes.  Check the ERRORLOG and see what port SQL is binding to.

10. Is IPSec services enabled?  This can break connectivity within a domain.  Run -> net start policyagent. 

11. Check SQL Server Configuration Manager and determine what ports you are really binding to.  Please forgive us for the funky GUI here, I agree it is confusing.  Read it carefully is my best advice.  I find it easier to look at the ERRORLOG to be honest that this GUI.

12. Am I using a clustered instance of SQL?  With clustered SQL, the only thing that might throw you off is that you can connect to the virtual ip as well as locally bound ips.  It can get confusing.  Avoid the local ips and use the clustered ips you see in Cluster Admistrator.

13. Did someone install a virus scanner on your SQL box recently?  Maybe it decided to block SQL from talking to the internet by blocking all sockets.  Oh yes I've seen this many times.

14. Running any unusual xprocs in SQL lately?  I have seen xprocs close our socket handles in the past, CloseHandle is a dangerous API, it does not preclude you from putting in some random value that is somebody else's handle.

15. Rootkits.  Did you know that a rootkit can piggyback the same ports you use for normal applications such as IIS and SQL and use these ports while you are also using these ports for normal activities?  And everything works fine for both your app and the rootkit?  This is exceedingly rare however, but does make a good case for regular network sniffing and intrusion checking.

I'm sure I can think of more of these, later!

Debugging Java Memory Leaks

Hello world, I am working late on the Microsoft SQL Server 2005 JDBC Driver.  Very fun job, but the stress tests are killing me.  I'm trying to run some stress tests where the JDBC driver is under heavy load for long periods of time and what I find is that working set memory keeps ramping up forever in the Java VM.

Well, my specialty is digging into memory issues, at least in the Windows world I know how memory works, but I am very rusty in the Java VM world, working at Microsoft for many years.

Anyway, I found a killer gadget from IBM that makes my life very easy.  Check it out sometime if you are a debugging geek like me.

What you do is get the latest IBM VM you can find first, and use this to run your tests.  I'm using the IBM VM that comes with trial developer version of WebSphere I'm testing some things on.  Search the Internet for HeapRoots205, it's up on the IBM site as well.  This tool totally rocks.  It's like a memory explorer for Java memory dump files.  What you do is start the VM with flags so you can dump memory.  There are even flags you can use that will allow you to dump memory when you press ctrl-c (see http://java.sun.com/developer/TechTips/2000/tt1222.html for example for a listing of the cool flags).  Run your stress from a console app and periodically hit the ctrl-c or just set a memory limit (use -Xmx200m Java Vm flag for example to limit total memory to 200MB) and let the VM run out of memory and it will dump.   Then use the HeapRoots205 tool to explore the dump file.

I wrote a little batch file that you pass in the heap dump file name to load it up:

"C:\Program Files\IBM\WebSphere\AppServer6\java\bin\java.exe" -Xbootclasspath/p:svcdump.jar -jar C:\Stress\HeapRoots205\HR205.jar -v -i %1

I've tried some other tools but HeapRoots205 appears to be the best out there I can find.

I am a bit spoiled by .NET framework and how easy it is to use the debugger extension sos and dig through memory dumps using !dumpheap etc.  The Java folks need to write some better tools for examining these dump files they are a bit primative right now.  Perhaps I am missing something obviously more cool out there (this is probably the case!).  If anyone out there knows of a cooler memory debugging tool for Java let me know.