Listing and retrieving data from SQL views when using odbc

[Tim Evans]

Hi,

I have data stored in a view in MS SQL Server and I am using ODBC to connect to this database from Stata. However when I'm now trying to access this view, I cant connect through to it and retrieve the data. When I use -odbc query- I can only retrieve a list of tables and none of the views in my database. Does anyone have any success with accessing views from within Stata? The sql code works absolutely fine in SQL Server Management Studio so it is not a case of permissions. I can post more information if it helps

This is my script:

#delimit ;
odbc load, exec("
SELECT OfficialCode
    , UTLA19CD
    , Age
    , Population Pop
    , Period [Year]
    FROM Populations.dbo.vRes_LSOA11_SingleYear a
     INNER JOIN LookupsShared.dbo.vLKP_LSOA11 b on b.LSOA11CD = a.OfficialCode
        WHERE Sex < 4") dsn(DataSource)
                clear ;

#delimit cr

[Joseph Coveney]

I've never had any problem retrieving from views from Microsoft SQL Server via ODBC in Stata and so I can't speak to your problem specifically, but aren't you missing something in the penultimate line?

WHERE Sex < 4 dsn(DataSource)

Maybe add something along the lines of

WHERE Sex < 4", dsn(DataSource)

or is that just a copy-and-paste glitch?

[Tim Evans]

Hi Joseph,

Thanks for your reply. Yes this is a copy and paste glitch. I've cut out a lot of the middle of the sql script!

When I use odbc query, I can only retrieve a list of tables and not views. Any thoughts on what this might be? odbc connection set-up?

Thanks
Tim

[Tim Evans]

I'm such an idiot - I specified the wrong database!!

[Joseph Coveney]

When I use odbc query, I can only retrieve a list of tables and not views. Any thoughts on what this might be?

Glad to hear in #4 that you got it worked out. I typically know the names of the objects in the schema that I'm working with and so haven't had to deal with this for a while, but as for seeing the views, try the verbose option for the odbc query command.

[Tim Evans]

Thanks Joseph Coveney I'll try that!

[Joseph Coveney]

Maybe second thoughts about that recommendation, as it's liable to flood you with the names of all sorts of database objects. You can try more specific alternatives such as those shown here. The first one seems the most facile.

.ÿ
.ÿversionÿ16.1

.ÿ
.ÿclearÿ*

.ÿ
.ÿlocalÿline_sizeÿ`c(linesize)'

.ÿsetÿlinesizeÿ80

.ÿ
.ÿquietlyÿsysuseÿauto

.ÿrenameÿforeignÿnondomestic

.ÿ
.ÿodbcÿexec("USEÿSandbox;"),ÿdsn(Sandbox13)


.ÿodbcÿexec("CREATEÿSCHEMAÿprobe;"),ÿdsn(Sandbox13)


.ÿodbcÿinsert,ÿtable(probe.Autos)ÿcreateÿdsn(Sandbox13)


.ÿ
.ÿlocalÿsql_statementÿCREATEÿVIEWÿprobe.DomesticÿASÿSELECTÿ*ÿFROMÿdbo.Autosÿ///
>ÿÿÿÿÿÿÿÿÿWHEREÿnondomesticÿ=ÿ0;

.ÿodbcÿexec("`sql_statement'"),ÿdsn(Sandbox13)


.ÿ
.ÿlocalÿsql_statementÿSELECTÿTABLE_SCHEMA,ÿTABLE_NAMEÿ///
>ÿÿÿÿÿÿÿÿÿFROMÿINFORMATION_SCHEMA.VIEWS;

.ÿ
.ÿodbcÿexec("`sql_statement'"),ÿdsn(Sandbox13)

ÿÿÿÿ1.ÿ+-----------------------------------------------------------------+
ÿÿÿÿÿÿÿ|TABLE_SCHEMAÿ|ÿprobeÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ|
ÿÿÿÿÿÿÿ|TABLE_NAMEÿÿÿ|ÿDomesticÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ|
ÿÿÿÿÿÿÿ+-----------------------------------------------------------------+



.ÿ
.ÿodbcÿexec("DROPÿVIEWÿprobe.Domestic;"),ÿdsn(Sandbox13)


.ÿodbcÿexec("DROPÿTABLEÿprobe.Autos;"),ÿdsn(Sandbox13)


.ÿodbcÿexec("DROPÿSCHEMAÿprobe;"),ÿdsn(Sandbox13)


.ÿ
.ÿsetÿlinesizeÿ`line_size'

.ÿ
.ÿexit

endÿofÿdo-file


.

[Tim Evans]

Thanks for the additional info!