“SQL Statement preparation failed”

www.obieefans.com 


Q: When I try to run the usage tracking request in Presentation Services I get an error, “SQL Statement preparation failed”?

Another little Oracle oopsie.

This bug takes a little bit of detective work.  Now you may remember that towards the beginning of the tutorial you were asked to change the name of “LOGIN” to “USER_NAME” in script “SQLServer_create_nQ_UserGroup.sql” since “LOGIN” is an Oracle reserved word (despite the fact that you’ve just run several Oracle specific setup scripts, you now have to modify a script intended for SQLServer, which says wonders about the care Oracle has taken in preparing and testing the contents of its OBIEE distribution).  In any case, with this change in place the view created by the script, “NQ_LOGIN_GROUP”, has two columns “USER_NAME” and “RESP”.

If you open the repository “sh_usagetracking.rpd” with the Administration Tool and, in the Physical layer, navigate to “OBI Usage Tracking => Catalog => dbo” you’ll find that “NQ_LOGIN_GROUP” also has two columns.  One is “RESP”, but the other is ... can you guess ... “LOGIN” – not “USER_NAME”.  So the BI Server is asking the database for the value of column “LOGIN”, but the database is saying in response, “Sorry, Guv, we don’t hav’ one of them”, or, in other words, “SQL Statement preparation failed”.

So while the tutorial has asked you to update the column in the view creation script, it has supplied you with a repository in which the corresponding change has not been made, and the instructions for editing the repository in the tutorial fail to mention that this change needs to be made.  So just rename column “LOGIN” to “USER_NAME”, save the repository, and restart the BI Server.  Then, fingers crossed, all will be well.