MarkLogic: SQL-NOCOLUMN: Column Not Found Error

This article addresses the error message that can be displayed through the CData Driver, such as: "Server error [SQL state: 08000]: SQL-NOCOLUMN: Column not found: main.employees.FirstName" when querying data from a MarkLogic instance. In this example, Main is a schema name, Employees is a table name, and FirstName is a column name

This Error can Occur for Two Reasons:

1. A column in the SELECT statement is not a column on the table

This cause of the issue should be self-explanatory and easy to identify/troubleshoot. In the case of MarkLogic, CData only does some minor parsing/translation of the input SQL query before sending the SQL statement to MarkLogic. Therefore, the solution to this cause would simply be to change the input query to CData to include a column that actually exists on the table, for example: (invalid) SELECT SomeInvalidColumn FROM Customers.Purchases (valid) SELECT FirstName FROM main.employees

2. The column is currently offline

If the cause of the issue is that the column is "offline," the issue should be temporary. MarkLogic documentation details the cases where a column can go offline temporarily while reindexing occurs: https://docs.marklogic.com/guide/sql/creating-template-views

Troubleshooting and Identifying the Issue Source

At verbosity 4, a CData Logfile may provide more insight on which of the above is the cause of the issue. A logfile may be collected by appending values to the connection string properties, such as the following: Logfile=C:\Users\Public\Documents\logfile.txt Verbosity=4 In the logfile, you can expect to see a section where CData retrieves metadata about the columns from the sys_columns table of Marklogic: 2020-01-01T09:50:43.696-04:00 3 [Connection: 6] Query to send: SELECT "schema", "table", "name", "type", "notnull", "pk" FROM "sys"."sys_columns" WHERE ("schema" = 'main') AND ("table" = 'employees') 2020-01-01T09:50:43.698-04:00 3 [Connection: 6]DataIn, Length: 409 2020-01-01T09:50:43.699-04:00 4 [Connection: 6] 54 00 00 00 94 00 06 73 63 68 65 6D 61 00 00 00 00 00 00 00 00 00 04 13 FF FF 00 00 00 00 00 00 74 61 62 6C 65 00 00 00 00 00 00 01 00 00 04 13 FF FF 00 00 00 00 00 00 6E 61 6D 65 00 00 00 00 00 00 02 00 00 04 13 FF FF 00 00 00 00 00 00 74 79 70 65 00 00 00 00 00 00 03 00 00 04 13 FF FF 00 00 00 00 00 00 6E 6F 74 6E 75 6C 6C 00 00 00 00 00 00 04 00 00 00 10 00 04 00 00 00 00 00 00 70 6B 00 00 00 00 00 00 05 00 00 00 10 00 04 00 00 00 00 00 00 44 00 00 00 3A 00 06 00 00 00 04 6D 61 69 6E 00 00 00 09 65 6D 70 6C 6F 79 65 65 73 00 00 00 0A 45 6D 70 6C 6F 79 65 65 49 44 00 00 00 03 69 6E 74 00 00 00 01 31 00 00 00 01 30 44 00 00 00 3C 00 06 00 00 00 04 6D 61 69 6E 00 00 00 09 65 6D 70 6C 6F 79 65 65 73 00 00 00 09 46 69 72 73 74 4E 61 6D 65 00 00 00 06 73 74 72 69 6E 67 00 00 00 01 31 00 00 00 01 30 44 00 00 00 3B 00 06 00 00 00 04 6D 61 69 6E 00 00 00 09 65 6D 70 6C 6F 79 65 65 73 00 00 00 08 4C 61 73 74 4E 61 6D 65 00 00 00 06 73 74 72 69 6E 67 00 00 00 01 31 00 00 00 01 30 44 00 00 00 3B 00 06 00 00 00 04 6D 61 69 6E 00 00 00 09 65 6D 70 6C 6F 79 65 65 73 00 00 00 08 50 6F 73 69 74 69 6F 6E 00 00 00 06 73 74 72 69 6E 67 00 00 00 01 31 00 00 00 01 30 43 00 00 00 0D 53 45 4C 45 43 54 20 34 00 5A 00 00 00 05 49 2020-01-01T09:50:43.699-04:00 1 [Connection: 6] Executed column schema query: [employees], Success: 4 results (3 ms). Removing whitespace characters from the DataIn section and using any hexadecimal data reader tool, we can read the string data to see columns which are returned by MarkLogic: schema table name type notnull pk main employees EmployeeID int 1 0 main employees FirstName string 1 0 main employees LastName string 1 0 main employees Position string 1 0

In this case, given that FirstName is correctly returned in the metadata by Marklogic, and it's both spelled correctly and quote-escaped correctly in the query to send, we can conclude that the SQL-NOCOLUMN error is caused by the MarkLogic server reindexing and should be a temporary issue.

Unfortunately, there is no information available through the MarkLogic API on sys_columns that could identify a column as disabled (and thus that information cannot be surfaced by CData), and there is no information available from CData on how long a column should be temporarily disabled.