Error we’re getting when trying to run reports in our production environment that have outer joins in the query:
[Microsoft][ODBC SQL Server Driver][SQL Server]Outer join operators cannot be specified in a query containing joined tables.1016
This is happening in version 5.1.4. Our DB where the repo and data is held is on MS SQL Server 2000. Prior to this happening, we have a modified SQLSRVEN.PRM file that kept the “*” from showing up in the query, which is causing us to get this error. Everything worked fine.
Here is what changed:
We upgraded and published several of these 5.1.4 reports to our v.6.1a repo. After this happened, we noticed that the .prm file was no longer removing the “*” form our queries with outer joins.
Now, regardless of report or version, any query that is run against our production repository gets this error when an outer join is present. We can run the same queries in any of our other environments without receiving this error. The only difference is that we did not publish those 5.1.4 docs to any of the other repositories (our environment consists of seperate repo’s for each environment).
And we have another mystery. We have a lone pc, which belongs to one of our DBAs, which for some reason is not affected by this change to the repository, so there must be another variable somewhere that is influencing this outer join problem.
So, we’re still looking, and getting passed around BO tech support, but we’re really at a loss of what could be influencing this.
Any ideas are greatly appreciated.
Thanks in advance,
Jesse
There was a bug in 6.1a regarding this. When you switched your joins to ANSI mode it still left the * operator in the join. If you edit the .prm(or SBO) file, you can fix this. Also, don’t forget that if you are using ZABO, the server is doing the work.
Also, don’t forget about the ANSI=Yes option in the universe.
Hopefully this will get you pointed in the right direction.
Did some playing around with the PRM files, and the ANSI92 parm, and we were able to get the query to run, but we’re still seeing some funny things. I changed ANSI92 from no to yes, ran the query, it worked. Changed it back to no, and the query still works. Tried it on two different systems, same thing happened. I also copied in some modified .prm files in the version 6 folders, and they seem to be working, but if we remove/rename them, we don’t see any difference in the query.
It seems that somehow loading the universe with ANSI92 = Yes from your local is fixing whatever bug was getting in the way of the .prm files doing what they should have been doing. Because as soon as the user imports the universe from the repo again, with ANSI92 = No, they are still able to run the same queries, and the problem is gone.
You wouldn’t know the bug number by any chance would you? I’m heading to techsupport site now to see if I can track it down. Thanks for the help Steve. I’ll post it when I find it.
I was unable to find an existing bug for this, and BO techsupport wasn’t much help. The fix I mentioned above seems to have fixed the problem. Publishing the universe with ANSI92 = yes, and then re-publishing with ANSI92= no did the trick. It took a day for the change to work, but all the queries with outer joins are now working, so something must have refreshed.