Eclipse Community Forums: BIRT » BIRT and SQL 2008 R2

Help

Home

Home » Archived » BIRT » BIRT and SQL 2008 R2(Difference between SQL 2005 and 2008 R2)

Show: Today's Messages :: Show Polls :: Message Navigator

BIRT and SQL 2008 R2 [message #836671]

Wed, 04 April 2012 19:28

Perry Smith

Messages: 4
Registered: April 2012

Junior Member

using BIRT 3.7.2 and Microsoft SQL Server JDBC Driver 4.0, data source connection tests for SQL 2005 and 2008 R2 both appear to be positive. But, when I open the editor for 2008 R2 data sets, in the Query section. I do not see all of the schemas I do when I am viewing the same for SQL 2005, only the main connection node. No data is returned. This message appears in details. What can I do to et BIRT working with 2008 R2?

org.eclipse.birt.report.engine.api.EngineException: Error happened while running the report.
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.doRun(DatasetPreviewTask.java:135)
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.runDataset(DatasetPreviewTask.java:97)
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.execute(DatasetPreviewTask.java:49)
at org.eclipse.birt.report.designer.data.ui.dataset.DataSetPreviewer.preview(DataSetPreviewer.java:69)
at org.eclipse.birt.report.designer.data.ui.dataset.ResultSetPreviewPage$5.run(ResultSetPreviewPage.java:337)
at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)
Caused by: org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot get the result set metadata.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:The connection is closed.
;
com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:52)
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:108)
at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:84)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getRuntimeMetaData(PreparedStatement.java:414)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getProjectedColumns(PreparedStatement.java:377)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.doGetMetaData(PreparedStatement.java:347)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.execute(PreparedStatement.java:563)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:927)
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:428)
at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1134)
at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:232)
at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:177)
at org.eclipse.birt.data.engine.impl.QueryResults.getResultMetaData(QueryResults.java:132)
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.extractQuery(DatasetPreviewTask.java:169)
at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.doRun(DatasetPreviewTask.java:126)
... 5 more
Caused by: org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:The connection is closed.
;
com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:464)
at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaUsingDefaultPolicy(Statement.java:371)
at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaData(Statement.java:313)
at org.eclipse.birt.report.data.oda.jdbc.bidi.BidiStatement.getMetaData(BidiStatement.java:56)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.doGetMetaData(OdaQuery.java:412)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.getMetaData(OdaQuery.java:379)
at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getRuntimeMetaData(PreparedStatement.java:407)
... 16 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:388)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.checkClosed(SQLServerStatement.java:978)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:284)
at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:460)
... 22 more

Report message to a moderator

Re: BIRT and SQL 2008 R2 [message #837442 is a reply to message #836671]

Thu, 05 April 2012 17:07

Jason Weathersby

Messages: 9167
Registered: July 2009

Senior Member

Can you try the JTDS driver as described in this bug?
https://bugs.eclipse.org/bugs/show_bug.cgi?id=298730

Jason

On 4/4/2012 3:28 PM, Perry Smith wrote:
> using BIRT 3.7.2 and Microsoft SQL Server JDBC Driver 4.0, data source
> connection tests for SQL 2005 and 2008 R2 both appear to be positive.
> But, when I open the editor for 2008 R2 data sets, in the Query section.
> I do not see all of the schemas I do when I am viewing the same for SQL
> 2005, only the main connection node. No data is returned. This message
> appears in details. What can I do to et BIRT working with 2008 R2?
>
>
>
> org.eclipse.birt.report.engine.api.EngineException: Error happened while
> running the report.
> at
> org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.doRun(DatasetPreviewTask.java:135)
>
> at
> org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.runDataset(DatasetPreviewTask.java:97)
>
> at
> org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.execute(DatasetPreviewTask.java:49)
>
> at
> org.eclipse.birt.report.designer.data.ui.dataset.DataSetPreviewer.preview(DataSetPreviewer.java:69)
>
> at
> org.eclipse.birt.report.designer.data.ui.dataset.ResultSetPreviewPage$5.run(ResultSetPreviewPage.java:337)
>
> at
> org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)
>
> Caused by: org.eclipse.birt.data.engine.odaconsumer.OdaDataException:
> Cannot get the result set metadata.
> org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does
> not return a ResultSet object.
> SQL error #1:The connection is closed.
> ;
> com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
> at
> org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:52)
>
> at
> org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:108)
>
> at
> org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:84)
>
> at
> org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getRuntimeMetaData(PreparedStatement.java:414)
>
> at
> org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getProjectedColumns(PreparedStatement.java:377)
>
> at
> org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.doGetMetaData(PreparedStatement.java:347)
>
> at
> org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.execute(PreparedStatement.java:563)
>
> at
> org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:927)
>
> at
> org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:428)
>
> at
> org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1134)
>
> at
> org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:232)
>
> at
> org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:177)
>
> at
> org.eclipse.birt.data.engine.impl.QueryResults.getResultMetaData(QueryResults.java:132)
>
> at
> org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.extractQuery(DatasetPreviewTask.java:169)
>
> at
> org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.doRun(DatasetPreviewTask.java:126)
>
> ... 5 more
> Caused by: org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL
> statement does not return a ResultSet object.
> SQL error #1:The connection is closed.
> ;
> com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
> at
> org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:464)
>
> at
> org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaUsingDefaultPolicy(Statement.java:371)
>
> at
> org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaData(Statement.java:313)
>
> at
> org.eclipse.birt.report.data.oda.jdbc.bidi.BidiStatement.getMetaData(BidiStatement.java:56)
>
> at
> org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.doGetMetaData(OdaQuery.java:412)
>
> at
> org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.getMetaData(OdaQuery.java:379)
>
> at
> org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getRuntimeMetaData(PreparedStatement.java:407)
>
> ... 16 more
> Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The
> connection is closed.
> at
> com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
>
> at
> com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:388)
>
> at
> com.microsoft.sqlserver.jdbc.SQLServerStatement.checkClosed(SQLServerStatement.java:978)
>
> at
> com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:284)
>
> at
> org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:460)
>
> ... 22 more
>

Report message to a moderator

Re: BIRT and SQL 2008 R2 [message #838090 is a reply to message #837442]

Fri, 06 April 2012 14:49

Perry Smith

Messages: 4
Registered: April 2012

Junior Member

When I test connection using net.sourceforge.jtds.jdbc.Driver (v1.1), this message appears;
"The selected driver cannot parse the given url."
org.eclipse.birt.report.data.oda.jdbc.JDBCException: The selected driver cannot parse the given url.
at org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager.testConnection(JDBCDriverManager.java:696)
at org.eclipse.birt.report.data.oda.jdbc.ui.util.DriverLoader.testConnection(DriverLoader.java:147)
at org.eclipse.birt.report.data.oda.jdbc.ui.util.DriverLoader.testConnection(DriverLoader.java:175)
at org.eclipse.birt.report.data.oda.jdbc.ui.util.DriverLoader.testConnection(DriverLoader.java:156)
at org.eclipse.birt.report.data.oda.jdbc.ui.profile.JDBCSelectionPageHelper.testConnection(JDBCSelectionPageHelper.java:715)
at org.eclipse.birt.report.data.oda.jdbc.ui.profile.JDBCSelectionPageHelper.access$7(JDBCSelectionPageHelper.java:683)
at org.eclipse.birt.report.data.oda.jdbc.ui.profile.JDBCSelectionPageHelper$7.widgetSelected(JDBCSelectionPageHelper.java:606)
at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:240)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1053)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4165)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3754)
at org.eclipse.jface.window.Window.runEventLoop(Window.java:825)
at org.eclipse.jface.window.Window.open(Window.java:801)
at org.eclipse.birt.report.designer.ui.dialogs.BaseDialog.open(BaseDialog.java:111)
at org.eclipse.birt.report.designer.data.ui.actions.EditDataSourceAction.doAction(EditDataSourceAction.java:69)
at org.eclipse.birt.report.designer.internal.ui.views.actions.AbstractElementAction.run(AbstractElementAction.java:70)
at org.eclipse.jface.action.Action.runWithEvent(Action.java:498)
at org.eclipse.jface.action.ActionContributionItem.handleWidgetSelection(ActionContributionItem.java:584)
at org.eclipse.jface.action.ActionContributionItem.access$2(ActionContributionItem.java:501)
at org.eclipse.jface.action.ActionContributionItem$5.handleEvent(ActionContributionItem.java:411)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1053)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4165)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3754)
at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.java:2701)
at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:2665)
at org.eclipse.ui.internal.Workbench.access$4(Workbench.java:2499)
at org.eclipse.ui.internal.Workbench$7.run(Workbench.java:679)
at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)
at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:668)
at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:149)
at org.eclipse.ui.internal.ide.application.IDEApplication.start(IDEApplication.java:123)
at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:110)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:79)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:344)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:179)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:622)
at org.eclipse.equinox.launcher.Main.basicRun(Main.java:577)
at org.eclipse.equinox.launcher.Main.run(Main.java:1410)

Report message to a moderator

Re: BIRT and SQL 2008 R2 [message #840040 is a reply to message #838090]

Mon, 09 April 2012 15:38

Jason Weathersby

Messages: 9167
Registered: July 2009

Senior Member

Did you change the url to look like:
jdbc:jtds:sqlserver://server:port/dbname?

Jason

On 4/6/2012 10:49 AM, Perry Smith wrote:
> When I test connection using net.sourceforge.jtds.jdbc.Driver (v1.1),
> this message appears;
> "The selected driver cannot parse the given url."
> org.eclipse.birt.report.data.oda.jdbc.JDBCException: The selected driver
> cannot parse the given url.
> at
> org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager.testConnection(JDBCDriverManager.java:696)
>
> at
> org.eclipse.birt.report.data.oda.jdbc.ui.util.DriverLoader.testConnection(DriverLoader.java:147)
>
> at
> org.eclipse.birt.report.data.oda.jdbc.ui.util.DriverLoader.testConnection(DriverLoader.java:175)
>
> at
> org.eclipse.birt.report.data.oda.jdbc.ui.util.DriverLoader.testConnection(DriverLoader.java:156)
>
> at
> org.eclipse.birt.report.data.oda.jdbc.ui.profile.JDBCSelectionPageHelper.testConnection(JDBCSelectionPageHelper.java:715)
>
> at
> org.eclipse.birt.report.data.oda.jdbc.ui.profile.JDBCSelectionPageHelper.access$7(JDBCSelectionPageHelper.java:683)
>
> at
> org.eclipse.birt.report.data.oda.jdbc.ui.profile.JDBCSelectionPageHelper$7.widgetSelected(JDBCSelectionPageHelper.java:606)
>
> at
> org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:240)
> at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
> at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1053)
> at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4165)
> at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3754)
> at org.eclipse.jface.window.Window.runEventLoop(Window.java:825)
> at org.eclipse.jface.window.Window.open(Window.java:801)
> at
> org.eclipse.birt.report.designer.ui.dialogs.BaseDialog.open(BaseDialog.java:111)
>
> at
> org.eclipse.birt.report.designer.data.ui.actions.EditDataSourceAction.doAction(EditDataSourceAction.java:69)
>
> at
> org.eclipse.birt.report.designer.internal.ui.views.actions.AbstractElementAction.run(AbstractElementAction.java:70)
>
> at org.eclipse.jface.action.Action.runWithEvent(Action.java:498)
> at
> org.eclipse.jface.action.ActionContributionItem.handleWidgetSelection(ActionContributionItem.java:584)
>
> at
> org.eclipse.jface.action.ActionContributionItem.access$2(ActionContributionItem.java:501)
>
> at
> org.eclipse.jface.action.ActionContributionItem$5.handleEvent(ActionContributionItem.java:411)
>
> at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
> at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1053)
> at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4165)
> at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3754)
> at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.java:2701)
> at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:2665)
> at org.eclipse.ui.internal.Workbench.access$4(Workbench.java:2499)
> at org.eclipse.ui.internal.Workbench$7.run(Workbench.java:679)
> at
> org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)
>
> at
> org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:668)
> at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:149)
> at
> org.eclipse.ui.internal.ide.application.IDEApplication.start(IDEApplication.java:123)
>
> at
> org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
>
> at
> org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:110)
>
> at
> org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:79)
>
> at
> org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:344)
>
> at
> org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:179)
>
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
> at java.lang.reflect.Method.invoke(Unknown Source)
> at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:622)
> at org.eclipse.equinox.launcher.Main.basicRun(Main.java:577)
> at org.eclipse.equinox.launcher.Main.run(Main.java:1410)
>

Report message to a moderator

Re: BIRT and SQL 2008 R2 [message #840042 is a reply to message #838090]

Mon, 09 April 2012 15:42

Perry Smith

Messages: 4
Registered: April 2012

Junior Member

Using this example from the link you provided as my guide, jdbc:jtds:sqlserver://shanghai:1433/test , I am able to connect to one form of Database URL but not another. The first successful connection test to a 2008 R2 DB looks like this jdbc:jtds:sqlserver://shanghai:1433;databaseName = Name

What does not work is my reference to a sub-instance; jdbc:jtds:sqlserver://shanghai\SQL1;databaseName=Name
I assume this is because I don't know what the syntax should be for jtds in this instance. The last 3 connections I need to establish are jdbc:jtds:sqlserver://shanghai\SQL1;databaseName=Name, jdbc:jtds:sqlserver://shanghai\SQL2:port;databaseName=Name, jdbc:jtds:sqlserver://shanghai\SQL3;databaseName=Name

Suggestions?

Report message to a moderator

Re: BIRT and SQL 2008 R2 [message #840088 is a reply to message #840042]

Mon, 09 April 2012 16:37

Jason Weathersby

Messages: 9167
Registered: July 2009

Senior Member

Have you seen this:
http://confluence.atlassian.com/display/JIRA/Connecting+to+named+instances+in+SQL+Server

Jason

On 4/9/2012 11:42 AM, Perry Smith wrote:
> Using this example from the link you provided as my guide,
> jdbc:jtds:sqlserver://shanghai:1433/test , I am able to connect to one
> form of Database URL but not another. The first successful connection
> test to a 2008 R2 DB looks like this
> jdbc:jtds:sqlserver://shanghai:1433;databaseName = Name
>
> What does not work is my reference to a sub-instance;
> jdbc:jtds:sqlserver://shanghai\SQL1;databaseName=Name
> I assume this is because I don't know what the syntax should be for jtds
> in this instance. The last 3 connections I need to establish are
> jdbc:jtds:sqlserver://shanghai\SQL1;databaseName=Name,
> jdbc:jtds:sqlserver://shanghai\SQL2:port;databaseName=Name,
> jdbc:jtds:sqlserver://shanghai\SQL3;databaseName=Name
>
> Suggestions?

Report message to a moderator