Emailing resultset problem ? - 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 2000 Forums

SQL Server Administration (2000)

Emailing resultset problem ?

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-03-17 : 10:01:36

Hi guys
Hope all is calm and well.

I have a little issue with a scheduled job I am setting up and wonder if anyone would be kind enough to help me please.

I use :
SELECT count(*) as [Members Joined Yesterday]
FROM new_members

in one of my job steps and on success send a message to the operator once this has been completed, the problem is the operator is receiving this message :

JOB RUN: 'job_MemberInformation' was run on 17/03/2006 at 14:51:35
DURATION: 0 hours, 0 minutes, 7 seconds
STATUS: Succeeded
MESSAGES: The job succeeded. The Job was invoked by User LER\Jim Thompson. The last step to run was step 1 (st_MemberInfo).

instead of the count from the table which he requires.
Why is this ???

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-17 : 10:05:42

Because there's nothing to tell it to send the resulset.

instead have a look at xp_sendmail.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-03-17 : 10:07:33

I thought xp_sendmail can only be used in sqlserver service ?? mail but will try anyway ! thank you...

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-03-17 : 13:58:55

Ok so I have set my job step up like this in EM and it parsed quiet alright, this is the code :

declare @cmd varchar(2000)

set @cmd =
'SELECT count(*) as [New Members Joined Yesterday]
FROM members
WHERE (DATEADD(day, dbo.Members.cardproduced - "38024", CONVERT(DATETIME, ''2004-02-05 00:00:00'', 102)) > CONVERT(DATETIME,
getdate() - 2 , 102))
SELECT count(*) as [Total Members Suspended]
FROM members
WHERE suspended = "Y"'

EXEC master.dbo.xp_sendmail @recipients = 'JimThompson@ler.co.uk', @subject = 'Member Information', @message = 'Morning Margaret this is yesterdays member information for you',@query = @cmd

Have set the job up to send me a email on succesful completion which it is doing although I am still getting no resultset back ...Is this the right way to do this task or am I completely off track here ???

Please help this is really frustrating me a simple job that has literally taken me all day and still I can't get it right

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-03-20 : 05:56:19

Hello people does anyone have a small clue as to help me with this please, I will be forever indebted to the person that does.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-20 : 06:29:36

The email on completion will just send you an email to say it has comlpeted - it doesn't know anything about the job except whether it is successful - importantly it doesn't know about a resultset.
The xp_sendmail is what sends the resultset.
Your command will produce two resultsets which could cause problems - but I would expect it to send the first.
I've also simplified the query a bit.

Try the xp_sendmail first without the @query to see if it will send the message.

set @cmd =
'select * from
(
SELECT count(*) as [New Members Joined Yesterday]
FROM members
WHERE (DATEADD(day, dbo.Members.cardproduced - 38024, ''20040205'') > getdate() - 2)
) a
cross join
(SELECT count(*) as [Total Members Suspended]
FROM members
WHERE suspended = ''Y''
) b
'

or better

set @cmd =
'select [New Members Joined Yesterday] = sum(case when (DATEADD(day, dbo.Members.cardproduced - 38024, ''20040205'') > getdate() - 2) then 1 else 0 end) ,
[Total Members Suspended] = sum(case when suspended = ''Y'' then 1 else 0 end)
FROM members
'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-20 : 06:31:24

Also try qualifying the table name with the database.
Use the profiler to see if xp_sendmail is getting a connection and what it is executing and where.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-03-20 : 07:10:45

thank you very much NR will have to try this in a couple of hours and give you some feedback.
thank you again I owe you a cold one.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-03-24 : 05:10:33

Sorry for the late reply to your advise nr but just to say a big thank you ,I scheduled the below code to run everyday in a job and it is now ruinning like clock work, Thanks again for the great help here ...

EXEC xp_sendmail @recipients = 'MatWilkin@ler.co.uk',
@query = 'select * from
(
SELECT count(*) as [New Members Joined Yesterday]
FROM loyaltydb.dbo.members
WHERE (DATEADD(day, loyaltydb.dbo.Members.cardproduced - 38024, ''20040205'') > getdate() - 2)
) a
cross join
(SELECT count(*) as [Total Members Suspended]
FROM loyaltydb.dbo.members
WHERE suspended = ''Y''
) b',
@subject = 'Automated Loyalty Member Information Report',
@message = 'Morning Margaret, Here is yesterdays loyalty information: ' ,
@blind_copy_recipients = 'JimThompson@ler.co.uk',
@separator = ' ',
@attach_results = 'False', @width = 80

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources