cancel
Showing results for 
Search instead for 
Did you mean: 

Upgrading SQL Server instance to 2005 - Upgrade Advisor Warning

cannelongo
Level 2
We are preparing to upgrade a SQL Server 2000 box that currently hosts the BEDB database to SQL Server 2005.  In planning for the upgrade, I have run the Upgrade Advisor tool against the server, and it identified the following incompatibility issue:
 
In SQL Server 2005, column aliases in the ORDER BY clause cannot be prefixed by the table alias.
 
The following stored procedures are affected:
 
ReportBackupSizeByResource
ReportConfigDiag
ReportDailyJobsByResource
ReportDailyPolicyJobs
ReportDailyPolicyJobsByResource
ReportDeviceJobDistribution
ReportFailedJobs
ReportMediaLocationByMediaSet
ReportShowActiveAlertsForServer
ReportShowAlertHistoryForServer
ReportShowNActiveAlerts
ReportShowNAlertHistoryEvents
ReportTestRuns

As is the following view:

MediaSummary_View

For instance, ReportBackupSizeByResource references table aliases in the ORDER BY clause:

Code:
ORDER BY    r.ServerName,    r.ResourceName,    p.PolicyName,    j.JobName


I'm not really sure I should be modifying stored procedures for a vendor's application, so I'm reluctant to try and correct these on my own.  Support said I might try simply uninstalling BE, removing it completely from the server, and reinstalling it once I've finished upgrading SQL Server.  They claim this might cause it to create stored procedures with different code that will work properly under 2005.  I can't help but be somewhat skeptical.

Would it be possible for someone running BE12 on SQL Server 2005 (a clean install, not upgraded to 2005) to send me the code for ReportBackupSizeByResource that they see on their system in a private message?  This would let me confirm that the code actually does differ when installed against SQL Server 2005 before I go through the hassle of reinstalling Backup Exec.  Also, if anyone has successfully upgraded from 2000 to 2005 with a BEDB database already in-place, your insight on the process would be appreciated.  I've been searching these forums and the knowledge base in addition to calling support, and I can't seem to get any definitive information, just some speculation from support.

1 REPLY 1

cannelongo
Level 2

I finally went and set up a test system with a clean SQL Server 2005 SP2 install and BE12.  There doesn't appear to be any differences in the stored procedures between them (though I only compared a sample of the affected objects).  I did notice that the BEDB database is created using compatibility mode 80.  I guess that makes sense; they intend to run the database on all supported server versions using the compatibility mode of the earliest such that only one version of each of the procedures needs to be maintained.  The only weird thing about it is that when I upped the compatibility mode to 90 (just to see what would happen), I didn't get any errors about the ORDER BY clause, and the views and stored procedures appeared to still execute normally.  Odd...

 

I guess the lesson here is simply to leave the compatibility at 80 for the Backup Exec databases when upgrading, which is the default anyway when upgrading.

Message Edited by cannelongo on 06-30-2008 10:26 AM