• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

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
0
willie0-360
Asked:
willie0-360
  • 6
  • 4
  • 2
  • +2
3 Solutions
 
arnoldCommented:
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 .......
0
 
NARANTHIRANCommented:
Hi,
   You can just take a backup of the existing database and restore in the new server.
While restoring the stored procedures and triggers are also restored.

After Restoring check the compatibility_level of the database.
And connect the application to the SQL 2008 Server and check....

The following command help's to view the store procedure..
SELECT name, create_date, modify_date FROM sys.procedures
0
 
willie0-360Author Commented:
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Scott PletcherSenior DBACommented:
In SQL 2000, easiest is to use:
dbo.syscomments.

That table contains the text for all these objects (in alpha order):
CHECK constraints
DEFAULT constraints
defaults
rules
stored procedure
triggers
views

Note that since each row is only 4000 characters long, for longer objects, you use column:
colid
to sequence the 4000-byte pieces back into the full object text.
0
 
Anthony PerkinsCommented:
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.
0
 
willie0-360Author Commented:
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
0
 
Anthony PerkinsCommented:
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.
0
 
willie0-360Author Commented:
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
0
 
Anthony PerkinsCommented:
Is there anything that I am doing wrong?
Perhaps you have failed to refresh.

Am I trying to find the triggers after the migration the wrong way?
Try it this way:
SELECT  *
FROM    sys.triggers
WHERE   OBJECT_NAME(parent_id) = 'DATATABLE'

Open in new window

Also, after the migration, do I have to "upgrade" the stored procedures and/or triggers to be at the SQL2008R2 functionality level?
You should make sure the compatibility level is set to 100 as in:
SELECT  *
FROM    sys.databases
WHERE compatibility_level<>100

Open in new window

And set it for those databases that are not:
ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 100

Open in new window

0
 
willie0-360Author Commented:
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
0
 
Anthony PerkinsCommented:
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.
0
 
arnoldCommented:
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.
0
 
willie0-360Author Commented:
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
0
 
willie0-360Author Commented:
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
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 6
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now