Re: st: odbc connectivity

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

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/