...
Server type: Database Engine
Server name: sql.hic-tre.dundee.ac.uk
Authentication: SQL Server Authentication (not Windows Authentication)
Login: your project ID as it appears under “Project” for your workspace (e.g. project-XXXX)
Password: leave this emtpy
Check Trust server certificate
| Note |
|---|
Note that on earlier versions of SQL Management Studio, it was not necessary to select “Trust Server Certificate” - Workspaces created from late July 2024 may require this. |
After pressing Connect, you will be able to see the server address on the left under Object Explorer. Using the database tree, you can navigate to your database and perform queries.
...
| Code Block | ||
|---|---|---|
| ||
> 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 |
...
| Code Block |
|---|
* (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;TrustServerCertificate=Yes"
* 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'") |
...
| Code Block | ||
|---|---|---|
| ||
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() |
...
| Code Block | ||
|---|---|---|
| ||
Add-OdbcDsn -Name "ProjectXXXX" -DriverName "SQL Server" -DsnType "System" -SetPropertyValue @("Server=sql.hic-tre.dundee.ac.uk", "Trusted_Connection=Yes", "Database=ProjectXXXX") |
...
| Code Block |
|---|
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; |
...
For queries or comments regarding HIC How To Articles contact, HICSupport@dundee.ac.uk
\uD83D\uDCCB Related articles
...