Database Server Name:
kdc02.kellogg.northwestern.edu
Connecting from KLC, first install the FreeTDS ODBC driver:
$. /kellogg/bin/freetds_config
Connecting from other Linux or MacOS, install FreeTDS using these directions.
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)