I’ve been able to take a break from my regularly scheduled duties and spend some time working with R.  This is a short log of what I did to get it working.

The main things I’m looking to do is regression modelling from a large dataset I have in postgresql and various stats calculations on some business data I have in SQL Server.  Today I got to the stage in my R learning where I wanted to hook up the databases.

My setup:

• R version 2.12.0 on windows 7
• postgresql 8.4.5 on ubuntu server, requiring SSL
• MS SQL Server 2005 on Windows 2003

R connects to databases via RJDBC, which (surprise) uses JDBC.  You need to download JDBC drivers for each server, and then can load those up inside R.

1. Install RJDBC
   1. Open R
   2. Packages -> Install package(s)
   3. pick a mirror near you
   4. select RJDBC
2. install JDBC driver for MSSQL
   1. I used jtds: http://jtds.sourceforge.net/ (there is also a Microsoft provided driver I didn’t hear about until I was done)
   2. download and unzip
   3. note the path to the jtds jar file (hereafter referred to as $JTDS and the jar filename
   4. open http://jtds.sourceforge.net/faq.html#driverImplementation, which has some magic strings JDBC wants
   5. optional – copy $JTDS/(x64|x86)/SSO/ntlmauth.dll into your %PATH% if you want to use windows authentication with SQL Server
3. install JDBC driver for Postgresql
   1. Download from http://jdbc.postgresql.org/
   2. note the path to the jar file (hereafter referred to as $PG) and the jar file name
   3. open http://jdbc.postgresql.org/documentation/head/load.html, which has some magic strings JDBC wants

Then, to connect with MSSQL:

> library(RJDBC)
> mssql <- JDBC("net.sourceforge.jtds.jdbc.Driver", "$JTDS/jtds-1.2.5.jar", "`")
> testdb <- dbConnect(mssql, "jdbc:jtds:sqlserver://host/dbname")
> typeof(dbGetQuery(testdb, "SELECT whathaveyou FROM whither"))
[1] "list"

And you’re off and running with a list of your results in a list and can do whatever you like.

Now for postgresql+ssl:

> pgsql <- JDBC("org.postgresql.Driver", "$PG/postgresql-9.0-801.jdbc3.jar", "`")
> testdb <- dbConnect(pgsql, "jdbc:postgresql://host/dbname?ssl=true", password="password")
> typeof(dbGetQuery(testdb, "SELECT whathaveyou FROM whither"))
[1] "list"

The connection here has a lot more options, and depends highly on your server’s pg_hba.conf.  It took a little while figure out the “?ssl=true” bit.  Luckily you get pretty descriptive error messages if you can’t connect, and the PostgreSQL JDBC docs are pretty good.

Now to re-learn everything I once knew about regression modeling!

I have a set of python scripts to help me manage a few SQL Servers at work, and one of the things I do is take database backups using BACKUP DATABASE and BACKUP LOG.  I’ve been using pymssql to connect, but today tried switching to pyodbc.  pymssql seems to be having momentum problems, so I figured I’d try the alternative and see how well it works.

I ran into two issues:

• pymssql supports “%s” as a parameter placeholders, and pyodbc wants “?”
• BACKUP and RESTORE operations on SQL Server don’t run like normal queries

The first was trivial to fix, the second took some digging.  If you try to run a BACKUP via pyodbc, the cursor.execute() call starts and finishes with no error, but the backup doesn’t get made.  With help from CubicWeb‘s post MS SQL Server Backuping gotcha, I learned that BACKUP and RESTOREs over ODBC trigger some kind of asynchronous / multiple result set mode.  To get around this, you can poll for file size (as Cubicweb did), but that gets ugly when making a backup on a remote server.

In a backup, I think each “X percent processed.” message is considered a different result set to ODBC. Instead of polling the file size, you can call cursor.nextset in a loop to get all the “percent processed” sets:

After adding that while loop, backups of small and medium sized databases worked like a charm.

A new decade means time for a fresh windows install at work.  I ran into some trouble with windows 7, visual studio 2008, and SQL 2008 Express.  Here’s how I resolved them.  Contrary to most things I found on the web, I’m not using betas or release candidates.

First off, installing SQL 2008 Express.  I only wanted the management tools, and this was a little hard to come by.  I downloaded various EXE files from MSDN, but none of them worked (they would error out, bring up an seemingly unrelated installer, or any other confusing behavior that may have led you here).  Here’s what worked for me:

1. Be sure any previous installation attempts have been purged via Add / Remove Programs
2. Go to the “other install options” page for SQL express: http://www.microsoft.com/express/Database/default.aspx#Installation_Options
3. Click the “Management Tools” install button (for me that’s: http://www.microsoft.com/web/gallery/install.aspx?appsxml=www.microsoft.com%2Fweb%2Fwebpi%2F2.0%2FWebProductList.xml%3Bwww.microsoft.com%2Fweb%2Fwebpi%2F2.0%2FWebProductList.xml&appid=134%3B135)
4. Install the “Microsoft Web Platform Installer” (MWPI) if it asks you to
5. Should be straightforward from here on

The funny thing here is the MWPI seems to download an installer that looks a lot like the one at Microsoft® SQL Server® 2008 Management Studio Express that didn’t work for me.

Next up, Visual Studio 2008 (VS2008).  My company has an MSDN subscription, so we downloaded an ISO (named en_visual_studio_2008_professional_x86_x64wow_dvd_X14-26326.iso) and I used freeware MagicISO to mount it, then ran “setup.exe”.  The install failed on the “Microsoft Visual Studio Web Authoring Component” (MVSWAC).  Here’s what worked for me:

1. IF YOU WANT SQL2008, DO THAT FIRST
2. Be sure any previous installation attempts have been purged via Add / Remove Programs
3. Download WebDesignerCore.EXE from microsoft
4. Run it
5. Install VS2008 from disc/iso as normal.

Digging into the ISO using 7zip, the problem is /WCU/WebDesignerCore/WebDesignerCore.EXE is corrupt.  To get VS2008 to install cleanly, first we need to install MVSWAC, at which point the VS2008 installer will happily skip past the corrupt file.  I ran across several blog/forum posts with horror stories about VS2008 installing SQL2005, and needing to uninstall half the planet to get things working right.

As always, be sure to hit up windows update, and change your update settings so you get fixes for VS2008 and SQL2008.

An update trigger using COLUMNS_UPDATED() was failing, and I figured out the problem:

Some simple SQL:

SELECT CAST(0×0200 as bigint), 0×0200 | 0
SELECT CAST(0×02000 as bigint), 0×02000 | 0
SELECT CAST(0×020000 as bigint), 0×020000 | 0
SELECT CAST(0×0200000 as bigint), 0×0200000 | 0
SELECT CAST(0×02000000 as bigint), 0×02000000 | 0
SELECT CAST(0×020000000 as bigint), 0×020000000 | 0
SELECT CAST(0×0200000000 as bigint), 0×0200000000 | 0
I had “IF COLUMNS_UPDATED() | 0 > 0″ in my trigger, and on large values of COLUMNS_UPDATED(), bitwise ORing with zero equals zero.  Of course, “IF COLUMNS_UPDATED() | 0 > 0″ is a retarded way to say “IF COLUMNS_UPDATED() > 0″, but whatever codepath that large number triggers wigs out there to.  Try these out:

SELECT 1 WHERE 0×0200000000 > 0
SELECT 1 WHERE CAST(0×0200000000 as bigint) > 0

So, to fix my trigger, I have “IF CAST(COLUMNS_UPDATED() as bigint) > 0″, which still seems a little retarded, but at least my trigger works.

Spent too much damn time debugging the database layer in rails again today. This was the error:

OLE error code:80004005 in Microsoft OLE DB Provider for SQL Server
Cannot create new connection because in manual or distributed transaction mode.

This was getting thrown after an insert, and the problem was very non-obvious.  I found one potential solution on Occasionally Useful Software‘s post about Ruby and SQL Server, but that seemed a little heavy-handed.

Somewhere in the bowels of ActiveRecord, dbi.rb, ADO.rb, and the SQLNCLI driver, something expected one result-set per SQL command. My insert was firing a database trigger, and so the ruby stack saw two result-sets, one saying “1 row affected” for the original insert, one saying “0 rows affected” for the trigger. I’m still a little confused, as I thought those “rows affected” messages were separate from proper result-sets (the results from a SELECT, for example), but apparently those little buggers count enough.

Adding a “SET NOCOUNT ON” to the top of the trigger fixed it.