JDBC: SQL Server and Named Instances

It’s been a while since I worked with Java and especially JDBC. Today I spent¬† a while trying to get the Oracle BI Publisher to connect to a SQL Server 2005 instance.

I found various examples of different ways to define the instance name in the connection string. After trying them all I could only conclude that defining the instance specific port number is the only way to get a connection established.

jdbc:hyperion:sqlserver://<server>:<port>;DatabaseName=<database>...

Reading the MSDN documentation a bit closer also revealed that it is the recommended way.

How do I know what the instance specific port number is? Easy!

  1. Fire Up the SQL Server Configuration Manager
  2. Expand the the SQL Server 2005 Network Configuration node
  3. Click on the instance you want
  4. Double click on the TCP/IP item
  5. Switch to the IP Addresses tab

The port number needed is the item named TCP Dynamic Ports.

Server Error in ‘/tfs’ Application

Setting up Team Foundation Server 2010 RC was easy, especially when selecting the Basic profile as it does not require you to setup SharePoint and the SQL Server Reporting Services. Everything on the Visual Studio and source control side worked well.

But when I fired up my browser the have a look at the Web Access now a partof the standards TFS, IIS prompted me with the following error:


I was not sure if the Basic profile included the Web Access as I could not find any info on it on the web.¬† The IIS diagnostic tools suggested the server had problems accessing the folder so I tried all sorts of things with the security settings. By googling I found some solutions for older IIS versions, but I didn’t want to do anything that would e.g. prevent me from updating my TFS setup later.

Finally I added a bug report to Microsoft Connect and as the solution they suggested was the same I had found earlier, so I tried it.

For some reason the installation of the web access web application had gone wrong. All I needed to do to fix the problem was to convert the web folder into an application:

Seconds later I could access projects using the browser!

Unfortunately my problems with the Web Access did not end there. At the moment I’m unable to create child work items using the web access interface:

Again, I’ve made a bug report on it. I will edit this post when I find a solution. If you are having any of these problems, give my bug reports your vote!

SQL Server CE 3.5: ISNULL not supported

Working with SQL Server CE 3.5 I noticed that it does not support the ISNULL function. Instead you have to use the COALESCE function to achive the same thing.

The coalesce function can be used much in the same way as the isnull function. The coalesce function returns the first none-null value in the parameter list.

This will give you all the rows where some_id_field is not null and equals the @filtering_id parameter:

SELECT some_value WHERE COALESCE(some_id_field, -1) = @filtering_id;