AC2DB Revision table extract

[Ben Gilles]

I am trying to use the AC2DB SQL connector to extract the Revision table from Rally1. I have had success with several other tables so i know that my configuration file is correct. the error that I get is as follows

Problem in copying... [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 18, column 1 (ObjectID). (4864) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 20, column 1 (ObjectID). (4864); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 27, column 6 (Description). (4864); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 29, column 1 (ObjectID). (4864); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 35, column 6 (Description). (4864); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 37, column 1 (ObjectID). (4864); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 42, column 6 (Description). (4864); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 44, column 6 (Description). (4864); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 46, column 6 (Description). (4864); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 50, column 6 (Description). (4864); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 58, column 6 (Description). (4864); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot bulk load because the maximum number of errors (10) was exceeded. (4865); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error. (7399); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)". (7330)


I understand that this table is very big and I need to parse out my query but this seems like either a meta data error or an unintentional SQL injection just based on the error message. can anyone offer up some advice?

------------------------------
Ben Gilles
------------------------------

[David LeDeaux]

Hi Ben,

This seems to be reporting an issue on the ObjectID column.  Do you have the ability to access the database via SQL Management Studio?  I'd like to get the Revision table's column definitions.

[Ben Gilles]

Yes, 

based on the data type I don't see why a bigint would not work. It does look like the Description column will truncate the text entered. 256 is not a lot of characters however I understand that the size of the data may be the limiting factor 

[David LeDeaux]

That's curious since the Description field on the revision object is 32,768 characters according to the object model

Did you precreate the table, or is the connector doing it for you?

[Ben Gilles]

The connector is creating the meta data for the table. It will give me an error if I try to have a pre-created table

[David LeDeaux]

I'm wondering if it would be possible to make modifications to the Description field to expand its size and then in the config, set

DropExistingTables: False

Regarding the ObjectID error.  I highly doubt we're exceeding a BIGINT in size.  I believe that's a 64 bit integer field so that's a very large number.   I'm wondering if for some reason it's coming through as a string in the CSV instead of an integer.  If you look in the CSV, can you see anything unique about those lines that the log is complaining about?  For example in your log above one of those lines is referencing row 29:
[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 29, column 1 (ObjectID). (4864);

[Ben Gilles]

I think i see the problem. There is a string opened with a double quote ("). The closing double quote several revisions later. This is causing the data type mis match because the closing quote is the next Open quote. so it is causing an abnormal end.

[David LeDeaux]

That sounds like a problem.  I am surprised we haven't heard about this issue before.  What's the version of the DB connector you're running?  Also, can you provide the pip modules versions from pip list

[Ben Gilles]

I am running the connector again with a static pre-created table with Varchar(max) for the description column. Once it is done running I will get the pip and version

[Ben Gilles]

Connector version is 1.1.0

Package Version
------------ -------
cffi 1.13.2
cryptography 2.8
pip 19.3.1
pycparser 2.19
pyodbc 4.0.21
pyral 1.4.1
PyYAML 3.12
requests 2.12.5
setuptools 28.8.0
six 1.13.0

[Ben Gilles]

I just upgraded to 20.0.2
the error messages are a bit more clear and the truncation of the description column is the the culprit. the application is still only allowing 256 characters causing the strings not to be closed. This is then causing the data types to mismatch.

[David LeDeaux]

Thanks for the follow up.  Let me ping the devs and see if they have any ideas on this.

[Ben Gilles]

Any Update on this?

[Ben Gilles]

I have thought of a work around for this but I don't know how to implement it. I am not worried about the long string entries. Using the query portion of the config yml can i request it to only return the strings within the Varchar limitation? This way I do not need to worry about it being truncated

[David LeDeaux]

I pinged dev on this, but never got a response and then lost sight of it.  I'm following up with them

[David LeDeaux]

I'm afraid that we don't really support using expressions in our query language like "length".

I got a response back from dev and apparently the TEXT datatype in MS SQL was deprecated.  (https://docs.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql?view=sql-server-ver15)

So, we can't create TEXT fields anymore and the script is now defaulting to nvarchar(256) fields for those large fields.  Unfortunately I'm not sure if this is something that we're going to address since they are telling me that we never really intended for the connector to pull rich text fields and full text like revisions.  I'm sorry I don't have better news on this