looping through all databases and executing a stored procedure while d

[jtammyg]

Hi!

I need to run my stored procedure on a monthly basis through all the
databases and spit out a report in Excel.


Here is my stored procedure for the report:


CREATE PROCEDURE usp_DR_Billing (@StartDate datetime,@EndDate
datetime,@PreviousDateEnd datetime)


AS


--insert all the fields from Matters into a temp table to use the
title of the project
select * into #temp_matter from Matters


--
SELECT RTRIM(CONVERT(VARCHAR(255), SERVERPROPERTY('ServerName'))) +
'.' + DB_NAME(DB_ID()) AS ServerName,max(b.title) as Title,
count(*) as [File Count],round(cast(sum(length) as decimal)/
1048576/1024,10) as [File Size]
into #temp_content_analysis
from files a
join #temp_matter b on a.matterkey = b.id
where a.id in
(select distinct documentkey from semantica_corpora where projectkey
in
(select id
from assignments
where lastprocesskey in
(select id from processlog
where task = 'Create Assignments'
and starttime >= @StartDate AND starttime <= @EndDate)
)
)
and a.id not in
(select distinct documentkey from semantica_corpora where projectkey
in
(select id
from assignments
where lastprocesskey in
(select id from processlog
where task = 'Create Assignments'
and starttime < @PreviousDateEnd)
)
)


--this query does not exclude older assignment, it is only used to
double-check that the previous query works fine
select RTRIM(CONVERT(VARCHAR(255), SERVERPROPERTY('ServerName'))) +
'.' + DB_NAME(DB_ID()) AS ServerName,max(b.title) as Title,
count(*) as [File Count],round(cast(sum(length) as decimal)/
1048576/1024,10) as [File Size]
into #temp_content_analysis2
from files a
join #temp_matter b on a.matterkey = b.id
where a.id in
(select distinct documentkey from semantica_corpora where projectkey
in
(select id
from assignments
where lastprocesskey in
(select id from processlog
where task = 'Create Assignments'
and starttime >= @StartDate AND starttime <= @EndDate)
)
)


select * from #temp_content_analysis


select * from #temp_content_analysis2
GO


Here is my stored procedure to loop through the databases:


CREATE PROCEDURE usp_DR_loop_through_dbs_find_assignment AS


declare @sql nvarchar(4000)
declare @db varchar(300)
declare @table sysname


--set @db = 'master'
declare cDB cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.name NOT IN ('master','model','msdb','pubs','northwind',
'tempdb')
ORDER BY name


open cDB
fetch cDB into @db
while (@@fetch_status = 0)
begin
PRINT ''
PRINT ''
PRINT '=========>> ' + @db
SET @sql = '
DECLARE c CURSOR FOR
SELECT title
FROM ' + @db + '.dbo.Assignments
WHERE title like ''20070518 Richards%''
'


EXEC sp_executesql @sql


OPEN c
fetch from c into @table
while (@@fetch_status = 0)
begin
print @table
fetch from c into @table
end
CLOSE c
DEALLOCATE c


fetch cDB into @db
end
close cDB
deallocate cDB


select @table


GO


Somewhere in this last sotred procedure I need to tell it to use the
first stored procedure and then give me the results I need with the
following fields:


database name, Title, [File Count], [File Size] from the first
stored procedure.


I was thinking maybe I should use DTS...but I am not sure how to
accomplish this.


Your help will be greatly appreciated.


Tammy