Link to home
Start Free TrialLog in
Avatar of willie0-360
willie0-360

asked on

SQL 2000 Stored Procedures and Triggers

Hello Experts:

As part of a side-by-side migration from SQL Server 2000 to SQL Server 2008R2, I need to find all the stored procedures and triggers that are on SQL 2000 and/or that are on each of the databases.  I do not know if I should worry about the stored procedures and triggers in the system databases.  

Please let me know how I can do that, and how I can migrate them to SQL Server 2008R2.  This migration will be taken place sometime in the future, but I want to start getting ready for this.

At the moment, all I can do is test all the parts of the migration.  Therefore, I want to learn how to do this part, the migration of the stored procedures and triggers, so that I can start testing it.


Thanks.
Willie
Avatar of arnold
arnold
Flag of United States of America image

Do not believe you have a direct path from 2000 to 2008.
An option you might want to consider is to use sql 2005 as an intermediary as well as to trouble shoot any changes.
sql 2005 express should do.

As to your direct question, is this an internal setup, or is the database part of a third party custom solution. The difference deals with when a third party is involved, you have to make sure that their application can function with the newer version of the SQL.
while database mode is one thing, the communication/commands might not always function as .......
ASKER CERTIFIED SOLUTION
Avatar of Naranthiran D
Naranthiran D
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of willie0-360
willie0-360

ASKER

I think your suggestion works to view the stored procedures.

I am using this command to view the stored procedures on SQL Server 2000 so that I can compare what I have on SQL Server 2000 to what I migrated over to SQL 2008R2:

select * from sysobjects where type='P';

Is there any other query I can run to do the same on SQL 2000?  I would like to know just to learn more.

Also, how do I check on triggers?  I see triggers in all the tables I have checked for a particular database, but I do not see those triggers after migrating such database with a full backup. How can I check on that on both SQL 2000 and SQL 2008R2 to make sure I am not missing something?

If by taking a full backup, these triggers do not get migrated, how do I migrate them?

Moreover, just like on database compatibility, page verification method set to CHECKSUM, row and page counts correction, after migrating the database to SQL 2008R2, do I have to "upgrade" stored procedures and triggers?  What I mean is that if there is something that needs to be done on stored procedures and triggers to function in SQL 2008R2.




Thanks.
Willie
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am using this command to view the stored procedures on SQL Server 2000 so that I can compare what I have on SQL Server 2000 to what I migrated over to SQL 2008R2:
I would use something like Red-Gate's SQL Compare.  This will show you all the differences and optionally deploy and/or create a deployment SQL script.
Thanks for your advice Anthony Perkins.  I will look into that.

Can anyone give me some feedback when it comes to triggers?

After taking a full backup from SQL 2000 and restoring on SQL 2008R2, I do not see the triggers migrated.

I do not know if these triggers are system triggers, if there is such a thing, or user defined.  How should I go about migrating triggers?

After the migration, do I have to "upgrade" the stored procedures and/or triggers to be at the SQL 2008R2 functional level?


Thanks.
Willie
After taking a full backup from SQL 2000 and restoring on SQL 2008R2, I do not see the triggers migrated.
When you restore from a backup you get all objects, including triggers.  You cannot exclude anything.
Please see the attached word document so that you can see what is my situation after I restore the full backup from SQL2000 to SQL2008R2.  I just want to make sure I am correct.  I cannot find the triggers after the migration.  

Is there anything that I am doing wrong?

Am I trying to find the triggers after the migration the wrong way?

Also, after the migration, do I have to "upgrade" the stored procedures and/or triggers to be at the SQL2008R2 functionality level?
mssql-triggers-after-migration.docx
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The

SELECT  *
FROM    sys.triggers
WHERE   OBJECT_NAME(parent_id) = 'DATATABLE'


query did not yield any results and no errors as well.

The compatibility level for this database is set to 100.

I hope you have more ideas on how to go about this.


Thanks.
Willie
I am afraid not.  I have never heard of a SQL Backup excluding certain objects.  So if you are convinced that you did in fact back up the correct database and there is no typo in the name of the table and your in the right database (try USE YourDatabaseName; SELECT  * FROM    sys.triggers to make sure) then I would open a case with Microsoft Product Support to see if they can help you.
The issue with a trigger might be related to an incompatibility or reliance on some resource that does not exist following the ...

Check the trigger creation instruction.

The triggers are added after the restore of data.

Try the following. Get and install  sql express 2005 it can be on a workstation. As long as your DB is less than 4GB, you can restore it from backup,  then see if the triggers transferred. With the restore, you then can repeat the backup/restore to sql 2008.

I believe there are some incompatibilities similar to upgrades of OS I,e. You used to be able to go one version up, but not two.
arnold:

I just tried restoring the database to an SQL EXPRESS 2005 instance, but it did not work.  The triggers do not transfer.

Anthony Perkins:

I also ran the command you suggested, but nothing was returned.  I believe the next step is either calling Microsoft or manually transferring these triggers by copy-pasting them.  

I believe that other than transferring the triggers, I have achieved everything my initial question asked.  Also, I got to learn a thing or two along the way.


Thanks.
Willie
Experts:

This is just an update for anyone else in a similar situation about triggers.

The only way I have been able to migrate triggers in a database is by detaching such database from the source when migrating it to the destination server.


Thanks.
Willie