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 :
> 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, 尹文才 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
>
