Hi Ben. It’s often recommended to use the jTDS driver [1] as the MS provided driver is considered buggy.
I don’t make this claim, however; I’ve always used this driver for ms sql server and never encountered issues. [1]: http://jtds.sourceforge.net/faq.html > On 11 Jan 2018, at 18:56, 尹文才 <batman...@gmail.com> wrote: > > Hi guys, I'm using the PutDatabaseRecord processor to write some data into > sql server and the processor is using the DBCP controller service as its > connection pool. > Sometimes I could see the following exception inside my log file: > org.apache.nifi.processor.exception.ProcessException: Failed to commit > database connection due to com.microsoft.sqlserver.jdbc.SQLServerException: > The connection is closed > > I know that DBCPConnectionPool is using the validation query property to > set test-on-borrow to true and also set the validation query accordingly, > so I set the validation query to "select 1", > but this doesn't seem to solve the problem. I tried to find the possible > reason and solution for this problem, the only thread I could find is that > someone talked about one possible reason on > StackOverFlow: > > "In case of IOException, the sqlserver jdbc driver marks the connection as > closed, but this is not detected by the pool. So the connection is returned > in the pool, while unusable." > > I'm not sure if the reason he mentioned above is the root cause of my > problem. Has anyone came across this kind of problem and how to work around > this issue? Thanks. > > Regards, > Ben