Page Comparison

We currently provision an SQL server for each project . This ensures to ensure total separation between each TRE project. For each of these, we provide you with the following details:

...

This article explains how you can connect to your SQL database using a variety of methods - whether that be from SQL Management Studio, Azure Data Studio, R, Python or Stata:

\uD83D\uDCD8 Windows (SQL Server Management Studio, SSMS)

...

Open Microsoft SQL Management Studio from the Windows application menu. Upon launching, it will prompt for some connection details.

...

Right clicking on a table name can allow you to quickly prepare SELECT queries for quickly getting an idea of the structure of the database.

🐧 Ubuntu Linux (Azure Data Studio)

Azure Data Studio is provided as an alternative to Microsoft SQL Management Studio fro Ubuntu workspaces as SQL Management Studio is not supported on Linux-based platforms. Azure Data Studio can be found in the application menu drop down, top left. When you first open Azure Data Studio, it will take you to a welcome page. From here, click “Create a connection” and enter the appropriate connection details.

...

The remaining fields can be left blank, followed by clicking Connect. An example of this is shown to the right. Once connected, your databases will be displayed under the Databases tree on the left side of the window.

🗒 R and R Studio (odbc and DBI)

You can access your SQL databases from R directly, saving the effort required to convert this into a flat file or other R-compatible format. The command transcript below provides an example on how to do this.

> install.packages('odbc')
> library('odbc')

# Windows uses the driver name "SQL Server"
> conn <- dbConnect(odbc(),
                   Driver="SQL Server",
                   Database="Your database name",
                   Server="sql.hic-tre.dundee.ac.uk",
                   UID="project-XXXX", PWD="", TrustServerCertificate="Yes")

# Under an Ubuntu environment, the driver name has a slightly longer name
> conn <- dbConnect(odbc(),
                   Driver="ODBC Driver 18 for SQL Server",
                   Database="Your database name",
                   Server="sql.hic-tre.dundee.ac.uk",
                   UID="project-XXXX", PWD="", TrustServerCertificate="Yes")
                  
> print(conn)
<OdbcConnection> user@sqlserver
  Database: ProjectXXXX
  Microsoft SQL Server Version: 15.00.4188

> print(dbGetQuery(conn, "SELECT name, database_id FROM sys.databases"))
                 name  database_id
1              master            1
2             temp_db            2
3               model            3
4                msdb            4
5         ProjectXXXX            5
6 ProjectXXXX_Working            6

...

From within R Studio, you will also be able to see a list of databases (and their tables) by navigating to the Connections tab on the right-side pane, once an SQL connection has been opened

🔢 Stata

If you have access to Stata within your TRE environment, you can also use this to connect to your project’s SQL server. Below is an example snippet of code to help you get started opening an SQL connection and running a query. Remember to set the database name and server with the values provided when your SQL database was set up.

Please note the first two commands are operating system specific. Older Ubuntu workspaces may not work, please contact HICSupport@dundee.ac.uk asking us to patch your Stata installation.

* (Windows) Setup the string which defines our connection 
local connString "Driver={SQL Server};Database=ProjectXXXX;Server=sql.hic-tre.dundee.ac.uk;UID=project-XXXX"

* (Ubuntu Linux) Setup the string which defines our connection
local connString "Driver={ODBC Driver 18 for SQL Server};Database=ProjectXXXX;Server=sql.hic-tre.dundee.ac.uk;UID=project-XXXX"

* Verify that we are able to connect by listing the tables
odbc query, conn("`connString'")

* Perform a simple query - this could be SELECT, UDPATE, INSERT, DELETE, etc.
odbc exec("SELECT TOP 10 * FROM Table"), conn("`connString'")

* Load an entire table into memory / Stata variables
odbc load, exec("SELECT * FROM Table") conn("`connString'")

🐍 Python

The pyodbc package needs to be installed on your workspace. Since this hasn’t been deployed to all workspaces yet, you may have to open a support ticket to have it added to your workspace.

import pyodbc
import os

# Replace these with the details provided to you
sqlServer = 'tcp:sql.hic-tre.dundee.ac.uk'
sqlUsername = 'project-XXXX'
sqlPassword = ''
sqlDatabase = 'ProjectXXXX'

# Chose the ODBC driver name based on your operating system
sqlDriver = 'ODBC Driver 18 for SQL Server'
if os.name == 'nt':
    sqlDriver = 'SQL Server'

# Define the connection string and create an ODBC object
connString = 'Driver={%s};Server=%s;UID=%s;PWD=%s;Database=%s;TrustServerCertificate=Yes' % (
    sqlDriver, sqlServer, sqlUsername, sqlPassword, sqlDatabase)
odbcConn = pyodbc.connect(connString)
odbcCursor = odbcConn.cursor()

# Run a simple query to display the version string
odbcCursor.execute("SELECT @@version;")
row = odbcCursor.fetchone()
while row:
    print(row[0])
    row = odbcCursor.fetchone()

📊 SPSS

SPSS requires the creation of an ODBC data source. The simplest way to do this is to open a Windows PowerShell prompt and paste the following command, replacing ProjectXXXX with your database name and database server.

Add-OdbcDsn -Name "ProjectXXXX" -DriverName "SQL Server" -DsnType "System" -SetPropertyValue @("Server=sql.hic-tre.dundee.ac.uk", "Trusted_Connection=Yes", "Database=ProjectXXXX")

...

This will then prompt you for some authentication details - select SQL Server authentication and put “user” in the username field, then click Next. Check the box “Change the default database to” and select your database from the dropdown list, then click Next. You can then click Finish and test the datasource. This should then appear as a datasource within SPSS.

💁 SAS Viya

The sample below is enough to get a connection open and visible within the Libraries section in SAS. Remember to update the username, password (blank) and database name within the connection string.

libname mydb odbc 
	complete="Driver=ODBC Driver 18 for SQL Server;Server=sql.hic-tre.dundee.ac.uk;Uid=username;Pwd=password;TrustServerCertificate=Yes;Database=dbname;"
	schema=dbo;
	
proc sql  ;
	connect using mydb ;
	
	select * from connection to mydb (
		select * from mydb.department ;
	);
	
	disconnect from mydb;
run;  

\uD83D\uDCCB Related articles