Cannot preview results using dbt-sqlserver

[needbrew]

When using 1.3 with latest plugin I cannot click the execute icon in the top right. the BDT compiles everything correctly and runs properly when issuing "dbt run", but when I click the execute I get

"code": -1,
"message": "Runtime Error\n Database Error\n ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'WITH'. (156) (SQLExecDirectW)")",
"data": ""Error: Traceback (most recent call last):\n File \"/usr/local/lib/python3.9/site-packages/dbt/adapters/sqlserver/sql_server_connection_manager.py\", line 242, in exception_handler\n yield\n File \"/usr/local/lib/python3.9/site-packages/dbt/adapters/sqlserver/sql_server_connection_manager.py\", line 396, in add_query\n cursor.execute(sql)\npyodbc.ProgrammingError: ('42000', \"[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'WITH'. (156) (SQLExecDirectW)\")\n\nThe above exception was the direct cause of the following exception:\n\nTraceback (most recent call last):\n File \"/home/mike.needham/.vscode-server/extensions/innoverio.vscode-dbt-power-user-0.10.1/dist/dbt_integration.py\", line 401, in execute_sql\n *self.adapter_execute(compiled_sql, fetch=True),\n File \"/home/mike.needham/.vscode-server/extensions/innoverio.vscode-dbt-power-user-0.10.1/dist/dbt_integration.py\", line 379, in adapter_execute\n return self.adapter.execute(sql, auto_begin, fetch)\n File \"/usr/local/lib/python3.9/site-packages/dbt/adapters/base/impl.py\", line 278, in execute\n return self.connections.execute(sql=sql, auto_begin=auto_begin, fetch=fetch)\n File \"/usr/local/lib/python3.9/site-packages/dbt/adapters/sqlserver/sql_server_connection_manager.py\", line 433, in execute\n _, cursor = self.add_query(sql, auto_begin)\n File \"/usr/local/lib/python3.9/site-packages/dbt/adapters/sqlserver/sql_server_connection_manager.py\", line 406, in add_query\n return connection, cursor\n File \"/usr/lib64/python3.9/contextlib.py\", line 135, in exit\n self.gen.throw(type, value, traceback)\n File \"/usr/local/lib/python3.9/site-packages/dbt/adapters/sqlserver/sql_server_connection_manager.py\", line 253, in exception_handler\n raise dbt.exceptions.DatabaseException(str(e).strip()) from e\ndbt.exceptions.DatabaseException: Database Error\n ('42000', \"[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'WITH'. (156) (SQLExecDirectW)\")\n\nDuring handling of the above exception, another exception occurred:\n\nTraceback (most recent call last):\n File \"/home/mike.needham/.vscode-server/extensions/innoverio.vscode-dbt-power-user-0.10.1/dist/node_python_bridge.py\", line 104, in \n value = eval(_compile(data['code'], '', 'eval'), _locals)\n File \"\", line 1, in \n File \"/home/mike.needham/.vscode-server/extensions/innoverio.vscode-dbt-power-user-0.10.1/dist/dbt_integration.py\", line 406, in execute_sql\n raise RuntimeException(str(e))\ndbt.exceptions.RuntimeException: Runtime Error\n Database Error\n ('42000', \"[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'WITH'. (156) (SQLExecDirectW)\")\n\n\tat ChildProcess.onMessage (/home/mike.needham/.vscode-server/extensions/innoverio.vscode-dbt-power-user-0.10.1/dist/extension.js:5540:184)\n\tat Object.onceWrapper (node:events:646:26)\n\tat ChildProcess.emit (node:events:526:28)\n\tat emit (node:internal/child_process:938:14)\n\tat processTicksAndRejections (node:internal/process/task_queues:84:21)""
}

[mdesmet]

This is a known issue. MS SQL doesn't support nested WiTH statements.

To execute the statements we need to limit the result set. We do this through rewriting the statement as following

https://github.com/innoverio/vscode-dbt-power-user/blob/ca1275b70e0ecc5679a2d203ca05fed7cef817e6/package.json#L72-L75

I can give you some general pointers on how to fix it so if you are willing to help out, let me know.

[needbrew]

I tried setting this in the settings to be "select top {limit} * from ({query}) as query_limit

[needbrew]

any assistance to get this working would be great

[mdesmet]

It can't be solved by configuration. You would have to rewrite the query to do something like

{withParts}
select top {limit} * from ({queryWithoutWithParts}) as query

As I said if you provide some code to do this in javascript i'll be happy to merge this.

[needbrew]

I am not clear on what you need from me? can you provide more information as I am not sure what "code" you are looking for and how that would fix this issue

[mdesmet]

Added instructions for MS SQL in https://github.com/innoverio/vscode-dbt-power-user/blob/master/README.md#dbtquerytemplate-for-ms-sql