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
Wed, 26 Jan 2011 15:41:30 +0100
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/