Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: st: odbc connectivity
From
Abhimanyu Arora <[email protected]>
To
[email protected]
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
<[email protected]> 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
> <[email protected]> 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
>> <[email protected]> wrote:
>>> On Mon, Jan 24, 2011 at 6:50 AM, Neil Shephard <[email protected]> wrote:
>>>> On Mon, Jan 24, 2011 at 10:01 AM, Abhimanyu Arora
>>>> <[email protected]> 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 - [email protected]
>>>> 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/