I spent the last day and a half trying to figure out why a query using FOR XML AUTO was returning the wrong result set. Some background is in order. I’m migrating a database from SQL 2000 to SQL 2005, as well as running the same database created from scripts on SQL 2005 but in a different environment. Both databases contain the exact same SQL in the stored procedure that was the source of the problem, but they were returning different result sets. I even restored the database that worked to the server that had the non-working database and saw that the database created from scripts was still working as expected.

I figured there was a database level setting causing the issue, so I opened up the database properties for both databases and compared them side by side, except I missed the 3 drop down boxes at the top, specifically the compatibility mode. I finally decided to run the SQL 2005 Update Advisor, which comes on the install media, or available online as a download. After about 10 minutes of checking my database (it was across a remote connection), it found the exact problem I was having, and the fix was to get rid of my derived tables, or set the comparability mode to 90.

From the Upgrade Advisor help, here is an example of my original problem.

Consider the following table:

CREATE TABLE Test(id int);
INSERT INTO Test VALUES(1);
INSERT INTO Test VALUES(2);

Now run this query, which produces different results under different compatibility levels.

SELECT * FROM 
   (SELECT a.id AS a, b.id AS b 
    FROM Test a JOIN Test b ON a.id=b.id)
AS DerivedTest 
FOR XML AUTO;

Under Compatibility Level 80 you get:

<a a="1"><b b="1"/></a><a a="2"><b b="2"/></a>

Under Compatibility Level 90 you get:

<DerivedTest a="1" b="1"/><DerivedTest a="2" b="2"/>

The XML under Level 90 is what I was looking for. Remmeber, under SQL 2000, the same query worked fine, it was only under SQL 2005 with Level 80 that I started having problems.

Of course after switching to level 90 I found another bug, and wouldn’t you know it, the compatibility wizard told me about this one as well, I just felt like ignoring it at first. This time it was a problem with a table prefix in an order by clause. Funny thing is, the table prefix didn’t even exist in the stored procedure, but under SQL 2000 it worked fine. Turns out someone else had already fixed the issue in the SQL 2005 version.

While looking for the stored procedure in error (since at first I ignored the warning in the upgrade advisor which even told me what stored procedure), I found a quick way to search stored procedures and functions in SQL 2005.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%SearchString%' 

Lesson of the day, try using and paying attention to the tools that Microsoft gives you.

Late last year I posted briefly on the SQL Publishing Wizard. I haven’t had a need for this since I reformatted and installed Server 2008 64 bit as my main workstation at work, and wouldn’t you know it, it’s not there now.

As usual I headed off to Google and found some posts that pointed to this install path, C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\1.2. Seeing as I am running a 64 bit Windows OS, my path is actually C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.2. Now you could just run SqlPubWiz.exe, or run the VSInt.reg file to add the context menu option in Visual Studio 2008.

Not so fast, the registry file doesn’t seem to work as is on a 74 bit OS due to the new subkey, Wow6432Node. I modified VSInet.reg to include the keys the Wow6432node, and in the end got my Publish to Provider context menu option back.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VWDExpress\9.0\Menus]
"{40d75537-ce10-4311-a7b0-6b164d80405d}"=",1000,1"
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\9.0\Menus]
"{40d75537-ce10-4311-a7b0-6b164d80405d}"=",1000,1"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\VWDExpress\9.0\Menus]
"{40d75537-ce10-4311-a7b0-6b164d80405d}"=",1000,1"
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\VisualStudio\9.0\Menus]
"{40d75537-ce10-4311-a7b0-6b164d80405d}"=",1000,1"

Just found a blog post by the Microsoft SQL Server Development Customer Advisory Team  entitled How can SQL Server 2005 help me evaluate and manage indexes?. This post goes over 5 ways to analyze your index usage in SQL 2005. There looks to be some good technical information on this blog, worthy of any DBA in training.

My domain controllers, and thus my member servers and workstations were having some serious time drift. I’ve seen +/- 30 minutes this week so far. I found a good article from Microsoft on how to enable a computer (including domain controllers) to sync to an external time source (http://support.microsoft.com/kb/816042). I’m running two instances of Server 2008 server core, so I used the remote registry functionality to set the registry keys for w32time and restarted. I’m syncing to north-america.pool.ntp.org (don’t forget the ,0x1 at the end of our peer list).

Since my domain controllers are virtual machines, I also setup one of my physical hosts to sync to an external source as a backup.

It’s been awhile (4 months) since I last update my HTC mogul from the stock ROM that came with the phone to a cooked ROM and leaked radio ROM. Lately my phone has been acting kind of buggy, with having to reset it every morning to get my data connection back. So I decided it’s time to upgrade to the RTM version of the radio, and a new version of DCD’s ROM.

I will be highlighting the steps I outlined in my last post Cooked ROMs on the HTC Mogul, which seems to get quite a few hits from the search engines. Here are some additional links with good information:

• ROM/Radio compatibility guide on Xda Developers
• HTC Titan Wiki on Xda Developers

Here are my updated steps for updating my phone

1. Download all required files
   • DCD Rom 3.2.2 (listed in forums not wiki)
   • Sprint CAB File
   • Olipro 2.4 (I already had this bootloader installed on my phone).
   • Titan Radio ROM 3.42.30 (listed in the forums, not wiki)
2. Write down your MSID, MSD and AKey settings from ##778#. If you do not know your MSL, you will need to get the SPC program.
3. Write down data from ##3282
4. Format your non-SDHC (smaller then 2GB Secure Digital Card) as FAT32. I backed up the contents of my SD card and formatted it clean for this process.
5. Extract the Titaimg.nbh file from the Sprint Radio Rom rar file and copy to your SD card. The file must be named Titaimg.nbh (notice there is no N, it’s not titaN).
6. Load the ROM by launching the boot loader using the power+camera+reset combination
   1. Wiki states that the update will stop 5 times for 9 seconds each time, so don’t freak out.
   2. When it says update complete, update success, do a soft reset and the the phone boot. You can verify the Radio version under Device Information in your System Settings page.
7. Extract the RUU_signed.nbh from DCD’s 3.2.2 executable you downloaded using winrar. Rename this file TITAIMG.nbh and copy to your SD card overwriting the previous .nbh file.
8. NOTE: You are about to wipe all data from your phone so back up what you need.
9. Reboot into the boot loader with your SD card loaded.
   1. Soft reset when you see update complete/update success
10. Complete the touch screen calibration, but perform a soft reset before the customization crap starts.
11. Run the sprint carrier cab on your device and then reset. This will let you access the ##778# and ##3282# screens. If you can’t use your phone or data connection, access the ## screens and re-enter the data you copied down in step 2

Step 11 thru me for a loop until I remembered I had downloaded that carrier cab and should probably install it. I miss the default gray theme that I had in the last ROM, and it takes awhile to load everything back up on my phone. I hope that my daily reset requirement is no more, or I will be a little disappointed in my time invested in this project.

We have some WSE 2.0 secured Web Services that we are migrating to a new data center. As part of this migration effort I needed to write some automated tests to make sure everything was working. In addition to working with the WSE 2.0 clients our customers have, I also wanted to see what was needed to make the services consumable by WCF clients. After finding this article on WSE 2.0 Interoperability on http://wcf.netfx3.com, it was pretty easy.

The main problem I was having was issues with the username token. In WCF some basic concepts were changed, such the removal of password encryption (instead recurring the transport, https for example to handle encryption), and replay detection.  

Here is the config section from the WSE 2.0 server piece (the diagnostic piece is optional)

<microsoft.web.services2>
   <security>
      <securityTokenManager xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" qname="wsse:UsernameToken">
         <replayDetection enabled="false"/>
      </securityTokenManager>
   </security>
   <diagnostics>
      <trace enabled="true" input="logs\InputTrace.webinfo" output="logs\OutputTrace.webinfo" />
   </diagnostics>
</microsoft.web.services2>

Here is a sample configuration section for the WCF Client:

<system.serviceModel>
        <bindings>
            <basicHttpBinding>
                <binding name="InventorySoap" closeTimeout="00:01:00" openTimeout="00:01:00"
                    receiveTimeout="00:10:00" sendTimeout="00:01:00" allowCookies="false"
                    bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"
                    maxBufferSize="65536" maxBufferPoolSize="524288" maxReceivedMessageSize="65536"
                    messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"
                    useDefaultWebProxy="true">
                    <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"
                        maxBytesPerRead="4096" maxNameTableCharCount="16384" />
                    <security mode="TransportWithMessageCredential">
                        <message clientCredentialType="UserName" algorithmSuite="Default" />
                    </security>
                </binding>
            </basicHttpBinding>
        </bindings>
        <client>
            <endpoint address="https://Server/Directory/Service.asmx"
                binding="basicHttpBinding" bindingConfiguration="InventorySoap"
                contract="MonitoringServices.Inventory.Wcf.InventorySoap"
                name="InventorySoap" />
        </client>
    </system.serviceModel>

Most of the client configuration was created for me when I added a service reference to my project. The key part is the mode=TransportWithMessageCredential and clientCredntialType = “UserName”.

We have another project underway were we want to rewrite the WSE 2.0 services with WCF while maintaining full compatibility with deployed WSE 2.0 clients. The netfx3 article covers this scenario as well, and it seems like we will be able to accomplish our task. Look for a future post on that endeavor.

Today I tried to use the web recorder in Visual Studio to create some new web tests, but the recorder tool bar would not show in IE. A quick search found Diagnosing and fixing Web Test recorder bar issues, by Michael Taute which had several troubleshooting tips.

I ended up changing my home page which is about:blank (and loads when web recorder is launched) to belong to the local intranet zone, as this is where my test site is. That didn’t fix the problem initially, so I deleted the following two registry keys using the 32 bit registry editor (C:\Windows\SysWOW64\regedit.exe):

• HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\Discardable\PostSetup\Component Categories\{00021493-0000-0000-C000-000000000046}
• HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\Discardable\PostSetup\Component Categories\{00021494-0000-0000-C000-000000000046}

You need to make sure all instances of IE are closed before deleting the registry keys. Once I did this, my web recorder was up and running. I’ve left about:blank in the local intranet zone for now.

Once again I need to generate some self signed certs for local development. Installing certificate services in Windows is a bit of an overkill for what I need, however, it would be nice to have a root CA to work with.

After some Googling, I found a site, http://ssl4net.com/ which allows you to create a self signed root CA, and then additional certs signed by the CA you created. It is extremely easy to use and once I got the certs I was able to install them without issue.

My only concern is that the certs do not appear to be encrypted on their server, nor do they use SSL (seems ironic). However, since I will only be using this for internal use, I have no problems with this. I would be a little cautious if I was going to use to verify my identity to other people.

It looks like they might be using open-ssl as the cert creation mechanism, so maybe it’s worth the effort to setting up a VM running linux to generate my own certs in house, but that’s for another day.

Monday morning I brought (rather I biked) my laptop back into the office after reformatting and installing Windows Server 2008 on it. FolderShare seems to be holding up rather well, but I ran into an unexpected issue, no Bluetooth. Not just no Bluetooth drivers, there is no bluetooth support in Server 2008.

Thankfully I found a good guide written by Gill that explains how he installed the Microsoft Bluetooth stack on windows server 2008. His guide, and a comment by Crispin Wright, was what I needed to get Bluetooth running on my HP nx9420 business class laptop.

First I followed Gill’s steps for coping the files to a temp folder and changing the INF files. The following list of files I was unable to find in the folder specified, but it turns out they were in the folder created by HP’s Bluetooth driver setup (kind of following Crispin’s instructions for his dell).

• btwaudio.inf
• btwaudio.sys
• btwavdt.inf
• btwavdt.sys
• btwrchild.inf
• btwrchid.sys

I also wrote a powershell scrip to go thru and modify the .inf files.

#Executes a find and replace on all files in the path $includes
#You can pass in a path with a wild card (c:\temp\*.txt)
#Credit: http://www.aaronlerch.com/blog/2007/03/powershell-replace-string-function.html 

function Replace-String($find, $replace, $includes)
{
get-childitem $includes | 
  select-string $find -list |
  % { (get-content $_.Path) |
  % { $_ -replace $find, $replace } |
  set-content $_.Path }
} 

#Actual find replace for blue tooth inf files
Replace-String "amd64...1" "amd64...3" ".\*.inf"

One thing I do was run the HP provided INF files thru the Powershell script as well before I could get the drivers to update. The device I updated first in the Device Manager was the HP Integrated module with a hardware id of USB\VID_03F0&PID_171D&REV_0100. Once this device was updated, then the other blue tooth devices were found, and I just pointed them to my temp BT folder with the updated INF files.

I still have one unknown device, and it appears to be for the Bluethooth PAN profile, but I have been unable to find a driver for it. I thought that I would try running the setup application provided by HP to install their drivers, but even after that, I was still left with that unknown device, so I would recommend not running the HP setup program, as it appears to provide no added benefit.

While installing my Bluetooth keyboard and mouse, I was prompted for drivers, and again, pointed to my temp BT folder to install the HID drivers. I’ve since rebooted a couple of times, and my mouse and keyboard continue to work. I don’t have any other Bluetooth devices to test at this time.

UPDATE:

Looks like I was in a hurry and missed coping the bthpan.inf file in the first step. Gill asked me to take a look again, and sure enough, I hadn’t copied it to my BT temp folder, so it never got updated. Once I made the change, I was able to get the PAN driver installed.

Thanks again Gill.

I’m in the process of setting up my work laptop after reformatting and installing Server 2008 64 bit (a change from Vista 32 bit). My primary reason for this was to continue my Hyper-V evaluation that I started at home. I just got an upgrade in the form of 4 GB of ram and a new external hard drive with a FireWire (FireWire 400/800, USB 2.0 and ESATA) for my laptop.

I’ve been using Microsoft Groove for folder synchronization between my home and work computers. Much to my surprise after getting Groove installed and my account moved over to my laptop, I was greeted with an error message indicating that folder synchronization was not supported on a 64 bit OS!?!?!

I think the first hit I got on Google summed up my thoughts, “You got to be kidding?”. Alas, they were not. So I’m back to using Folder Share, which has gotten an upgrade from when I stopped using it just over a year ago. It does in fact support 64 bit, and actually has better limits then Groove, including a max file size of 2GB and 10,000 files per folder. The only major limitation is that you can only have 10 folders shared, but I only need 5-6, maybe 7, so it’s good enough for me right now.