HOWTO get business days between two dates using TSQL

Ok, so I looked around and found varying formulas for calculating the number of business days between two dates and all of them had shortcomings that made them unreliable so I went about making my own.  Now I do borrow code from http://www.kodyaz.com/articles/sql-server-dates-table-using-tsql-cte-calendar-table.aspx  to create my query.  Here is the result:

 DECLARE @TotalWorkingDays INT

 DECLARE @StartDate  DATETIME
 DECLARE @EndDate  DATETIME
 DECLARE @CurrentDate DATETIME
...

SET DATEFIRST 7;

WITH CTE_DatesTable
 AS
 (
   SELECT DATEPART (dw,@StartDate) as [dn], @StartDate AS [date]
   UNION ALL
   SELECT DATEPART (dw,DATEADD(dd, 1, [date])) as [dn], DATEADD(dd, 1, [date])
   FROM CTE_DatesTable
   WHERE DATEADD(dd, 1, [date]) <= @EndDate
 )
 SELECT @TotalWorkingDays =  COUNT([dn]) FROM CTE_DatesTable WHERE [dn] NOT IN (7,1)
 OPTION (MAXRECURSION 0);

 SELECT @TotalWorkingDays


What happens with this query is that it counts the days between the start and end dates excluding Saturday and Sunday (7 and 1 using DATEPART with the dw argument). 

Note: this query does NOT take in to consideration any holidays.

HOWTO get TiVO daily call and updates working under Solaris 11

I still use my Gen 1 TiVO because I bought it when you could still buy a lifetime subscription. The problem that I ran into when I switched to Magic Jack for phone service was that it was no longer possible to obtain updates because the dial up connection would timeout probably due to a slow data connection. So I setup an old laptop that had a bad HDD and a dead battery to run a live distro of FC which let me use the instructions found everywhere for connecting a gen 1 TiVO to your network using a serial cable (with a null modem) and Linux. Today I ran out of luck as that old laptop finally croaked and that had me wondering if I could have my Ultra 11 step up to the plate and work. Surprisingly, I was able to get everything going after a few hours of "why doesn't this work?" 

Step 1: Get a USB->Serial adapter. 

 I actually had the least amount of problems with this step. I went to Best Buy and, by some miracle, they had an open box/returned Dynex USB->Serial (DX-UDBB9). I came home wondering if I was going to have to go scouring the Internet for obscure drivers to work under Solaris, but, to my complete surprise, it worked as soon as I plugged it in without even needing to reboot. So I thought the next part would be the easiest, but it took some head scratching only because I wasn't familiar with enabling forwarding on the Solaris TCP/IP stack. 

Step 2: Enable setting up pppd on Solaris 

There are two parts to this: 

 a) Enabling forwarding of TCP/IP packets. 

 This part gave me headaches because under Linux it's: echo 1 > /proc/sys/net/ipv4/ip_forward 

Under Solaris, after much searching, I found the obscure equivalent: routeadm -e ipv4-routing -u 

 If you're running a firewall or firewall rules on your Solaris box then you may have to adjust some/all of your rule sets to accommodate this setting. I leave this to you because I don't run any firewall rules on my box since I'm behind a firewall appliance. 

 b) The actual pppd command. 

I haven't created an elaborate SMF service or anything else (yet) so I open a terminal and then execute the following (as root): pppd cua/0 115200 noauth proxyarp nocrtscts nobsdcomp nodeflate persist local lcp-max-configure 28800 asyncmap 0 passive netmask 255.255.255.0 192.168.a.x:192.168.a.y 

Where a is your local number (e.g. 0 for 192.168.0.x) 

x is an unused number on your network (e.g. 253) 

y is an unused number on your network (e.g. 254) 

cua/0 is the /dev on my Solaris box for the USB->Serial adapter. 

If you're in doubt you can launch the Device Driver Utility (located under Applications->System Tools) and look at the devfs path for your serial adapter then see what file under /dev is linked to it. 

Anyone looking for the modifications on the TiVO end of things can look here

New Oracle Solaris Studio 12.3 for Solaris 11 Install instructions (IPS)

Oracle has released new instructions for installing Oracle Studio 12.3 using IPS. This method seems to be aimed at eventually pushing users in to requiring a support contract for using the IPS version of Oracle Studio 12.3. For those with a valid support contract the instructions from Oracle are:

• Visit Oracle Certificate Requests to request an Oracle Solaris Studio certificate - http://pkg-register.oracle.com
• Per instructions at site, download and install certificate and key on your system
• Per instructions at site, add the solarisstudio publisher and install Oracle Solaris Studio 12.3

HOWTO install Oracle Solaris Studio 12.3 (release version) on Solaris 11 11/11 using pkg (not tgz)

Oracle has finally released Oracle Studio 12.3 to the public, but with one persistent gotcha that was present in the Beta: it isn't (yet) available to install using pkg on Solaris 11. I decided to figure out how to unravel the shar for Solaris 10 so that pkg could install 12.3 and it's a lot more difficult than the Beta was. To give an overview of the process you have a shar that contains a giant .jar file inside it. Once you get that .jar then you can unzip it and you will discover a bunch of .class files plus .jar files with names such as data1.jar. These .jar files actually have the IPS files needed for use with pkg.

The first problem that needs to be overcome is getting the main .jar file out of the downloaded shar. The problem here is that Oracle has made the shar so it detects both the version of Solaris being run and modifications to the shar itself. Since the shar is just a giant shell script it's actually very easy to bypass both.

Step 1: Modifying the shar
Open the shar that was downloaded from Oracle with your favorite text editor (after extracting it from the .tgz). Now modify the following:
Line 113: EXTRACT_ONLY=0
to:
EXTRACT_ONLY=1
Comment out lines 229 and 870

run: sh ./solarisstudio.sh

Step 2: Getting and installing the pkg files
unzip bundle.jar
There will now be a directory structure that's a mixture of .class and .jar files. We are only interested in the .jar files. Make a directory (e.g. mkdir ext). Now: cd data ; find . -name *.jar -exec unzip {} -d `pwd`/../ext \;
cd ../ext ; find . -exec pkgadd -d {} \;

Oracle Studio 12.3 will now be installed in /opt/solarisstudio12.3

Notes on upgrading to Solaris 11

This is just a posting about the snags I encountered while upgrading from Solaris 11 Express to Solaris 11 11/11. The biggest problem that I encountered is that I simply could not upgrade using the package upgrade utility. It would install everything fine, but once rebooted grub would complain about a missing kernel (which has a Xen label buried in it). So I decided to try to use Oracle Solaris 11 11/11 Automated Installer. That would be mistake number 2.

The automated installer not only blew away all my partitions, but, I discovered painfully, is missing more than half the packages needed to get X running (note to Oracle: I hate you). My first thought was to download the repo images on the download page and the missing binaries would be on there. No go.

The solution to getting X working on Solaris is making sure your network connection works (at least the automated installer got the config files right - mostly) and using pkg list -a. You will need to install practically everything remotely related to X including the themes and the display drivers (why does an over the wire automated installer NOT do this for me?) before you can get X working. Once you've got the X working you can then leisurely install any and all remaining packages using the graphical installer.

HOWTO install Oracle Studio 12.3Beta on Solaris 11 using pkg (NOT tgz)

The normal installer is broken so installing via GUI is not possible. However, I did some digging and a bit of guesswork which resulted in me finding the solution. The first part of the solution is to extract the installer files from the shar that you downloaded from Oracle (SolarisStudio12.3-beta-solaris-x86.sh in my case) which is accomplished by running: sh ./SolarisStudio12.3-beta-solaris-x86.sh --extract-installation-data .

This will result in 20 different files being unpacked in to the current directory. To install them you need to call them using: pkgadd -d filename. Filename is replace by each of the 20 files such as cc, c++, etc.

Kudos to Tech-Recipes for the pkgadd command. http://www.tech-recipes.com/rx/350/install-a-solaris-datastream-package/

HOWTO get Drivel working with Blogger/Blogspot

I was tearing my hair out trying to find the answer to this one because I want/need to use Drivel for posting unless I want to logout of my Google apps account. I couldn't get it going until I found this on the Drivel mailing list (http://mail.gnome.org/archives/drivel-list/2010-July/msg00002.html):
===================
blogger.com does not work as Blogger 2.0, only as blogger 1.0. I've just tested this: http://drivel-dev.blogspot.com/2010/07/another-test-for-blogger-10.html Change to Blogger 1.0, drivel will then select: http://www.blogger.com/api/RPC2 for the server address.
===================
Major kudos to Neil Williams.