Hi!

I have 2 questions:


1) how do i use a stored procedure within Excel VBA passing a
nvarchar(100) parameter?

2) how do I get the users to select that parameter from a dropdown
list in excel so that there are no typos?


Here is the code for the stored proc:


CREATE PROCEDURE usp_DR_Preview_Report (@Matter nvarchar(100))
AS

if exists (select [id] from master..sysobjects where [id] = OBJECT_ID
('master..DR_Preview_Report'))
drop table master.dbo.DR_Preview_Report_FINAL


SET NOCOUNT ON


-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- STORED PROCEDURE : USP_DR_PREVIEW_REPORT

-- DESCRIPTION: This stored procedure populates the Excel Report
called 'PREVIEW REPORT.XLS'

-- CREATED BY: Tammy Gottfeld (TG)

-- DATE CREATED: 11/02/2007

-- DATE MODIFIED: 11/05/2007

-- VERSION: 1.00 Stored procedure creation
-- 2.00 Removed unnecessary fields TG (11/05/2007)
-- 3.00 Removed 'Other Files' and added 'Extracted Files' ,
-- Removed [Original Files Per GB] and inserted [Extracted Files
Per GB],
-- Changed the formula for calculating the size in GB. TG
(11/07/2007)
--

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


-- 1) we get all the custodians names and id's into a temp table for
ALL CLIENTS

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



set @db = 'master'
declare cDB cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.name = @Matter
ORDER BY name


CREATE TABLE #temp_custodian_name_id([Server Name]
nvarchar(40),
[Database Name]
nvarchar(100),
[custodian ID] int,
[Custodian Name]
nvarchar(300)
)



open cDB
FETCH NEXT FROM cDB INTO @db
while (@@fetch_status = 0)
begin

SET @sql= 'SELECT @@SERVERNAME as ''[Server Name]'', '
+
'''' + @db + '''' + ' as ''[Database
Name]'',' +
'[ID] as ''[custodian ID]'',' +
'name as ''[Custodian Name]''' +
'FROM ' + @db + '.dbo.filo_Owners WHERE ''' + @db + '''
like ''Client%'''


INSERT #temp_custodian_name_id

EXEC sp_executesql @sql


fetch cDB into @db
end
close cDB
deallocate cDB



--2) We are creating and populating the table
temp_Source_Media_file_count with SOURCE MEDIA file information.


declare @sql1 nvarchar(4000)
declare @db1 varchar(300)



set @db1 = 'master'
declare cDB1 cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.name = @Matter
ORDER BY name




CREATE TABLE #temp_Source_Media_file_count([Server Name]
nvarchar(40),
[Database Name]
nvarchar(100),
[Media Name] nvarchar(100),
[Ownerkey] int,
[Processlog ID] int,
MatterKey int,
saveProcessKey int,
length bigint,
[Start time] datetime,
)



open cDB1
FETCH NEXT FROM cDB1 INTO @db1
while (@@fetch_status = 0)
begin

SET @sql1 = 'SELECT @@SERVERNAME as ''[Server Name]'', '
+
'''' + @db1 + '''' + ' as ''[Database
Name]'',' +
'c.[name] as ''[Media Name]'',' +
'a.[ownerkey] as ''[Ownerkey]'',' +
'b.[id] as ''[Processlog ID]'',' +
'a.MatterKey as ''MatterKey'',' +
'a.saveProcessKey as ''saveProcessKey'',' +
'length as ''length'',' +
'starttime as ''[Start Time]''' +
'FROM ' + @db1 + '.dbo.filo_files a join ' + @db1 +
'.dbo.filo_processLog b on a.saveprocesskey = b.id ' +
'join ' + @db1 + '.dbo.filo_Media c on a.mediumkey = c.id '
+
'WHERE rootContainerKey IS NULL'



INSERT #temp_Source_Media_file_count

EXEC sp_executesql @sql1


fetch cDB1 into @db1

end
close cDB1
deallocate cDB1




select [Server Name],[Database Name],[Media Name],[ownerkey],count(*)
as [Original File count], (cast(sum(length) as decimal)/1048576) as
[Original File Size (MB)]
into #temp_Source_Media_file_count_FINAL
from #temp_Source_Media_file_count
group by [Server Name],[Database Name],[Media Name],[ownerkey]
order by [Server Name],[Database Name],[Media Name],[ownerkey]





select b.[Server Name],
b.[Database Name],
b.[Media Name],
a.[custodian name],
[Original File count],
[Original File Size (MB)]
into #temp_Source_Media_file_count_FINAL_CUSTODIAN
from #temp_custodian_name_id a
join #temp_Source_Media_file_count_FINAL b
on a.[Database Name] = b.[Database Name] and a.[custodian ID] =
b.ownerkey




--3) we are looking for number of near dups files



declare @sql2 nvarchar(4000)
declare @db2 varchar(300)



set @db2 = 'master'
declare cDB2 cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.name = @Matter
ORDER BY name


CREATE TABLE #temp_near_dups_file_count([Server Name]
nvarchar(40),
[Database Name]
nvarchar(100),
[Media Name] nvarchar(100),
[Ownerkey] int,
[Near Dups File Count]
int,
[Near Dups File Size (MB)] decimal(10,4)
)



open cDB2
FETCH NEXT FROM cDB2 INTO @db2
while (@@fetch_status = 0)
begin
set @sql2 = 'SELECT @@SERVERNAME as ''[Server Name]'', '
+
'''' + @db2 + '''' + ' as ''[Database
Name]'', ' +
'b.[name] as ''[Media Name]'',' +
'a.ownerkey as ''[Ownerkey]'',' +
' count(a.id) as ''[Near Dups File Count]'', ' +
'(cast(Sum(length) as decimal)/1048576) as ''[Near Dups File
Size (MB)]''' +
' FROM ' + @db2 + '.dbo.filo_Files a join ' + @db2 +
' .dbo.filo_Media b on a.mediumkey = b.id where suppressstate =
''nearduped'' group by b.[Name],a.ownerkey'


INSERT #temp_near_dups_file_count

EXEC sp_executesql @sql2


fetch cDB2 into @db2
end
close cDB2
deallocate cDB2



select b.[Server Name],
b.[Database Name],
b.[Media Name],
a.[custodian name],
[Near Dups File Count],
[Near Dups File Size (MB)]
into #temp_near_dups_file_count_FINAL_CUSTODIAN
from #temp_custodian_name_id a
join #temp_near_dups_file_count b
on a.[Database Name] = b.[Database Name] and a.[custodian ID] =
b.ownerkey




--4) we are looking for number of exact dups files

declare @sql3 nvarchar(4000)
declare @db3 varchar(300)



set @db3 = 'master'
declare cDB3 cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.name = @Matter
ORDER BY name


CREATE TABLE #temp_exact_dups_file_count([Server Name]
nvarchar(40),
[Database Name]
nvarchar(100),
[Media name] nvarchar(100),
[Ownerkey] int,
[Exact Dups File Count] int,
[Exact Dups File Size (MB)] decimal(10,4)
)



open cDB3
FETCH NEXT FROM cDB3 INTO @db3
while (@@fetch_status = 0)
begin
set @sql3 = 'SELECT @@SERVERNAME as ''[Server
Name]'', ' +
'''' + @db3 + '''' + '
as ''[Database Name]'', ' +
'b.[name] as ''[Media Name]'',' +
'a.ownerkey as ''[Ownerkey]'',' +
' count(a.id) as ''[Exact Dups File Count]'', ' +
'(cast(Sum(length) as decimal)/1048576) as ''[Exact Dups File
Size (MB)]''' +
' FROM ' + @db3 + '.dbo.filo_Files a join ' + @db3 +
' .dbo.filo_Media b on a.mediumkey = b.id where suppressstate =
''deduped'' group by b.[Name],a.ownerkey'


INSERT #temp_exact_dups_file_count

EXEC sp_executesql @sql3


fetch cDB3 into @db3
end
close cDB3
deallocate cDB3



select b.[Server Name],
b.[Database Name],
b.[Media Name],
a.[custodian name],
[Exact Dups File Count],
[Exact Dups File Size (MB)]
into #temp_exact_dups_file_count_FINAL_CUSTODIAN
from #temp_custodian_name_id a
join #temp_exact_dups_file_count b
on a.[Database Name] = b.[Database Name] and a.[custodian ID] =
b.ownerkey




--5) we are looking for number of unsuppressed files

declare @sql4 nvarchar(4000)
declare @db4 varchar(300)



set @db4 = 'master'
declare cDB4 cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.name = @Matter
ORDER BY name


CREATE TABLE #temp_unsuppressed_file_count( [Server Name]
nvarchar(40),
[Database Name]
nvarchar(100),
[Media Name] nvarchar(100),
[Ownerkey] int,
[Unsuppressed File Count]
int,
[Unsuppressed File Size (MB)] decimal(10,4)
)



open cDB4
FETCH NEXT FROM cDB4 INTO @db4
while (@@fetch_status = 0)
begin
set @sql4 = 'SELECT @@SERVERNAME as ''[Server
Name]'', ' +
'''' + @db4 + '''' +
' as ''[Database Name]'', ' +
'b.[name] as ''[Media Name]'',' +
'a.ownerkey as ''[Ownerkey]'',' +
' count(a.id) as ''[Unsuppressed File Count]'', ' +
'(cast(Sum(length) as decimal)/1048576) as ''[Unsuppressed File
Size (MB)]''' +
' FROM ' + @db4 + '.dbo.filo_Files a join ' + @db4 +
' .dbo.filo_Media b on a.mediumkey = b.id where suppressstate =
''unsuppressed'' group by b.[Name],a.ownerkey'


INSERT #temp_unsuppressed_file_count


EXEC sp_executesql @sql4


fetch cDB4 into @db4
end
close cDB4
deallocate cDB4



select b.[Server Name],
b.[Database Name],
b.[Media Name],
a.[custodian name],
[Unsuppressed File Count],
[Unsuppressed File Size (MB)]
into #temp_unsuppressed_file_count_FINAL_CUSTODIAN
from #temp_custodian_name_id a
join #temp_unsuppressed_file_count b
on a.[Database Name] = b.[Database Name] and a.[custodian ID] =
b.ownerkey




--6) We are creating and populating the temp table with extracted
files information.

declare @sql5 nvarchar(4000)
declare @db5 varchar(300)



set @db5 = 'master'
declare cDB5 cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.name = @Matter
ORDER BY name




CREATE TABLE #temp_Extracted_file_count([Server Name]
nvarchar(40),
[Database Name]
nvarchar(100),
[Media Name] nvarchar(100),
[Ownerkey] int,
[Extracted File Count] int,
[Extracted File Size (MB)] decimal(10,4)
)



open cDB5
FETCH NEXT FROM cDB5 INTO @db5
while (@@fetch_status = 0)
begin

SET @sql5 = 'SELECT @@SERVERNAME as ''[Server Name]'', '
+
'''' + @db5 + '''' + ' as ''[Database
Name]'',' +
'b.[name] as ''[Media Name]'',' +
'a.[ownerkey] as ''[Ownerkey]'',' +
' count(a.id) as ''[Extracted File Count]'', ' +
'(cast(sum(length) as decimal)/1048576) as ''[Extracted File Size
(MB)]''' +
'FROM ' + @db5 + '.dbo.filo_files a join ' + @db5 +
'.dbo.filo_Media b on a.mediumkey = b.id ' +
'WHERE rootContainerKey IS NOT NULL group by b.
[Name],a.ownerkey'



INSERT #temp_Extracted_file_count

EXEC sp_executesql @sql5


fetch cDB5 into @db5

end
close cDB5
deallocate cDB5




select b.[Server Name],
b.[Database Name],
b.[Media Name],
a.[custodian name],
[Extracted File Count],
[Extracted File Size (MB)]
into #temp_Extracted_file_count_FINAL_CUSTODIAN
from #temp_custodian_name_id a
join #temp_Extracted_file_count b
on a.[Database Name] = b.[Database Name] and a.[custodian ID] =
b.ownerkey





-- 7) Insert all the temp tables into a main table
-- Insert all the temp tables into a main table


CREATE TABLE #DR_Preview_Report(
[Server Name] nvarchar(40),
[Database Name]
nvarchar(100),
[Media Name] nvarchar(100),
[Custodian Name]
nvarchar(300),
[Original File Count] int,
[Original File Size (MB)] decimal(10,4),
[Extracted File Count] int,
[Extracted File Size (MB)] decimal(10,4),
[Extracted Files Per GB] decimal(10,4),
[Near Dups File Count] int,
[Near Dups File Size (MB)] decimal(10,4),
[Exact Dups File Count] int,
[Exact Dups File Size (MB)] decimal(10,4),
[Unsuppressed File Count] int,
[Unsuppressed File Size (MB)] decimal(10,4),
[Unsuppressed Files Per GB] decimal(10,4)
)



insert into #DR_Preview_Report(
[Server Name],
[Database Name],
[Media Name],
[Custodian Name],
[Original File Count],
[Original File Size (MB)])
select [Server Name],
[Database Name],
[Media Name],
[Custodian Name],
[Original File Count],
[Original File Size (MB)]
from #temp_Source_Media_file_count_FINAL_CUSTODIAN
order by [Database Name],[Media Name],[Custodian Name]



insert into #DR_Preview_Report(
[Server Name],
[Database Name],
[Media Name],
[Custodian Name],
[Extracted File Count],
[Extracted File Size (MB)],
[Extracted Files Per GB])
select [Server Name],
[Database Name],
[Media Name],
[Custodian Name],
[Extracted File Count],
[Extracted File Size (MB)],
([Extracted File Size (MB)]/1024) as [Extracted Files Per GB]
from #temp_Extracted_file_count_FINAL_CUSTODIAN
order by [Database Name],[Media Name],[Custodian Name]



insert into #DR_Preview_Report(
[Server Name],
[Database Name],
[Media Name],
[Custodian Name],
[Near Dups File Count],
[Near Dups File Size (MB)])
select [Server Name],
[Database Name],
[Media Name],
[Custodian Name],
[Near Dups File Count],
[Near Dups File Size (MB)]
from #temp_near_dups_file_count_FINAL_CUSTODIAN
order by [Database Name],[Media Name],[Custodian Name]





insert into #DR_Preview_Report(
[Server Name],
[Database Name],
[Media Name],
[Custodian Name],
[Exact Dups File Count],
[Exact Dups File Size (MB)])
select [Server Name],
[Database Name],
[Media Name],
[Custodian Name],
[Exact Dups File Count],
[Exact Dups File Size (MB)]
from #temp_exact_dups_file_count_FINAL_CUSTODIAN
order by [Database Name],[Media Name],[Custodian Name]



insert into #DR_Preview_Report(
[Server Name],
[Database Name],
[Media Name],
[Custodian Name],
[Unsuppressed File Count],
[Unsuppressed File Size (MB)],
[Unsuppressed Files Per GB])
select [Server Name],
[Database Name],
[Media Name],
[Custodian Name],
[Unsuppressed File Count],
[Unsuppressed File Size (MB)],
([Unsuppressed File Size (MB)]/1024) as [Unsuppressed Files Per GB]
from #temp_unsuppressed_file_count_FINAL_CUSTODIAN
order by [Database Name],[Media Name],[Custodian Name]






CREATE TABLE master.dbo.DR_Preview_Report_FINAL(
[Server Name] nvarchar(40),
[Database Name]
nvarchar(100),
[Media Name] nvarchar(100),
[Custodian Name]
nvarchar(300),
[Original File Count] int,
[Original File Size (MB)] decimal(10,4),
[Extracted File Count] int,
[Extracted File Size (MB)] decimal(10,4),
[Extracted Files Per GB] decimal(10,4),
[Near Dups File Count] int,
[Near Dups File Size (MB)] decimal(10,4),
[Exact Dups File Count] int,
[Exact Dups File Size (MB)] decimal(10,4),
[Unsuppressed File Count] int,
[Unsuppressed File Size (MB)] decimal(10,4),
[Unsuppressed Files Per GB] decimal(10,4)
)




insert into master.dbo.DR_Preview_Report_FINAL
select [Server Name],
[Database Name],
[Media Name],
[Custodian Name],
max([Original File Count]) as [Original File Count],
max([Original File Size (MB)]) as [Original File Size (MB)],
max([Extracted File Count]) as [Extracted File Count],
max([Extracted File Size (MB)]) as [Extracted File Size (MB)],
max([Extracted Files Per GB]) as [Extracted Files Per GB],
max([Near Dups File Count]) as [Near Dups File Count],
max([Near Dups File Size (MB)]) as [Near Dups File Size (MB)],
max([Exact Dups File Count]) as [Exact Dups File Count],
max([Exact Dups File Size (MB)]) as [Exact Dups File Size (MB)],
max([Unsuppressed File Count]) as [Unsuppressed File Count],
max([Unsuppressed File Size (MB)]) as [Unsuppressed File Size (MB)],
max([Unsuppressed Files Per GB]) as [Unsuppressed Files Per GB]
from #DR_Preview_Report
group by [Server Name],
[Database Name],
[Media Name],
[Custodian Name]




----------NOT IN USE ANYMORE TG 11/7/2007
--------------------------------------------------------------------------------
/*
-------------- Calculate [other file count] fields
UPDATE a
SET [Other Files Count] = (b.[Unsuppressed File Count] - b.[Exact
Dups File Count] - b.[Original File Count])
from master.dbo.DR_Preview_Report_FINAL a
join master.dbo.DR_Preview_Report_FINAL b on a.[Server Name] = b.
[Server Name] and a.[Database Name] = b.[Database Name] and a.
[Custodian Name] = b.[Custodian Name]






-- calculate [Other File Size (MB)]
UPDATE a
SET [Other Files Size (MB)] = cast(b.[Unsuppressed File Size (MB)] as
decimal(10,4)) - cast(b.[Exact dups File Size (MB)] as decimal(10,4))
- cast(b.[Original File Size (MB)] as decimal(10,4))
from master.dbo.DR_Preview_Report_FINAL a
join master.dbo.DR_Preview_Report_FINAL b on a.[Server Name] = b.
[Server Name] and a.[Database Name] = b.[Database Name] and a.
[Custodian Name] = b.[Custodian Name]
*/


--select * from master.dbo.DR_Preview_Report_FINAL

SET NOCOUNT OFF
GO






and here is the code for the vba:


CREATE PROCEDURE usp_DR_Preview_Report (@Matter nvarchar(100))
AS

if exists (select [id] from master..sysobjects where [id] = OBJECT_ID
('master..DR_Preview_Report'))
drop table master.dbo.DR_Preview_Report_FINAL


SET NOCOUNT ON


-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- STORED PROCEDURE : USP_DR_PREVIEW_REPORT

-- DESCRIPTION: This stored procedure populates the Excel Report
called 'PREVIEW REPORT.XLS'

-- CREATED BY: Tammy Gottfeld (TG)

-- DATE CREATED: 11/02/2007

-- DATE MODIFIED: 11/05/2007

-- VERSION: 1.00 Stored procedure creation
-- 2.00 Removed unnecessary fields TG (11/05/2007)
-- 3.00 Removed 'Other Files' and added 'Extracted Files' ,
-- Removed [Original Files Per GB] and inserted [Extracted Files
Per GB],
-- Changed the formula for calculating the size in GB. TG
(11/07/2007)
--

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


-- 1) we get all the custodians names and id's into a temp table for
ALL CLIENTS

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



set @db = 'master'
declare cDB cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.name = @Matter
ORDER BY name


CREATE TABLE #temp_custodian_name_id([Server Name]
nvarchar(40),
[Database Name]
nvarchar(100),
[custodian ID] int,
[Custodian Name]
nvarchar(300)
)



open cDB
FETCH NEXT FROM cDB INTO @db
while (@@fetch_status = 0)
begin

SET @sql= 'SELECT @@SERVERNAME as ''[Server Name]'', '
+
'''' + @db + '''' + ' as ''[Database
Name]'',' +
'[ID] as ''[custodian ID]'',' +
'name as ''[Custodian Name]''' +
'FROM ' + @db + '.dbo.filo_Owners WHERE ''' + @db + '''
like ''Client%'''


INSERT #temp_custodian_name_id

EXEC sp_executesql @sql


fetch cDB into @db
end
close cDB
deallocate cDB



--2) We are creating and populating the table
temp_Source_Media_file_count with SOURCE MEDIA file information.


declare @sql1 nvarchar(4000)
declare @db1 varchar(300)



set @db1 = 'master'
declare cDB1 cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.name = @Matter
ORDER BY name




CREATE TABLE #temp_Source_Media_file_count([Server Name]
nvarchar(40),
[Database Name]
nvarchar(100),
[Media Name] nvarchar(100),
[Ownerkey] int,
[Processlog ID] int,
MatterKey int,
saveProcessKey int,
length bigint,
[Start time] datetime,
)



open cDB1
FETCH NEXT FROM cDB1 INTO @db1
while (@@fetch_status = 0)
begin

SET @sql1 = 'SELECT @@SERVERNAME as ''[Server Name]'', '
+
'''' + @db1 + '''' + ' as ''[Database
Name]'',' +
'c.[name] as ''[Media Name]'',' +
'a.[ownerkey] as ''[Ownerkey]'',' +
'b.[id] as ''[Processlog ID]'',' +
'a.MatterKey as ''MatterKey'',' +
'a.saveProcessKey as ''saveProcessKey'',' +
'length as ''length'',' +
'starttime as ''[Start Time]''' +
'FROM ' + @db1 + '.dbo.filo_files a join ' + @db1 +
'.dbo.filo_processLog b on a.saveprocesskey = b.id ' +
'join ' + @db1 + '.dbo.filo_Media c on a.mediumkey = c.id '
+
'WHERE rootContainerKey IS NULL'



INSERT #temp_Source_Media_file_count

EXEC sp_executesql @sql1


fetch cDB1 into @db1

end
close cDB1
deallocate cDB1




select [Server Name],[Database Name],[Media Name],[ownerkey],count(*)
as [Original File count], (cast(sum(length) as decimal)/1048576) as
[Original File Size (MB)]
into #temp_Source_Media_file_count_FINAL
from #temp_Source_Media_file_count
group by [Server Name],[Database Name],[Media Name],[ownerkey]
order by [Server Name],[Database Name],[Media Name],[ownerkey]





select b.[Server Name],
b.[Database Name],
b.[Media Name],
a.[custodian name],
[Original File count],
[Original File Size (MB)]
into #temp_Source_Media_file_count_FINAL_CUSTODIAN
from #temp_custodian_name_id a
join #temp_Source_Media_file_count_FINAL b
on a.[Database Name] = b.[Database Name] and a.[custodian ID] =
b.ownerkey




--3) we are looking for number of near dups files



declare @sql2 nvarchar(4000)
declare @db2 varchar(300)



set @db2 = 'master'
declare cDB2 cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.name = @Matter
ORDER BY name


CREATE TABLE #temp_near_dups_file_count([Server Name]
nvarchar(40),
[Database Name]
nvarchar(100),
[Media Name] nvarchar(100),
[Ownerkey] int,
[Near Dups File Count]
int,
[Near Dups File Size (MB)] decimal(10,4)
)



open cDB2
FETCH NEXT FROM cDB2 INTO @db2
while (@@fetch_status = 0)
begin
set @sql2 = 'SELECT @@SERVERNAME as ''[Server Name]'', '
+
'''' + @db2 + '''' + ' as ''[Database
Name]'', ' +
'b.[name] as ''[Media Name]'',' +
'a.ownerkey as ''[Ownerkey]'',' +
' count(a.id) as ''[Near Dups File Count]'', ' +
'(cast(Sum(length) as decimal)/1048576) as ''[Near Dups File
Size (MB)]''' +
' FROM ' + @db2 + '.dbo.filo_Files a join ' + @db2 +
' .dbo.filo_Media b on a.mediumkey = b.id where suppressstate =
''nearduped'' group by b.[Name],a.ownerkey'


INSERT #temp_near_dups_file_count

EXEC sp_executesql @sql2


fetch cDB2 into @db2
end
close cDB2
deallocate cDB2



select b.[Server Name],
b.[Database Name],
b.[Media Name],
a.[custodian name],
[Near Dups File Count],
[Near Dups File Size (MB)]
into #temp_near_dups_file_count_FINAL_CUSTODIAN
from #temp_custodian_name_id a
join #temp_near_dups_file_count b
on a.[Database Name] = b.[Database Name] and a.[custodian ID] =
b.ownerkey




--4) we are looking for number of exact dups files

declare @sql3 nvarchar(4000)
declare @db3 varchar(300)



set @db3 = 'master'
declare cDB3 cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.name = @Matter
ORDER BY name


CREATE TABLE #temp_exact_dups_file_count([Server Name]
nvarchar(40),
[Database Name]
nvarchar(100),
[Media name] nvarchar(100),
[Ownerkey] int,
[Exact Dups File Count] int,
[Exact Dups File Size (MB)] decimal(10,4)
)



open cDB3
FETCH NEXT FROM cDB3 INTO @db3
while (@@fetch_status = 0)
begin
set @sql3 = 'SELECT @@SERVERNAME as ''[Server
Name]'', ' +
'''' + @db3 + '''' + '
as ''[Database Name]'', ' +
'b.[name] as ''[Media Name]'',' +
'a.ownerkey as ''[Ownerkey]'',' +
' count(a.id) as ''[Exact Dups File Count]'', ' +
'(cast(Sum(length) as decimal)/1048576) as ''[Exact Dups File
Size (MB)]''' +
' FROM ' + @db3 + '.dbo.filo_Files a join ' + @db3 +
' .dbo.filo_Media b on a.mediumkey = b.id where suppressstate =
''deduped'' group by b.[Name],a.ownerkey'


INSERT #temp_exact_dups_file_count

EXEC sp_executesql @sql3


fetch cDB3 into @db3
end
close cDB3
deallocate cDB3



select b.[Server Name],
b.[Database Name],
b.[Media Name],
a.[custodian name],
[Exact Dups File Count],
[Exact Dups File Size (MB)]
into #temp_exact_dups_file_count_FINAL_CUSTODIAN
from #temp_custodian_name_id a
join #temp_exact_dups_file_count b
on a.[Database Name] = b.[Database Name] and a.[custodian ID] =
b.ownerkey




--5) we are looking for number of unsuppressed files

declare @sql4 nvarchar(4000)
declare @db4 varchar(300)



set @db4 = 'master'
declare cDB4 cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.name = @Matter
ORDER BY name


CREATE TABLE #temp_unsuppressed_file_count( [Server Name]
nvarchar(40),
[Database Name]
nvarchar(100),
[Media Name] nvarchar(100),
[Ownerkey] int,
[Unsuppressed File Count]
int,
[Unsuppressed File Size (MB)] decimal(10,4)
)



open cDB4
FETCH NEXT FROM cDB4 INTO @db4
while (@@fetch_status = 0)
begin
set @sql4 = 'SELECT @@SERVERNAME as ''[Server
Name]'', ' +
'''' + @db4 + '''' +
' as ''[Database Name]'', ' +
'b.[name] as ''[Media Name]'',' +
'a.ownerkey as ''[Ownerkey]'',' +
' count(a.id) as ''[Unsuppressed File Count]'', ' +
'(cast(Sum(length) as decimal)/1048576) as ''[Unsuppressed File
Size (MB)]''' +
' FROM ' + @db4 + '.dbo.filo_Files a join ' + @db4 +
' .dbo.filo_Media b on a.mediumkey = b.id where suppressstate =
''unsuppressed'' group by b.[Name],a.ownerkey'


INSERT #temp_unsuppressed_file_count


EXEC sp_executesql @sql4


fetch cDB4 into @db4
end
close cDB4
deallocate cDB4



select b.[Server Name],
b.[Database Name],
b.[Media Name],
a.[custodian name],
[Unsuppressed File Count],
[Unsuppressed File Size (MB)]
into #temp_unsuppressed_file_count_FINAL_CUSTODIAN
from #temp_custodian_name_id a
join #temp_unsuppressed_file_count b
on a.[Database Name] = b.[Database Name] and a.[custodian ID] =
b.ownerkey




--6) We are creating and populating the temp table with extracted
files information.

declare @sql5 nvarchar(4000)
declare @db5 varchar(300)



set @db5 = 'master'
declare cDB5 cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.name = @Matter
ORDER BY name




CREATE TABLE #temp_Extracted_file_count([Server Name]
nvarchar(40),
[Database Name]
nvarchar(100),
[Media Name] nvarchar(100),
[Ownerkey] int,
[Extracted File Count] int,
[Extracted File Size (MB)] decimal(10,4)
)



open cDB5
FETCH NEXT FROM cDB5 INTO @db5
while (@@fetch_status = 0)
begin

SET @sql5 = 'SELECT @@SERVERNAME as ''[Server Name]'', '
+
'''' + @db5 + '''' + ' as ''[Database
Name]'',' +
'b.[name] as ''[Media Name]'',' +
'a.[ownerkey] as ''[Ownerkey]'',' +
' count(a.id) as ''[Extracted File Count]'', ' +
'(cast(sum(length) as decimal)/1048576) as ''[Extracted File Size
(MB)]''' +
'FROM ' + @db5 + '.dbo.filo_files a join ' + @db5 +
'.dbo.filo_Media b on a.mediumkey = b.id ' +
'WHERE rootContainerKey IS NOT NULL group by b.
[Name],a.ownerkey'



INSERT #temp_Extracted_file_count

EXEC sp_executesql @sql5


fetch cDB5 into @db5

end
close cDB5
deallocate cDB5




select b.[Server Name],
b.[Database Name],
b.[Media Name],
a.[custodian name],
[Extracted File Count],
[Extracted File Size (MB)]
into #temp_Extracted_file_count_FINAL_CUSTODIAN
from #temp_custodian_name_id a
join #temp_Extracted_file_count b
on a.[Database Name] = b.[Database Name] and a.[custodian ID] =
b.ownerkey





-- 7) Insert all the temp tables into a main table
-- Insert all the temp tables into a main table


CREATE TABLE #DR_Preview_Report(
[Server Name] nvarchar(40),
[Database Name]
nvarchar(100),
[Media Name] nvarchar(100),
[Custodian Name]
nvarchar(300),
[Original File Count] int,
[Original File Size (MB)] decimal(10,4),
[Extracted File Count] int,
[Extracted File Size (MB)] decimal(10,4),
[Extracted Files Per GB] decimal(10,4),
[Near Dups File Count] int,
[Near Dups File Size (MB)] decimal(10,4),
[Exact Dups File Count] int,
[Exact Dups File Size (MB)] decimal(10,4),
[Unsuppressed File Count] int,
[Unsuppressed File Size (MB)] decimal(10,4),
[Unsuppressed Files Per GB] decimal(10,4)
)



insert into #DR_Preview_Report(
[Server Name],
[Database Name],
[Media Name],
[Custodian Name],
[Original File Count],
[Original File Size (MB)])
select [Server Name],
[Database Name],
[Media Name],
[Custodian Name],
[Original File Count],
[Original File Size (MB)]
from #temp_Source_Media_file_count_FINAL_CUSTODIAN
order by [Database Name],[Media Name],[Custodian Name]



insert into #DR_Preview_Report(
[Server Name],
[Database Name],
[Media Name],
[Custodian Name],
[Extracted File Count],
[Extracted File Size (MB)],
[Extracted Files Per GB])
select [Server Name],
[Database Name],
[Media Name],
[Custodian Name],
[Extracted File Count],
[Extracted File Size (MB)],
([Extracted File Size (MB)]/1024) as [Extracted Files Per GB]
from #temp_Extracted_file_count_FINAL_CUSTODIAN
order by [Database Name],[Media Name],[Custodian Name]



insert into #DR_Preview_Report(
[Server Name],
[Database Name],
[Media Name],
[Custodian Name],
[Near Dups File Count],
[Near Dups File Size (MB)])
select [Server Name],
[Database Name],
[Media Name],
[Custodian Name],
[Near Dups File Count],
[Near Dups File Size (MB)]
from #temp_near_dups_file_count_FINAL_CUSTODIAN
order by [Database Name],[Media Name],[Custodian Name]





insert into #DR_Preview_Report(
[Server Name],
[Database Name],
[Media Name],
[Custodian Name],
[Exact Dups File Count],
[Exact Dups File Size (MB)])
select [Server Name],
[Database Name],
[Media Name],
[Custodian Name],
[Exact Dups File Count],
[Exact Dups File Size (MB)]
from #temp_exact_dups_file_count_FINAL_CUSTODIAN
order by [Database Name],[Media Name],[Custodian Name]



insert into #DR_Preview_Report(
[Server Name],
[Database Name],
[Media Name],
[Custodian Name],
[Unsuppressed File Count],
[Unsuppressed File Size (MB)],
[Unsuppressed Files Per GB])
select [Server Name],
[Database Name],
[Media Name],
[Custodian Name],
[Unsuppressed File Count],
[Unsuppressed File Size (MB)],
([Unsuppressed File Size (MB)]/1024) as [Unsuppressed Files Per GB]
from #temp_unsuppressed_file_count_FINAL_CUSTODIAN
order by [Database Name],[Media Name],[Custodian Name]






CREATE TABLE master.dbo.DR_Preview_Report_FINAL(
[Server Name] nvarchar(40),
[Database Name]
nvarchar(100),
[Media Name] nvarchar(100),
[Custodian Name]
nvarchar(300),
[Original File Count] int,
[Original File Size (MB)] decimal(10,4),
[Extracted File Count] int,
[Extracted File Size (MB)] decimal(10,4),
[Extracted Files Per GB] decimal(10,4),
[Near Dups File Count] int,
[Near Dups File Size (MB)] decimal(10,4),
[Exact Dups File Count] int,
[Exact Dups File Size (MB)] decimal(10,4),
[Unsuppressed File Count] int,
[Unsuppressed File Size (MB)] decimal(10,4),
[Unsuppressed Files Per GB] decimal(10,4)
)




insert into master.dbo.DR_Preview_Report_FINAL
select [Server Name],
[Database Name],
[Media Name],
[Custodian Name],
max([Original File Count]) as [Original File Count],
max([Original File Size (MB)]) as [Original File Size (MB)],
max([Extracted File Count]) as [Extracted File Count],
max([Extracted File Size (MB)]) as [Extracted File Size (MB)],
max([Extracted Files Per GB]) as [Extracted Files Per GB],
max([Near Dups File Count]) as [Near Dups File Count],
max([Near Dups File Size (MB)]) as [Near Dups File Size (MB)],
max([Exact Dups File Count]) as [Exact Dups File Count],
max([Exact Dups File Size (MB)]) as [Exact Dups File Size (MB)],
max([Unsuppressed File Count]) as [Unsuppressed File Count],
max([Unsuppressed File Size (MB)]) as [Unsuppressed File Size (MB)],
max([Unsuppressed Files Per GB]) as [Unsuppressed Files Per GB]
from #DR_Preview_Report
group by [Server Name],
[Database Name],
[Media Name],
[Custodian Name]




----------NOT IN USE ANYMORE TG 11/7/2007
--------------------------------------------------------------------------------
/*
-------------- Calculate [other file count] fields
UPDATE a
SET [Other Files Count] = (b.[Unsuppressed File Count] - b.[Exact
Dups File Count] - b.[Original File Count])
from master.dbo.DR_Preview_Report_FINAL a
join master.dbo.DR_Preview_Report_FINAL b on a.[Server Name] = b.
[Server Name] and a.[Database Name] = b.[Database Name] and a.
[Custodian Name] = b.[Custodian Name]






-- calculate [Other File Size (MB)]
UPDATE a
SET [Other Files Size (MB)] = cast(b.[Unsuppressed File Size (MB)] as
decimal(10,4)) - cast(b.[Exact dups File Size (MB)] as decimal(10,4))
- cast(b.[Original File Size (MB)] as decimal(10,4))
from master.dbo.DR_Preview_Report_FINAL a
join master.dbo.DR_Preview_Report_FINAL b on a.[Server Name] = b.
[Server Name] and a.[Database Name] = b.[Database Name] and a.
[Custodian Name] = b.[Custodian Name]
*/


--select * from master.dbo.DR_Preview_Report_FINAL

SET NOCOUNT OFF
GO



Thanks!


Tammy