IBM WebSphere 6.1 now supports SQL Server 2005 JDBC driver v1.2

IBM WebSphere 6.1 now supports SQL Server 2005 JDBC driver v1.2

  • Comments 20

Many of you have asked us before, "Is the Microsoft SQL Server JDBC driver supported by IBM WebSphere?".  Now, we have the answer.  In collaboration with the IBM WebSphere team, the SQL Server JDBC team is happy to announce starting with IBM WebSphere 6.1 update 6.1.0.15, our SQL Server JDBC driver v1.2 is officially Tested with IBM® WebSphere® Application Server Software and is listed on IBM website's list of supported JDBC drivers for WebSphere 6.1.

Jimmy Wu
SQL Server JDBC Team

Leave a Comment
  • Please add 3 and 2 and type the answer here:
  • Post
  • Any documentation on how to setup this JDBC driver with WebSphere 6.1? I've been googling like mad, and I can't find anything except this blog. I'm not sure what to put in for DataStoreHelper, and when I pick the default, it complains about being incompatible with the ConnectionPoolDataSource.

  • Are you using the WebSphere 6.1 Fixpack which enables the use of our driver?

    http://www-1.ibm.com/support/docview.wss?rs=180&context=SSEQTP&dc=D400&uid=swg24018265&loc=en_US&cs=UTF-8&lang=en

  • Yeah, I know about the fixpack 15+ requirement. I downloaded fixpack 17 (oh boy, you have to install a new update installer to get that to install, also uninstall your previous fixpacks and reboot after every change). Oh, there are so many flaming hoops to jump through, makes one wonder why they pay prime dollar for Web$phere. Oh, JBoss is gonna eat you up big blue! Anyway, the fixpack is the *easy* part (and it isn't really easy... One of our developers had his RAD completely hosed by fp17).

    But I digress. What should have taken 10 minutes (if well documented) ended up taking 4 hours of JDBC trial and error fun. So others do not need to go through this, I will quickly document what you have to do for Web$phere 6.1. Just setting up a connection pool data source, and no XA driver stuff (that is what we are escaping from!!!), no windows authentication.

    Microsoft's documentation is better, but very vague in terms of app server specific screens... That is really IBM's responsibility though. I'd say the MS documentation is fairly good, but you need to take educated guesses from there.

    1. download the Microsoft SQL Server 2005 JDBC driver version 1.2

    2. unzip it

    3. copy the sqljdbc jar into [drive]:\IBM\WebSphere\AppServer\lib

    4. restart Web$phere service

    5. open admin console

    5. create the JDBC provider:

      a. open resources -> jdbc -> jdbc providers

      b. create new provider

      c. database type = "user-defined"

      d. implementation class = "com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource"

      e. give it whatever name you like

      f. hit apply and hit save

    6. create the JDBC data source:

      a. open resources -> jdbc -> data sources

      b. create a new datasource

      c. give it a datasoure name and jndi name (later used in your app) and click next

      d. select an existing jdbc provider (the one you just created)

      e. Datastore helper class name = "com.ibm.websphere.rsadapter.MicrosoftSQLServerDataStoreHelper" CMP is up to you. I unchecked it since we use BMP exclusively.

      f. you click finish, but you're not done. Go back into this datasource and at the bottom of the page you need to configure the database name, server name, and port. Then click apply, save, and test the datasource (may need to stop/start Web$phere).

    So that is all I wanted. Some step-by-step instructions on how to install the jdbc driver so I didn't have to experiment. I made the mistake of using the DataSource class instead of the ConnectionPoolDataSource class, and was barking up the wrong tree for a while. Thanks to MS's docs, I was able to see that is what I wanted. See, IBM's admin console UI calls it a "DataSource" and not a "ConnectionPoolDataSource" but it is the latter, and not the former.

    The "user-defined" jdbc provider is another big, F'in "huh"? Yeah, you may want to pick SQL Server from the list (of deprecated drivers) but that is not a good idea. You will end up with an old and obsolete driver. Ideally, if IBM supports this in fixpack 15+, it should be an option to pick in the list. It should ship with fp 15+.

    I think, for what Web$phere costs, they should really provide this info. IBM, you can even copy my notes, clean them up, and put it in you docs. Please let people know how to do this... I saw many more questions than answers when I googled this furiously for 4 hours or so.

    I hope this helps someone out there so they can get back to writing code instead of playing the jdbc guessing game... This info should be part of IBMs documentation.

  • Websphere works very well with DB2 :)

    You dont need Websphere application server v6.1 with fixpack 15 for "Microsoft SQL Server 2005 JDBC Driver" to work.

    For older systems, download the newest drivers from microsoft and create a new user defined jdbc provider.

    Use:

    com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource

    as the implementation

    and

    com.ibm.websphere.rsadapter.MSSQLDataStoreHelper

    as data store helper in your datasource settings.

    You can also use:

    com.ibm.websphere.rsadapter.GenericDataStoreHelper

    but it will give you a warning each time you use it.

    Fix pack 15 makes this setup easier and uses com.ibm.websphere.rsadapter.MicrosoftSQLServerDataStoreHelper instead.

    Also checkout:

    http://publib.boulder.ibm.com/infocenter/wasinfo/v6r1/index.jsp?topic=/com.ibm.websphere.express.doc/info/exp/ae/rdat_minreqmssql.html

  • Do not copy sqljdbc.jar to websphere lib directory.

    Install the microsoft sql jdbc driver files under program files.

    Then create a websphere variable like ${MICROSOFT_JDBC_DRIVER_PATH} that points to this directory. (usually C:\Program Files\Microsoft SQL Server 2005 JDBC Driver\sqljdbc_1.2\enu)

    Include ${MICROSOFT_JDBC_DRIVER_PATH}/sqljdbc.jar in classpath while creating your jdbc provider.

    This way you dont have to restart websphere.

  • How to specify the failover partner for JDBC driver? Is it done using 6.1 console or is it to be done while making the connection?

  • Hi Jimmy,

    thanks for the update. One question I have is what are the downsides of using the com.ibm.websphere.rsadapter.GenericDataStoreHelper rather then the com.ibm.websphere.rsadapter.MSSQLDataStoreHelper

    Is it supported by IBM? provides some limitations?

    Thanks C.

  • In this step:

    f. you click finish, but you're not done. Go back into this datasource and at the bottom of the page you need to configure the database name, server name, and port. Then click apply, save, and test the datasource (may need to stop/start Web$phere).

    There's no field to configure database name, server name, and port.

    Any sugestions?

  • I need the Microsoft 2.0 JDBC driver to work with WebSphere 7.0.  I can get it to work, but IBM doesn't list the 2.0 driver on its prerequisites page (at http://www-01.ibm.com/support/docview.wss?rs=180&uid=swg27012421).  I posted this same question on the MSDN board at http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/3be5c043-7084-4f5f-95e0-4fe9ffca312f.  Are you working with IBM on this?  How does certification occur?

  • Hey Wes,

    Sorry for taking a little while to get back to you. When IBM releases a major version they will certify our latest JDBC version. That's why JDBC 1.2 is certified for WAS 7.0. After that, IBM just claims forward support for later released versions. Thus, since WAS 7.0 was already released and already certified for 1.2, they offer support 2.0 and not certification. They do not believe there are major changes between 1.2 and 2.0.

    --Tres London [SQL Server]

  • thank you Guys for your helping hand

  • /*

    In this step:

    f. you click finish, but you're not done. Go back into this datasource and at the bottom of the page you need to configure the database name, server name, and port. Then click apply, save, and test the datasource (may need to stop/start Web$phere).

    There's no field to configure database name, server name, and port.

    */

    I have similar problem. Any updates on this issue?

  • You need to add them as custom properties:

    databaseName: java.lang.String

    serverName: java.lang.String

    portNumber: java.lang.Integer

    Think this should work.

  • Any idea when/if IBM will add support for SQLServer to Websphere standalone ( see http://publib.boulder.ibm.com/infocenter/wmqv7/v7r0/index.jsp ). I tried this with my MQ7 + JDBC2.0 install and it did not seem to work.

    Basically I used the

    qMgr = new MQQueueManager("QM1")

     Connection con = qMgr.getJDBCConnection( xads );

     qMgr.begin()

    pattern passing in a SQLServerXADataSource.

  • Dear team,

    I am very happy the way you guys are explain.Alos I need one help from your side.

    I am facing one problem. When I am connecting SQLSERVER 2000 my application is calling store procedure which runing fine and when needed i am getting SQLException in java code which i am easily dispaly in the view page but when I am coonecting SQLSERVER 2008(64) bit it is also running fine as it should behave but when the system needs to throw exception it is not throwing exception when calling the same store procedure in side java code, so that i am not able to display the error mesage in view page. Please suggest me what i will do. Thanks In advance

Page 1 of 2 (20 items) 12