6
$\begingroup$

I'm wondering if it's possible to how to connect to MSSQL 2017?

I am running Mathematic 12 on Windows 10 x64.

Verified MSSQL was working perfectly using SSMS. Everything worked perfectly (as it has for the past 5 years).

Tried this, no luck:

Needs["DatabaseLink`"];
conn = OpenSQLConnection[
  JDBC["Microsoft SQL Server(jTDS)", 
   "localhost:1433/MSSQLSERVER2017"], "Username" -> "sa", 
  "Password" -> "mypassword"]
----
JDBC::error: Network error IOException: Connection refused: connect

I've also tried the wizard, which returned an error, no matter which settings I tried:

conn = OpenSQLConnection[]

I definitely have a jTDS driver installed:

JDBCDriverNames[]
{"Microsoft Access(UCanAccess)", "Derby(Embedded)", "Derby(Server)", \
"Microsoft Excel", "Firebird", "H2(Embedded)", "H2(Memory)", \
"H2(Server)", "hsqldb", "HSQL(Memory)", "HSQL(Server)", \
"HSQL(Server+TLS)", "HSQL(Standalone)", "HSQL(Webserver)", \
"HSQL(Webserver+TLS)", "jtds_sqlserver", "jtds_sybase", "mysql", \
"MySQL(Connector/J)", "PostgreSQL", "SQLite", "SQLite(Memory)", \
"Microsoft SQL Server(jTDS)", "Sybase(jTDS)", "Microsoft SQL \
Server(jTDS)", "Microsoft SQL Server(jTDS)"}

I'm a bit stuck until I can get this working - no data, no analysis.

Improve this question
$\endgroup$
3

1 Answer 1

Reset to default
6
$\begingroup$

Got it.

Turn on TCP/IP in SQL Server Configuration Manager:

SQL Server 2017         C:\Windows\SysWOW64\SQLServerManager14.msc
SQL Server 2016         C:\Windows\SysWOW64\SQLServerManager13.msc
SQL Server 2014 (12.x)  C:\Windows\SysWOW64\SQLServerManager12.msc
SQL Server 2012 (11.x)  C:\Windows\SysWOW64\SQLServerManager11.msc

Do a test connection with SSMS:

enter image description here

Then in Mathematica:

Needs["JLink`"];
Needs["DatabaseLink`"]
conn = OpenSQLConnection[
  JDBC["net.sourceforge.jtds.jdbc.Driver", 
   "jdbc:jtds:sqlserver://PHIENGINE:1433/;instance=MSSQLSERVER2017"], 
  "Username" -> "sa", "Password" -> "mypassword", "Catalog" -> "DatabaseName"]

Then select from a table in database DatabaseName:

SQLSelect[conn, "TableName", MaxRows -> 10]

And execute arbitrary SQL:

SQLExecute[conn, "SELECT TOP 10 * FROM TableName"]
Improve this answer
$\endgroup$
3
  • $\begingroup$ Curiously enough, the documentation doesn't mention this, and none of the other Mathematica answers worked either. I discovered this precise syntax by complete frustration, trial and error over the space of about 2 hours. Mathematica mods, perhaps you could update the docs for MSSQL Server 2017? And perhaps add support for ODBC drivers as well, considering the difficulty that JDBC drivers can sometimes present? $\endgroup$
    – Contango
    Jun 10, 2019 at 21:04
  • $\begingroup$ You should send your suggestions to the support team at WRI - that will have more impact than asking here. Well done on finding the answer! $\endgroup$
    – Carl Lange
    Jun 10, 2019 at 21:17
  • $\begingroup$ Thanks, will do. I purchased a license for Mathematica 12 a few months ago, so I guess I can do that if I get too stuck. I've posted some notes on the relevant docs pages within Mathematica, so hopefully these pages will be updated and future users will not have to navigate the same path that I did. $\endgroup$
    – Contango
    Jun 10, 2019 at 21:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.