Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.
From | "Joseph Coveney" <jcoveney@bigplanet.com> |
To | <statalist@hsphsun2.harvard.edu> |
Subject | st: Re: changeing the database with odbc without using dialog box |
Date | Sat, 31 Jul 2010 01:40:26 +0900 |
Dimitriy V. Masterov wrote: Is there a way to tell Stata which odbc database to use without going through the dialog prompt? I am loading some data with: odbc load, dsn("MIHQDB50") table(tabWeeklyStoreItemPricingAll) clear This produces the error: The ODBC driver reported the following diagnostics [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tabWeeklyStoreItemPricingAll'. SQLSTATE=42S02 r(682); This is because the default is to use the database DataWarehouse. When I use the dialog box to change to database to Orders, where the table I want is located, everything works, but requires manual effort: odbc load, dsn("MIHQDB50") table(tabWeeklyStoreItemPricingAll) dialog(prompt); Is there a way to let Stata know which database I want to use? -------------------------------------------------------------------------------- Try using the fully specified table name (i.e., Database.owner.Table) in an -odbc load, exec()- call. Assuming that you've assigned table ownership to the database owner, it would be: odbc load, exec("SELECT * FROM Orders.dbo.tabWeeklyStoreItemPricingAll;") /// dsn(MIHQDB50) clear I'm curious as to why you're not setting up separate DSNs for each of the databases . . . Joseph Coveney * * For searches and help try: * http://www.stata.com/help.cgi?search * http://www.stata.com/support/statalist/faq * http://www.ats.ucla.edu/stat/stata/