Importing stored procedure

Hi,

I am trying to import a database from other server which is in sql2000 to server in sql 2005, i can successfully import all the tables but not able to import the stored procedure. Can any one suggest me the method to import stored procedure from other server.

Thanx

We deploy our sp's to multiple different database servers with a deploy (.sql) script.

So every stored procedure is also stored in this script.

Or you can use the following script:

USE MYDATABASE

SELECTname,text

FROM sysobjects

INNERJOIN syscomments

ON sysobjects.id= syscomments.id

WHERE xtype='p'ANDNameLIKE'%'

ORDERBYNAME

|||

What tool or method are you using to import the database? Is the stored procededure import failing? Do you get an error message?

An easy method to transfer a database from server to server is to backup the database and restore it to the new server - using SQL Server Management Studio (or the express version which is a free download). The tool will take care of upgrading the database from 2000 to 2005 for you behind the scenes.

|||

Hi divya4k2007,

You can script your database to a *.sql file and then run this query file in a new database. After that you will have a db with the same schema/structure as your orginal one ( All the tables, sp's will be copied but the data won't. So you need to Impor the data later).

Another easy way is to use backup/restore (if migrate direction is sql2000 to sql2005). You can also try database detach/attach,however, since i don't have sql2000 installed on my box,i'm not sure if detach/attach will work. You can refer to my another post which addresses the same issue: http://forums.asp.net/t/1144769.aspx

Hope my suggestion helps