I have a workflow that reads from an MS Access table, and saves to SQL Server. After adding some new fields to the table in Access, now I get "ERROR Database Writer 0:10 Execute failed: Incorrect syntax near ‘501’. when I execute the database writer. Can someone tell me more about this error? What is ‘501’, is this possibly a problem with a field(s) name?. Any help is much appreciated!
I am unable to determine the issue looking at the log file below (my apologies, I’m a complete noob):
2020-04-29 11:43:11,097 : DEBUG : KNIME-Worker-7 : WorkflowManager : Database Writer : 0:10 : Database Writer 0:10 doBeforePostExecution
2020-04-29 11:43:11,097 : DEBUG : KNIME-Worker-7 : NodeContainer : Database Writer : 0:10 : Database Writer 0:10 has new state: POSTEXECUTE
2020-04-29 11:43:11,097 : DEBUG : KNIME-Worker-7 : WorkflowManager : Database Writer : 0:10 : Database Writer 0:10 doAfterExecute - failure
2020-04-29 11:43:11,097 : DEBUG : KNIME-Worker-7 : Node : Database Writer : 0:10 : reset
2020-04-29 11:43:11,097 : DEBUG : KNIME-Worker-7 : Node : Database Writer : 0:10 : clean output ports.
2020-04-29 11:43:11,097 : DEBUG : KNIME-Worker-7 : WorkflowDataRepository : Database Writer : 0:10 : Removing handler f36028c5-4bf8-47dc-9595-9255b988befb (Database Writer 0:10: ) - 1 remaining
2020-04-29 11:43:11,097 : DEBUG : KNIME-Worker-7 : NodeContainer : Database Writer : 0:10 : Database Writer 0:10 has new state: IDLE
2020-04-29 11:43:11,100 : WARN : KNIME-Worker-7 : Node : Database Writer : 0:10 : Existing table “PFS_Inv_Weekly_Week_Over_Week” will be dropped!
2020-04-29 11:43:11,101 : DEBUG : KNIME-Worker-7 : Node : Database Writer : 0:10 : Configure succeeded. (Database Writer)
2020-04-29 11:43:11,101 : DEBUG : KNIME-Worker-7 : NodeContainer : Database Writer : 0:10 : Database Writer 0:10 has new state: CONFIGURED
2020-04-29 11:43:11,101 : DEBUG : KNIME-Worker-7 : NodeContainer : Database Writer : 0:10 : PFS_Weekly_Trending 0 has new state: CONFIGURED
2020-04-29 11:43:11,101 : DEBUG : KNIME-WFM-Parent-Notifier : NodeContainer : : : ROOT has new state: IDLE
2020-04-29 11:43:11,107 : DEBUG : KNIME-Node-Usage-Writer : NodeTimer$GlobalNodeStats : : : Successfully wrote node usage stats to file: C:\Users\louis.moffitt\knime-workspace.metadata\knime\nodeusage_3.0.json
Can you post row data you 're trying to write?
Do you have ’ char in your data?
Hi Cal,
can you also provide the stack trace that should be in your KNIME log. To view the KNIME log open KNIME Analytics Platform and go to View-> Open KNIME log. In the log view search (Ctrl+f) for the error message and copy this part from the log file here.
Thanks
Tobias
Ok. The problem is that in SqlServer, if a field name start with a number (501R_FLAG), column name has to be inserted into double quotes chars “501R_FLAG”.
So INSERT query has to be formatted in this way.
Which node you’re using to write to database? I want see if it’s provide a mechanism to quote field name.
If not we have to change column name into “501R_FLAG” (with quotes).
Okay, that makes sense. I am using the Knime “default” Database Writer node (not labs) see attached
. I’m thinking I can either alias the column name with the “AS” function in the Database Reader node or just re-name it in the Access data base.
According to me you don’t have to change DBReader Node because the problem is not in source db, it’s in dest db.
So it’s more clear add or change column just befire writing to SqlServer (or to change field name in sqlServer).
I changed the column name in the data source (the Access DB table) to FLAG_501R so it no longer starts with a number. Now it shows up in the Database reader as FLAG_501R and it is written to SQL Server as FLAG_501R by the Database writer. That resolved the issue, it works now!
Thanks so much for your guidance, I appreciate your time and help!!!
I think this is a fantastic forum!
Now you can flag the solution… if you want 
Hi there @Cal,
new database nodes with KNIME version 4.0 are no longer in Labs.
https://www.knime.com/whats-new-in-knime-40#knime-database-extension
If nothing is blocking you from update I suggest you give them a try. There you’ll find dedicated Access connector and much more
Br,
Ivan
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.