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...
>> 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
>>
>>
>
>.
>|||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...
> 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...
> >> 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
> >>
> >>
> >>
> >>
> >
> >
> >.
> >|||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...
>> 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...
>> >> 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
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>