Specifiy or select non default schema when pulling external source from Azure SQL server using JDBC

In previous versions of Aperture Data Studio is stated that their were bug fixes related to SQL Server:  

  • You can now preview and load data from other schemas, not just the connected user’s default schema.  

A current using version 2.0.0

I am connecting to Azure SQL and during the Add Data, select tables I only see the tables without schema prefix.

Appears that if there is more than one schema in the database specified in the connection string, then we hit an error even thought schema was specified as part of JDBC connection. I understand there is a limitation between JDBC and MS SQL and the schema cannot be used in the connection string.


So it has recognised the database and seen the tables.

Following a debug on SQL server I can observe that the schema is not passed into the SQL statement/Stored Proc.

This workaround would fix this problem.

Answers

  • I should add that I don't wish to set the default schema for the logged in user as there are multiple database schemas of interest.

    As a test if I specify dbo as the schema in the JDBC connection then it does pick this up fine. Presume because it will look in dbo first.

    This applies to Azure and on prem sql server.

  • Henry SimmsHenry Simms Administrator

    Hi @DTAconsulting , you're correct to say that by default the schema name is not used in queries initiated by the driver, so the SQL Server user's default schema is used.

    To force the schema name to be used, and therefore allow tables to be loaded from non-default schemas, you should check the "Force JDBC schema name" option in the external system's advanced settings:

    1. Edit your existing Azure SQL Server external system and check that you've filled in the schema name in connection properties
    2. Expand Show advanced settings
    3. Check the Force JDBC schema name checkbox


    I'm using 2.0.10 for this. It should work in 2.0.0, but you may still want to upgrade if you are able to do so, to pick up a raft of new features and fixes

Sign In or Register to comment.