Connecting From Code

Set the "ODBCSYSINI" environmental variable, then launch Stata and establish an ODBC connection:

$ export ODBCSYSINI=~/.odbc/ 
$ module load stata/15 #compatible with Stata 15+ on KLC 
$ xstata-mp 
. set odbcmgr unixodbc 
. odbc load, dsn("kdc-tds") user("kellogg\YOUR_NETID") password("PASSWORD_HERE") exec("SELECT TOP 1000 * FROM Census.dbo.CensusTract")

Prior to running your code, the Stata executable should be opened either by right-clicking the application and selecting "Run as different user" or using the "runas" command from the command line (ex. runas /netonly /user:KELLOGG\YOUR_NETID "c:\path\Stata.exe").

The following code will open connection to KDWH02 database on KDC02 server and execute enclosed query code. The results will be downloaded to STATA and saved as local variables:

clear
odbc load, connectionstring("DRIVER={SQL Server};SERVER=kdc02\kdwh02;DATABASE=RAC;") exec("select * from Exam where year(datetaken)=2006")

This code may be adjusted for any other database (replace RAC with your name). /

Below version allows breaking line in SQL section.

clear
#delimit ;
odbc load, connectionstring("DRIVER={SQL Server};SERVER=kdc02\kdwh02;DATABASE=Dataquick;")
exec("
select top 100 * from History
");
#delimit cr

Set the "ODBCSYSINI" environmental variable, then launch python and establish an ODBC connection:

$ export ODBCSYSINI=~/.odbc/ 
$ python 
import pyodbc 
USER='kellogg\\YOUR_NETID' 
PASSWORD = 'PASSWORD_HERE' 
cnxn = pyodbc.connect('DSN=kdc-tds;Database=CME;UID='+USER+';PWD='+PASSWORD) 
cursor = cnxn.cursor() 
cursor.execute("select * from information_schema.tables") 
row = cursor.fetchone() 
print(row)

Prior to running your code, the Python executable should be opened either by right-clicking the application and selecting "Run as different user" or using the "runas" command from the command line (ex. runas /netonly /user:KELLOGG\YOUR_NETID "c:\path\python.exe").

import pyodbc
 
cnxn = pyodbc.connect(
    r'Trusted_Connection=yes;'
    r'DRIVER={SQL Server};'
    r'SERVER=kdc02.kellogg.northwestern.edu;'
    r'PORT=1443;'
    r'DATABASE=CME;'
    )

cursor = cnxn.cursor()
cursor.execute("select * from information_schema.tables")
row = cursor.fetchone()
print(row)

Configure Microsoft SQL Server JDBC for Windows.

Prior to running your code, the MATLAB executable should be opened either by right-clicking the application and selecting "Run as different user" or using the "runas" command from the command line (ex. runas /netonly /user:KELLOGG\YOUR_NETID "c:\path\matlab.exe").

% Connect to KDC02 database server and use CME database
con = database('CME','','','com.microsoft.sqlserver.jdbc.SQLServerDriver','jdbc:sqlserver://kdc02.kellogg.northwestern.edu\kdwh02;databaseName=CME;integratedSecurity=true;');

% Fetch data from the database.
e = exec(con,'select top 100 * from dbo.T_FEOD');
x = fetch(e);

%result will be in data variable
data = x.Data;

% Close the connection.
close(con);

KLC: include the following line in ~/.matlab/VERSION/javaclasspath.txt:

/kellogg/bin/jtds-1.3.1.jar

Linux: configure Microsoft SQL Server JDBC for Linux.

Mac: configure Microsoft SQL Server JDBC for macOS.

Within MATLAB:

>> password=input('Enter your NetId password: ', 's');
>> # to protect your password, right click and delete this entry from the command history

>> # the following example uses the Census database:
>> conn=database('Census', 'YOUR_NETID', password, 'net.sourceforge.jtds.jdbc.Driver', 'jdbc:jtds:sqlserver://kdc01.kellogg.northwestern.edu:1433;databaseName=Census;domain=kellogg;useNTLMv2=true;')
>> cursor = exec(conn, 'SELECT TOP 1000 * FROM Census.dbo.CensusTract')
>> cursor = fetch(cursor);
>> fetch(cursor).Data

Prior to running your code, the R executable should be opened either by right-clicking the application and selecting "Run as different user" or using the "runas" command from the command line (ex. runas /netonly /user:KELLOGG\YOUR_NETID "c:\path\R.exe").

install.packages("RODBC")
library(RODBC)
dbhandle <- odbcDriverConnect('driver={SQL Server};server=kdc02\\kdwh02;database=CME;trusted_connection=true')
res <- sqlQuery(dbhandle, 'select * from information_schema.tables')
odbcClose(dbhandle)

Set the "ODBCSYSINI" environmental variable, then launch R and establish an ODBC connection:

$ export ODBCSYSINI=~/.odbc/
$ R
 install.packages("RODBC")
 library(RODBC)
 conn <- odbcConnect("kdc-tds", uid="kellogg\\YOUR_NETID", pwd="YOUR_PASSWORD")
 res <- sqlQuery(conn, "SELECT TOP 10 * FROM Census.dbo.CensusTract")
odbcClose(conn)