In MSSQL, own schemas not seen, only default ones

[nicolamarangoni]

I created the following adapter:

CREATE OR REPLACE JAVA ADAPTER SCRIPT adapter.jdbc_adapter_mssql AS
  // This is the class implementing the callback method of the adapter script
  %scriptclass com.exasol.adapter.jdbc.JdbcAdapter;

  // This will add the adapter jar to the classpath so that it can be used inside the adapter script
  // Replace the names of the bucketfs and the bucket with the ones you used.
  %jar /buckets/bucketfs1/bucket_adapter/virtualschema-jdbc-adapter-dist-1.6.0.jar;

  // You have to add all files of the data source jdbc driver here (e.g. Hive JDBC driver files)
  %jar /buckets/bucketfs1/bucket_adapter/mssql-jdbc-7.2.1.jre8.jar;
/

Then I created a connection using SCHEMA_NAME = 'dbo'. It works fine. However, when I set SCHEMA_NAME = 'export', I get to following error message:

SQL Error [22002]: VM error: 
com.exasol.adapter.AdapterException: Schema export does not exist. Available schemas: db_accessadmin, db_backupoperator, db_datareader, db_datawriter, db_ddladmin, db_denydatareader, db_denydatawriter, db_owner, db_securityadmin, dbo, guest, INFORMATION_SCHEMA, sys
Stack trace:
com.exasol.adapter.jdbc.JdbcMetadataReader.findSchema(JdbcMetadataReader.java:221)
com.exasol.adapter.jdbc.JdbcMetadataReader.readRemoteMetadata(JdbcMetadataReader.java:46)
com.exasol.adapter.jdbc.JdbcAdapter.readMetadata(JdbcAdapter.java:135)
com.exasol.adapter.jdbc.JdbcAdapter.readMetadata(JdbcAdapter.java:125)
com.exasol.adapter.jdbc.JdbcAdapter.handleCreateVirtualSchema(JdbcAdapter.java:118)
com.exasol.adapter.jdbc.JdbcAdapter.adapterCall(JdbcAdapter.java:44)

It looks like the adapter can only access the default MSSQL schemas like dbo, guest, sys, db_..., but not other created schemas.

[snehlsen]

The available schemas are retrieved with DatabaseMetaData.getSchemas() this should work out of the box. Maybe it is a matter of metadata visibility. Have you tried selecting sys.schemas with that same user you are using in the virtual schema? Maybe some GRANTs are missing.

[nicolamarangoni]

Privileges in MSSQL are OK because I can access the schemas and their tables with IMPORT using the same connection object (therefore the same user). That's why I suppose that it is a Virtual-Schema-specific issue.

[nicolamarangoni]

I also have the impression that there is a datatype issue. A simple SELECT * FROM ... gives this error:

Query execution failed

Reason:
SQL Error [42636]: ETL-3003: [Column=229 Row=16] [String data right truncation. String length exceeds limit of 21 characters] (Session: 1627876099131976898)

Could it depends on some issues with the character set recognition? The DDL I get from MSSQL with Dbeaver has lines like this: col0 nvarchar(40) COLLATE Latin1_General_CI_AS The DDL I get from the table in the virtual scheme for the same column has this: col0 VARCHAR(20) UTF8 IMPORT in a new physical table created by myself has no issues. So it should be really an issue within the adapter.

[nicolamarangoni]

The available schemas are retrieved with DatabaseMetaData.getSchemas() this should work out of the box. Maybe it is a matter of metadata visibility. Have you tried selecting sys.schemas with that same user you are using in the virtual schema? Maybe some GRANTs are missing.

With SELECT * FROM sys.schemas; I don't get the wished shemas. So you're right, I have to ask for metadata visibility.

[snehlsen]

So since this is a permission problem in the MS SQLServer you are using, I will close this ticket.