October 14, 2010 at 2:41 pm
Few weeks back we successfully upgraded the database from SQL 2000 to SQL 2008, however the compatibility is still at level 80 (SQL 2000).
If we change it to 90 or higher, few stored procedures start to error out right away.
We would like the compatibility level to be set at 100 (SQL 2008).
Is there any script / program that will identify and list down all the Stored Procedures / Triggers / SSIS packages / Dynamic Queries that need to be changed before the compatibility level is set at 100 (SQL 2008)?
So, far it looks like I can do the following to test the database prior to changing the compatibility level to 100:
1. Run Upgrade Advisor
2. Run Profiler (after changing the compatibility level to 100) to identify issues with the dynamic queries.
3. Run this script:
http://www.sqlservercentral.com/scripts/Compatibility/62093/
However this script tries to create all the stored procedures in the database with the name: 'Temp_TestProc_DeleteMe'
Any other ideas? Any other alternatives?
I also found that the non-ansi joins are not supported in SQL Server 2008. These are join conditions that use the *= or =* operators. How to do a scan of all the stored procedures and application code for either of those 2 operators?
Please let me know.
Thanks
October 14, 2010 at 2:52 pm
The deprecated join is only th *= (LEFT OUTER) join. For some goofy reason they left the RIGHT OUTER join shorthand alone (at least in initial releases of SQL 2008).
If you already know some of the issues and want to locate all occurances of them I would download the RedGate SQLSearch tool. It is free and can search through most all database objects for occurances of string values that you provide. Very nice tool that I use almost daily.
http://www.red-gate.com/products/SQL_Search/index.htm
Other than that, the upgrade advisor seems to do a pretty thorough job of pointing out compatibility issues.
October 14, 2010 at 3:19 pm
I am in the process of doing the same, please advise if you find anything else.
I have not as of yet
THANKS
October 14, 2010 at 10:04 pm
You might want to run this select statement and either output to text or to a file. Set SSMS to output text to a new window, you can cut and paste the text output into a Word Doc, and then search the word doc for those items the upgrade advisor suggests you change.
Or select as the SSMS output a report. A Crystal Report (.rpt) will be created where you specify. The rpt file can be opened with Notepad or Microsoft Word and then searched.
SELECT * FROM sys.all_sql_modules
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply