Question

Error: "Invalid object name" when trying to read tables from multi schema MS SQLserver

  • 30 December 2021
  • 5 replies
  • 203 views
T

In FME Workbench I have a MS SQLServer database that has multiple schemas.

Schema1.Schema2.Tablename

Workbench sees the tables in the Reader setup but I get the following error when I try to apply the Reader.

JDBC Reader: Error reading schema for table 'DataBank.AccountsPayable.Invoices'. Error: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'DataBank.AccountsPayable.Invoices'

 

I am on Mac Monterey but VS Code can read the data in the tables.

5 replies

nampreetatsafe
Rank
Userlevel 1
Badge +10

Hi @thehiggman, I just tested this out with a SQL Server database that has multiple schemas and it worked. I selected the table from the Reader Parameter as shown here (as it sounds like you did as well):imageA few questions to get a better picture of what's happening:

  1. What version of FME are you using?
  2. When exactly are you getting this error? Does it occur when running the workspace/reading in the data? Or when you initially add the Reader feature type to the canvas (after entering the Reader parameters)?
  3. I noticed you're using the JDBC version of the MS SQL Server Reader. Can you try the non-JDBC version and see if you have any luck?image
  4. Ensure you are selecting the "Non-Spatial" vs. "Spatial" Reader as appropriate. i.e. if you don't have any tables that have spatial geometry, you'll want to ensure you're using the "Microsoft SQL Server Non-Spatial" Reader.

 

Looking forward to hearing if you have any luck.

T

Hi @thehiggman, I just tested this out with a SQL Server database that has multiple schemas and it worked. I selected the table from the Reader Parameter as shown here (as it sounds like you did as well):imageA few questions to get a better picture of what's happening:

  1. What version of FME are you using?
  2. When exactly are you getting this error? Does it occur when running the workspace/reading in the data? Or when you initially add the Reader feature type to the canvas (after entering the Reader parameters)?
  3. I noticed you're using the JDBC version of the MS SQL Server Reader. Can you try the non-JDBC version and see if you have any luck?image
  4. Ensure you are selecting the "Non-Spatial" vs. "Spatial" Reader as appropriate. i.e. if you don't have any tables that have spatial geometry, you'll want to ensure you're using the "Microsoft SQL Server Non-Spatial" Reader.

 

Looking forward to hearing if you have any luck.

Hello @nampreetatsafe​ . Thank you for your reply.

Yes, that worked fine. When I hit OK. I get the following:

Screen Shot 2022-01-03 at 10.05.44 AMwhich shows I can see the tables. When I hit OK I get the following:

Screen Shot 2022-01-03 at 10.06.05 AMThe log states:

Failed to obtain any schemas from reader 'MSSQL_JDBC_NONSPATIAL_2' from 1 datasets. This may be due to invalid datasets or format accessibility issues due to licensing, dependencies, or module loading. See logfile for more information

Program Terminating

Translation FAILED.

COM.safe.fme.jdbc.TableProbablyDoesntExistException: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'MunisDataBank.AccountsPayable.InvoiceDetails'.

Failed to obtain any schemas from reader 'MSSQL_JDBC_NONSPATIAL_2' from 1 datasets. This may be due to invalid datasets or format accessibility issues due to licensing, dependencies, or module loading. See logfile for more information

Workspace generation failed. Parameter file was /private/var/folders/zn/gs4th4jd3jzbqh7vkctpc3bm0000gp/T/FME_1641222353833_30206.

 

T

Hi @thehiggman, I just tested this out with a SQL Server database that has multiple schemas and it worked. I selected the table from the Reader Parameter as shown here (as it sounds like you did as well):imageA few questions to get a better picture of what's happening:

  1. What version of FME are you using?
  2. When exactly are you getting this error? Does it occur when running the workspace/reading in the data? Or when you initially add the Reader feature type to the canvas (after entering the Reader parameters)?
  3. I noticed you're using the JDBC version of the MS SQL Server Reader. Can you try the non-JDBC version and see if you have any luck?image
  4. Ensure you are selecting the "Non-Spatial" vs. "Spatial" Reader as appropriate. i.e. if you don't have any tables that have spatial geometry, you'll want to ensure you're using the "Microsoft SQL Server Non-Spatial" Reader.

 

Looking forward to hearing if you have any luck.

Sorry, I missed the 2nd half of your response. I am using SAFE FME 2021.2 on Mac OS Monterey.

I tried the SQL connector. Get the following error:

Worker 43860 > Module 'MSSQL_ADO' is unavailable for use with this FME edition

Worker 43860 > Failed to open the dataset 'FME_SENTINEL_DATASET_FOR_DATABASE_LISTING'

Worker 43860 > DATASET INFO:

Worker 43860 > DATASET: FME_SENTINEL_DATASET_FOR_DATABASE_LISTING

Worker 43860 > NUMBER OF PARAMETERS: 6

Worker 43860 > SERVER = **.dev.tylertechnologies.com<backslash>TYLERCI

Worker 43860 > USE_SSPI = NO

Worker 43860 > USER_NAME = **

Worker 43860 > PASSWORD = ********

Worker 43860 > COMMAND_TIMEOUT =

Worker 43860 > RETRIEVE_ALL_DATABASES = YES

danminneyatsaf
Rank
Userlevel 2
Badge +10

@thehiggman​ Can you try setting up your Database Connection so that it uses "DataBank" as the Database, and then you can use AccountsPayable.Invoices as the Table you want to read in. I'll outline this in a couple steps to make it more clear:

  • When creating a new Microsoft SQL Server Non Spatial (JDBC) Connection, ensure that you have set the Database parameter to DataBank (or the correct database name in this case)

2022-01-04_14-11-24

  • Press Save to add the new connection. Then click Parameters to select your table. In the window that opens, enter AccountsPayable.Invoices (or the correct Schema.Table) as the Table you want to read.

2022-01-04_14-17-26

  • Press OK to close the Parameters and then press OK again to add the Reader to your workspace.

 

If you are still encountering the same error then please let me know. I also suggest that when selecting the Database and the Tables, you use the 3 dots This will prevent you from entering any Database or Table names that are not valid.

Let me know if this works for you!

T

@thehiggman​ Can you try setting up your Database Connection so that it uses "DataBank" as the Database, and then you can use AccountsPayable.Invoices as the Table you want to read in. I'll outline this in a couple steps to make it more clear:

  • When creating a new Microsoft SQL Server Non Spatial (JDBC) Connection, ensure that you have set the Database parameter to DataBank (or the correct database name in this case)

2022-01-04_14-11-24

  • Press Save to add the new connection. Then click Parameters to select your table. In the window that opens, enter AccountsPayable.Invoices (or the correct Schema.Table) as the Table you want to read.

2022-01-04_14-17-26

  • Press OK to close the Parameters and then press OK again to add the Reader to your workspace.

 

If you are still encountering the same error then please let me know. I also suggest that when selecting the Database and the Tables, you use the 3 dots This will prevent you from entering any Database or Table names that are not valid.

Let me know if this works for you!

Hello DanMAtSafe, thank you for your response. The issue I believe is that there is an additional 'owner/schema' in the database.

 

The database name is databankprod

There is a Master owner databank

a secondary owner AccountsPayable

and tables.

 

So the query would look like SELECT * FROM databank.AccountsPayable.invoices

 

I have tried [databank.AccountsPayable].invoices but I get there following error:

Error: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ']'

FME can see all the tables in the database. I can load the Tables box with the Fully Qualified Table Name. It is when I hit OK that I receive the error.

I have one table that does not have the 2 Schemas/owner and I am able to use a reader to get this data.

Thanks

Reply


Community Stats

28,724
Posts
109,520
Replies
36,564
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.