Search code examples
jdbcwolfram-mathematicasql-server-2008r2-express

Opening SQL Server Express via JDBC in Mathematica?

I have two computers with two (ostensibly) identical installations of SQL Server Express 2008 R2, both running Windows 7 64-bit. On one computer (call it "red"), I can execute the following mathematica code to open a connection to the database and read data

Needs["DatabaseLink`"]

conn = OpenSQLConnection[
  JDBC[
   "Microsoft SQL Server(jTDS)",
   "localhost"],
  "Instance" -> "SQLExpress"]

I have the jTDS driver for sql server installed in c:\windows\system32\ntlmauth.dll. I copied the dll from the working machine "red" to the non-working machine "black."

I used the SQL-Server import and export tool to transfer a very simple database from "red" to "black" and verified that the database is accessible by running LinqPad on "black" and reading data. All good.

Now, I try to run the Mathematica code above on "black" and I get an undiagnosable error message, namely:

JDBC::error: "!(TraditionalForm`\"Network error IOException: Connection refused: connect\") "

I just know this is going to be one of those nightmarish permission issues with the localservice account or the network-service account. I do not have Sql-Server Management Studio on machine "black" and I was unable to find the appropriate version of SSMS to install for SQL Server 2008 R2 Express (the SSMS Express 2008 version does not install, citing "known compatibility issues.") I don't really miss SSMS since LinqPad works fine for my development tasks.

I do not know how to diagnose or workaround or proceed in any way -- I'm completely blocked and would be very grateful for advice or guidance.

Solution

  • Somewhat guessing here, but I'd try

    conn = OpenSQLConnection[ JDBC[ "Microsoft SQL Server(jTDS)", "localhost:1433;instance=SQLExpress"]]

    I'm pretty sure OpenSQLConnection[] doesn't take an "Instance" option. It might work if passed like this:

    conn = OpenSQLConnection[ JDBC[ "Microsoft SQL Server(jTDS)", "localhost:1433"], "Properties"->{"instance"->"SQLExpress"}]