JDBC URL for MS SQL Server - Windows vs. Linux

[emillerphx]

I'll begin by confessing.  I'm a mechanical engineer who grew up on Fortran.  I'm enjoying my journey into database reporting but struggling with this whole Java OO thing...  what does not kill me will make me stronger.

One set of reports I'm making do some fancy queries on our MS Dynamics accounting system.  MS SQL Server on a windows server.  I get a good data connection in iReport on my XP64 windows desktop.  But our JasperReports server is on a Linux machine.  If I use the same JDBC driver and URL, and when I test the connection it does not work. 

I then tried it in iReport because you get an error message there.  Details below.

I can see the machine from the Linux machine (ping) 

Wrong driver?  Wrong syntax between windows and linux?  Stupid mistake?  As always, any pointers would be appreciated.

Eric

Details:

JDBC Driver: sun.jdbc.odbc.JdbcOdbcDriver

JDBC URL: jdbc:odbc://underdog;DSN=Dynamics

Error whenI test my Connection:
Message:
    java.sql.SQLException: [unixODBC][Driver Manager]Data source name not found, and no default driver specified
Level:
    SEVERE
Stack Trace:
    sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
    sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
    sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3074)
    sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.java:323)
    sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:174)
    it.businesslogic.ireport.connection.JDBCConnection.getConnection(JDBCConnection.java:105)
    it.businesslogic.ireport.connection.JDBCConnection.test(JDBCConnection.java:404)
    it.businesslogic.ireport.connection.gui.ConnectionDialog.jButtonTestActionPerformed(ConnectionDialog.java:322)
    it.businesslogic.ireport.connection.gui.ConnectionDialog.access$300(ConnectionDialog.java:24)
    it.businesslogic.ireport.connection.gui.ConnectionDialog$4.actionPerformed(ConnectionDialog.java:274)
    javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1849)
    javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2169)
    javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
    javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)
    javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
    java.awt.Component.processMouseEvent(Component.java:5517)
    javax.swing.JComponent.processMouseEvent(JComponent.java:3135)
    java.awt.Component.processEvent(Component.java:5282)
    java.awt.Container.processEvent(Container.java:1966)
    java.awt.Component.dispatchEventImpl(Component.java:3984)
    java.awt.Container.dispatchEventImpl(Container.java:2024)
    java.awt.Component.dispatchEvent(Component.java:3819)
    java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212)
    java.awt.LightweightDispatcher.processMouseEvent(Container.java:3892)
    java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822)
    java.awt.Container.dispatchEventImpl(Container.java:2010)
    java.awt.Window.dispatchEventImpl(Window.java:1791)
    java.awt.Component.dispatchEvent(Component.java:3819)
    java.awt.EventQueue.dispatchEvent(EventQueue.java:463)
    java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:242)
    java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:163)
    java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:153)
    java.awt.Dialog$1.run(Dialog.java:535)
    java.awt.Dialog$2.run(Dialog.java:565)
    java.security.AccessController.doPrivileged(Native Method)
    java.awt.Dialog.show(Dialog.java:563)
    java.awt.Component.show(Component.java:1302)
    java.awt.Component.setVisible(Component.java:1255)
    it.businesslogic.ireport.gui.ConnectionsDialog.jButtonNewParameterActionPerformed(ConnectionsDialog.java:550)
    it.businesslogic.ireport.gui.ConnectionsDialog.access$200(ConnectionsDialog.java:53)
    it.businesslogic.ireport.gui.ConnectionsDialog$6.actionPerformed(ConnectionsDialog.java:216)
    javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1849)
    javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2169)
    javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
    javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)
    javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
    java.awt.Component.processMouseEvent(Component.java:5517)
    javax.swing.JComponent.processMouseEvent(JComponent.java:3135)
    java.awt.Component.processEvent(Component.java:5282)
    java.awt.Container.processEvent(Container.java:1966)
    java.awt.Component.dispatchEventImpl(Component.java:3984)
    java.awt.Container.dispatchEventImpl(Container.java:2024)
    java.awt.Component.dispatchEvent(Component.java:3819)
    java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212)
    java.awt.LightweightDispatcher.processMouseEvent(Container.java:3892)
    java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822)
    java.awt.Container.dispatchEventImpl(Container.java:2010)
    java.awt.Window.dispatchEventImpl(Window.java:1791)
    java.awt.Component.dispatchEvent(Component.java:3819)
    java.awt.EventQueue.dispatchEvent(EventQueue.java:463)
    java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:242)
    java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:163)
    java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:153)
    java.awt.Dialog$1.run(Dialog.java:535)
    java.awt.Dialog$2.run(Dialog.java:565)
    java.security.AccessController.doPrivileged(Native Method)
    java.awt.Dialog.show(Dialog.java:563)
    java.awt.Component.show(Component.java:1302)
    java.awt.Component.setVisible(Component.java:1255)
    it.businesslogic.ireport.gui.ConnectionsDialog.setVisible(ConnectionsDialog.java:623)
    it.businesslogic.ireport.gui.ToolbarConnectionPanel.jButtonDatasourcesActionPerformed(ToolbarConnectionPanel.java:114)
    it.businesslogic.ireport.gui.ToolbarConnectionPanel.access$100(ToolbarConnectionPanel.java:45)
    it.businesslogic.ireport.gui.ToolbarConnectionPanel$2.actionPerformed(ToolbarConnectionPanel.java:100)
    javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1849)
    javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2169)
    javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
    javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)
    javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
    java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:231)
    java.awt.Component.processMouseEvent(Component.java:5517)
    javax.swing.JComponent.processMouseEvent(JComponent.java:3135)
    java.awt.Component.processEvent(Component.java:5282)
    java.awt.Container.processEvent(Container.java:1966)
    java.awt.Component.dispatchEventImpl(Component.java:3984)
    java.awt.Container.dispatchEventImpl(Container.java:2024)
    java.awt.Component.dispatchEvent(Component.java:3819)
    java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212)
    java.awt.LightweightDispatcher.processMouseEvent(Container.java:3892)
    java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822)
    java.awt.Container.dispatchEventImpl(Container.java:2010)
    java.awt.Window.dispatchEventImpl(Window.java:1791)
    java.awt.Component.dispatchEvent(Component.java:3819)
    java.awt.EventQueue.dispatchEvent(EventQueue.java:463)
    java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:242)
    java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:163)
    java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:157)
    java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:149)
    java.awt.EventDispatchThread.run(EventDispatchThread.java:110)
 

 

 

[crax]

Is there a reason you are using odbc and not the jdbc drivers for mssql?  While those drivers have issues, it is possible and I have had them working on a few different setups with sql authentication.

I'm using

JDBC driver:  com.microsoft.sqlserver.jdbc.SQLServerDriver

JDBC url:        jdbc:sqlserver://sqlservername;DatabaseName=dbname

 

I've had issues with the 2.0 ctp mssql drivers, but have had good luck with the 1.2 released ones, which you can find here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=c47053eb-3b64-4794-950d-81e1ec91c1ba&DisplayLang=en

after you get the drivers, you will need to put them in the right place for ireport to use which might vary depending on the ireport version.  You will also need to put them out on your server as well

Post Edited by Glen Ki at 11/14/08 16:27

Post Edited by Glen Ki at 11/14/08 16:29

[emillerphx]

I've tried that driver as well. Works on my windows machine but not on the Linux machine.

I'm begining to think it must be some security setting somewhere that keeps me from connecting. 

Could it be something in SQL Server where it only allows windows machines to connect?

Thank you for your suggestion. 

 

FYI, under the microsoft driver I get a little different error:

Exception
Message:
    com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database requested in login 'DYNAMICS'. Login fails.
Level:
    SEVER

I've double checked the password as well.

Eric

 

Post Edited by Eric Miller at 11/14/08 22:02

[emillerphx]

I have gotten it to work.

i was using the wrong syntax, ignorant of the difference between a DSN and a Database Name:

JDBC Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver

JDBC URL: jdbc:sqlserver://underdog;DSN=Dynamics

Now I'm off to generate pages of useful project accounting reports!

Eric