November 21, 2023
This document provides guidance on how to connect to SQL server from STATA in the Windows Enclave Virtual Desktop using ODBC.
Note that this solution has been validated using the Windows 10 Enclave Virtual Desktop connection to a MS Windows SQL Server with an ODBC driver.
The Microsoft ODBC Drivers for SQL Server are stand-alone ODBC drivers which provide an application programming interface (API) implementing the standard ODBC interfaces to Microsoft SQL Server.
- Access to Enclave should already be granted and your workspace provisioned. If you do not have approval to access the Enclave Platform, complete and submit the Access Request Form.
- You should be logged on to your Enclave Windows Virtual Desktop. You cannot access the resources in the Enclave environment from outside the Enclave environment.
- You are using the 64-bit ODBC Data Source Administrator.
- You have your Data Source Name (DSN) configured correctly.
Step 1. Create a DSN
The simplest way to make a connection from STATA is using a Data Source Name (DSN). You will need to create a DSN for the data source you are trying to connect to (i.e. Complete PDSR Curated Data Set, COVID19 Mart, etc.)
Please follow the step-by-step instructions in the document, "HOWTO: Create a Connection to a Data Source in the Enclave Windows VDI", to create a DSN for your application programs, to connect to the database (if you have not done so already).
You can use STATA command, odbc list, to view the list of all the DSNs you have created to connect to your data sources.
Step 2. Creating a Database Connection using STATA Commands
ODBC works by setting up a connection or channel from the client to the DBMSs as specified in the DSN. Such connections are normally used throughout a session as shown in the example below.
The simplest way to make a connection from STATA is using DSN.
odbc exec("select getdate()"), dsn("<DSN_Name>")
Make sure you replace <DSN_Name> exactly as it appears in the Data Source Name (DSN) from Step 1 above
Below are two STATA example commands, using DSN=PDSR_Monthly (component of the Complete PDSR Curated Data Set) and DSN=i2b2 (Demo Data Source).
Example 1.1: Connect to PDSR_Monthly
Use the following ODBC-specific STATA command to test the connection to the PDSR_Monthly Data Source and access the data.
odbc exec("select getdate()"), dsn("PDSR_Monthly")
Example 1.2: Connect to i2b2 (Demo Data Source)
Use the following ODBC-specific STATA command to test the connection to the i2b2 Data Source and access the data.
odbc exec("SELECT * FROM i2b2.dbo.ACT_COVID"), dsn("i2b2")
Quick Start Codes
List all defined Data Source Names (DSNs) to which STATA can connect
List available table names in <DSN_Name>
odbc query "<DSN_Name>"
Describe the column names and data types in table <Table_Name> from <DSN_Name>
odbc describe "<Table_Name>", dsn("<DSN_Name>")
Load <Table_Name> into memory from <DSN_Name>
odbc load, table("<Table_Name>") dsn("<DSN_Name>")
Please Note: The above code was executed successfully using STATA/SE 16.1.
- Connect to an ODBC Data Source (SQL Server Import and Export Wizard) - SQL Server Integration Services (SSIS) | Microsoft Docs
- Excel® import and export
- Importing and exporting text-delimited data
- Formatting cells in Excel
- How To Import Excel® data into Stata®
- How To Copy/paste data from Excel® into Stata®
- How To Create Word documents from within Stata
- How To Create PDF reports from within Stata