Page Comparison

> 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

* (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'")

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()

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

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;