Non-ANSI Outer Join Operator Issue (reposted due to text issues)

[Former Member]

I am currently using Designer 11.5.0.0. Itu2019s XI Rel 2, but Iu2019m not sure what service pack. I have created several universes with outer joins against a SQL Server 2005 database, but when I try using them in a Crystal report, I get the following error:

Failed to retrieve date from the database. Details: 42000:[Microsoft][ODBC SQL Server Driver][SQL Server] The query uses non-ANSI outer join operators (u201C=u201D or u201C=u201D). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

Here is my ODBC DSN configuration:

Microsoft SQL Server ODBC Driver Version 03.85.1132

Data Source Name: FlexOPS

Data Source Description:

Server: dalsvrw031

Database: (Default)

Language: (Default)

Translate Character

Data: Yes

Log Long Running Queries: No

Log Driver Statistics: No

Use Integrated Security: No

Use Regional Settings: No

Prepared Statements Option: Drop temporary procedures on disconnect

Use Failover Server: No

Use ANSI Quoted Identifiers: Yes

Use ANSI Null, Paddings and Warnings: Yes

Data Encryption: No

Okay, so I understand what the issue is. It appears that the version of Designer that I am using does not default the ANSI92 parameter to u201CYesu201D. So all the outer joins I have created in each of my universe are using the old *= as the join operator. And apparently, the ODBC driver I am using is not very happy with that.

As I understand it from what Iu2019ve read on other sites, I have the following options:

1) Set the ANSI92 parameter to Yes, drop all my joins, close and re-open Designer, and recreate all of the joins.

2) Find a different driver or connectivity method that will support non-ANSI joins.

3) Set my database back to SQL 2000 compatibility.

Option 1 is unappealing as it will cause a lot of time redoing all the work that Iu2019ve spent the past month doing. Option 2 is only a band-aid fix at best. Option 3 really isnu2019t an option for us.

[Former Member]

So I am wondering what other options I have to change these non-ANSI joins to ANSI compatible joins. Do I need to update Designer with a service pack? Is there a script out there that will automatically do this in each of the universes? I would appreciate any suggestions or guidance on this.

Thanks,

Lee

[Former Member]

Hi Lee,

Did you ever find a way of checking for and/or replacing all the Non-ANSI joins in an automated way.

I have about 150+ crystal 8.5 reports that need to be initially checked for Non-ANSI join and then changing.

Interestingly, any new ones created or existing ones modified not seems to contain them, compared to original historic ones with left outer joins?

cheers,

Keith.