Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

mssql_drop_foreign_key generates statement that raises pyodbc.ProgrammingError (Incorrect syntax near '`') #716

Closed
Nuqlear opened this issue Jul 24, 2020 · 7 comments
Labels
bug Microsoft SQL Server

Comments

@Nuqlear
Copy link
Contributor

Nuqlear commented Jul 24, 2020

Describe the bug

op.drop_column("assignment", "employee_profile_id", mssql_drop_foreign_key=True)

This line is generating the following statement:

declare @const_name varchar(256)
select @const_name = [name] from
sys.foreign_keys fk join sys.foreign_key_columns fkc
on fk.object_id=fkc.constraint_object_id
where fkc.parent_object_id = object_id('assignment')
`and col_name(fkc.parent_object_id, fkc.parent_column_id) = 'employee_profile_id'
exec('alter table assignment drop constraint ' + @const_name)

which leads to pyodbc.ProgrammingError:

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '`'. (102) (SQLExecDirectW)")

Expected behavior
Foreign key dropped alongside column without errors.

Versions.

  • OS: Ubuntu 18.04
  • Python: 3.7.4
  • Alembic: 1.4.2
  • SQLAlchemy: 1.3.14
  • Database: Azure SQL Server
  • DBAPI: pyodbc==4.0.30
@Nuqlear Nuqlear added the requires triage label Jul 24, 2020
Nuqlear added a commit to Nuqlear/alembic that referenced this issue Jul 24, 2020
@CaselIT
Copy link
Member

CaselIT commented Jul 24, 2020

Strange. Seems to be a typo introduced in 6e30795.

@CaselIT CaselIT added bug Microsoft SQL Server and removed requires triage labels Jul 24, 2020
@CaselIT
Copy link
Member

CaselIT commented Jul 24, 2020

@gordthompson I'm not familiar sql server, do backticks (`) have a particular meaning in it?

@sqla-tester
Copy link
Collaborator

sqla-tester commented Jul 24, 2020

Oleg Shigorin has proposed a fix for this issue in the master branch:

Fixes #716. Remove typo in _exec_drop_col_fk_constraint https://gerrit.sqlalchemy.org/c/sqlalchemy/alembic/+/2105

@gordthompson
Copy link
Member

gordthompson commented Jul 24, 2020

@gordthompson I'm not familiar sql server, do backticks (`) have a particular meaning in it?

AFAIK, no.

@CaselIT
Copy link
Member

CaselIT commented Jul 24, 2020

thanks, should be a typo then.

@zzzeek
Copy link
Member

zzzeek commented Jul 25, 2020

how did this ever work? user at #621 (comment) maybe didnt actually run this part?

@sqla-tester
Copy link
Collaborator

sqla-tester commented Jul 25, 2020

Oleg Shigorin has proposed a fix for this issue in the master branch:

Remove typo in _exec_drop_col_fk_constraint https://gerrit.sqlalchemy.org/c/sqlalchemy/alembic/+/2105

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Microsoft SQL Server
Projects
None yet
Development

No branches or pull requests

5 participants