Home > Data Management > Connection string support added to odbc command

Connection string support added to odbc command

Stata’s odbc command allows you to import data from and export data to any ODBC data source on your computer. ODBC is a standardized way for applications to read data from and write data to different data sources such as databases and spreadsheets.

Until now, before you could use the odbc command, you had to add a named data source (DSN) to the computer via the ODBC Data Source Administrator. If you did not have administrator privileges on your computer, you could not do this.

In the update to Stata 11 released 4 November 2010, a new option, connectionstring(), was added to the odbc command. This option allows you to specify an ODBC data source on the fly using an ODBC connection string instead of having to first add a data source (DSN) to the computer. A connection string lets you specify all necessary parameters to establish a connection between Stata and the ODBC source. Connection strings have a standard syntax for all drivers but there are also driver-specific keyword/value pairs that you can specify. The three standard things that you will probably need in a connection string are DRIVER, SERVER, and DATABASE. For example,

odbc load, … ///
connectionstring(“DRIVER={SQL Server};SERVER=myserver;DATABASE=db;”)

If you also need to specify a username and password to get access to your database you would type

odbc load, …///
conn(“DRIVER={SQL Server};SERVER=server;DATABASE=db;UID=id;PWD=pwd;”)

Again, there are driver specific keyword/value pairs you can add to the connection string. You can perform a search on the Internet for “connection string” and your database name to find what other options you can specify in the connection string. Just remember to separate each connection string keyword/value pair with a semicolon. You can read more about connection string syntax on Microsoft’s website.

To get this capability in your copy of Stata 11, simply type update all and follow the instructions to complete the update. You can then type help odbc to read more about the connectionstring() option.

  • Pingback: Connect to MySQL database using Stata | AndrewDyck.com()

  • Ala.Frosty

    I tried this and although my (post update) documentation says that it should work, STATA complains “Dsn is required.” Interestingly, the doc was updated appropriately, but the PDF documentation was not.nnI’m attempting to connection to a SQL Server instance on the same machine as I’m running STATA. No love. I’d rather use the connectionstring, but I guess I’m stuck in ODBC-DSN land for now.n

  • http://blog.stata.com/ The Stata Blog Team

    One posibility is that your update is only partially complete; the executable portion of your update could be out of date. Type “update executable, force” followed by “update swap” to make sure your executable is fully up-to-date.nnThere are other reasons you could receive such an error depending on the capabilities supported by the ODBC driver you are using. Contact Technical Services at http://www.stata.com/support/tech-support/ and they will be happy to help.

  • http://pulse.yahoo.com/_EIQNWK33R35ZDQVV75QCWULR54 Viengkhone

    Try configure your DNS in the ODBC Data Source Administrator and test it successfully, and use it as your ODBC data source

  • Pingback: Stata-MySQL a first encounter « Stata Daily()

  • mendyk

    Can someone look at my syntax and tell me why I am getting this error?  I can access the data just fine using the standard dsn method.

     odbc load varA, table(“tblA”) conn(“Driver = {Microsoft Access Driver (*.mdb *.accdb)}; Dbq =C:mydata.accdb; Uid=; Pwd=;”) lower clear

    The ODBC driver reported the following diagnostics
    [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
    SQLSTATE=IM002

    Thanks,

    Andy

  • Guest

    Thanks for posting this tip. I needed to access an SQL Server database, and this syntax worked a treat – I just needed to add in the Table option, which Stata kindly informed me with the error message.

  • R.M

    Is there any way to add an ON DUPLICATE KEY UPDATE string to the odbc insert command? I can read the current table to a separate dataset and merge, or alternatively loop through the observations and constructing an odbc exec command for each observation, but, again, adding an appropriate option to the odbc insert command can really help here.

    Thanks!

  • Kevin Crow

    We will look into adding this.