Thanks Brett, I will switch to use jTDS and see how it goes. Regards, Ben
2018-01-11 16:22 GMT+08:00 Brett Ryan <brett.r...@gmail.com>: > 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 >