Here is my situation. We are running Edge 3.0 using SQL Server 2005. I have a Webi report that I created with an existing Universe and the report is able to run fine. I didn't create the Universe, but I had to add new tables to the Universe.
For some reason when I went to create a System DSN to connect to the Universe, it didn't save the System DSN, but I was able to save it as a User DSN using the same name as the System DSN created on the BOXI Server. I was able to add the new tables and objects.
I then went to add the new objects to the existing web report and ran the query, I then received the error "A database error occured. The database error text is: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.. (WIS 10901)". If I removed the new objects, I am able to run the Query fine.
When I add the new objects, I can look at the SQL, copy it and run it in MS SQL Server. Once I try to run the query with the new objects, I get the previously stated database error. Then I try to generate the SQL and get the message that Webi can't generate the SQL even though I was able to see the SQL if I hadn't run it.
I am not sure why I am getting this error, because the report is able to run before adding any new objects. Any suggestions would be helpful. Thanks
The issue was that I needed to get permission on my computer to create a system DSN and not use a local DSN. My local policy was changed on my computer.
But I am now writing a different report and getting the same error. Let me explain the situation.
The report was created and running fine, but the database was created by someone and it only contained partial data. The database is created in a star schema. I had to add more dimension tables. The fact table already had all the columns for the new dimension tables but had a default value of -1. Once I added the missing dimension tables, I reran the DI job that re-populated the fact table with data from all dimension tables. When I go now and run the report if I only run a query with the old dimensions, then the report runs fine and returns data. If I add a object from the newly created dimension fields then I get the error 'A database error occured. The database text is: ... Statement(s) could not be prepared. (WIS 10901)'.
I am not sure where the problem is. The report is using the same System DSN and the SQL created from the new dimensions works just fine in SQL Server Management Studio. We use Windows AD authentication and use an Windows users as our system account to access the database and it has read permissions.
Any help would be great. Thanks
I think the new object that you are trying to create comes from a new table. you said the query fails when a new object is added. This means that there could be a problem when the new table is joined with the existing tables. In the universe deigner do the following things
1> After adding the new table in the universe, see to it that join between this tables and other tables are proper. To check this you can do a Tools->Check Integrity. Check if there are any parse errors due to the join. Also do a refresh structure so that universe objects and schema is refreshed.
2>If you do not get any errors at step 1, export the universe and try to updateor create a new report. If you get errors at step 1> you will have to execute the sql in some db tools like winsql to see if the sql fails even here.
Please let me know if this solves the issue.
I have done all that before and it parsed correctly and the SQL worked in a db tool. What I had to solve the issues is delete the ODBC and recreate it. For some reason the old ODBC wasn't working correctly with the new data structure. I have no idea why how it happened but it is working now. Thanks for your input.