[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-02-20
5
Medium Priority
?
205 Views
Last Modified: 2013-11-10
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?  
0
Comment
Question by:CabinMomma
  • 2
  • 2
5 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23694799
Do you have a list stored in a table or something?
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23694999
0
 

Author Comment

by:CabinMomma
ID: 23695218
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.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 1000 total points
ID: 23695408
Do you have a linked server between the two?  If so, you can pull the procedure from information_Schema.routines and push them to the new server.  If the procedure already exists, this will error out unless you DROP it first since the routine_definition carries the CREATE only.
declare 
    @i     int
   ,@pname sysname
   ,@sql   nvarchar(max)
   ,@dsql  nvarchar(max)
 
declare @p table
(i     int identity
,pname sysname
,pDef  nvarchar(max)
)
insert into @p (pname,pdef)
select routine_name, routine_Definition
from information_schema.routines
where routine_name in ('YOUR LIST', 'OF PROCEDURES')
 
set @i=1
while @@rowcount>0
begin
   select @pname = pname 
          ,@sql = pdef
   from @p
   where i=@i
 
  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
  end
 
 
select top 1 @i=i from @p where i > @i order by i
if @@rowcount=0 break
end

Open in new window

0
 

Author Closing Comment

by:CabinMomma
ID: 31551198
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

867 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