setting up MS SQL Server as a data source with JBoss

Hi,
I am having a few problems trying to set up MS SQL Server as a data source with JBoss.
I am following the instructions in the Using MS SQL Server with JBoss documentation.
As I have j2sdk1.4.0_03 installed I do not have to download a JDBC driver. What I have is the dll file C:\j2sdk1.4.0_03\jre\bin\JdbcOdbc.dll which I guess links to my jdbc drivers, so I need to add JdbcOdbc.dll to my JBOSS file which deals with JDBC drivers.
The problem is that I do not know what this file is called. According to the docs it is called jboss.jcml but that file does not exist.
I am using version jboss-3.2.0RC2_tomcat.4.1.18.
I would be grateful if anyone can tell me what the file is know called.
Thanks Tony

Further investigation has revealed the following files in the examples directory:
mssql-ds.xml
mssql-service.xml
mssql-xa-ds.xml
mssql-xa-service.xml
I guess the way forward is to copy these into the JBOSS server directory and modify them for my use.
I am not sure where they should be copied to, I guess it should be the

C:\jboss-3.2.0RC2_tomcat.4.1.18\server\default\conf directory, as this contains all the xml files that are needed at start-up.
Am I correct in this thanks for any help.
Cheers Tony

Hi there,
The only configuration file you'll need to work with (I believe) is mssql-ds.xml. Just modify it and place it in the deploy directory of your default server instance. For instance, if you're using 'default', place the file in '<jboss>/server/default/deploy/'.
The mssql-server.xml will work as well, though I think it's included for 3.0.x backward compatbility. As for 'xa-ds.xml' and 'xa-service.xml', I haven't used them so I can't speak about them :-)
BTW, the '-ds.xml' files are new to JBoss 3.2 so there's not a lot of documentation out on them that I have been able to scope out. That's why I speak conditionally about them.
I can tell you that I've recently switched from 'postgresql-service.xml' to 'postgresql-ds.xml' in my own code and have suffered so serious effects :-)
Cheers,
Darryl

BTW, you'll also want to place the JDBC driver in the deploy directory as well. If you're working with a DDL, I think you'll have to use a JDBC-ODBC bridge to work with the database and, I have to apologize, I've never used one of these things so I can't help you configure it.
I do know that there are JDBC drivers for MSSQL 2000, however, so look around for them. I believe Microsoft has one available for download.
Darryl

Hi Darryl
Thanks very much your mail was a great source of help, and has saved me a lot of time.
What I did was I modified the mssql-ds.xml , downloaded the mssqlserver.jar and put both in my
C:\jboss-3.2.0RC2_tomcat.4.1.18\server\default\deploy directory.
I still have a problem with the following warning message :
10:25:00,453 WARN [LocalTxConnectionManager] Please change your datasource setup to use TransactionManagerService with object name jboss.tm:service=Transaction ManagerService
10:25:00,453 WARN [LocalTxConnectionManager] instead of TransactionManager with value java:/TransactionManager
But hopfully will be able to solve it.
Thanks again for your help.
Tony

Tony,
The messages about the transaction manager service can supposedly be ignored, according to posts on the jboss-user mailing list. Changes have been made for RC3 that should address this problem. In fact, RC3 became available yesterday. Download it and give it a try. Do you have any other questions about data source configuration?
Craig

I recently tried the same thing using JBOSS 3.2 and Microsoft SQL Server. I originally copied only the mssqlserver.jar into the deploy directory and received a class not found error for Microsoft's JDBC base class. I went back and copied the msbase.jar and the msutil.jar to the deploy directory and everything works fine. No warnings either.

Is it possible to perform connection pooling for this datastore in JBoss.
Any link or code sample in the databasename-ds.xml file would be helpful, the connection pool should it be specified in this file or where to implement it ?
Thanks
-Prashant.

Hi! I was wondering how you can set up MSSQL as the default DBMS? I tried by changing the login-config.xml file and adding a new <application-policy> element but it didn't work.
Is this even necessary?

I followed the following step to get MS-SQL to work:
how to configure MS SQL server in jboss?
1. delete the hsql*-service.xml file in the C:\jboss\jboss-3.0.6_tomcat-4.1.18\server\default\deploy
directory.
2. copy the mssql-service.xml file from the C:\jboss\jboss-3.0.6_tomcat-4.1.18\docs\examples\jca directory
into C:\jboss\jboss-3.0.6_tomcat-4.1.18\server\default\deploy
3. change the C:\jboss\jboss-3.0.6_tomcat-4.1.18\server\default\config
a) standardjaws.xml
<jaws>
<datasource>java:/MSSQLDS</datasource>
<type-mapping>MS SQLSERVER2000</type-mapping>
<debug>false</debug>

b) standardjbosscmp-jdbc.xml files.
<defaults>
<datasource>java:/MSSQLDS</datasource>
<datasource-mapping>MS SQLSERVER2000</datasource-mapping>

4. in the mssql-service.xml following changes:
<attribute name="ManagedConnectionFactoryProperties">
<properties>
<config-property name="ConnectionURL" type="java.lang.String">jdbc:microsoft:sqlserver://68.165.1.253:1450;DatabaseName=CABIN</config-property>
<config-property name="DriverClass" type="java.lang.String">com.microsoft.jdbc.sqlserver.SQLServerDriver</config-property>

<config-property name="UserName" type="java.lang.String">mina</config-property>
<config-property name="Password" type="java.lang.String">sm4321</config-property>
</properties>
</attribute>
......
and also
<depends optional-attribute-name="ManagedConnectionPool">

<mbean code="org.jboss.resource.connectionmanager.JBossManagedConnectionPool" name="jboss.jca:service=XaTxPool,name=MSSQLDS">

5. in the C:\jboss\jboss-3.0.6_tomcat-4.1.18\server\default\config dir
change the login-config.xml file
<application-policy name = "MSSQLDbRealm">
<authentication>
<login-module code = "org.jboss.resource.security.ConfiguredIdentityLoginModule" flag = "required">
<module-option name = "principal">sa</module-option>
<module-option name = "userName">user</module-option>
<module-option name = "password">passwd</module-option>
<module-option name = "managedConnectionFactoryName">jboss.jca:service=XaTxCM,name=MSSQLDS</module-option>
</login-module>
</authentication>
</application-policy>

thank alot