Schema not automatically created in secondary warehouse

[cheyney-w]

If you attempt to materialize a model in a second warehouse by setting its database property (and you've already materialized at least one model in your target database), you receive the following error:

('42000', '[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The specified schema name "dbt_mycustomschema" either does not exist or you do not have permission to use it. (2760) (SQLMoreResults)')

It seems that the fabric__list_schemas macro in dbt/include/fabric/macros/adapters/metadata.sql always runs against the target database, not the database configured for the relation. I think this could be resolved by adding a USE {{database}} statement immediately above the select ... from sys.schemas statement:

    USE {{database}};
    select  name as [schema]
    from sys.schemas {{ information_schema_hints() }} {{ apply_label() }}

Reproduction steps

1. Create two warehouses in the same workspace: WH_Primary and WH_Secondary
2. Configure the profiles target to connect to WH_Primary and the schema to be dbt_mycustomschema
3. Create a model and run dbt. Confirm the model is successfully materialized in WH_Primary under dbt_mycustomschema
4. Create a second model and configure its database to WH_Secondary.
5. Run dbt again. You'll get the error above.

This is because when the adapter attempts to materialize the second model and checks for the existence of the schema, it checks in the WH_Primary database (in which it exists because of step 3) and skips creating it.

[prdpsvs]

I think adding USE database statement should work, however, there are few catalog queries which require additional work.
And I do not know the impact of this change with table/view/snapshot/clone materializations.

I will not prioritize this for V1.8.8. However, will start looking into this soon.

[boxysean]

I am working with a team who is also experiencing the exact same issue.

[prdpsvs]

Addressed this change in #251. All integration tests are passing. Test and let me know if you still this issue. Please re-open if required.