search

gordthompson / sqlalchemy-sybase

SAP ASE (Sybase) support for SQLAlchemy implemented as an external dialect
MIT License
12 stars 4 forks source link

Reflecting Sybase ASE tables into MS SQL (Azure DB) - Syntax error in DDL #10

Closed ZachMassia closed 1 year ago

ZachMassia commented 1 year ago

Hi,

Reposting this here, originally created a discussion at https://github.com/sqlalchemy/sqlalchemy/discussions/9243

I'm attempting to load metadata from Sybase ASE, and then run create_all() on an SQL Server connection, however the generated DDL is causing syntax errors.

It fails on the first column, because create default zero_dflt as 0 or a variation of it, based on the datatype, is being appended to each line.

Full column line for example: colname SMALLINT NOT NULL DEFAULT create default zero_dflt as 0,

Here is a gist containing the relevant code, the full DDL, and the full error message: https://gist.github.com/ZachMassia/6bae47330d21e9da490178ea6c8f4f46

In the meantime I'm attempting to move things over with SSMA, but it would be fun to have it all in one place, in Python.

gordthompson commented 1 year ago

It is not uncommon for people to try migrating table definitions from one DBMS to another using a technique similar to the one you describe. Unfortunately, it is also fairly common for such attempts to fail due to differences in the underlying implementations of the different DBMSs.

If you can use a proper migration tool then that would be the best approach.

ZachMassia commented 1 year ago

Hi Gord,

Thanks for the reply.

I've managed to get the tables moved over with SSMA (https://learn.microsoft.com/en-us/sql/ssma/sybase/migrating-sybase-ase-databases-to-sql-server-azure-sql-db-sybasetosql?view=sql-server-ver16)

Would you recommend using the MS tools for the recurring ETL as well?

gordthompson commented 1 year ago

Would you recommend using the MS tools for the recurring ETL as well?

I have no experience with them, but they seem to be built specifically for your use case and appear to be pretty well-documented

https://learn.microsoft.com/en-us/sql/ssma/sybase/migrating-sybase-ase-data-into-sql-server-azure-sql-db-sybasetosql?view=sql-server-ver16

so if I was in your situation I would be inclined to give them a try first.

ZachMassia commented 1 year ago

Sounds good. Thanks!