Hitachi Vantara Pentaho Community Forums
Results 1 to 14 of 14

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

  1. #1
    Join Date
    Feb 2008
    Posts
    216

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

    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?

  2. #2
    Join Date
    Sep 2007
    Posts
    834

    Default

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

  3. #3
    Join Date
    Feb 2008
    Posts
    216

    Default

    Quote Originally Posted by Maria Roldan View Post
    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.

  4. #4
    Join Date
    Apr 2008
    Posts
    4,696

    Default

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

  5. #5
    Join Date
    Feb 2008
    Posts
    216

    Default

    Quote Originally Posted by gutlez View Post
    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?

  6. #6
    Join Date
    Feb 2008
    Posts
    216

    Default

    Nope, that didn't work...

  7. #7
    Join Date
    Nov 2008
    Posts
    143

    Default

    Did you delete the old driver?

  8. #8
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by DebbieKat View Post
    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

  9. #9
    Join Date
    Feb 2008
    Posts
    216

    Default

    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).

  10. #10
    Join Date
    Feb 2008
    Posts
    216

    Default

    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...

  11. #11
    Join Date
    Feb 2008
    Posts
    216

    Default

    This just in. Don't use the truncate tables job entry either. Same deal. I guess it will have to be an actual sql statement.

  12. #12
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    I thought that was how the option was intended to work...

    Truncate the table before insert = Delete from <table>; Insert into <table> values (?,?,...)

  13. #13
    Join Date
    Feb 2008
    Posts
    216

    Default

    Quote Originally Posted by gutlez View Post
    I thought that was how the option was intended to work...

    Truncate the table before insert = Delete from <table>; Insert into <table> values (?,?,...)
    I think it depends which connection type you use. I assume since this is a generic database connection it is using the least common denominator. Some databases don't have an actual truncate command. But delete logs everything and truncate does not.

  14. #14
    Join Date
    Mar 2010
    Posts
    159

    Default

    Delete from can generate some huge rollback segments and take a while if many rows relative to a truncate.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.