Microsoft SQL Server - Restricted data type attribute violation

[agronholm]

Originally reported by: CyberJacob (Bitbucket: CyberJacob, GitHub: CyberJacob)

---

When trying to use sqlacodegen to analyse some Microsoft SQL server tables, a sqlalchemy.exc.DBAPIError exception is thrown, with the message

(pyodbc.Error) ('07006', '[07006] [Microsoft][ODBC Driver 11 for SQL Server]Restricted data type attribute violation (0) (SQLGetData)')

I'm not entirely sure if this is caused by pyodbc or sqlacodegen, though I haven't managed to do anything in sqlalchemy that recreates the issue, so I'm inclined to think it's a sqlacodegen issue.

The specific command I ran was:
sqlacodegen mssql+pyodbc://lansync/ --schema dbo --tables SyncRoomTypes

The full traceback:

Traceback (most recent call last):
  File "c:\python27\lib\runpy.py", line 174, in _run_module_as_main
    "__main__", fname, loader, pkg_name)
  File "c:\python27\lib\runpy.py", line 72, in _run_code
    exec code in run_globals
  File "C:\Python27\Scripts\sqlacodegen.exe\__main__.py", line 9, in <module>
  File "c:\python27\lib\site-packages\sqlacodegen\main.py", line 40, in main
    metadata.reflect(engine, args.schema, not args.noviews, tables)
  File "c:\python27\lib\site-packages\sqlalchemy\sql\schema.py", line 3804, in reflect
    Table(name, self, **reflect_opts)
  File "c:\python27\lib\site-packages\sqlalchemy\sql\schema.py", line 436, in __new__
    metadata._remove_table(name, schema)
  File "c:\python27\lib\site-packages\sqlalchemy\util\langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "c:\python27\lib\site-packages\sqlalchemy\sql\schema.py", line 431, in __new__
    table._init(name, metadata, *args, **kw)
  File "c:\python27\lib\site-packages\sqlalchemy\sql\schema.py", line 507, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "c:\python27\lib\site-packages\sqlalchemy\sql\schema.py", line 519, in _autoload
    self, include_columns, exclude_columns
  File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 1528, in run_callable
    return callable_(self, *args, **kwargs)
  File "c:\python27\lib\site-packages\sqlalchemy\engine\default.py", line 364, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "c:\python27\lib\site-packages\sqlalchemy\engine\reflection.py", line 590, in reflecttable
    table_name, schema, **table.dialect_kwargs):
  File "c:\python27\lib\site-packages\sqlalchemy\engine\reflection.py", line 369, in get_columns
    **kw)
  File "<string>", line 2, in get_columns
  File "c:\python27\lib\site-packages\sqlalchemy\engine\reflection.py", line 54, in cache
    ret = fn(self, con, *args, **kw)
  File "c:\python27\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 1570, in wrap
    tablename, dbname, owner, schema, **kw)
  File "c:\python27\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 1579, in _switch_db
    return fn(*arg, **kw)
  File "c:\python27\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 1967, in get_columns
    row = cursor.first()
  File "c:\python27\lib\site-packages\sqlalchemy\engine\result.py", line 1203, in first
    self.cursor, self.context)
  File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 1393, in _handle_dbapi_exception
    exc_info
  File "c:\python27\lib\site-packages\sqlalchemy\util\compat.py", line 202, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "c:\python27\lib\site-packages\sqlalchemy\engine\result.py", line 1199, in first
    row = self._fetchone_impl()
  File "c:\python27\lib\site-packages\sqlalchemy\engine\result.py", line 1056, in _fetchone_impl
    return self.cursor.fetchone()
sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('07006', '[07006] [Microsoft][ODBC Driver 11 for SQL Server]Restricted data type attribute violation (0) (SQLGetData)')

And here's the output of exec sp_columns SyncRoomTypes, which describes the table layout:

COLUMN_NAME		TYPE_NAME		PRECISION	LENGTH	SCALE	RADIX	NULLABLE	SQL_DATETIME_SUB	CHAR_OCTET_LENGTH	ORDINAL_POSITION	IS_NULLABLE	SS_DATA_TYPE
SyncRoomTypeId	int	identity	10			4		0		10		0			NULL				NULL				1					NO			56
SyncTimestamp	datetime		23			16		3		NULL	1			3					NULL				2					YES			111
GroupId			nvarchar		12			24		NULL	NULL	0			NULL				24					3					NO			39
SourceSiteId	nvarchar		12			24		NULL	NULL	0			NULL				24					4					NO			39
RoomTypeCode	nvarchar		12			24		NULL	NULL	0			NULL				24					5					NO			39
Description		nvarchar		255			510		NULL	NULL	0			NULL				510					6					NO			39
RoomTypeClass	int				10			4		0		10		0			NULL				NULL				7					NO			56
RFlag			int				10			4		0		10		0			NULL				NULL				8					NO			56

---

• Bitbucket: https://bitbucket.org/agronholm/sqlacodegen/issue/38

[agronholm]

Original comment by Alex Grönholm (Bitbucket: agronholm, GitHub: agronholm):

---

This happens during reflection so it's not a problem with sqlacodegen. Probably a configuration issue on the DB side. If not, raise on issue on SQLAlchemy directly.