CabinMomma
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?
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?
Do you have a list stored in a table or something?
Take a look on:
http://blogs.msdn.com/mattm/archive/2007/04/18/roll-your-own-transfer-sql-server-objects-task.aspx
Helped?
regards,
Pedro
www.pedrocgd.blogspot.com
http://blogs.msdn.com/mattm/archive/2007/04/18/roll-your-own-transfer-sql-server-objects-task.aspx
Helped?
regards,
Pedro
www.pedrocgd.blogspot.com
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.routine s
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.REMOTEDATEBAS E.dbo.sp_E xecutesql @dsql
print 'creating procedure ' + @pname
exec REMOTESERVER.REMOTEDATEBAS E.dbo.sp_E xecutesql @sql
select @sql = 'GRANT EXECUTE ON dbo.' + @pname + ' to user'
exec REMOTESERVER.REMOTEDATEBAS E.dbo.sp_E xecutesql @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
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.routine
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.REMOTEDATEBAS
print 'creating procedure ' + @pname
exec REMOTESERVER.REMOTEDATEBAS
select @sql = 'GRANT EXECUTE ON dbo.' + @pname + ' to user'
exec REMOTESERVER.REMOTEDATEBAS
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