Can I configure MS SQL in Jasper Server. If yes then how. I have already project working under mysql but my other projects are using MS SQL, so wanted to make everything in one DB. So can anyone please help me to achieve that.
Thanks in advance.
Post Edited by Vaishali Soneta at 02/04/09 13:55
Post Edited by Vaishali Soneta at 02/04/09 14:07
Post Edited by Vaishali Soneta at 02/04/09 14:08
15 Answers:
Thank you so much clhubert. I will go ahead and then use the mysql only.. But then I am having an issue to login with the username and password from the jasper login page.. Can you please help me .. I am trying out with username and password as joeuser /joeuser as suggested by the login page... Should I be using the my own Database username ?
JasperServer does support MS SQL as a report and analysis data source.
As mentioned, you can configure the repository to be in MS SQL as well. Have a look at the source build guide.
The JasperServer Professional edition comes with MS SQL support.
-------------
joeuser/joeuser will work if you loaded the sample data.
If you not load the sample data, only jasperadmin will work.
Sherman
Jaspersoft
Post Edited by Sherman Wood at 02/07/09 21:58
Hello Sherma,
I downloaded the trial version of the jasperserver from the below url ie. from jasperForge.org only.
http://www.jaspersoft.com/jaspersoftv3_eval_win_download.html?newreg=1
But the install guide which is at the same url doesn't say anything about MS SQL... Can you please let me know where is documentation which talks about MS SQL in Jasper
After you install, you can put the jTDS MS SQL/Sybase driver https://sourceforge.net/project/showfiles.php?group_id=33291 in <tomcat>/common/lib and then create a data source using that driver to report or analyze data in MS SQL. There are instructions in the install guide about a full MS SQL install.
Sherman
Jaspersoft
swood
Wrote:
|
After you install, you can put the jTDS MS SQL/Sybase driver https://sourceforge.net/project/showfiles.php?group_id=33291 in <tomcat>/common/lib and then create a data source using that driver to report or analyze data in MS SQL. There are instructions in the install guide about a full MS SQL install. Sherman Jaspersoft |
Hi Sherman,
My JasperServer Pro 3.1 has been installed on a WinTel server running Windows 2000. I am trying to pull/access data from SQL Server 2000 database that's running on a different Windows 2000 server.
I've followed your suggestion and dropped the jtds 1.2.2 into all the necessary folders.
Test connection is Succeessful..!
However, I am not able to accessanything from the SQL Server. Must I buikd some form of schema to present data to JasperServer?
Also, when I start my iReports, I only have choices of JDBC-ODBC, MySQL and PostgreSQL.
How do I present the SQL Server to iReports?
iReport is a separate desktop tool. You need to set up a report data source in iReport, which will use jTDS. You need to add tje jTDS jar to your iReport classpath. There are options to do that in iReport.
There is an option in the iReport plugin to pull data source definitions from JasperServer into iReport.
Given you have the data source working in JasperServer, the process from here is:
- Install the jTDS jar into your iReport classpath
- Pull the data source definitions from JasperServer into iReport using the plugin
- build a report in iReport using the now defined MSSQL data source
- publish the report to JasperServer using the plugin
- Login to JasperServer and run the report in the browser
- You can also run the report on the server from iReport through the plugin
If you set up a domain in JasperServer, you can use the ad hoc report designer in JasperServer without using iReport.
Sherman
Jaspersoft
Hi Sherman,
I shall start this project by using JasperServer to extract data from my SQL Server first.
My test connection was successful.
What are the steps that I should follow next?
When I click Create Ad Hoc Report, everything seems to point back to the sample database. How do I get JasperServer to connect to my SQL Server?
David
Have a look at the JasperServer Professional User Guide.
For ad hoc with non-domain data sources, you need to create "topics" that connect to your data source and do a query. Topics are a report unit: they have a starting JRXML to define the query and the field mappings, connected to a particular data source. You can create a topic JRXML in iReport, test it out as a report, and then deploy it to JasperServer into the /adhoc/topics folder. You new topic will appear when you create a new ad hoc report and can select from the list.
For domains, you need to set up a domain based on your data source. Again this is in the JasperServer Professional User Guide. In the repository explorer, you can add a domain connected to your data source.
Sherman
Jaspersoft
I do apologise aforehand for being rather novice. This is the first time I'm evaluating JasperServer to see if it could be the answer to our search for a corporate reporting tool.
Whilst trying to set up iReports, I've completed the following:
1. downloaded the JTDS driver from sourceforge;
2. place jtds-1.2.2.jar into lib of iReport;
3. extract JtdsXA.dll and ntlmauth.dll into the root of iReport;
4. placed msbase.jar, msutil.jar and mssqlserver.jar in libs folder of iReport;
5. restarted iReport;
6. entered Server Address & Database name into the JDBC URL Wizard and clicked Wizard;
7. encountered ClassNotFoundError exception citing net.sourceforge.jtds.jdbc.Driver.
I've attached both error screen capture and exception trail. Please assist, many thanks in advance..
Post Edited by David Lim at 03/24/09 09:07
So you are working to get iReport to have a data source connected to SQL Server.
You do not need both the jTDS and the Microsoft JDBC driver. Use jTDS only.
You don't need the DLLs. just leave the jTDS JAR as it is.
In iReport, you need to add the jTDS jar onto the classpath. Putting it into the lib directory may not be enough. In iReport you can update the classpath by going to Tools > Options > iReport > Classpath tab. Add the jTDS JAR in there.
Sherman
Jaspersoft
Hi Sherman, thanks loads for your help. I'm able to add the jTDS to iReport.
However, my ClassNotFound exception still needs to be erradicated.
The error message thus:
Exception
Message:
java.sql.SQLException: The syntax of the connection URL 'jdbc:jtds:sqlserver://jdbc:jtds:sqlserver://CITI-DBSVR2:1433/instance=CER-LIVE' is invalid.
Could you help shed some light into where I could have gone wrong? It's teh same exception that the Wixard kicked out earlier. Many, many thanks in advance!
David
Hi guys,
I meet this problem too. I created the js-mssql-ds.xml under MYSERVER\\deploy folder
which is like below
<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
<jndi-name>jdbc/wlsconnect</jndi-name>
<connection-url>jdbc:jtds:sqlserver://CNSHG2k3RP01/CAS_WLSC_Connect</connection-url>
<driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
<user-name>username</user-name>
<password>password</password>
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
<idle-timeout-minutes>15</idle-timeout-minutes>
<metadata>
<type-mapping>MS SQLSERVER</type-mapping>
</metadata>
<check-valid-connection-sql>select 1</check-valid-connection-sql>
</local-tx-datasource>
</datasources>
Then I created the jndi datasource in Jasper server .Service name: jdbc/wlsconnect
But test failed. I have put the jtds jar under \\lib\\ext\\jtds-1.2.2.jar
Do you have any idea what is going on here? I have mysql and db2 jndi datasource run well . But not this jtds sqlserver one.
