September 15, 2008 at 2:57 am
Hi have the following script which does not error but will not send results to email when SQL script returns 1 row or more. I have tested first part of script if I add print 'Hello' it returns the hello please advise.
set transaction isolation level read uncommitted
set nocount on
SELECT tablename, transfered, DATEDIFF(day, transfered, GETDATE()) AS diff
INTO ##temp_table
FROM [product].dbo.table_updated
WHERE DATEDIFF(day, transfered, GETDATE()) >2
ORDER BY transfered
IF @@rowcount > 0
BEGIN
EXEC msdb..sp_send_dbmail
@recipients = 'email@company.com,
@file_attachments = 'results.txt',
@subject = 'Product Database tables out of date',
@query = 'set nocount on select * from ##temp_table',
@exclude_query_output = 'TRUE',
@body = 'This is an auto-generated email from SQL server'
drop table ##temp_table
September 16, 2008 at 2:24 pm
Try replacing your "If @@rowcount > ..." statement
with the following and see if it works for you:
(put this at top of your code before the select)
Declare @tblrows int
(and put this right after your select)
Set @tblrows = @@rowcount
If @tblrows > 0
....
September 16, 2008 at 3:59 pm
You should be getting a syntax error based on your syntax, since you are missing a quote in the sp_send_dbmail call......
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 17, 2008 at 3:09 am
Thank you for the reply have tried but no luck even though I know the sql has generated rows nothing sent
September 17, 2008 at 3:12 am
Yes the syntax I posted is missing a quote. Sorry this is correct in my code, took it out by accident when posting code to forum.
September 17, 2008 at 7:05 am
It's also missing an END statement to match up with the BEGIN. Some other things to look at include the error log, if any, that would be associated with any trouble coming out of the sp_send_dbmail procedure, and, checking to see that the procedure works without any conditions - test it by just executing it for the sole purpose of seeing if it will send any given e-mail.
Steve
(aka smunson)
:):):)
hazelb (9/17/2008)
Yes the syntax I posted is missing a quote. Sorry this is correct in my code, took it out by accident when posting code to forum.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 17, 2008 at 7:32 am
Thanks all for help checked logs nothing reported not even getting to mail queue so started from scratch, this time decided not to set as an attachment and this has worked. Still do not understand why first code failed but this will do as I require. (see revised code below)
set transaction isolation level read uncommitted
set nocount on
SELECT tablename, transfered, DATEDIFF(day, transfered, GETDATE()) AS diff
INTO ##tempResults
FROM [product].dbo.table_updated
WHERE DATEDIFF(day, transfered, GETDATE()) >2
ORDER BY transfered
IF @@rowcount > 0
BEGIN
EXEC msdb..sp_send_dbmail
@recipients = 'user@domain.com',
@subject = 'SQL Product Database',
@query = 'select * from ##tempResults',
@body = 'This is an auto-generated email from SQL and contains details of Table updates.'
drop table ##tempResults
END
September 17, 2008 at 7:51 am
Glad you got it working. As to the file attachment, you might want to try specifying a complete path, including the drive letter.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 17, 2008 at 8:46 am
I suspect that the reason that your original script was failing is this line:
@query = 'set nocount on select * from ##temp_table',
I do not think that the "SET NOCOUNT ON" is a good idea in the @query parameter. I also notice that in your second script, that worked, you took it out.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply