How to connect SQL Server 2005 in WAS 5.1

Hi All,

One of our database server has upgraded from SQL Server 2000 to 2005 version some days ago, but I found the application which could run before cannot work fine now. I have followed some intructions got from other guys, such as replace the old driver with a new version, but it still did not work. Now I have two questions

1. What is the driver implementation class name? I found there were several answers. (I don't use XA connections)

2. What is the data source helper class name?

I suspect that the above questions are the causes. However, please give me some other hints if any.

Thank you very much!

The driver implementation class name is com.microsoft.sqlserver.jdbc.SQLServerDriver.

The datasource name of the com.microsoft.sqlserver.jdbc.SQLServerDataSource. You can use com.ibm.websphere.rsadapter.DerbyDataStoreHelper to setup our datasource.

Here is some info that may be of use:

Setting up our driver as the JDBC Provider

You should first set up our driver as the JDBC provider. Expand Resources menu on the left of the Administrative Console and choose JDBC Providers. Select New to create a new provider.

1) General Properties: Select the following.

Step 1: User-defined

Step 2: User-defined JDBC Provider

Step 3: User-defined

2) Next Screen

A) General Properties

You can leave scope and name as they are. You can choose your own description. My configuration is as follows

Name: SQLJDBC2005

Class path: path to the sqljdbc.jar file

Implementation class name: com.microsoft.sqlserver.jdbc.SQLServerDataSource

You have to click on save at the top of the screen so that your changes will be saved. You will also be prompted to save incase you forget.

3) Choosing a Datasource

Once you’ve saved and the provider is in place click on the provider name from the providers list. Click on the data sources below Additional Properties. Click on new. You can leave the scope as it is. My configuration is as follows. Unlisted options are left the way they are.

JNDI Name: SQLJDBC2005

Data store helper class name: Generic data store helper

(com.ibm.websphere.rsadapter.GenericDataStoreHelper)

4) Setting up authentication properties for the data source

There are different ways to set up authentication properties for the data source. The following is for the configuration options listed above.

A) Click on the saved data source and go to Custom Properties under Additional Properties

B) Enter serverName, password and user as additional properties. You only need to enter the connection string keyword as the name and the corresponding value.

Once you save all your changes go back to data sources under this provider and click on test connection. You should see the message that your connection succeeded at the top of the screen. You will be given an error message if the connection does not succeed.

|||

Your Step 1 didn't make any sense.

I'm using WAS 5.1.1.3, I'm having a problem trying to get to SQL Server 2005 data and I get errors when I Test Connection. error says:

Test Connection failed for datasource SQL2005 on server server1 at node localhost with the following exception: java.lang.Exception: DSRA8101E: DataSource class cannot be used as one-phase: ClassCastException: com.microsoft.sqlserver.jdbc.SQLServerDataSource

Aside from the vague step 1 instructions which don't follow the provider prompts, aren't there some custom properties that need to be created?