Database Mail Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Database Mail Question

Author

Topic

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2011-12-07 : 09:07:23

I have multiple SQL Server 2005 Instances where i run the following SQL every week or so to capture Database sizes
which I then copy the results to an EXCEL worksheet:


/**************************************************************************************************/
DECLARE @sysdb BIT -- 1 for include sysdatabases ,0 for Not include sysdatabases
DECLARE @order int -- set the order of columns
DECLARE @orderby bit -- 0 for asc, 1 for desc 
set nocount on 
SET @sysdb = 0
SET @order = 1
SET @orderby = 1
declare @id int 
,@type character(2) 
,@pages bigint 
,@dbname sysname 
,@dbsize bigint 
,@logsize bigint 
,@reservedpages bigint 
,@sqlstring varchar(2000) 
create table #spacetemp ( 
id int identity(1,1) 
,DBname varchar(500) 
,DBcreationDate datetime 
,DBsize decimal(10,2) 
,Avispace decimal(10,2)
,DBStatusid bigint
,DBStatus varchar(200)
) 
create table #stemp ( 
sid int identity(1,1) 
,mdfpath varchar(200) 
,ldfpath varchar(200) 
,ds bigint 
,dl bigint 
) 
insert into #spacetemp (DBname,DBcreationDate,DBStatusid,DBStatus) 
select [name],crdate, status,case
when status=1 then 'autoclose'
when status=4 then 'select into/bulkcopy'
when status=8 then 'trunc'
when status=16 then 'torn page detection'
when status=32 then 'loading'
when status=64 then 'pre recovery'
when status=128 then 'recovering'
when status=256 then 'not recovered'
when status=512 then 'offline'
when status=1024 then 'read only'
when status=2048 then 'dbo use only'
when status=4096 then 'single user'
when status=32768 then 'emergency mode'
when status=4194304 then 'autoshrink'
when status=1073741824 then 'cleanly shutdown'
end
from master..sysdatabases where status!=512 and dbid >case when @sysdb=1 then 0 else 4 end
Declare @sDBname varchar(500) 
Declare @sDBcreationDate datetime 
Declare @counter int 
Declare @Maxid int 
set @counter = 1 
select @maxid = max(id) from #spacetemp 
while (@counter<=@maxid) 
begin 
set @id=0 
set @type ='' 
set @pages = 0 
set @dbsize = 0 
set @logsize = 0 
set @reservedpages = 0 
set @sqlstring = '' 
select @dbname = DBname from #spacetemp where id =@counter 
IF @type = 'SQ' 
 SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue 
--set @sqlstring= 'update #spacetemp set ds = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) 
-- ,dl = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) from #spacetemp join '+@dbname+'..sysfiles as ss 
--on #spacetemp.dbname=ss.name' 
set @sqlstring= 'insert into #stemp (ds,dl) 
select sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) 
 ,sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) from ['+@dbname+']..sysfiles' 
exec (@sqlstring) 
select @dbsize=ds,@logsize=dl from #stemp where sid=@counter 
update #spacetemp set 
dbsize = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) 
 * 8192 / 1048576,15,2)), 
Avispace = ltrim(str((case when @dbsize >= @reservedpages then 
 (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) 
 * 8192 / 1048576 else 0 end),15,2)) 
 where [DBNAME]=@DBNAME 
--select @dbname,@counter,ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) 
-- * 8192 / 1048576,15,2)+' MB'), 
--ltrim(str((case when @dbsize >= @reservedpages then 
-- (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) 
-- * 8192 / 1048576 else 0 end),15,2)+' MB'),@dbsize,@logsize 
set @counter = @counter+1 
end 
set @sqlstring='
select 
[DBname] as ''Database Name'' 
,[dbcreationdate] as ''Creation Date'' 
,convert(varchar(200),[dbsize])+'' MB'' as ''size'' 
--,convert(varchar(200),[avispace])+'' MB'' as ''Aviable Space''
--,convert(varchar(200),[dbsize]+[avispace])+'' MB'' as ''Total''
--,[DBstatus] as ''Database Status'' 
--,DBStatusid 
from #spacetemp order by '+case @order
when 1 then 'DBname'
when 2 then 'dbcreationdate'
when 3 then 'dbsize'
--when 4 then 'avispace'
--when 5 then 'dbsize+avispace'
else 'DBname' end+' '+case @orderby when 1 then 'asc' when 0 then 'desc'end +''
exec (@sqlstring)
drop table #spacetemp 
drop table #stemp

Attempting to try and auotmate the process a little by using sp_send_dbmail to send the results to
my INBOX like this:


EXEC msdb.dbo.sp_send_dbmail@recipients=N'my.email@gmail.com',
@body='Message Body', @subject ='Message Subject',
@profile_name ='mailProfile',@query = 
'DECLARE @sysdb BIT -- 1 for include sysdatabases ,0 for Not include sysdatabases
DECLARE @order int -- set the order of columns
DECLARE @orderby bit -- 0 for asc, 1 for desc 
set nocount on 
SET @sysdb = 0
SET @order = 1
SET @orderby = 1
declare @id int 
,@type character(2) 
,@pages bigint 
,@dbname sysname 
,@dbsize bigint 
,@logsize bigint 
,@reservedpages bigint 
,@sqlstring varchar(2000) 
create table #spacetemp ( 
id int identity(1,1) 
,DBname varchar(500) 
,DBcreationDate datetime 
,DBsize decimal(10,2) 
,Avispace decimal(10,2)
,DBStatusid bigint
,DBStatus varchar(200)
) 
create table #stemp ( 
sid int identity(1,1) 
,mdfpath varchar(200) 
,ldfpath varchar(200) 
,ds bigint 
,dl bigint 
) 
insert into #spacetemp (DBname,DBcreationDate,DBStatusid,DBStatus) 
select [name],crdate, status,case
when status=1 then 'autoclose'
when status=4 then 'select into/bulkcopy'
when status=8 then 'trunc'
when status=16 then 'torn page detection'
when status=32 then 'loading'
when status=64 then 'pre recovery'
when status=128 then 'recovering'
when status=256 then 'not recovered'
when status=512 then 'offline'
when status=1024 then 'read only'
when status=2048 then 'dbo use only'
when status=4096 then 'single user'
when status=32768 then 'emergency mode'
when status=4194304 then 'autoshrink'
when status=1073741824 then 'cleanly shutdown'
end
from master..sysdatabases where status!=512 and dbid >case when @sysdb=1 then 0 else 4 end
Declare @sDBname varchar(500) 
Declare @sDBcreationDate datetime 
Declare @counter int 
Declare @Maxid int 
set @counter = 1 
select @maxid = max(id) from #spacetemp 
while (@counter<=@maxid) 
begin 
set @id=0 
set @type ='' 
set @pages = 0 
set @dbsize = 0 
set @logsize = 0 
set @reservedpages = 0 
set @sqlstring = '' 
select @dbname = DBname from #spacetemp where id =@counter 
IF @type = 'SQ' 
 SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue 
--set @sqlstring= 'update #spacetemp set ds = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) 
-- ,dl = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) from #spacetemp join '+@dbname+'..sysfiles as ss 
--on #spacetemp.dbname=ss.name' 
set @sqlstring= 'insert into #stemp (ds,dl) 
select sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) 
 ,sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) from ['+@dbname+']..sysfiles' 
exec (@sqlstring) 
select @dbsize=ds,@logsize=dl from #stemp where sid=@counter 
update #spacetemp set 
dbsize = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) 
 * 8192 / 1048576,15,2)), 
Avispace = ltrim(str((case when @dbsize >= @reservedpages then 
 (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) 
 * 8192 / 1048576 else 0 end),15,2)) 
 where [DBNAME]=@DBNAME 
--select @dbname,@counter,ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) 
-- * 8192 / 1048576,15,2)+' MB'), 
--ltrim(str((case when @dbsize >= @reservedpages then 
-- (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) 
-- * 8192 / 1048576 else 0 end),15,2)+' MB'),@dbsize,@logsize 
set @counter = @counter+1 
end 
set @sqlstring='
select 
[DBname] as ''Database Name'' 
,[dbcreationdate] as ''Creation Date'' 
,convert(varchar(200),[dbsize])+'' MB'' as ''size'' 
--,convert(varchar(200),[avispace])+'' MB'' as ''Aviable Space''
--,convert(varchar(200),[dbsize]+[avispace])+'' MB'' as ''Total''
--,[DBstatus] as ''Database Status'' 
--,DBStatusid 
from #spacetemp order by '+case @order
when 1 then 'DBname'
when 2 then 'dbcreationdate'
when 3 then 'dbsize'
--when 4 then 'avispace'
--when 5 then 'dbsize+avispace'
else 'DBname' end+' '+case @orderby when 1 then 'asc' when 0 then 'desc'end +''
exec (@sqlstring)
drop table #spacetemp 
drop table #stemp ', 
@attach_query_result_as_file = 1,
@query_attachment_filename ='Results.txt'

Problem is... I get this error:

Msg 102, Level 15, State 1, Line 37
Incorrect syntax near 'autoclose'.Msg 911, 
Level 16, State 1, 
Line 47Could not locate entry in sysdatabases for database 'only'. 
No entry found with that name. 
Make sure that the name is entered correctly.

Can anyone help me out with this?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-12-07 : 14:45:00

Your @query parameter is a string. When you have embedded single quotes within a string, you need to use two of them (e.g., change 'dbo use only' to be ''dbo use only''). Also, in your CASE processing you are testing for equality but since status is a bit field, wouldn't you want to mask off the appropriate bit first (e.g., (Status & 1) = 1)?

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2011-12-07 : 14:52:20

I guess my question really is.. I am not understanding why my SQL works OK when run standalone but when I try and wrap it in the sp_sendmail procedure.. it doesn't work?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-07 : 15:33:27

Because of what Bustaz Kool said. It's the same problem you'd experience when using dynamic SQL. You have to double quote the strings inside your @query string.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-12-07 : 15:58:02

Thank-you, Tara.

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-07 : 16:01:20

I typically wrap my code into a stored procedure to get around this "issue". I'd especially do that here with such a huge script.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2011-12-08 : 08:23:51

Thank you all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources