Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL 2008 SQL Agent Job to E-mail Query result for DB freespace

Posted on 2010-11-16
4
793 Views
Last Modified: 2012-05-10
Hello,

I have the following working query and results:
USE TopsData
GO

SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,  
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;
Results:
bName                                                                                                                           FileName                                                                                                                         CurrentSizeMB                           FreeSpaceMB
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- ---------------------------------------
TopsData                                                                                                                         NewTops_Data                                                                                                                     36946.437500                            33787.250000
TopsData                                                                                                                         NewTops_Log                                                                                                                      8146.562500                             8084.000000
However, when I attempt to use it with the following to send the results as an e-mail:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Administrator',
@recipients = 'administrator@dayspringfhc.com',
@query = 'SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,  
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;
EXEC msdb.dbo.sp_send_dbmail',
@subject = 'Error Log ',
@attach_query_result_as_file = 1 ;
I get an error at Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'SpaceUsed'.
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
A  'SpaceUsed' is not a recognozed option appears when I mouse hover. However, the works without using a send mail attempt.
I am attempting to monitor the freespace inside a single DB and want to run this as a scheduled job once a night.
Help!

0
Comment
Question by:NetManaged
4 Comments
 
LVL 15

Accepted Solution

by:
jorge_toriz earned 62 total points
ID: 34152322
Change your query to:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Administrator',
@recipients = 'administrator@dayspringfhc.com',
@query = 'SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,  
size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;
EXEC msdb.dbo.sp_send_dbmail',
@subject = 'Error Log ',
@attach_query_result_as_file = 1
0
 
LVL 16

Assisted Solution

by:EvilPostIt
EvilPostIt earned 63 total points
ID: 34153148
You could also create a view for the query specified in the @query statement.

Also you seem to be attempting to execute the sp_send_dbmail statement in teh query text too. You should take this out as it wont work.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Administrator',
@recipients = 'administrator@dayspringfhc.com',
@query = 'SELECT DB_NAME() AS DbName, 
name AS FileName, 
size/128.0 AS CurrentSizeMB,  
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB 
FROM sys.database_files;
[b]EXEC msdb.dbo.sp_send_dbmail',[/b]
@subject = 'Error Log ',
@attach_query_result_as_file = 1 ; 

Open in new window


Also when working with query outputs I always use
@query_result_no_padding=1
as it makes the email more readable.

0
 

Author Comment

by:NetManaged
ID: 34155410
Jorge & EvilPostit

Thanks for the prompt response. Using your suggestions from each of you I got the results I was looking for. I want to split the points as both of you had input that corrected problems I was having with the original script.
Here is the finished product:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Administrator',
@recipients = 'administrator@somebody.com,
@query = 'USE TopsData
SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,  
size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;',
@subject = "Error Log",
@attach_query_result_as_file = 1

Thanks heaps!
0
 

Expert Comment

by:cgitst
ID: 34774122
I dont know if this thread is still being monitored but I am trying to something similiar and getting errors when its trying to process the query.  My query is below:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'CGSQL',
    @recipients = 'user@abc.com',
    @query = 'SELECT object_name(IPS.object_id) AS [TableName],
            SI.name AS [IndexName],
            IPS.Index_type_desc,
            IPS.avg_fragmentation_in_percent,
            IPS.avg_fragment_size_in_pages,
            IPS.avg_page_space_used_in_percent,
            IPS.record_count,
            IPS.ghost_record_count,
            IPS.fragment_count,
            IPS.avg_fragment_size_in_pages
      FROM sys.dm_db_index_physical_stats(db_id(N'SQLDBName'), NULL, NULL, NULL , 'DETAILED') IPS
            JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
            JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
      WHERE ST.is_ms_shipped = 0
      ORDER BY 1,5',
      @subject = 'Automated Success Message',
      @attach_query_result_as_file = '1'

The error I am getting is:
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'SQLDBName'.

If i run this query without the EXEC msdb.dbo.sp_send_dbmail, it runs fine.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question