LogSpace Script

I am writing a logspace checking script. For now I just
want it to send me the size of all my log files. But I
am doing something stupid or don't understand cursors
because it's only putting the last database into @.msg3.
If anyone has any ideas what I'm doing wrong, I'd sure
appreciate it as this is driving me crazy!
declare @.server varchar(50)
declare @.subject2 varchar(50)
declare @.msg2 varchar(8000)
declare @.msg3 varchar(8000)
declare @.email_body varchar(8000)
declare @.field1 varchar(100)
declare @.field2 varchar(100)
--declare testint int
set @.server = 'SQL_3'
set @.subject2 = 'LOGSPACE ON ' + @.server + ' FOR DBs WITH
LOG SPACE OVER 2G'
print @.subject2
if exists (select name from tempdb..sysobjects where name
like '#temcmlogspace%')
begin
drop table #temcmlogspace
end
create table #temcmlogspace (dbname varchar(100) null,
logsize2 numeric(16, 6) null,
logusedpcnt numeric(16,6) null, status2 int null)
--The size is in Megs:
insert into #temcmlogspace
exec ('dbcc sqlperf(logspace)')
set @.msg3 = ''
declare email_cursor cursor for
select dbname, logsize2 from #temcmlogspace
open email_cursor
while @.@.fetch_status = 0
fetch next from email_cursor into @.field1, @.field2
begin
set @.msg2 = @.field1 + ' ' + cast(@.field2 as varchar
(20))
set @.msg3 = @.msg3 + @.msg2
print '@.msg2'
print @.msg2
end
close email_cursor
deallocate email_cursor
print '@.msg3'
print @.msg3Is it possible that @.field1 and/or @.field2 is ever NULL?
http://www.aspfaq.com/
(Reverse address to reply.)
"Alley" <anonymous@.discussions.microsoft.com> wrote in message
news:0c9901c4fe6b$3632a120$a601280a@.phx.gbl...
> I am writing a logspace checking script. For now I just
> want it to send me the size of all my log files. But I
> am doing something stupid or don't understand cursors
> because it's only putting the last database into @.msg3.
> If anyone has any ideas what I'm doing wrong, I'd sure
> appreciate it as this is driving me crazy!
>
> declare @.server varchar(50)
> declare @.subject2 varchar(50)
> declare @.msg2 varchar(8000)
> declare @.msg3 varchar(8000)
> declare @.email_body varchar(8000)
> declare @.field1 varchar(100)
> declare @.field2 varchar(100)
> --declare testint int
> set @.server = 'SQL_3'
> set @.subject2 = 'LOGSPACE ON ' + @.server + ' FOR DBs WITH
> LOG SPACE OVER 2G'
> print @.subject2
> if exists (select name from tempdb..sysobjects where name
> like '#temcmlogspace%')
> begin
> drop table #temcmlogspace
> end
> create table #temcmlogspace (dbname varchar(100) null,
> logsize2 numeric(16, 6) null,
> logusedpcnt numeric(16,6) null, status2 int null)
> --The size is in Megs:
> insert into #temcmlogspace
> exec ('dbcc sqlperf(logspace)')
> set @.msg3 = ''
> declare email_cursor cursor for
> select dbname, logsize2 from #temcmlogspace
> open email_cursor
> while @.@.fetch_status = 0
> fetch next from email_cursor into @.field1, @.field2
> begin
> set @.msg2 = @.field1 + ' ' + cast(@.field2 as varchar
> (20))
> set @.msg3 = @.msg3 + @.msg2
> print '@.msg2'
> print @.msg2
> end
> close email_cursor
> deallocate email_cursor
> print '@.msg3'
> print @.msg3
>
>|||Shouldn't be: it's taking the results from DBCC
Logspace. Why?
>--Original Message--
>Is it possible that @.field1 and/or @.field2 is ever NULL?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Alley" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0c9901c4fe6b$3632a120$a601280a@.phx.gbl...
just[vbcol=seagreen]
WITH[vbcol=seagreen]
name[vbcol=seagreen]
>
>.
>|||Well, if you set @.msg3 = @.msg3 + NULL, guess what happens?
http://www.aspfaq.com/
(Reverse address to reply.)
"Alley" <anonymous@.discussions.microsoft.com> wrote in message
news:1c6201c4fe6f$54022c30$a501280a@.phx.gbl...[vbcol=seagreen]
> Shouldn't be: it's taking the results from DBCC
> Logspace. Why?
> message
> just
> WITH
> name|||Thx for the help, but I figured out what was wrong. I
had the incorrect order. This script works:
declare @.server varchar(50)
declare @.subject2 varchar(50)
declare @.msg2 varchar(8000)
declare @.msg3 varchar(8000)
declare @.email_body varchar(8000)
declare @.field1 varchar(100)
declare @.field2 numeric(16, 6)
--declare testint int
set @.server = 'SQL_3'
set @.subject2 = 'LOGSPACE ON ' + @.server + ' FOR DBs WITH
LOG SPACE OVER 2G'
print @.subject2
if exists (select name from tempdb..sysobjects where name
like '#temcmlogspace%')
begin
drop table #temcmlogspace
end
create table #temcmlogspace (dbname varchar(100) null,
logsize2 numeric(16, 6) null,
logusedpcnt numeric(16,6) null, status2 int null)
--The size is in Megs:
insert into #temcmlogspace
exec ('dbcc sqlperf(logspace)')
--select * from #temcmlogspace
set @.msg3 = ''
declare email_cursor cursor for
select dbname, logsize2 from #temcmlogspace
open email_cursor
while @.@.fetch_status = 0
begin
fetch next from email_cursor into @.field1, @.field2
set @.msg2 = @.field1 + ' ' + cast(@.field2 as varchar
(20))
set @.msg3 = @.msg3 + @.msg2
--print '@.msg2'
--print @.msg2
end
close email_cursor
deallocate email_cursor
--print '@.msg3'
--print @.msg3
>--Original Message--
>Well, if you set @.msg3 = @.msg3 + NULL, guess what
happens?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Alley" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1c6201c4fe6f$54022c30$a501280a@.phx.gbl...
NULL?[vbcol=seagreen]
But I[vbcol=seagreen]
cursors[vbcol=seagreen]
@.msg3.[vbcol=seagreen]
sure[vbcol=seagreen]
null,[vbcol=seagreen]
varchar[vbcol=seagreen]
>
>.
>sql