Thank you for the prompt and useful reply!
I am sorry, I forgot to mention it, but yes I am connecting to a
Microsoft SQL server database, so your solution with loading the
objects worked fine, thank you.
On a more general note, I assume this would not work with all kinds of
databases, so is there some specific reason why Stata is not showing
views when using odbc query? I know I can see views in other
softwares, eg. SPSS. And does anyone know if this is something Stata
plan to address in coming updates (given that there is no perticular
reason for this)?
Thank you once again,
Richard
On 6/29/07, Joseph Coveney <[email protected]> wrote:
Richard Ohrvall wrote (excerpted):
I am trying to retrieve data from a SQL-database. I have no problem
loading tables using odbc list, odbc query and odbc load. However,
when I use odbc query I only see the tables in the database and not
the stored views there. I can retrieve the views by using: odbc load,
exec("select * from viewname") user(user) password(password), but it
would be helpful if I could see all the views available without having
to use another program than Stata. Does anyone know if this is
possible and if so how it is done?
--------------------------------------------------------------------------------
If you're talking about a Microsoft SQL Server database, then you can get
that information (actually, all of the database's objects) from within Stata
by executing the built-in stored procedure "sp_help". Try the sequence of
commands below, mutatis mutandis.
odbc exec("USE master;"), dsn("LocalServer")
odbc exec("EXECUTE sp_help;"), dsn("LocalServer")
You can also -odbc load- this information for inspection and use:
odbc load , exec("EXECUTE sp_help;") dsn("LocalServer")
tabulate Name if Object_type == "view"
levelsof Name if Object_type == "view", local(ListofMyDatabaseViews)
There is also a more specifie stored procedure, "sp_tables", but specifying
views-only requires surrounding the parameter with double quotes in the SQL
EXECUTE statement.
Joseph Coveney
*
* For searches and help try:
* http://www.stata.com/support/faqs/res/findit.html
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
*
* For searches and help try:
* http://www.stata.com/support/faqs/res/findit.html
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
