Outer Joins with SQL Server

building semantic layer
1

I know this topic has been covered in a couple of other posts but I seem to be missing something somewhere. :confused:

I am using a SQL Server driver to attach to the SQL Server database in a BO 5.1.* environment. When I try to create a report that outer joins 3 tables, the syntax of the SQL shows = and = terminology for the outer join, of course this doesn’t work.

I found the topics about the sqlsrven.prm file and have made the changes to the sqlsrven.prm file that is located on my local copy of the file, the relevant rows now read
LEFT_OUTER=
RIGHT_OUTER=
OUTERJOINS_GENERATION=ANSI_92

The changes didn’t seem to make any difference, the error still remains and the sql is still being generated with the *= syntax.

Does BO only ever check the .prm file from the machine that the report is being run on? I know I have a version of the file on the BCA server that will have to be amended when I start scheduling the report, but are there other versions that BO looks at? I have asked my server managers to see if the file exists on any of the db servers (clutching at straws :!: ) but they don’t. Is there something I should be doing in the universe? Should something be ‘flicked’ in the db?

This is becoming an issue as most of the reports that are being run on this universe contain an outer join somewhere.

Thanks in advance
Susan

suz_reid2 (BOB member since 2003-01-20)

2

Susan,

Are you running the report via web or full client?
You’ll need to make the change on your Webi server if it’s via web.
It is a machine by machine basis in 5.1.x

3

Full client.

The machine that I’m creating the report has had the file amended.

Susan

suz_reid2 (BOB member since 2003-01-20)

4

Are you using ODBC or OLEDB?

5

ODBC.

I am using the SQL Server driver, version 2000.80.194.00

Susan

suz_reid2 (BOB member since 2003-01-20)

6

Interesting - there are two PRM files - one for ODBC and one for OLEDB.
Have you changed the OLEDB one instead?

7

No, I’ll give it a go now.
Susan

suz_reid2 (BOB member since 2003-01-20)

8

:blue:

That worked.

Susan

suz_reid2 (BOB member since 2003-01-20)

9

I have the exact situation, I made the appropriate changes, but the reports are still being generated in the *= form. I am running the reports in Webi , using 6.1a Will I have to change files on Tomcat?

billbejeck (BOB member since 2004-06-03)

10

You will need to change the file(s) on your Webi server

11

I made changes to the *.prm files I could find. The outer joins work in Business Objects reports but not the Webi reports I’m not sure what is meant by Webi server. Does that mean Tomcat? There were no *.prm files in Tomcat.

Thanks,
Bill

billbejeck (BOB member since 2004-06-03)

12

Somewhere on the Webi server, they equivalent .prm files sit. (I can’t check exactly where at the moment, 'cos I don’t have access to the file system on a webi server). I think they’re still under <BO Install Directory\Data Access 5.0\ for v5.

You need to make the same additions to these files on the server, as these are the files that it will use when it generates SQL.

The webi server is the physical machine that runs the WebIntelligence services. Depending on how things are set up, this could be the same machine as the one running Tomcat and the web server software.

Paul Williams :uk: (BOB member since 2002-07-10)

13

OK, I’m now officially confused. :confused:

I have amended the ODBC and OLEDB versions of the sqlsrven.prm file and they are both the same.

After I had changed these, I refreshed the reports I had already created and hey presto, the syntax changed and the reports refreshed with no errors.

I am now trying to create a new report, again containing outer joins, but the error has returned.

Where is the client picking up the parameters from???

Susan

suz_reid2 (BOB member since 2003-01-20)

14

Has the universe been exported from a different machine where the PRM file has not been changed?

15

Do you one Webi server or more than one?

If you have more than one, you’ll need to make the same change to all of them.

Paul Williams :uk: (BOB member since 2002-07-10)

16

The Universe is always imported and exported on my PC where both copies of the file have been amended.

It is exported to a Unix box with Oracle repository.

Susna

suz_reid2 (BOB member since 2003-01-20)