Connecting to SQL Server via ODBC: Able to see table and fields, but then error SQLSTATE=42S02 / r(682)

[Vanessa Palmer]

Hello,

I'm attempting to connect Stata to a SQL Server database. I can query the table in SQL Server Management Studio and in Tableau, so I don't believe it's a permissions issue.

I've tried:

odbc load, exec("SELECT * FROM student_metrics_c;") dsn("ODS_PROD")

and
odbc load, exec("SELECT * FROM student_metrics_c;") dsn("ODS_PROD") dialog(prompt), then re-indicating the directory in the dialog

as well as going through stepwise:

odbc query "ODS_PROD"
odbc desc "student_metrics_c"
odbc load, exec("SELECT * FROM student_metrics_c;") dsn("ODS_PROD")

This last approach shows me all the available tables within ODS_PROD, then all the available fields on my table (student_metrics_c), but then gives the same error as the others.

With each, I get the error:

The ODBC driver reported the following diagnostics
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'student_metrics_c'.
SQLSTATE=42S02
r(682);

At this point, I'm out of ideas. Any help would be very, very much appreciated!

Vanessa

[wbuchanan]

What types of data are stored in the table and which driver are you using?

[Joseph Coveney]

Try using more of the fully qualified table name, such as [Schema Name].student_metrics_c in your SQL statement and see whether that helps.

Also, make sure that you're in the database that you think that you are. Sometimes the default database for a connection is another database than the one you wish to access. In that case, you could try using even more of the fully qualified table name, such as [Database Name].[Schema Name].student_metrics_c. (Based upon what you're saying with the odbc query and odbc describe it seems that this isn't the case, though.)

[Vanessa Palmer]

Hi all,

Thanks for the responses.

wbuchanan: The table contains a variety of data types (see screenshot), and I'm using ODBC Driver 11 for SQL server. I reinstalled the driver this morning with no change.

Joseph Coveney: Good idea to use a more qualified name. However, I've now tried [Schema Name].student_metrics_c (dbo.student_metrics_c) as well as [Database Name].[Schema Name].student_metrics_c (ODS.dbo.student_metrics_c) with no success.

What's puzzling to me is the fact that I can see the field names on the table in Stata (as in the screenshot), and can load the table and see the data in SQL Server Management Studio and Tableau -- but can't load the table in Stata.

I get the same error when I specify a made-up table (i.e., odbc load, exec("SELECT * FROM madeuptable;") dsn("ODS_PROD")). Am I missing a simple syntax error?

Any other ideas? I'm really stumped.

Thanks,
Vanessa

[Vanessa Palmer]

Nevermind!

Wrong schema name.

I'm simultaneously jumping for joy and slapping my own forehead.

Thanks very much for the suggestions -- I don't know that I would have figured this out without going through the process of adding more qualifiers to the table name.

[wbuchanan]

Vanessa Palmer no need to slam your head. If SQL Server implemented SQL the same way nearly all other platforms do you wouldn't need to specify the schema unless there were duplicate table names and you needed to retrieve data from the non-defaulted schema or the schema that is further down the search path.

[Giovanni Colitti]

I'm not sure if I should reopen this issue with another question. But my situation is nearly identical to the title of this post.

The following are my attempts at loading the table (all result in the same 'SQLSTATE=42S02' error):

Attempt 1:

odbc query "mis-dwh" , verbose
odbc desc "Fact Hourly Ranch Weather"
odbc load , table("Fact Hourly Ranch Weather")

Attempt 2:

odbc query "mis-dwh" , verbose
odbc desc "Fact Hourly Ranch Weather"
odbc load, exec(`"SELECT * FROM "Fact Hourly Ranch Weather""')

Attemp 3:


odbc query "mis-dwh" , verbose
odbc desc "Fact Hourly Ranch Weather"
odbc load, exec(`"SELECT * FROM "Farming.Fact Hourly Ranch Weather""')

Attempt 4:

odbc query "mis-dwh" , verbose
odbc desc "Fact Hourly Ranch Weather"
odbc load, exec(`"SELECT * FROM "GrimmwayDataWarehouse.Farming.Fact Hourly Ranch Weather""')

Attempt 5:

odbc query "mis-dwh" , verbose
odbc desc "Fact Hourly Ranch Weather"
odbc load, exec(`"SELECT * FROM "GrimmwayDataWarehouse.Farming.Fact Hourly Ranch Weather""') dsn("mis-dwh")


Where 'GrimmwayDataWarehouse' is the database (which I have already set to the default so I shouldn't need it), 'Farming' is the schema name identified using the -schema- option with -odbc query-, and 'Fact Hourly Ranch Weather' is the name of the view.

I can see the tables/views after -odbc query- and I can even use -odbc desc- to see the contents of the view I wish to load. It is the -odbc load- command that issues the error. Tableau can load the data just fine. I'm out of ideas and would really appreciate any advice.

[Giovanni Colitti]

Solved!

My problem is that I am unfamiliar with SQL, and didn't realize the quotes must be around the table/view name but not the schema name.

The following worked:

odbc query "mis-dwh"
odbc load , exec(`"SELECT * FROM Farming."Fact Hourly Ranch Weather""')

[wbuchanan]

Giovanni Colitti
SQL Server has some "unique" semantics. If you have tables/column names that include spaces, the standard notation is to inclose it in square brackets. If by default you wrap all database names, schemas, and tables in square brackets you shouldn't have issues with this type of stuff in the future and won't have to worry about using the appropriate quotation marks and all that.