Link to home
Start Free TrialLog in
Avatar of CabinMomma
CabinMommaFlag for United States of America

asked on

Bulk copy Stored Procedures from MS SQL 2000 db to MS SQL 2005 (Reporting Server)

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?  
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image

Do you have a list stored in a table or something?
Avatar of CabinMomma

ASKER

I don't have the list stored in a table. It's in an excel spreadsheet, but it's easy enough to put that into a table, if that would facilitate things.
ASKER CERTIFIED SOLUTION
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America 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
Great Solution.  Thanks so much for the help.

For the benefit of anyone using this:
There is a small typo on line 28: put in the '+' before @pname
You will need have the servers set up as linked servers.
You need to put in the grant statement for the procs.
Finally, the if@@rowcount=0 break line (line 36) caused me issues by breaking after the first row everytime.  I changed to incrementing and put in some print statements to more easily read my errors (and to nail down why the line above wasn't working - never did figure it out, but my changes got me through it).

Here is what I used:

BEGIN
declare

    @i  int
   ,@ii int
   ,@pname sysname
   ,@sql   nvarchar(4000)
   ,@dsql  nvarchar(400)
 
declare @p table
(i     int identity
,pname sysname
,pDef  ntext
)
insert into @p (pname,pdef)
select routine_name, routine_Definition
from information_schema.routines
where routine_name in ('MY LIST')

select @ii = max(i) from @p
print '@ii: ' + CAST(@ii AS char(20))

set @i=1
   select @pname = pname
          ,@sql = pdef
   from @p
   where i=@i
print ' '
print '---------------------------------'
print @pname

while @i<@ii
begin

 print '@rowcount: ' + CAST(@@rowcount AS char(20))  + '; @i: ' + CAST(@i AS char(20))
 print @pname
 
  if @sql is not null
  begin
     print 'dropping procedure ' + @pname
     set @dsql = 'drop procedure ' + @pname
     exec REMOTESERVER.REMOTEDATEBASE.dbo.sp_Executesql @dsql
     print 'creating procedure ' + @pname
     exec REMOTESERVER.REMOTEDATEBASE.dbo.sp_Executesql @sql
     select @sql = 'GRANT EXECUTE ON dbo.' + @pname + ' to user'
     exec REMOTESERVER.REMOTEDATEBASE.dbo.sp_Executesql @sql

  end
 
  --select top 1 @i=i from @p where i > @i order by i
   select @i=@i + 1
   print '@rowcount: ' + CAST(@@rowcount AS char(20))  + '; @i: ' + CAST(@i AS char(20))

   select @pname = pname
          ,@sql = pdef
   from @p
   where i=@i

   print '@rowcount: ' + CAST(@@rowcount AS char(20))  + '; @i: ' + CAST(@i AS char(20))
   print ' '
   print '---------------------------------'
   print @pname

 -- if @@rowcount=0 break

end
END