MSSQL Migration and Stored Procedures For SQL 7 and SQL 2000
	What's New in IT IT Consulting Phones/VoIP Internet Security About Us Home

	Web Site Hosting	Web Design	Broadband/Access	E-Mail Solutions	Accept Credit Cards	Hosting Support

When an account is migrated to a single SQL server, ownership of all database objects will be changed automatically so that you (the customer) and not the database owner (the DBO) owns the objects. However, the migration does not modify SQL code in stored procedures (or triggers or anything else) that reference objects qualified with the DBO. This is something that you will need to change.

Usually, such a change is straightforward. However, for some persons, this change can cause considerable difficulty and, if the code must be modified by them manually, the amount of time and the financial cost could be high. Fortunately, SQL Enterprise Manager provides tools that make such code modifications much easier to perform.

Making the Necessary Modifications to Your SQL Code

You need to change the stored procedure code to reference the tables with an unqualified name. However, changing the stored procedure code need not be a time-consuming, painful, or expensive task. Using SQL Server Enterprise Manager, you can use the Generate SQL Script Tool to generate the code to recreate the stored procedures referencing the correct table names.

The following steps can be used to recreate the stored procedures using the SQL 2000 version of Enterprise Manager. Note: The steps with the SQL 7 version of Enterprise Manager are similar to those shown below for the SQL 2000 version of Enterprise Manager.

1. Log in to Enterprise Manager.
2. Right-click your database and then click All Tasks -> Generate SQL Script.
3. Click Show All.
4. Click All Stored Procedures.
5. In the Formatting tab, verify that Generate CREATE... and Generate DROP... are selected. Verify that Generate scripts for dependent objects is NOT selected.
6. In the Options tab, verify all Security Options and Table Scripting Options are NOT selected.
7. Click OK and specify the location where you want to save the .SQL file. Scripting will complete automatically.
8. Click OK.

You should now have an SQL file with a script that will contain CREATE PROCEDURE statements for all procedures in your database. You will need to modify the script slightly in order to change the statements so that they correctly qualify the procedure names and other object names referenced in the procedures. This most easily performed with the Find and Replace feature that is available in many text editors.

1. Open the .sql file in your text editor.
2. Replace all instances of "dbo." with nothing (that is, "" and, of course, don't include the quotation marks). This will ensure that all tables that are qualified with dbo. are now unqualified.

The SQL Enterprise Manager likes to include SETUSER statements in the script that it generates. These statements are not supported on the shared hosting platform and are thus unnecessary. Therefore, they need to be removed from the script. Again, the easy way to accomplish this is to use your text editor's Find and Replace feature to “comment out” those statements. To do this, just replace all instances of "setuser" with "-- setuser" and then save the SQL file.

Executing Your Code

After the steps mentioned in the previous sections of this document have been completed, you will need to execute the SQL file on the target database. In general, you just need to do the following:

1. Connect to the database using a query tool such as SQL Query Analyzer.
2. Open the SQL file and run it. This should, in turn, automatically create All Stored Procedures.

If you have procedures that reference other procedures, you may get warnings similar to the following:

Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'objectname'. The stored procedure will still be created.

You can avoid such errors by altering the generated SQL file so that the dependent object is created before the objects that call it.

If you need any further information regarding how to manually migrate your SQL databases, please contact ITS through our on-line contact form.

Please note: the information on this page applies to ITS web hosting plans. It may or may not apply to other environments. If you are looking for a feature described here, or better support from your hosting provider, please consider hosting your site with ITS!

Copyright © 1996-2024, Integrated Technical Solutions, Inc., all rights reserved. See Terms/Legal for trademarks, copyright, and terms of use.