Details

Use DataConnectionObject to represent data located in a Microsoft SQL database in Wolfram Language.
Import can be used to retrieve data from a DataConnectionObject .
RelationalDatabase can be used to represent the schema of a "MicrosoftSQL" database.

Properties

DataConnectionObject [assoc] creates a reference to a data source. The source does not need to exist yet.

General keys typically found in assoc are:

"ConnectionName" "MicrosoftSQL"

"Location" SQL query given as a string

"Redownload" whether to rerun the query and overwrite cached results

Required keys in assoc are:

"DatabaseName" string giving the database to access

"Host" the host servicing the database

Typical keys in assoc are:

"Port" the server port to access the database

Possible forms of "Location" when connecting to a Microsoft SQL database include:

"query" SQL query given as a string

Supported Types

BIT True or False

BINARY, IMAGE, VARBINARY ByteArray

DATE DateObject with day granularity

DATETIME, DATETIME2, SMALLDATETIME DateObject with instant granularity

DATETIMEOFFSET DateObject with instant granularity and time zone support

DECIMAL, NUMERIC Real with fixed precision

BIGINT, INTEGER, SMALLINT, TINYINT Integer

FLOAT, REAL Real

CHAR, NCHAR, NTEXT, NVARCHAR, TEXT, VARCHAR, XML String

TIME TimeObject

Account Setting & Authentication

Possible values for Authentication are:

Automatic attempt to use saved credentials or give a dialog

"Dialog" give authentication parameters via a dialog

SystemCredential ["key"] retrieve authentication information from secure storage

SystemCredentialData […] stored authentication data

assoc provide explicit credentials

Use the form Authentication :>SystemCredential ["key"] to delay evaluating securely stored credentials.

The following keys are accepted by Authentication assoc:

"Username" username

"Password" password

Examples

Basic Examples (2)

Create a DataConnectionObject to data from a Microsoft SQL query:

Wolfram Language code:

DataConnectionObject[<|"ConnectionName" -> "MicrosoftSQL", "DatabaseName" -> "dbname", "Host" -> "my.host.com", "Location" -> "SELECT * FROM users"|>, Authentication -> <|"Username" -> "reader1", "Password" -> "reader1"|>]

Retrieve the data using Import :

Wolfram Language code:

Import@DataConnectionObject[<|"ConnectionName" -> "MicrosoftSQL", "DatabaseName" -> "dbname", "Host" -> "my.host.com", "Location" -> "SELECT * FROM table"|>, Authentication -> <|"Username" -> "user", "Password" -> "pass"|>]