Connect to MS-SQL Server?

[Park]

I've set up MS-SQL Server 2000 on a Win2k Server machine. How can I connect from OOBase running on a Linux machine to a database on that system? The setup for an ODBC connection asks for a source on the local system. I've tried inputting various combination of the server / database name, but haven't had any luck. I've installed the FreeTDS package and am able to connect from a command line using tsql. But I'm not sure how to integrate FreeTDS into OOBase as a driver.

[DrewJensen]

Park,

Looking at the TDS site it seems that you might want to pursue using the JTDS driver instead, since the later is a JDBC implementation, and that is what OOBase would like to see.

Have a look at http://jtds.sourceforge.net/

Drew
_________________
Blog - http://baseanswers.spaces.live.com/

[Park]

Thanks for the tip. I downloaded the JTDS driver and added it using the OO package manager. Following the URL directions at the website I set up the datasource URL and driver; java.sql.Driver. When I click on the Test Class button I get a "JDBC Driver loaded successfully", but then in the next step of the wizard, the Test Connection button gives a "Specified Driver Could Not Be Loaded!" error. So, I guess I need to do some more research.

This leads to another question; Which is the best way to add the driver package; the GUI interface or using unopkg from the command line? I tried both and ended up with two instances of jtds-1.1.jar in the listing. I tried one, then the other, then both, but they all give the same error.

[sgriffin]

I realize that this is an old thread, but for future generations

I am using OOo Database 2.3.0 and SQL Server 2000. This should work for similar set-ups.

Follow these steps to connect to sql server using jdbc jtds.

1. Open OOo Database and create a new database. Go to Tools>Options and select the Java tab. This should tell you the location of your JRE.

2. Obtain the most recent jtds java archive. Mine is jtds-1.2.2.jar. Copy this jar file into your JRE/lib/ext directory. Mine is located at /usr/lib/jvm/java-6-sun-1.6.0.03/jre/lib/ext, but yours may be different.

3. Click the "Class Path" button then on the "Class Path" form click on the "Add Archive" button. Navigate to the location of your jtds<version>.jar file and select it in the file list. Click "Open". Click the "OK" button until all of the open forms are closed.

4. Restart OOo Database.

5. On the Database Wizard, click the radio button entitled "Connect to an existing database", select the JDBC option in the pull down list and click "Next".

In the Datasource URL field type your connection string:

jtds:sqlserver://hostname:portnumber/databasename.

(example: jtds:sqlserver://intranetdb:1433/ourintranet)

In the JDBC driver class field type:

net.sourceforge.jtds.jdbc.Driver

Click "Test class". If you get a message that reads "The JDBC driver was loaded successfully" you are good to go. Click next.

6. Set up your authentication and click Test Connection. You should get "The connection was established successfully". Click "Next".

7. Configure the rest of your options on the final form and click "Finish" Save your database file. You can use this file to open your database connection to Sql Server in the future.

Good luck!

[preserver3]

As an added note, you can drop the microsoft driver in the ./JRE/lib/ext above, add it with the classpath tool in ooBase, and then when you setup a connection, use a standard jdbc string (ex:jdbc:microsoft:sqlserver://servername:portnum;DatabaseName=dbname)
and
com.microsoft.jdbc.sqlserver.SQLServerDriver as the driver to load

And simply use the class 4 driver meant for this purpose. Its great for testing JBoss or Tomcat connection strings since they'd be identical.

[ottoshmidt1]

[seforsource]

I tried first:
URL
jdbc(default):jtds:sqlserver://localhost:1433/myBase
Class JDBC
net.sourceforge.jtds.jdbc.Driver
it works!
I tried two:
URL
jdbc(default):sqlserver://localhost:1433;databaseName=myBase
Class JDBC
com.microsoft.sqlserver.jdbc.SQLServerDriver
it works!

But I can not edit the data! Not any! What do I do?
p.s.: my English is poor!

[ottoshmidt1]

[seforsource]

ottoshmidt1, i'm connect to base MSSQL Express under user sa!
I think that I incorrectly configured driver!
But I have not changed, I just installed it and everything!

[brianke]

SOLVED: I failed to remove the out sqljdbc.jar from lib/ext.

I came across this thread and am trying to connect using the Microsoft driver (com.microsoft.sqlserver.jdbc.SQLServerDriver). I get a success message when I test the class but when I test the connection I get the following pop-up error message:

Java Runtime Environment (JRE) version 1.6 is not supported by this driver. use the sqljdbc4.jar class library, which provides support for JDBC 4.0.

The error message is pretty direct and I added the sqljdbc4.jar to my Java class path in OpenOffice and restarted. However, when I try to test the connection I get the same error message.

Is there an updated jar that I am not finding or something I am missing? I am running JRE v. 1.6.0_20

Thank you,
Brian Enderle

[JayBofMA]

I have tried this JTDS integration with LibreOffice 3.x Database and I am not impressed. The performance is terrible. Too much overhead of LiberOffice I suspect, despite 64bit on 8cores with 8GB.

The same JTDS can be used as a third-party driver in the free Oracle SQL Developer. The driver will add a SQL Server tab to connection property dialogs. JTDS performance is quite good in SQL Developer. Although you will loose some of the IDE features of the native Oracle drivers. (e.g. no code completion so database names and fields are not suggested while typing)