After upgrading the SQL Server repository database from SQL 2008 R2 to SQL 2019, I get the following errors after logging in to Supervisor or Business Objects (Reporter):
USR0057 USR0015
37000
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ‘*=’.
102
37000
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
8180
*= is old, non ANSI92 left outer join syntax.
The old school syntax was:
SELECT
stuff
FROM
tables
WHERE
table joins
conditions
Nowadays, general syntax is
SELECT
stuff
FROM
tables joined to tables
WHERE
conditions
It’s probably also the case that 5.1.8 won’t have a chance of supporting SQL 2019.given that it’s about 20 years old. The last time I had a BO server with 5.1.8 on, it was a Windows 2000 server running SQL Server 2000.
close enough, we are currently running this on 2008 R2. The issue that you have identified appears to be the cause, however, it’s not within the universe connections themselves as they support full_outer joins. The problem seems to be with BusObjs itself, where the application (Supervisor/Reporter) tries to initialise after login by retrieving a list of universes etc. from the repository. These values are selected from the repository using old style outer joins - confirmed by the BOLOGGAPP.exe Tracker application. I’m trying to figure out if there is a way to change the syntax of these queries either in configuration files (prm etc.) or maybe in the registry. Any suggestions gratefully received.
Could you temporarily change the compatibility level of the database to 80?
ALTER DATABASE yourdbnamehere set compatibility_level = 80
Then set it back to 150 after the migration?
Alternatively, upgrading via XIr2 to XI3 to keep the full client capabilities.
Or are you stuck on 5.1.8?
Thanks for the suggestion @MarkP, unfortunately we are stuck with 5.1.8. We have tried lowering the compatibility levels as low as possible but unfortunately no joy with that.
It may be that you just won’t be able to upgrade your SQL Server (if it’s not too late). You may be stuck with an older version if you can’t upgrade Business Objects.
Agree with this. If you’re stuck on 5.1.8, which is well beyond end of life, you’re probably stuck with an old version of SQL Server.