How to Connect to a Data Source from STATA in Enclave Windows VDI

Posted on
Updated on

November 21, 2024

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.

Requirements

  1. 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.
  2. 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.
  3. You are using the 64-bit ODBC Data Source Administrator.
  4. 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

odbc list

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

Limitations

Please Note:  The above code was executed successfully using STATA/SE 16.1.

Related References

  1. Connect to an ODBC Data Source (SQL Server Import and Export Wizard) - SQL Server Integration Services (SSIS) | Microsoft Docs
  2. Excel® import and export
  3. Importing and exporting text-delimited data
  4. Formatting cells in Excel
  5. How To Import Excel® data into Stata®
  6. How To Copy/paste data from Excel® into Stata®
  7. How To Create Word documents from within Stata
  8. How To Create PDF reports from within Stata

Additional Resources

Go to KB0039848 in the IS Service Desk

Related articles