The script is exactly the same, but doesn't work as part of a DTS package.

-- **************
-- ************** SETUP TEMP TABLES
-- **************
declare @logsize real
declare @logspaceused real
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(1024),
filename varchar(1024)
)
declare @cmd nvarchar(1024)
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmplg]'))
drop table #tmplg
CREATE TABLE #tmplg
(
DBName varchar(32),
LogSize real,
LogSpaceUsed real,
Status int
)
SELECT @cmd = 'dbcc sqlperf (logspace)'
INSERT INTO #Tmplg EXECUTE (@cmd)
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_stats]'))
drop table #tmp_stats
create table #tmp_stats (
totalextents int,
usedextents int,
dbname varchar(40),
logsize real,
logspaceused real
)

-- **************** MASTER

use [master]

set @cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@cmd)


select @logsize= logsize from #tmplg where dbname = 'master'
select @logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'master'
set @cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsize,logspace used)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'master'+
char(39) + ',' +
cast(@logsize as varchar) + ',' +
cast(@logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @cmd

-- **************** FORMAT AND MAIL RESULTS

DECLARE @STAT1 VARCHAR(30)
DECLARE @STAT2 VARCHAR(10)
DECLARE @STAT3 VARCHAR(10)
DECLARE @STAT4 VARCHAR(10)
DECLARE @STAT5 VARCHAR(10)
DECLARE @RESULTS VARCHAR(5000)
DECLARE @MAILCMD NVARCHAR(4000)
DECLARE stats_Cursor CURSOR FOR
SELECT dbname,totalextents*64/1024 as datasize , usedextents*64/1024 as
dataused ,
logsize ,logspaceused from #tmp_stats

OPEN stats_Cursor

FETCH NEXT FROM stats_Cursor INTO @STAT1,@STAT2,@STAT3,@STAT4,@STAT5
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RESULTS = @RESULTS + '
Database : ' + @STAT1 + '
Database Size: ' + @STAT2 + ' Megs
Database Used: ' + @STAT3 + ' Megs
Log Size: ' + @STAT4 + ' Megs
Log Used: ' + @STAT5 + ' Megs
'
FETCH NEXT FROM stats_Cursor INTO @STAT1,@STAT2,@STAT3,@STAT4,@STAT5
END

CLOSE stats_Cursor
DEALLOCATE stats_Cursor


SET @MAILCMD = 'sp_SMTPMail ' + char(39) + @@servername + char(39) + ',' +
char(39) + @@SERVERNAME + '@loanbright.com' + char(39) + ',' +
char(39) + 'SysAdmin' + char(39) + ',' +
char(39) + 'me (AT) mydomain (DOT) com' + char(39) + ',' +
char(39) + 'Sql DB Usage for ' + @@SERVERNAME + char(39) + ',' +
char(39) + @RESULTS + char(39) + ',' +
char(39) + 'smtp.mail.server' + char(39)

exec sp_executesql @MAILCMD

A) Try putting it in a stored proc
B) EXACTLY how are you executing the DTS package ?

"Mike Morella" <MikeMorella (AT) discussions (DOT) microsoft.com> wrote

Unfortunately, Stored Procs and triggers don't allow the "use" statement.

I am executing the DTS package by opening EM on the server as the windows
admin, using the sa login to register the local instance in EM (now I am
master of the machine and Sql Server). Then I create the DTS package with
two objects: a connection to the local machine using the sa login, and an
Execute Sql task. The execute Sql task uses the connection in the package.
The Execute SQL task uses the EXACT sql you see below, with my email address
and my smtp server (both tested and working). I have narrowed the issue down
to the spot in the Sql where the variable @Results is being built. There is
something about the way that is being done that works in QA, but not in DTS.
If I set that variable to "test" or something silly like that, it works like
a charm! It's only when I try to do something useful with it that it breaks.


"Jeff Block" wrote:

Mike,

I do not see the need for USE master here.

Ilya

"Mike Morella" <MikeMorella (AT) discussions (DOT) microsoft.com> wrote

I would repeat the block starting with "use" for each db on the server....
Think: use db1
gather stats
use db2
gather stats
etc .

"The Margolins" wrote:

Then seperate our your queries into seperate ExecuteSQL tasks

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Mike Morella" <MikeMorella (AT) discussions (DOT) microsoft.com> wrote

Good suggestion Allan. Unfortunately, the #temp tables don't persist across
the seperate ExecuteSql tasks. I didn't find any mention in the
documentation on persisting them other than creating a non-temporary table,
which for policy reasons, I can't do. Is there something I'm missing?

"Allan Mitchell" wrote:

Use global temp tables, i. e. ##temp.

"Mike Morella" <MikeMorella (AT) discussions (DOT) microsoft.com> wrote

That did the trick. Thank you folks

"The Margolins" wrote: