SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Testing all procs before upgrading to Compatibility Level 90

By Luiz Barros,

If you use SQL Server 2005 but your database is still in Compatibility Level 80 (SQL Server 2000), you can use this script to test if any procs or functions won't work when you upgrade the database.

The script is quite simple. All that it does is to try to recreate a proc with a different name (Temp_TestProc_DeleteMeTemp_TestProc_DeleteMe). If something goes wrong, the script will record the proc name, the code used to create the proc, and the error message. After trying this with all procs in the database, the procs that couldn't be created will be listed. Those will be the ones that must be fixed before upgrading the database to compatibility level 90.

A word of caution:
1. If the problem is inside of a dynamic SQL code, it won't be detected.
2. This script is intended to find incompatibility only in procs and functions. You still have to check codes used to create triggers and views.

Luiz Barros.

Total article views: 3992 | Views in the last 30 days: 4
Related Articles

Database Snapshot Create/Drop/Restore SQL Script.

Database Snapshot Creat/Drop/Restore SQL Script for Single or multiple data files of databases.


script to create indexes existing in a database

script to create indexes existing in a database


A Script A Day - Day 6 - Drop and Create Database Snapshots

Today's Script will drop all database snapshots and create a database snapshot for all online read w...


Create Login Script

Error with Create Login Script


Create Database

Create database taking forever