Solved

SQL 2000 Stored Procedures and Triggers

Posted on 2014-03-23
14
223 Views
Last Modified: 2014-04-07
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
Comment
Question by:willie0-360
  • 6
  • 4
  • 2
  • +2
14 Comments
 
LVL 76

Expert Comment

by:arnold
Comment Utility
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
 
LVL 5

Accepted Solution

by:
NARANTHIRAN earned 250 total points
Comment Utility
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
 

Author Comment

by:willie0-360
Comment Utility
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 50 total points
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 

Author Comment

by:willie0-360
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:willie0-360
Comment Utility
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
Comment Utility
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
 

Author Comment

by:willie0-360
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 76

Expert Comment

by:arnold
Comment Utility
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
 

Author Comment

by:willie0-360
Comment Utility
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
 

Author Comment

by:willie0-360
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now