I am trying to build a workflow that download data from a database.
I connect with the “Microsoft SQL server connector”, the connection is done correctly.
Then I read the data with the “DataBase Reader” node.
After fetching data to get the overview of the tables in the config window, I write down my SQL query, as in the example below :
As you can see, in the config window, I click on “TABLE”, I have then the choice between several tables. I choose one of them, I double-click to get the available fields. I build my query with these tables/field names.
But the same error always happens, with every tables and fields : “Execute failed: Invalid object name ‘A004’.” in the example of the picture.
I do not manage to understand what is the problem. If you can give me a piece of help, it would be amazing
Yes, That’s exactly true. The table name in node configuration must include schema name (SchemaName.TableName).
But I think this issue should be fixed in KNIME. For example if fetching metadata produces schema names as well then the user don’t need to put schema name manually.
Whether or not you need to use the schema name depends on your role on the server (sysadmin, etc.) and whether the database was set up using the “dbo” default schema. I’ve used this node a handful of times to connect to SQL Server and Oracle. Sometimes I’ve had to use the schema but other times I have not. I’m pretty sure this would be the case whether you use Knime, SQL Developer or any other platform.
lol You are just making it more complicated. Creating synonyms for already built db and not having them in KNIME db browser … I prefer to type the schema name… hahahha
Having the schema name auto-populated would clearly be much more practical than having to type it (when you have been able to guess it). Working for a big company, this quite a hurdle having to remember all the schema’s names or even having to find them by talking to right db admin. The more systems you access, the more complicated it gets…
You are just making it more complicated. Creating synonyms for already built db and not having them in KNIME db browser … I prefer to type the schema name… hahahha 