Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.
| From | Abhimanyu Arora <abhimanyu.arora1987@gmail.com> |
| To | statalist@hsphsun2.harvard.edu |
| Subject | Re: st: odbc connectivity |
| Date | Thu, 3 Feb 2011 09:18:53 +0100 |
Dear statalist Thanks to the helpful staff at statacorp, here is the solution for the odbc problem I was facing for some days, for benefit of other users: in this case the tables were displayed when I used -verbose- option (with or without schema), documented in stata 11 help files. Also, just to let you know the executables have been updated, just type -update all- to get the latest version. Best regards and have a nice day! Abhimanyu On Wed, Jan 26, 2011 at 3:41 PM, Abhimanyu Arora <abhimanyu.arora1987@gmail.com> wrote: > Hello again, > I just realized that there is more to the problem than meets the eye. > More specifically, I dont even see the list of tables via the -odbc > query- command. I have the following output > > . odbc list > > Data Source Name Driver > ------------------------------------------------------------------------------- > MS Access Database Microsoft Access Driver (*.mdb, *.accdb) > Excel Files Microsoft Excel Driver (*.xls, *.xlsx, *.xl > dBASE Files Microsoft Access dBASE Driver (*.dbf, *.ndx > HerculesDB SQL Server > ------------------------------------------------------------------------------- > > . odbc query "HerculesDB" > > DataSource: HerculesDB > Path : Hercules > ------------------------------------------------------------------------------- > ------------------------------------------------------------------------------- > > It is a slight progress from the previous obstacle only in the sense > that I don't see a connection error. > Ideally, I should get the list of tables, so that I can then select to > see the variables using -odbc describe-. I have tried all combinations > possible as far as the connect_options are concerned (incl as Neil had > mentioned earlier, username & password as ell as dialog prompt), but > to no avail. > I would value any suggestion to help solve this issue. > > Best regards > Abhimanyu > > > > > > On Wed, Jan 26, 2011 at 12:35 PM, Abhimanyu Arora > <abhimanyu.arora1987@gmail.com> wrote: >> Dear statalist >> I was finally able to solve the problem. It had to do with (lack of) >> grant of access to the database for my username by the administrator. >> But thanks very much indeed, in particular to Neil and Sergiy. >> The issue that is of concern now is reading/loading the tables from >> the ODBC datasource into stata. Is there any interactive way, such as >> via the use of dialog boxes to see (and select) all the tables >> contained (rather than remembering their names) and the associated >> relevant variables? It would be rather cumbersome to specify scores of >> variables in the -odbc load- command... >> Thanks and best regards >> Abhimanyu >> >> >> On Mon, Jan 24, 2011 at 10:54 PM, Sergiy Radyakin >> <serjradyakin@gmail.com> wrote: >>> On Mon, Jan 24, 2011 at 6:50 AM, Neil Shephard <nshephard@gmail.com> wrote: >>>> On Mon, Jan 24, 2011 at 10:01 AM, Abhimanyu Arora >>>> <abhimanyu.arora1987@gmail.com> wrote: >>>>> Dear statalist >>>>> I would like to view and load an ODBC source (connected by SQL server) >>>>> into stata. Earlier, I had followed the necessary steps (control >>>>> panel->administrative tools->data sources (odbc)->relevent dialog >>>>> boxes) to successfully create the odbc source to be viewed. >>>>> The problem is that while stata lists the source (called HerculesDB), >>>>> it doesn't load the tables >>>>> >>>>> . odbc list >>>>> >>>>> Data Source Name Driver >>>>> ------------------------------------------------------------------------------- >>>>> MS Access Database Microsoft Access Driver (*.mdb, *.accdb) >>>>> Excel Files Microsoft Excel Driver (*.xls, *.xlsx, *.xl >>>>> dBASE Files Microsoft Access dBASE Driver (*.dbf, *.ndx >>>>> HerculesDB SQL Server >>>>> ------------------------------------------------------------------------------- >>>>> >>>>> . odbc query "HerculesDB" >>>>> ODBC error; type -set debug on- and re-run command to see extended >>>>> error information >>>>> r(682); >>>>> >>>>> . set debug on >>>>> >>>>> . odbc query "HerculesDB" >>>>> Connection Parameters: DSN=HerculesDB; >>>>> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user >>>>> 'ECON\n09040'., SQLSTATE=28000 >>>>> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user >>>>> 'ECON\n09040'. >>>>> r(682); >>>>> >>>>> The description of the error says that a "common cause is insufficient >>>>> permissions to connect to the data source". Having tried all options >>>>> nevertheless, >>>> >>>> Your code above doesn't appear to include the option that seems most pertinent. >>>> >>>> The error message is saying "Login failed for user 'ECON\n09040'". >>>> >>>> On your line where you attempt to query the database using -odbc- you >>>> do not appear to be specifying any ,-user() password()- options which >>>> may be the cause of the login failing. >>>> >>>>> I would appreciate your alternative suggestions to solve >>>>> the problem.. >>>> >>>> I'd try using the -user()- and -password()- options to -odbc- when >>>> attempting to connect. >>>> >>>> Neil >>> >>> The above suggestion would not hurt, but Stata should pop-up the login >>> window if there is not enough >>> information to connect. In general you wouldn't store your password in >>> the do-file. >>> >>> Make sure your login information is still current/valid and you can >>> access the database of interest >>> using other means. >>> >>> Based on the SQLSTATE that you cite, see if the following will resolve >>> your problem: >>> http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/36df4673-c3ec-4e30-bbdf-69a8690fd28b >>> >>> Best, Sergiy Radyakin >>> >>> >>> >>> >>>> >>>> >>>> -- >>>> “Truth in science can be defined as the working hypothesis best suited >>>> to open the way to the next better one.” - Konrad Lorenz >>>> >>>> Email - nshephard@gmail.com >>>> Website - http://kimura.no-ip.org/ >>>> Photos - http://www.flickr.com/photos/slackline/ >>>> >>>> * >>>> * 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/ >>>> >>> >>> * >>> * 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/ >>> >> > * * 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/