Preview not working with dbt-sqlserver adapter, LIMIT causes an error

[AndreasPutschandl]

Hi there!

I hope somebody can help me with this issue. I´m trying to use dbt Power User with SQL Server but the preview throws the following error:

This makes perfect sense as TSQL uses TOP instead of LIMIT. So I tried to change the "dbt.queryTemplate" setting to "set rowcount {limit}; {query}" as sugested in below link and also changed the default dbt.queryLimit from 500 to 100 to ensure I´m using the correct settings (User vs Workspace): https://docs.myaltimate.com/setup/optConfig/#deprecated-mssql-synapse-oracle-query-preview-config

Unfortunately, despite the customisation of the queryTemplate, the "LIMIT 100" is still added to my query, which causes the above error. To me it looks like the dbt.queryTemplate setting is ignored, unlike dbt.queryLimit which works.

Executing my dbt models against the database works without problems, only the preview of the extension throws the error!

The versions I use are:

I would really appreciate any advice on how to solve this problem, thank you in advance!

[anandgupta42]

@AndreasPutschandl The querytemplate functionality has been depreacted and is no longer required. The query preview should work out of the box for sqlserver. Can you remove the template, restart vscode and try again?

[tabit01]

@anandgupta42 I have the same issue connecting to my Fabric Synapse warehouse. I have tried setting the query template

"dbt.queryTemplate": "select top {limit} * from ({query}) as query"

, as well as removing it. Still the dispatched query is appended with LIMIT 500. Latest version of dbt and extension, have tried reinstalling extension and restarting VS Code.

BTW: I did not realize the query template was deprecated until I read this thread. At least I seem to remember that the settings field "dbt.queryTemplate": had a value.

EDIT: I created issue #1456