Bulk copy Stored Procedures from MS SQL 2000 db to MS SQL 2005 (Reporting Server)
Posted on 2009-02-20
We have a MSSQL 2000 db that contains tables and stored procs that support our SSRS reports. We are in the process of creating a quasi-ODS to use for our reporting. This will contain both the replicated transactional tables and the ones we use to support our reports. Therefore, there's no backup/restore method that I know that will work for us.
There are many stored procs and tables/views that are no longer required for our reports, so we only want to transfer a subset of each. Our dba is transferring the tables/views to the new db (all replicated online objects have already been transferred and replication is working).
We have identified all the tables/views and procs that we need. The dba should be done with the tables today and I have been tasked with transferring the stored procs. The only method I know of is to open each in Management Studio, right-click and do a create script, change the db and run it. The problem is, there are 366 procs that I need to move.
Is there a faster, easier, better way?