Error: [SQL Server]Incorrect syntax near the keyword 'into'

[kapilsaini2009]

Discussed in #9765

^{Originally posted by kapilsaini2009 April 16, 2024}
I am trying to run GX with MSSQL but getting incorrect syntax error.

Problem:

Generated SQL by the framework for the given query is incorrect.
Input query for RuntimeBatchRequest - "select * from summary_data"
Generated SQL- SELECT *
FROM (SELECT * into #gx_temp_405773af from summary_data) AS anon_1
WHERE 1 = 1

Error Message:

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'into'. (156) (SQLExecDirectW)")
[SQL: SELECT *
FROM (SELECT * into #gx_temp_405773af from summary_data) AS anon_1
WHERE 1 = 1]

Code:

asset = 'summary_data'
 query = "select * from summary_data"

 batch_request = RuntimeBatchRequest(
     datasource_name='sql_server_datasource',
     data_connector_name='sql_gx_data_connector',
     data_asset_name=f'{asset}',
     runtime_parameters={
         "query": query,
     },
     batch_identifiers={"sql_gx_identifier": 'summary_checkpoint'}
 )

 validation_result = context.run_checkpoint(checkpoint_name="my_quickstart_checkpoint",
                                                expectation_suite_name="summary_data_suite_1", batch_request=batch_request)

Error Logs:

Traceback (most recent call last):
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context
self.dialect.do_execute(
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
cursor.execute(statement, parameters)
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'into'. (156) (SQLExecDirectW)")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/u1/kasaini/kpfiles/gx/gx/gx_db_batch.py", line 86, in
main()
File "/u1/kasaini/kpfiles/gx/gx/gx_db_batch.py", line 49, in main
validation_result = context.run_checkpoint(checkpoint_name="my_quickstart_checkpoint",
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/great_expectations/core/usage_statistics/usage_statistics.py", line 266, in usage_statistics_wrapped_method
result = func(*args, **kwargs)
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/great_expectations/data_context/data_context/abstract_data_context.py", line 2107, in run_checkpoint
return self._run_checkpoint(
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/great_expectations/data_context/data_context/abstract_data_context.py", line 2151, in _run_checkpoint
result: CheckpointResult = checkpoint.run_with_runtime_args(
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/great_expectations/checkpoint/checkpoint.py", line 914, in run_with_runtime_args
return self.run(**checkpoint_run_arguments)
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/great_expectations/core/usage_statistics/usage_statistics.py", line 266, in usage_statistics_wrapped_method
result = func(*args, **kwargs)
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/great_expectations/checkpoint/checkpoint.py", line 305, in run
self._run_validation(
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/great_expectations/checkpoint/checkpoint.py", line 480, in _run_validation
validator: Validator = self._validator or self.data_context.get_validator(
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/great_expectations/data_context/data_context/abstract_data_context.py", line 2374, in get_validator
self.get_batch_list(
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/great_expectations/core/usage_statistics/usage_statistics.py", line 266, in usage_statistics_wrapped_method
result = func(*args, **kwargs)
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/great_expectations/data_context/data_context/abstract_data_context.py", line 2543, in get_batch_list
return self._get_batch_list(
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/great_expectations/data_context/data_context/abstract_data_context.py", line 2624, in _get_batch_list
return datasource.get_batch_list_from_batch_request(batch_request=result)
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/great_expectations/datasource/new_datasource.py", line 191, in get_batch_list_from_batch_request
) = data_connector.get_batch_data_and_metadata( # type: ignore[call-arg]
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/great_expectations/datasource/data_connector/runtime_data_connector.py", line 223, in get_batch_data_and_metadata
batch_data, batch_markers = self._execution_engine.get_batch_data_and_markers(
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/great_expectations/execution_engine/sqlalchemy_execution_engine.py", line 1379, in get_batch_data_and_markers
batch_data = SqlAlchemyBatchData(
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/great_expectations/execution_engine/sqlalchemy_batch_data.py", line 175, in init
self._selectable = self._generate_selectable_from_query( # type: ignore[call-overload] # python/mypy#14764
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/great_expectations/execution_engine/sqlalchemy_batch_data.py", line 386, in _generate_selectable_from_query
_, temp_table_name = self._create_temporary_table(
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/great_expectations/execution_engine/sqlalchemy_batch_data.py", line 306, in _create_temporary_table
self.execution_engine.execute_query_in_transaction(sa.text(stmt))
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/great_expectations/execution_engine/sqlalchemy_execution_engine.py", line 1472, in execute_query_in_transaction
result = connection.execute(query)
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1422, in execute
return meth(
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 514, in _execute_on_connection
return connection._execute_clauseelement(
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1644, in _execute_clauseelement
ret = self._execute_context(
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1850, in _execute_context
return self._exec_single_context(
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1990, in _exec_single_context
self._handle_dbapi_exception(
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2357, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context
self.dialect.do_execute(
File "/u1/kasaini/kpfiles/gx/my_venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'into'. (156) (SQLExecDirectW)")
[SQL: SELECT *
FROM (SELECT * into #gx_temp_405773af from summary_data) AS anon_1
WHERE 1 = 1]
(Background on this error at: https://sqlalche.me/e/20/f405)

Can someone please suggest what is wrong in my implementation or is there any other issue ?

[FaridFArab]

I knew this issue and it is because of using SQLALchemy with SQL Server. I will fix it.