[Logo] SpagoWorld Forums
  [Search] Search   [Recent Topics] Recent Topics   [Hottest Topics] Hottest Topics   [Members]  Member Listing  
[Groups] Back to home page  [Moderation Log] Moderation Log   [Register] Register /  [Login] Login 
MS SQL Server datasource  XML
Forum Index » Technical Help - SpagoBI Server - CORE
Author Message
abhijit_100.spw



Joined: 01/11/2011 11:05:32
Messages: 6
Offline

I installed SPAGOBI server today and I am trying to work with MSSQL Server 2008 db
but when I try to create a datasource for sql sever i always get a connection test failure

I have placed the jdbc driver files in tomcat\lib

under datasources i have entered the foll:

LABEL SQL Server
DESCRIPTION MS SQL Server 2008
DIALECT SQL Server
MULTISCHEMA No
SCHEMA ATTRIBUT [blank]
TYPE Jdbc
JNDI NAME [blank]
URL jdbc:sqlserver:AVD\sqlexpress;databaseName=AdventureWorks;
USER ***
PASSWORD ***
DRIVER com.microsoft.sqlserver.jdbc.SQLServerDriver


In the server.xml file under tomcat\conf I have added the foll:
<GlobalNamingResources>
<Resource auth="Container" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" maxActive="20" maxIdle="10" maxWait="-1"
name="jdbc/sqlserver" password="***" type="javax.sql.DataSource" url="jdbc:sqlserver:AVD\sqlexpress;databaseName=AdventureWorks;" username="***"/>
</GlobalNamingResources>

*** Not sure what goes for type="javax.sql.DataSource" for MS SQL Server

What am I missing here?
I have not installed mySQL, but the connection test for SpagoBI passes(although foodmart and BAM fail)
Any help is appreciated.
hahmed.spw

Newby

Joined: 12/08/2011 05:27:15
Messages: 18
Offline

You don't need to define GlobalNamingResoruce in server.xml to add a datasource so first of all you can remove that.

Secondly your JDBC URL should be: jdbc:sqlserver://[SERVERNAME OR IP];instanceName=sqlexpress;databaseName=AdventureWorks

It should work after that.
abhijit_100.spw



Joined: 01/11/2011 11:05:32
Messages: 6
Offline

I have tried with the foll but none of this works
jdbc:sqlserver://127.0.0.1;databaseName=AdventureWorks
jdbc:sqlserver://AVD\\SQLEXPRESS;databaseName=AdventureWorks
jdbc:sqlserver://AVD;instanceName=sqlexpress;databaseName=AdventureWorks
jdbc:sqlserver://AVD:1437;instanceName=sqlexpress;databaseName=AdventureWorks
jdbc:sqlserver://localhost;instanceName=sqlexpress;databaseName=AdventureWorks
hahmed.spw

Newby

Joined: 12/08/2011 05:27:15
Messages: 18
Offline

You have the SQL JDBC .jar file in tomcat/lib folder?

Can you check logs/SpagoBI.log and logs/catalina.out for any errors?
abhijit_100.spw



Joined: 01/11/2011 11:05:32
Messages: 6
Offline

I found this in the SpagoBI log file, i do have sqljdbc.var and sqljdbc4.jar in tomcat\lib
[http-8080-6] 07 Nov 2011 18:43:04,500 ERROR it.eng.spagobi.tools.datasource.service.TestConnectionAction.service:108 - Driver not found
java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver
at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1387)
at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1233)
hahmed.spw

Newby

Joined: 12/08/2011 05:27:15
Messages: 18
Offline

Somehow SpagoBI can not find the jar file. You should only have sqljdbc4.jar in lib folder.
aashish.jain16.spw

Newby

Joined: 12/06/2012 15:51:01
Messages: 20
Offline

Hi Abhijit,

I think Now You will have done Connectivity With SQL-SERVER.. bcz help of this only i have done. If you have not

done then I think may be the error for Wrong jar file only or you try after keeping the jar inside tomcat all 3, lib folder and check....

So I wanted to tell you.thanx.....

Ashish Jain
zsoltputnoky.spw

SpagoBI friend

Joined: 03/12/2012 16:13:48
Messages: 94
Offline

Hi There,

I had similar issue earlier, have a look on this post:

http://www.spagoworld.org/jforum/posts/list/2045.page

I had to copy the SQLJDBC4.JAR into another folder as well. You will find it in the post mentioned.

Hope it helps,

Zsolt
aashish.jain16.spw

Newby

Joined: 12/06/2012 15:51:01
Messages: 20
Offline

Hi Zsolt,

Thanks a lot for your valuable solution.

To move further I am facing one more issue after connecting to SQL-SERVER I am not able to generate the reports,Charts,Graphs from my sql server bcz I am not able to find any options for that. And also I have both databases in SQL-Server RDBMS and OLAP DB.

So Plz Plz help me on this... and Save me...

Thanks in Advance.
Ashish
zsoltputnoky.spw

SpagoBI friend

Joined: 03/12/2012 16:13:48
Messages: 94
Offline

Hi Ashis,

Well that is a big subject. What you can do 1st is to have a look on the Wiki : http://wiki.spagobi.org/xwiki/bin/view/Main/ where you can find some information. What you need for sure is to install SpagoBI Studio, this is an Eclipse based tool to develop reports. There you can connect to MS SQL database and use either BIRT or Jasper to develop the reports.

I advise you to check out these two software to have an idea what to do with them.

I am not an expert myself, fighting to make it work :) properly, but try to help as much as possible.

Let me know what info you need,

Zsolt
aashish.jain16.spw

Newby

Joined: 12/06/2012 15:51:01
Messages: 20
Offline

Thanks Zsolt For Help and So Quick Response.

But Now I am Bit Confused like I have Installed Spago-BI all In one Package and using SapgoBI-Server now I am able to see and analyze only there dump data like foodmart,spagobi.
So Your Mean to say we can't Analyze our own DB reports or Create reports or Create analysis without the help of SpagoBI-Studio Is it.

Thanks,
Ashish Jain
zsoltputnoky.spw

SpagoBI friend

Joined: 03/12/2012 16:13:48
Messages: 94
Offline

Hi Ashish,

The SpagoBI All-in-One gives you the server and all the SpagoBI Engines in a Tomcat bundle. This works properly and you can check all the functions of SpagoBI with the help of the samples. You can also create a new datasource - from Resources -> Data Source. Here you can add the connection to your own database.

To develop reports - which then are deployed in the server - you can use Birt or Jasper -> each has its own report design tool. But, the best is to use the SpagoBI Studio to create them. In the Studio you can declare your server, data sources, etc, design the queries, business models, and report layouts. You can also test them before you deploy it.

Once you are okay with all that, you can do your own SpagoBI implementation by removing the sample database and not used engines from Tomcat. It is a bit complex, but can be done

Zsolt
aashish.jain16.spw

Newby

Joined: 12/06/2012 15:51:01
Messages: 20
Offline

Thanks A lot Zsolt for helping me.

I got your point now i have 1 more doubt.

As you told in All in one server we can connect with our own database with help of -> datasource - from Resources -> Data Source. Here you can add the connection to your own database.
So what is Use for this ....
I mean If we can't create reports charts etc in all in one then what we will do after connecting with Our own DB.

Thanks,
Ashish Jain
zsoltputnoky.spw

SpagoBI friend

Joined: 03/12/2012 16:13:48
Messages: 94
Offline

aashish.jain16.spw wrote: Thanks A lot Zsolt for helping me.

I got your point now i have 1 more doubt.

As you told in All in one server we can connect with our own database with help of -> datasource - from Resources -> Data Source. Here you can add the connection to your own database.
So what is Use for this ....
I mean If we can't create reports charts etc in all in one then what we will do after connecting with Our own DB.

Thanks,
Ashish Jain


Hi there,

The All-in-One means: you have a Tomcat server, a SpagoBI MetaDB and all engines packaged into one file. This also means that you dont have to install Tomcat and configure, your MetaDB is up and running and you have all the engines installed. It is a fully working system. I did all my report using this version, then once all my tests were done, I created a custom installation - my own Tomcat, SpagoBI Meta is running in MySQL and deployed only the engines I wanted to use.

Here is what you do: install SpagoBI - this is a server actually, then install SpagoBI Studio which is used to design the reports, once the reports are designed you can upload them to the server where you can specify the access rights, and the organization, declare the different users, etc. The server itself allows you to do kinda basic reports - as far as I know - not an expert on this - but if you want something performant, use the Studio.

Zsolt
aashish.jain16.spw

Newby

Joined: 12/06/2012 15:51:01
Messages: 20
Offline

Ok Fine Sir Ji..

Thanks a lot for Support.
If needed I will make u trouble again.

Ashish Jain

This message was edited 1 time. Last update was at 28/01/2013 10:03:41

 
Forum Index » Technical Help - SpagoBI Server - CORE
Go to:   
Powered by JForum 2.1.9 © JForum Team