To email the query exceuted from SQL 2005.

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

Development Tools

ASP.NET

To email the query exceuted from SQL 2005.

Author

Topic

Kohila vani
Starting Member

7 Posts

Posted - 2009-09-10 : 10:48:00

Hi All,

Actaully i was very new to SQL platform. I was finding the way to email by exceuting the script result by using the Database Mail functionality.Where the email should sent to my client by every morning 5:00 with the exceuted script result.

I was trying by executing the below code and recieved the error "Incorrect syntax near '@execute_query_database'.".

exec msdb.dbo.sp_send_dbmail
@profile_name = 'Database Mail',
@recipients = 'myownemail.com',
@subject = 'daily report',
@body = 'HTML',
@query = 'EXEC proc_name'
@execute_query_database = 'database_name',
@query_attachment_filename = 'output.csv'

Can anyone assists me to find the solution.Also let me know what value should be given for the parameter "@execute_query_database'.

Many thanks in advance.

Many thanks,
Kohila

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-13 : 14:36:41

you missed a , after @query = 'EXEC proc_name'

Kohila vani
Starting Member

7 Posts

Posted - 2009-09-18 : 06:15:20

Could you please elaborate what i have missed please

Many thanks,
Kohila

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-09-18 : 06:22:20

exec msdb.dbo.sp_send_dbmail
@profile_name = 'Database Mail',
@recipients = 'myownemail.com',
@subject = 'daily report',
@body = 'HTML',
@query = 'EXEC proc_name',---u have missed comma here
@execute_query_database = 'database_name',
@query_attachment_filename = 'output.csv'

-------------------------
R...

Kohila vani
Starting Member

7 Posts

Posted - 2009-09-23 : 06:57:56

Many thanks:). Again i have tried to format my query result by using the HTML tag. But recieveing the below error

Coding:

=======

DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML =

N'<H1>Automated report</H1>' +

N'<table border="1">' +

N'<tr><th>EIN </th><th>OUC</th>' +

N'<th>Advisor Role</th><th>Location Code</th><th>Machine IP</th>' +

N'<th>Login Date</th><th>Logout Date</th><th>Session Status</th></tr>' +

cast((SELECT td = B.EIN,'',

td = B.OUC,'',

td = B.PROFILE_ID ,'',

td = B.LOCATION_CODE, '',

td = B.MACHINE_IP, '',

td = B.LOGIN_DATE, '',

td = B.LOGOUT_DATE, '',

td = (SELECT CASE LOGOUT_DATE WHEN ISNULL(LOGOUT_DATE,' ') THEN 'CLOSED' ELSE 'OPEN' END FROM tblsession_log_trn A WHERE A.SESSION_LOG_ID=B.SESSION_LOG_ID), '',

from tblsession_log_trn B WHERE B.LOGIN_DATE BETWEEN CONVERT(VARCHAR(10), GETDATE()-1, 103) and CONVERT(VARCHAR(10), GETDATE(), 103)))

N'</table>' ;

Error:

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'from'.

Could you please assist me on debugging the error?

Many thanks,
Kohila

Kabila
Starting Member

33 Posts

Posted - 2009-09-23 : 07:30:56

DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =

N'<H1>Automated report</H1>' +

N'<table border="1">' +

N'<tr><th>EIN </th><th>OUC</th>' +

N'<th>Advisor Role</th><th>Location Code</th><th>Machine IP</th>' +

N'<th>Login Date</th><th>Logout Date</th><th>Session Status</th></tr>' +

'cast((SELECT td = B.EIN,'''',td = B.OUC,'''',
td = B.PROFILE_ID ,'''',td = B.LOCATION_CODE, '''',td = B.MACHINE_IP, '''',td = B.LOGIN_DATE,'''',
td = B.LOGOUT_DATE, '''',
td = (SELECT CASE LOGOUT_DATE WHEN ISNULL(LOGOUT_DATE,'' '') THEN ''CLOSED'' ELSE ''OPEN'' END
FROM tblsession_log_trn A ,tblsession_log_trn B WHERE A.SESSION_LOG_ID=B.SESSION_LOG_ID and B.LOGIN_DATE BETWEEN CONVERT(VARCHAR(10), GETDATE()-1, 103) and CONVERT(VARCHAR(10), GETDATE(), 103)))'
+
N'</table>'

Kohila vani
Starting Member

7 Posts

Posted - 2009-09-24 : 09:58:33

Hi Kabila, Many thanks........

I am tring to send an automated email(with my daily report) to client. I have the query formatted with HTML tag and other query to send the email.

Please find the HTML formatted query:

DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =

N'<H1>Automated report</H1>' +

N'<table border="1">' +

N'<tr><th>EIN </th><th>OUC</th>' +

N'<th>Advisor Role</th><th>Location Code</th><th>Machine IP</th>' +

N'<th>Login Date</th><th>Logout Date</th><th>Session Status</th></tr>' +

'cast((SELECT td = B.EIN,'''',td = B.OUC,'''',
td = B.PROFILE_ID ,'''',td = B.LOCATION_CODE, '''',td = B.MACHINE_IP, '''',td = B.LOGIN_DATE,'''',
td = B.LOGOUT_DATE, '''',
td = (SELECT CASE LOGOUT_DATE WHEN ISNULL(LOGOUT_DATE,'' '') THEN ''CLOSED'' ELSE ''OPEN'' END
FROM tblsession_log_trn A ,tblsession_log_trn B WHERE A.SESSION_LOG_ID=B.SESSION_LOG_ID and B.LOGIN_DATE BETWEEN CONVERT(VARCHAR(10), GETDATE()-1, 103) and CONVERT(VARCHAR(10), GETDATE(), 103)))'
+
N'</table>'

When I tried to run the query its displaying the output as “command exceuted successfully”. I need to put this query as a stored procedure and should get the result in a formatted way

I need to put the above query in a stored procedure and should place the procedure name in the below code and schedule the job.

exec msdb.dbo.sp_send_dbmail
@profile name = 'Database Mail',
@recipients = 'ownemaiid@gmail.com,
@subject = 'Test',
@body = 'HTML',
@query = 'EXEC SP_DAILY_USERS_LOGGED_IN'
@execute_query_database = 'database_name';

Kindly any one can assists me to accomplist it...........

Many thanks,
Kohila

Kohila vani
Starting Member

7 Posts

Posted - 2009-09-25 : 10:08:36

Please ignore the above email.......

I am tring to schedule an automatic email report which to be sent to my client every day using the database mail 2005.
Below is the qury i used.

Report query:
CREATE procedure[dbo].[sp_user_sessionreport]

Select B.EIN as EIN, B.OUC as OUC, B.PROFILE_ID as [USER ROLE] , B.LOCATION_CODE as [LOCATION CODE] , B.MACHINE_IP as [MACHINE IP], B.LOGIN_DATE as [LOGIN DATE] , B.LOGOUT_DATE as [LOGOUT DATE],(SELECT CASE LOGOUT_DATE WHEN ISNULL(LOGOUT_DATE,' ') THEN 'CLOSED' ELSE 'OPEN' END FROM tblsession_log_trn A WHERE A.SESSION_LOG_ID=B.SESSION_LOG_ID) as [Session Status] from tblsession_log_trn B WHERE B.LOGIN_DATE BETWEEN CONVERT(VARCHAR(10), GETDATE()-1, 103) and CONVERT(VARCHAR(10), GETDATE(), 103)

And i have tried to exceute the procedure

exec msdb.dbo.sp_send_dbmail
@profile_name = 'Database Mail',
@recipients = 'kohilavani.murthy@bt.com',
@subject = 'Test',
@body = 'HTML',
@query = 'EXEC sp_user_sessionreport'
@execute_query_database = 'EPG0707';

But getting the erorr as

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 478
Query execution failed: ?Msg 242, Level 16, State 3,
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

could any one advise me on debugging the error please.....

Many thanks,
Kohila

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-25 : 14:40:37

dont convert the dates to varchar in where condition. keep them as datetimes itself

Kohila vani
Starting Member

7 Posts

Posted - 2009-09-29 : 09:58:30

Hi Visakh,

Thank you very much. I have debugged the error. Actaully i am tring to send a automated report to my client with daily report using the database mail functionality in the SQL server 5000.

I am able to recive the email with the report but in the unformatted way.

Here is the report which i am getting when i exceute my stored procedure with msdb.dbo.sp_send_dbmail.

Stored procedure:

CREATE procedure[dbo].[sp_user_sessionreport]

Select B.EIN as EIN, B.OUC as OUC, B.PROFILE_ID as [USER ROLE] , B.LOCATION_CODE as [LOCATION CODE] , B.MACHINE_IP as [MACHINE IP], B.LOGIN_DATE as [LOGIN DATE] , B.LOGOUT_DATE as [LOGOUT DATE],(SELECT CASE LOGOUT_DATE WHEN ISNULL(LOGOUT_DATE,' ') THEN 'CLOSED' ELSE 'OPEN' END FROM tblsession_log_trn A WHERE A.SESSION_LOG_ID=B.SESSION_LOG_ID) as [Session Status] from tblsession_log_trn B WHERE B.LOGIN_DATE BETWEEN CONVERT(VARCHAR(10), GETDATE()-1) and CONVERT(VARCHAR(10), GETDATE())

exec msdb.dbo.sp_send_dbmail
@profile_name = 'Database Mail',
@recipients = 'kohilavani.murthy@email.com,
@subject = 'Test',
@body = 'HTML',
@query = 'EXEC sp_user_sessionreport',
@execute_query_database = 'database_name';

I am reciving the email with the report in unformatted way
===========================================================

EIN OUC USER ROLE LOCATION CODE
MACHINE IP LOGIN DATE LOGOUT DATE Session Status
-------------------------------------------------- --------------- ---------------------------------------------------------------------------------------------------- --------------------------------
-------------------------------------------------------------------- -------------------- ----------------------- ----------------------- --------------
603711006 MYZU11 advisor TLP-DD
10.229.255.20 2009-09-28 12:43:13.473 2009-09-28 19:23:49.300 C
LOSED
603821248 MYZU10 advisor TLP-DD
10.229.242.20 2009-09-28 12:43:58.100 2009-09-28 16:28:23.267 C
LOSED
604743921 MYZC91 advisor BCK-CT
10.236.152.134 2009-09-28 12:44:02.227 2009-09-28 12:44:51.210 C
LOSED
604744003 MYZC91 advisor BCK-CT
10.236.152.136 2009-09-28 12:44:43.400 2009-09-28 15:55:15.077 C
LOSED
603091160 MYZNA1 advisor NCC-NE
10.230.231.110 2009-09-28 12:46:35.747 2009-09-28 21:13:47.447 C
LOSED
701730831 MKNPB MANAGER ACC-BT
147.149.1.202 2009-09-28 12:54:59.247 2009-09-28 13:00:24.617 C
LOSED
700774300 MYZDBC advisor DCC-DN
10.230.250.36 2009-09-28 13:01:06.743 NULL OPEN
702388406 MKNPF advisor ACC-BT
147.149.1.202 2009-09-28 13:02:08.340 2009-09-28 13:06:13.080 C
LOSED
603538207 MYZU11 advisor TLP-DD
10.229.255.79 2009-09-28 13:02:41.497 2009-09-28 20:57:13.510 C
LOSED
604185844 MYZAC4 advisor ARM-BT
147.149.1.202 2009-09-28 13:02:49.217 NULL OPEN
702235786 MYZGA7 advisor ABH-G2
10.232.182.171 2009-09-28 13:02:54.497 2009-09-28 13:07:32.270 C
LOSED

I have tried to use HTML tags but dint find a solution. Could any one assist me on this please.

Many thanks,
Kohila

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 14:07:38

try giving @body_format ='HTML' also as an argument for sp_send_Dbmail

Kohila vani
Starting Member

7 Posts

Posted - 2009-09-30 : 07:00:01

Hi visakh,

Tried by using the parameter @body fomat ='HTML' but received the email without any space

EIN OUC USER ROLE LOCATION CODE MACHINE IP LOGIN DATE LOGOUT DATE Session Status -------------------------------------------------- --------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- ------------ -------------------- ----------------------- ----------------------- -------------- 604792202 MKNPF advisor ARM-BT 193.113.139.177 2009-09-29 11:53:28.497 2009-09-29 21:01:19.387 C LOSED 604745925 MYZL91 advisor CWT-LA 10.232.211.175 2009-09-29 11:53:32.823 2009-09-29 12:04:19.860 C LOSED 702037571 MYZDA3 advisor DCC-DN 10.230.249.98 2009-09-29 11:53:37.230 2009-09-29 12:48:18.227 C LOSED 604799362 MYZP1 advisor ACC-BT 193.113.139.177 2009-09-29 11:54:04.623 NULL OPEN 600168148 MYZPA1 advisor ACC-BT 193.113.139.177 2009-09-29 11:54:19.873 2009-09-29 18:31:10.007 C LOSED 604821582 MKNPF advisor ARM-BT 193.113.139.177 2009-09-29 11:54:27.123 2009-09-29 21:02:19.000 C LOSED 604821629 MKNPF advisor ARM-BT 193.113.139.177 2009-09-29 11:54:32.060 NULL OPEN 603399785 MYZAC4 advisor ARM-BT 193.113.139.177 2009-09-29 11:56:47.220 NULL OPEN 604382526 MYZAD8 advisor ARM-BT 193.113.139.177 2009-09-29 11:57:00.033 2009-09-29 20:59:07.523 C LOSED 604821261 MKNPF advisor ARM-BT 193.113.139.177 2009-09-29 11:57:18.143 NULL OPEN 604829106 MYZP1 advisor ACC-BT 193.113.139.177 2009-09-29 11:57:32.910 NULL OPEN 600974923 MYZAD4 advisor ARM-BT 193.113.139.177 2009-09-29 11:58:22.990 2009-09-29 20:48:58.193 C LOSED 602244123 MYZDM8 advisor DCC-DN 10.230.249.12 2009-09-29 11:58:31.770 2009-09-29 18:16:49.743 C LOSED 604792080 MKNPF advisor ARM-BT 193.113.139.177 2009-09-29 11:58:45.350 NULL OPEN

Please suggest if any other way to format....How to use the HTML tags to format the result.

Many thanks,
Kohila

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-08-22 : 11:09:38

i tried this query.

how to apply to send email notification if only it hits the where condition?

because it seems like no matter what condition it still send me email.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-22 : 12:17:03

for that either you need to add an IF condition with same conditions in query and inside that call sp_send_dbmail. other way is to return count of records also along with query and then check if its > 0 before calling sp_send_Dbmail.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources