Re: st: odbc connectivity

[Abhimanyu Arora <abhimanyu.arora1987@gmail.com>]

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/