Please Help: SQL Server Support error persists

[JasonBrandt]

We are using the new DAP 1.3.1 client. We perform a database initialization. The API runs fine for all tables until we get to the assignments table. Every single time we are receiving the same error regardless of what we do:

File "C:\Users\jbrandt\Desktop\NewDap\.venv\Lib\site-packages\pysqlsync\base.py", line 1173, in _generate_records
transformer = self._get_transformer(
^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\jbrandt\Desktop\NewDap\.venv\Lib\site-packages\pysqlsync\base.py", line 1210, in _get_transformer
raise ValueError(
ValueError: column "submission_types" not found in table "canvas"."assignments"

It looks like when the upserts begin, the sql generated should be using assignments.type as the column. Submission_types does not exist in the table that I can see.

There is a foreign key from assignments to the submission__types table. 

Hoping someone can help:)

[ColinMurtaugh]

You shouldn't have any foreign keys in your CD2 database; that will make it basically impossible to load tables independently. In my (postgresql) database, `canvas.accounts__submission_types` is a data type, and the `canvas.assignments.submission_types` column uses that data type. I don't know how SQL Server handles custom types, but it seems like something is off there. 

---Colin

[LeventeHunyadi]

Normally, DAP client library doesn't create or use foreign keys, even if there is a known relationship between two Canvas tables. However, Microsoft SQL Server has neither enumeration, nor array types, and canvas.assignments.submission_types happens to be both an enumeration and an array type. (It has its own dedicated named type in PostgreSQL with the array flag set.) Consequently, instead of creating a column of an enumeration array type, the DAP client library (for Microsoft SQL Server) should create a separate (join) table with each row representing an entry as it were an enumeration array element. In turn, each row should have a foreign reference back to the origin table (assignments) and a foreign reference to the table with the possible enumeration values. Our team should likely look into why this is not happening with the particular Microsoft SQL Server instance.

[ColinMurtaugh]

Ah -- good to know! 

[JamesSorenson]

I've been waiting over a month for an answer to this issue.  I submitted a ticket in mid-December and get snippets of solutions, but none of them are practical are beneficial.

EVERY other table in the DAP Client loads into my MS SQL instance without issue.  I can query those tables without issue.  It's almost as though the problem is that whomever wrote the CREATE TABLE script for the MSSQL portion just left out a column name.  If I create the missing column in the table, the syncdb DAP command will remove the column, then error because the column doesn't exist. 

My hopes are not high that Instructure will resolve this issue in a timely manner. 

[LeventeHunyadi]

The case is a little bit more complex for the column assignments.submission_types in Microsoft SQL Server than other column types or other database engines. Microsoft SQL Server doesn't support enumeration types or array types but assignments.submission_types is an array of an enumeration type. (This is the only column of such a type across all Canvas.) An array of enumeration types is modelled as a many-to-many relation in Microsoft SQL Server, which is a separate table, not a column in assignments. As far as I am aware, an engineer is already assigned to this task, and actively working on resolving this issue.