Stata odbc gives SQL error, but connection works in SAS

[Cyrus Levy]

My SAS program below works for connecting an SQL database. I tried to convert it to Stata using -odbc- but I keep receiving SQL errors shown below. It seems to be a driver related issue, but the driver is installed and that is how the SAS code works. So, I am not sure why Stata cannot load the specified driver. Can somebody please help me figure how to make the Stata code below work for the connection. Again, SAS code below works.

The ODBC driver reported the following diagnostics
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
SQLSTATE=IM002

/*
SAS code that works
SQL Server: DATPOT
Database: ABC
Schema: DATTECH
*/

libname ABCD oledb
provider = sqloledb
init_string = "Provider = SQLNCLI11.1; Integrated Security = SSPI; Persist Security Info = True; Initial Catalog = ABC; Data Source = DATPOT"
schema = DATTECH

// Stata code that gives error
odbc load, exec("SELECT * FROM DATTECH.TableA") conn("Provider=SQLNCLI11.1; Integrated Security=SSPI; Persist Security Info=True; Initial Catalog=ABC; Data Source=DATPOT") clear

[Dimitriy V. Masterov]

I would first list ODBC sources to which Stata can connect with

odbc list

Then try

odbc query "DataSourceName"

and

odbc describe "TableName"

If there are any error messages, report back.

[Joseph Coveney]

You're using OLE DB with SAS and not ODBC. Stata won't work with the former.

If you don't see any pertinent Microsoft SQL Server ODBC data sources listed after you follow Dimitriy's recommendation, then you'll have to set one up via your ODBC Data Source Administrator application (assuming you're using Windows)..

[Cyrus Levy]

If there are any error messages, report back.

Alright, -odbc list- lists database ABC as one of the data sources. When I type -odbc query ABC- it lists some links that I do not recognize and none of them is DATTECH where the tables that SAS code can access. So, when I type -odbc describe DATTECH.TableA- it shows an empty table. When I click load, it gives an error telling that it is "invalid object name" and it gives the same error if I type -odbc describe DATTECH- or -odbc describe TableA-

[Cyrus Levy]

You're using OLE DB with SAS and not ODBC. Stata won't work with the former.

If you don't see any pertinent Microsoft SQL Server ODBC data sources listed after you follow Dimitriy's recommendation, then you'll have to set one up via your ODBC Data Source Administrator application (assuming you're using Windows)..

Stata gets to the database ABC, but it does not show DATTECH schema in there where the tables are. So, if that is related to OLEDB, then how can I set it up in ODBC Data Source Administrator application (yes, I use Windows) so that I can establish access to the tables with Stata?

[Joseph Coveney]

Whoever set up the named ODBC data source ("ABC") that you're seeing might not have configured it in a way convenient to you. You can use a connection string in lieu of the named data source.

From what you show in #1, you might be using SQL Server Native Client 11.1, but assuming that you're odbc list shows, say, ODBC Driver 13 for SQL Server (you haven't shown the output), then try a connection string along the following lines. I'm using the server name that you show above in #1 for SAS.

local connection_string ///
    Driver={ODBC Driver 13 for SQL Server};Server=DATPOT;Database=ABC;Trusted_Connection=Yes;

And then use the local macro containing the connection string in a connectionstring() option instead of using the dsn(). Try something like this.

odbc describe DATTECH.TableA, connectionstring("`connection_string'")
odbc load, exec("SELECT * FROM DATTECH.TableA;") connectionstring("`connection_string'") clear

[Cyrus Levy]

From what you show in #1, you might be using SQL Server Native Client 11.1, but assuming that you're odbc list shows, say, ODBC Driver 13 for SQL Server (you haven't shown the output), then try a connection string along the following lines.

This actually did work! Thank you so much! The driver that -odbc list- listed next to ABC is SQL Server Native Client 11.0, and writing that within Driver={...} just as in your example did the trick! I also tried using SQLNCLI11.1 instead of SQL Server Native Client 11.0, but that did not work. So, the driver needs to be written, word by word, letter by letter, number by number as how -odbc list- shows it next to the database. Anyway, now I can access DATTECH.TableA and get all the variables directly.

A follow-up question: How can I list the names of all available tables in DATTECH schema? Running the following code provides a list of many many things that are both relevant and irrelevant to DATTECH schema. How can I filter that with respect to what is only in DATTECH? Adding Schema=DATTECH to the connection string did not work.

local connection_string Driver={SQL Server Native Client 11.0};Server=DATPOT;Database=ABC;Trusted_Connection=Yes;Schema=DATTECH;
odbc query, verbose conn("`connection_string'")

[Joseph Coveney]

The driver that -odbc list- listed next to ABC is SQL Server Native Client 11.0 . . .

Yeah, I suppose that the most recent version is 11.0 (Microsoft seems to have deprecated Native Client), and that the "11.1" shown in #1 is is the version number of the OLE DB piggyback to go through the Native Client.

A follow-up question: How can I list the names of all available tables in DATTECH schema?

Probably the most convenient would be the following.

#delimit ;
local sql_statement
    SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = 'DATTECH' AND TABLE_TYPE IN ('BASE TABLE', 'VIEW')
        ORDER BY TABLE_NAME;
#delimit cr

odbc exec("`sql_statement';"), connectionstring("`connection_string'")

It will give views as well as user tables in that schema. I've set up my instance's collation to be case sensitive, but yours might not be and you might be able to get away with lower case throughout.

( you're odbc list → your odbc list )