Business Objects - SQL server outer join error

[Mutagen]

We have a universe built on a SQL Server DB which incorporates left outer joins.

In order to have this function, we changed the SQLSVR.prm files to accept outer joins.

This worked fine for about a year (until now). Now we get MS error code 1016 messages every time our users run the report, or any report utilizing the outerjoined tables.

I have checked the prm files they are as they were:

STG= STSRVen
SORT_BY_NO=NO
GROUPBYCOL=NO
EXT_JOIN=YES
CONCAT=+
UNION=UNION
INTERSECT=
MINUS=
OWNER=Y
QUALIFIER=Y
COMMA=+' '+
REFRESH_COLUMNS_TYPE=Q
CHECK_OWNER_STATE=N
CHECK_QUALIFIER_STATE=N
KEY_INFO_SUPPORTED=Y
OUTERJOINS_GENERATION=FULL_ODBC
OUTERJOINS_COMPLEX=Y
BACK_QUOTE_SUPPORTED=Y
TABLE_DELIMITER="

Does anyone have any ideas what may be causing this to fail?

Any help would be greatly appreciated.

Thanks,

Dave

 

[Sridharan]

Hi Dave,

Just run the SQL that BO generates in the SQL Analyzer and check up whether it throws the error.

Regards,
Sridharan

 

[Mutagen]

Thanks Sridharan,
Unfortunately it returned the same error message.
We are in the process of reformatting the query into native SQl .

It seems to work then. It has been submitted to our users for review of returned data.

Thanks again,

Dave

 

[skrandel]

Are your joins showing up in the FROM clause or the WHERE clause?
--------------------------
BusinessObjects version 5.1.x supports ANSI SQL syntax.
Switch to ANSI_92 in the OUTERJOINS_GENERATION statement according to the steps below:

1. Locate the .prm file for your database:
For example, the Informix Dynamic Server 2000 has the file, Ifxen.prm (where "en" refers to the language used: en for English, fr for French, etc). The .prm file is stored under the Business Objects 5.0/Data Access/Informix folder.

2. In the prm file, change the OUTERJOINS_GENERATION statement to:

OUTERJOINS_GENERATION=ANSI_92

3. Remove the lines below from the .prm file:
LEFT_OUTER=$*
RIGHT_OUTER=*$

Note: You may or may not have these lines in your .prm file, depending upon which database you have. You can either remove the lines, or "comment" them by putting a semicolon at the beginning, or by deleting $* and *$ respectively.

Now the inner and outer joins are specified in the FROM
clause instead of the WHERE clause, producing the example Select statement below:
SELECT
orders.order_num,
items.item_num,
stock.stock_num
FROM
((orders LEFT OUTER JOIN items ON (orders.order_num=items.order_num) )

INNER JOIN stock ON (stock.stock_num=items.stock_num) )
Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com

 

[Mutagen]

Hi Steve,

Thanks for the help.
Here is the whole query:

******************Begin of SQL***************
SELECT
dbo.CHG_Change.Summary,
dbo.CHG_Change.Description,
dbo.CHG_Change.Change_ID_,
dbo.CHG_Change.Requester_Name_,
dbo.PKG_SAP_Transport.Description,
dbo.PKG_SAP_Transport.Comments,
dbo.PKG_SAP_Transport.OSS_Notes,
dbo.PKG_SAP_Transport.DV2K,
dbo.PKG_SAP_Transport.OBJS,
dbo.PKG_SAP_Transport.Tested_By,
dbo.PKG_SAP_Transport.Technical_Sign_Off,
dbo.PKG_SAP_Transport.BSA_Sign_Off_By,
dbo.PKG_SAP_Transport.User_Sign_Off,
dbo.PKG_SAP_Transport.Submitter,
convert(datetime,(convert(decimal(38,6),dbo.A_CHG_ChangeTask.Time__Closed/86400)+25567+convert(decimal(38,6),(dbo.A_CHG_ChangeTask.Time__Closed-(dbo.A_CHG_ChangeTask.Time__Closed/86400)*86400)*.00001157407407407))),
dbo_SJC_BO_Integer_Lookups2.CHG_Emergency,
dbo.CHG_Change.Supervisor_Name_,
dbo.A_CHG_ChangeTask.Request_ID,
dbo.PKG_SAP_Transport.Request_ID,
dbo.A_CHG_ChangeTask.Tasks,
dbo.A_CHG_ChangeTask.Input
FROM
((((dbo.PKG_SAP_Transport LEFT OUTER JOIN dbo.PKG_TASK_To_PKG ON (dbo.PKG_TASK_To_PKG.Package_ID*=dbo.PKG_SAP_Transport.Request_ID) )
LEFT OUTER JOIN dbo.A_CHG_ChangeTask ON (dbo.A_CHG_ChangeTask.Request_ID*=dbo.PKG_TASK_To_PKG.Task_ID) )
LEFT OUTER JOIN dbo.CHG_Change ON (dbo.A_CHG_ChangeTask.Change_Request_ID=*dbo.CHG_Change.Change_ID_) )
INNER JOIN dbo.SJC_BO_Integer_Lookups dbo_SJC_BO_Integer_Lookups2 ON (dbo_SJC_BO_Integer_Lookups2.Integer_Value=dbo.CHG_Change.Status) )

WHERE
(
dbo.A_CHG_ChangeTask.Request_ID IN @variable('Task ID(s):')
)
******************End of SQL***************

I altered the SQLSRVEN.PRM files in the ODBC folder as well as the OLEDB folder. Still getting the error.

It used to work fine.
I am awaiting the return of the app. owner who updated the app. last week.

Thanks again,

Best Regards,

Dave

 

[skrandel]

Have you recently upgraded BO?

The problem is that the *s that are showing up in your outer joins.

Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com

 

[Mutagen]

No. (re-upgrade)
We are running 5.1.4 for Designer, Sup. et al.
Should I reimport and export the universe post change to the ANSI in PRM?

Thanks,

Dave

 

[skrandel]

I don't know what to tell you. I do know the problem is the *s.

Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com