ilike not supported in T-SQL

[MarkrJames]

Describe the bug

ilike not supported in T-SQL. When running generate_source macro the complied SQL throws an error.

Steps to reproduce

dbt run-operation generate_source --args '{"schema_name": "sink"}'

Expected results

.yml file containing a list of all tables in sink schema

Actual results

Encountered an error while running operation: Database Error
  ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]An expression of non-boolean type specified in a context where a condition is expected, near 'ilike'. (4145) (SQLExecDirectW)")

Screenshots and log output

17:06:09.584072 [info ] [MainThread]: Running with dbt=1.3.0
17:06:09.584072 [debug] [MainThread]: running dbt with arguments {'write_json': True, 'use_colors': True, 'printer_width': 80, 'version_check': True, 'partial_parse': True, 'static_parser': True, 'profiles_dir': 'C:\\Users\xxxxx\\.dbt', 'send_anonymous_usage_stats': True, 'event_buffer_size': 100000, 'quiet': False, 'no_print': False, 'macro': 'generate_source', 'args': '{schema_name: sink}', 'which': 'run-operation', 'rpc_method': 'run-operation', 'indirect_selection': 'eager'}
17:06:09.584072 [debug] [MainThread]: Tracking: tracking
17:06:09.601213 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'start', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x00000204239B7708>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x00000204239B7848>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x00000204239B7788>]}
17:06:10.319117 [debug] [MainThread]: Partial parsing enabled: 0 files deleted, 0 files added, 0 files changed.
17:06:10.319117 [debug] [MainThread]: Partial parsing enabled, no changes found, skipping parsing
17:06:10.348178 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'load_project', 'label': '51552c34-aa33-467e-b0ca-b695db136455', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x0000020423A57C48>]}
17:06:10.394193 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'runnable_timing', 'label': '51552c34-aa33-467e-b0ca-b695db136455', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x0000020423CDAF08>]}
17:06:10.399190 [debug] [MainThread]: Acquiring new sqlserver connection "macro_generate_source"
17:06:10.401180 [debug] [MainThread]: On macro_generate_source: COMMIT
17:06:10.503190 [debug] [MainThread]: SQLServer adapter: Using sqlserver connection "macro_generate_source".
17:06:10.504188 [debug] [MainThread]: SQLServer adapter: On macro_generate_source: 

        select distinct
            table_schema as "table_schema",
            table_name as "table_name",

            case table_type
                when 'BASE TABLE' then 'table'
                when 'EXTERNAL TABLE' then 'external'
                when 'MATERIALIZED VIEW' then 'materializedview'
                else lower(table_type)
            end as "table_type"

        from sqlserver.information_schema.tables
        where table_schema ilike 'sink'
        and table_name ilike '%'
        and table_name not ilike ''

17:06:10.504188 [debug] [MainThread]: Opening a new connection, currently in state init
17:06:10.505184 [debug] [MainThread]: SQLServer adapter: Using connection string: DRIVER={ODBC Driver 17 for SQL Server};SERVER=sqlserver.database.windows.net,1433;Database=sqldb;UID={auser};PWD=***;encrypt=Yes;TrustServerCertificate=No;Application Name=dbt-sqlserver/1.3.0
17:06:11.768674 [debug] [MainThread]: SQLServer adapter: Connected to db: sqldb
17:06:11.881675 [debug] [MainThread]: SQLServer adapter: Database error: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]An expression of non-boolean type specified in a context where a condition is expected, near 'ilike'. (4145) (SQLExecDirectW)")
17:06:11.882676 [debug] [MainThread]: On macro_generate_source: ROLLBACK
17:06:11.883675 [debug] [MainThread]: On macro_generate_source: Close
17:06:11.884673 [debug] [MainThread]: SQLServer adapter: Error running SQL: macro generate_source
17:06:11.885676 [debug] [MainThread]: SQLServer adapter: Rolling back transaction.
17:06:11.887677 [error] [MainThread]: Encountered an error while running operation: Database Error
  ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]An expression of non-boolean type specified in a context where a condition is expected, near 'ilike'. (4145) (SQLExecDirectW)")
17:06:11.889678 [debug] [MainThread]: 
17:06:11.891678 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'end', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x0000020423B7E248>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x0000020423B7E388>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x0000020423B7EDC8>]}
17:06:11.892680 [debug] [MainThread]: Flushing usage events
17:06:12.288065 [debug] [MainThread]: Connection 'macro_generate_source' was properly closed.

System information

The contents of your packages.yml file:

packages:
  - package: dbt-labs/dbt_utils
    version: 0.9.2
  - package: dbt-labs/codegen
    version: 0.8.1

Which database are you using dbt with?

• [ ] postgres
• [ ] redshift
• [ ] bigquery
• [ ] snowflake
• [x] other (specify: Azure SQL )

The output of dbt --version:

Core:
  - installed: 1.3.0
  - latest:    1.3.0 - Up to date!

Plugins:
  - sqlserver: 1.3.0 - Up to date!

The operating system you're using:

The output of python --version:

py --version    
Python 3.7.8

Additional context

Issue with ilike

Are you interested in contributing the fix?

[MarkrJames]

New to dbt, looks like I need to use tsql_utils to "shim" codegen to allow functionality to work with Azure SQL(?)

I may have missed something, but please can a warning or notification be added under the 'Installation' heading of the README to mention some functionality not supported for Azure SQL to help avoid any unnecessary struggles.

Will leave open until reviewed and README updated

[github-actions[bot]]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions[bot]]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.