Creating EXEC msdb.dbo.sp_send_dbmail

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

General SQL Server Forums

New to SQL Server Programming

Creating EXEC msdb.dbo.sp_send_dbmail

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2010-04-02 : 10:53:28

Hi,

I need some help. I have this view to pull for incorrect Worker's Comp Fees, I want this to send an email to me with the records that are incorred. How would I do this? Any help would be great. The email that I recived, I want it to return every column that I have here.

This is my view....

Select Distinct---*
pro.PRO_PCS,
prop_uac,
prop_trm,
pro.pro_lname,
pro.pro_fname,
prop_prd

FROM impact_prod.dbo.pro pro
JOIN impact_prod.dbo.prop prop
ON pro.pro_id1 = prop.prop_id1 AND prop.prop_prd = 'WCP'
LEFT JOIN impact_prod.dbo.pcsa pcsa
ON pro.pro_id1 = pcsa.pcsa_id1 and pcsa.pcsa_Stat NOT IN ('NOGOOD')
WHERE
pro.pro_xtyp NOT IN ('BILL', 'EOTF') and
(LEFT(prop.prop_cont,2) <> 'NP' or prop.prop_cont is null)and prop_trm is null and
prop_uac IN ('A','B','F','J','N') and pro.pro_state = 'PA'
order by pro_pcs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 11:09:34

you mean send a mail with this query result as subject? or send a mail to each persons returned by this query?

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

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2010-04-02 : 11:11:35

Send an email to me and a coworker with the query results in the body. Then the subject, I want it to say "I want Incorrect WCP".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 11:16:42

you can use sp_send_dbmail and use @query parameter to pass this query and it will mail you results

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

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2010-04-02 : 12:06:52

thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 12:33:01

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources