How to swap JTDS driver for MSFT driver for SQL Server?

[DebbieKat]

Hello - I'm trying to debug an issue we are having running PDI 3.2.0 with a VM SQL Server database target. We've been using this database for several months without an issue, but when we attempted to re-seed some tables that were large, we ran into rollback errors in PDI but no accompanying error on the database side. It was suggested that maybe I should use Microsoft's native JDBC driver rather than the default jtds shipped with PDI. But I'm not sure how to do this. I have the Microsoft driver specified in my classpath on the workstation and my Carte remote server. I can't remove the jtds driver as I get errors from spoon when I attempt to do this, but I am not seeing the Microsoft native driver available on the list of connection types. Can anyone provide some direction on this? What else do I need to do to have Spoon see the Microsoft driver?

[Maria Roldan]

You have to copy that drive in \libext\JDBC\ inside Kettle installation folder.

[DebbieKat]

You have to copy that drive in \libext\JDBC\ inside Kettle installation folder.

I did that, but I don't see any additional options in spoon for connection types when I restart it.

[gutlez]

3.2.0 is a while back... I believe you have to then use the Generic DB Driver, and enter your connection string information.

[DebbieKat]

3.2.0 is a while back... I believe you have to then use the Generic DB Driver, and enter your connection string information.

So, I know how to enter the url, but what do I put for the custom driver class name? The name of the jar file?

[DebbieKat]

Nope, that didn't work...

[renatopb]

Did you delete the old driver?

[gutlez]

So, I know how to enter the url, but what do I put for the custom driver class name? The name of the jar file?

According to this website,
class: com.microsoft.jdbc.sqlserver.SQLServerDriver
URL: jdbc:microsoft:sqlserver://servername:1433

[DebbieKat]

After working through this with Pentaho, you do have to use the generic database connection type. They also helped me determine which version of the msft driver to use as apparently 1.2 is a bit problematic. I'm using 3.0 now. The url and driver class is as follows:

jdbc:sqlserver://{servername}:1433;databaseName={databasename}
com.microsoft.sqlserver.jdbc.SQLServerDriver

No need to delete any drivers (and removing the jtds driver will cause errors).

[DebbieKat]

Thought I should warn you all that the microsoft driver appears to translate the truncate option in a table output step as a delete statement instead of a truncate statement. Be forewarned. You might have to change your jobs to use the truncate tables job entry instead...