Skip to Content
0
Former Member
Jul 06, 2009 at 07:54 PM

Non-ANSI Outer Join Operator Issue

130 Views

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.

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

Edited by: Lee Vance on Jul 6, 2009 10:02 PM