Eclipse Community Forums: BIRT » Birt: Query errors on SQL 2000 [SOLVED]

Home » Archived » BIRT » Birt: Query errors on SQL 2000 [SOLVED](Connection to SQL server is good, but errors on query)

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

Birt: Query errors on SQL 2000 [SOLVED] [message #1694701]

Thu, 07 May 2015 09:18

Martins Zieds

Messages: 7
Registered: May 2015

Junior Member

So,

I'm having a a bit of a weird problem with setting up with BIRT and creating a report.
As for the connection I'm using jtds driver from here
jtds.sourceforge.net
the most popular one - version 1.3.1
The connection to MS SQL 2000 works fine (I know, it's an old db..)
The connection string is correct , i can make a simple data set with simple query and it works, but for some weird reason, when "date" type is introduced it gives this error(see the image)

index.php/fa/21739/0/

To give you an idea of a query, the same error would be thrown is something like this would be executed:

SELECT
(REPLACE(CONVERT(VARCHAR(10), DATEADD(DAY, 0, GETDATE()), 111),'/', '') +'080000')
FROM TEST.TABLE1 WHERE name='test'

I've searched the web and couldn't really find a similar problem. If someone on the first glance has some ideas what the problem could be, i'd much appreciate them. If other info needed, tell me i'll post it.

Attachment: error_2.jpg
(Size: 89.43KB, Downloaded 446 times)

[Updated on: Mon, 11 May 2015 13:04]

Report message to a moderator

Re: Birt: Query errors on SQL 2000 [message #1694728 is a reply to message #1694701]

Thu, 07 May 2015 12:38

Eric Rizzo

Messages: 3070
Registered: July 2009

Senior Member

Moving this to the BIRT forum. In the future please post BIRT-specific questions there.

Report message to a moderator

Re: Birt: Query errors on SQL 2000 [message #1694787 is a reply to message #1694728]

Thu, 07 May 2015 21:01

Michael Williams

Messages: 1925
Registered: July 2009

Senior Member

What happens if you don't use the DATEADD function? It seems like a driver issue, not a BIRT issue, but I can't say that for sure.

Michael

Developer Evangelist, Silanis

Report message to a moderator

Re: Birt: Query errors on SQL 2000 [message #1694818 is a reply to message #1694787]

Fri, 08 May 2015 08:19

Martins Zieds

Messages: 7
Registered: May 2015

Junior Member

If I don't use the DATEADD function then query works, but I think i could be a driver problem - have not found the right one with would support such functions like DATEADD

[Updated on: Fri, 08 May 2015 08:20]

Report message to a moderator

Re: Birt: Query errors on SQL 2000 [message #1694822 is a reply to message #1694818]

Fri, 08 May 2015 08:42

Martins Zieds

Messages: 7
Registered: May 2015

Junior Member

Forgot to mention, tad the query using the same driver (from jtds.sourceforge.net version 1.31) with DBiever (which is made on Eclipse) works very well (with DATEADD and other functions), so maybe it's not the driver, but something else.

Report message to a moderator

Re: Birt: Query errors on SQL 2000 [message #1694866 is a reply to message #1694822]

Fri, 08 May 2015 14:33

Michael Williams

Messages: 1925
Registered: July 2009

Senior Member

It must be an issue with the JDBC ODA in BIRT, then. You could look at the source and see if there are any issues causing this issue. You might try creating a stored procedure with your query and calling that from BIRT instead.

Michael

Developer Evangelist, Silanis

Report message to a moderator

Re: Birt: Query errors on SQL 2000 [message #1695022 is a reply to message #1694866]

Mon, 11 May 2015 13:03

Martins Zieds

Messages: 7
Registered: May 2015

Junior Member

Alight.. i have found a solution/workaround for this problem. The problem was not in the jdbc driver but with the "Data Source" type.

If i choose "JDBC Database connection for Query builder" and then SQL server 2000 it gives these errors when writing "complex" queries.

If i choose "JDBC data source" i can write all my complex queries and everything works.

So, if anyone has to make a connection to an old SQL server , then as a "Data Source" I suggest to use "JDBC data source" and NOT "JDBC Database connection for Query builder"

Report message to a moderator