Stored Procedure Email - 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

Transact-SQL (2005)

Stored Procedure Email

Author

Topic

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-10-05 : 10:33:11

I am trying to send out a queury within an email, but here are my problems, I need to pass an id field in a where clause to the query but it always gives me errors hwo can I do this. And I always get 3 emails, why is this.

ALTER proc [dbo].[UnitEmail]
AS

Declare @Id as int,
@Commander as Varchar(80),
@1SG as Varchar(80),
@Admin as Varchar(80),
@Medical as Varchar(80),
@Clear as Varchar(80),
@Train as Varchar(80),
@Log as Varchar(80),
@Issues as varchar(6),
@Taskname as varchar(40)

Declare Unit_Cursor CURSOR FOR Select Distinct uf.intUnitMobId,
ud.strCommander,
ud.str1SG,
ud.strAdmin,
ud.strMedical,
ud.strClear,
ud.strTrain,
ud.strLog
from tblUnitFilters as uf INNER JOIN
tblUnitDeployData as ud on
Ud.intUnitMobId = uf.intUnitMobId

OPEN Unit_Cursor

FETCH NEXT FROM Unit_Cursor INTO @Id,@Commander,@1SG,@Admin,@Medical,@Clear,@Train,@Log

while @@FETCH_STATUS =0

BEGIN
Select Count(t.intFilterId),
t.strTaskName
from MobtrackerDotnet.dbo.tblTask as T INNER JOIN
tblUnitFilters as uf on uf.intAssignedId = t.intAssignedId
Where uf.intUnitMobId = @Id
Group by strTaskname

FETCH NEXT FROM Unit_Cursor INTO @Id,@Commander,@1SG,@Admin,@Medical,@Clear,@Train,@Log

EXEC msdb.dbo.sp_send_dbmail
@profile_name ='Default',
@recipients='kevin.deutschman@mn.ngb.army.mil',
@body= 'Welcome to MobTracker Dot net',
@subject ='Test email functionality of MobTracker Program',
@sensitivity = 'Private',
@query = 'Select Count(t.intFilterId) as Issues,
t.strTaskName as Taskname
from MobtrackerDotnet.dbo.tblTask as T INNER JOIN
MobtrackerDotnet.dbo.tblUnitFilters as uf on uf.intAssignedId = t.intAssignedId
Group by strTaskname',
@query_result_header = 1,
@query_result_width = 200

end

Close Unit_Cursor
deallocate Unit_Cursor

asgast
Posting Yak Master

149 Posts

Posted - 2009-10-05 : 10:45:14

1) you can't add variable to a @query in the send_mail, create tamp table, store value there, send_mail just select data from there

2) ou send mail in a cursor, cursor runs 3 times, you get 3 mails, but this is just my guess.

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-10-05 : 11:52:16

ok,
then how can i put the results of the upper query in an email that i can send out????

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-05 : 14:08:51

do you mean your @id value will vary for each mail?

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-10-06 : 08:31:02

Yes,
the first go round grabs my ids from the database, then the sencond go round grabs the records based on the ID.

asgast
Posting Yak Master

149 Posts

Posted - 2009-10-06 : 09:23:00

try:
DECLARE @query1 nvarchar(max)

SET @query1 = 'Select Count(t.intFilterId),
t.strTaskName
from MobtrackerDotnet.dbo.tblTask as T INNER JOIN
tblUnitFilters as uf on uf.intAssignedId = t.intAssignedId
Where uf.intUnitMobId ='+ CAST(@Id as varchar(10))

.......
@query = @query1

Also your cursor is rather strange:
FETCH NEXT FROM Unit_Cursor INTO @Id,@Commander,@1SG,@Admin,@Medical,@Clear,@Train,@Log

while @@FETCH_STATUS =0

BEGIN
Select Count(t.intFilterId),
t.strTaskName
from MobtrackerDotnet.dbo.tblTask as T INNER JOIN
tblUnitFilters as uf on uf.intAssignedId = t.intAssignedId
Where uf.intUnitMobId = @Id
Group by strTaskname

FETCH NEXT FROM Unit_Cursor INTO @Id,@Commander,@1SG,@Admin,@Medical,@Clear,@Train,@Log
I suspect this line needs to go after sending mail

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-10-06 : 12:35:50

Hi,
Thanks for the help, it works somewhat but gives me an error that
Column 'MobtrackerDotnet.dbo.tblTask.strTaskName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I have tired putting back in the where clause but it keeps giving me errors

Group by strTaskname'

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2009-10-06 : 12:39:36

ok,
Think I got working but now I get this error
Where uf.intUnitMobId ='' + CAST(@id as int)''
Group by strTaskname'

Conversion failed when converting the varchar value ' + CAST(@id as varchar(10))' to data type int.

I declare it as an int in my declaation statement

asgast
Posting Yak Master

149 Posts

Posted - 2009-10-07 : 03:25:12

in @query1 I store nvarchar string and later pass it parameter to dbmail, and a part of query you show and error suggest that you have some mistakes in it
the query should look like

'Select Count(t.intFilterId), t.strTaskName
from MobtrackerDotnet.dbo.tblTask as T
INNER JOIN tblUnitFilters as uf on uf.intAssignedId = t.intAssignedId
Where uf.intUnitMobId = '+CAST(@Id AS VARCHAR(10)+'
Group by strTaskname'

I don't know why you had some double quotes int yours, try it now

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources