Fixing Cross-Database Query Error in Azure SQL Data Ingestion

ingestion
1

Original Slack Thread

Hello.
I am trying to ingest data from AzureSQL by the UI.
version 0.13.2

My recipe is:
source: type: mssql config: host_port: '<http://az-dtp-sqs-001.database.windows.net:1433|az-dtp-sqs-001.database.windows.net:1433>' database: az-dtp-sdb-001 username: bbdd_azdtpsdb001_gobierno_r include_views: false include_tables: true profiling: enabled: true profile_table_level_only: true stateful_ingestion: enabled: true options: connect_args: cafile: /etc/ssl/certs/ca-certificates.crt validate_host: false schema_pattern: allow: - stage view_pattern: allow: - 'az-dtp-sdb-001.stage.GESAG_VW_QV*' password: '${azureServerSQLProd}' table_pattern: allow: - 'az-dtp-sdb-001.stage.sf$*'
I am filtering by view and table, but I get this error:
Error [40515] [S0001]: Reference to database and/or server name in 'msdb.dbo.sysjobs' is not supported in this version of SQL Server.
Does anyone know how to fix it?

2

Hey there! :wave: Make sure your message includes the following information if relevant, so we can help more effectively!

  1. Are you using UI or CLI for ingestion?
  2. Which DataHub version are you using? (e.g. 0.12.0)
  3. What data source(s) are you integrating with DataHub? (e.g. BigQuery)

<@U06TM7M40RK> Can you help with the questions in this thread?

:information_source: We’re experimenting with a new AI assistant for questions in the <#CUMUWQU66> channel to help you track down helpful resources more quickly. Keep in mind that responses may not always be spot-on; feel free to ask it follow-up questions and don’t forget to provide its responses a :thumbsup: or :thumbsdown: so other folks in the Community know if you’re still looking for help on the topic!

3

To address the error you’re encountering with Azure SQL ingestion using DataHub, it seems related to the fact that cross-database queries are not allowed in Azure SQL. This is indicated by the error message:

Error [40515] [S0001]: Reference to database and/or server name in 'msdb.dbo.sysjobs' is not supported in this version of SQL Server.

Steps to Resolve:

  1. Change Recipe Type: According to the <Slack discussion>, it is recommended to change your recipe type from mssql to mssql-odbc for better compatibility with Azure SQL.

  2. Update Configuration: Ensure that your configuration is set up correctly for ODBC. Here is an updated version of your recipe:

source:
    type: mssql-odbc
    config:
        host_port: 'az-dtp-sqs-001.database.windows.net:1433'
        database: az-dtp-sdb-001
        username: bbdd_azdtpsdb001_gobierno_r
        include_views: false
        include_tables: true
        profiling:
            enabled: true
            profile_table_level_only: true
        stateful_ingestion:
            enabled: true
        uri_args:
            driver: 'ODBC Driver 17 for SQL Server'
            autocommit: true
            Encrypt: yes
            TrustServerCertificate: yes
            ssl: true
        schema_pattern:
            allow:
                - stage
        view_pattern:
            allow:
                - 'az-dtp-sdb-001.stage.GESAG_VW_QV**'
        password: '${azureServerSQLProd}'
        table_pattern:
            allow:
                - 'az-dtp-sdb-001.stage.sf$**'

Additional Considerations:

Would you like more detailed steps on any of these points?

Sources:

4

I am working in kubernetes and I follow the deploy describe it by you.
When I use your recomended recipe, I get this error:
uri_args is not supported when ODBC is disabled (type=value_error)