Query regarding mail... - 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

SQL Server Administration (2005)

Query regarding mail...

Author

Topic

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-07-25 : 08:51:58

Hello All,

How can i send whole table data by means of mail?

i mean i do have table called 'OrderTable' which gives me data for a particular day updation. Now i want to send that whole data to all users.

can any one please help?

-- Thanks
Prashant

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-07-25 : 09:00:34

In what format do they want the data? You could for example , export to Excel and then send the file . This process could be automated via SSIS

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-07-26 : 01:16:49

i want to send data in tabular format.

For example.....

PitID Price Size Type
201 88 10 Bid
201 103 30 Offer
202 44 10 Bid
203 15 10 Bid
243 645 10 Bid
244 666 10 Bid

is there any other way to do this except SSIS?

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-07-26 : 02:39:14

Just to be clear , do you want this to be an automated process or is this a one-ff situation?

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-07-26 : 02:53:56

Yep, i want this to be an. automated process.

Actually i needed this process to occur automatically at the end of day ans send mails to related users in Tabular Fomat Data.

I do have query which gives me day by day tabular reports. I do have a Query which gives me no of related mail addresses also.

the thing is that i dont know how to send mail with tabular format??

if its not clear to you then let me know i will try again to explain you...

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-07-26 : 05:46:53

The automated process you can handle via a Job.
The tabular format you can handle through db mail , such as :


DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT Product AS 'td','',SUM(SalePrice) AS 'td' 
FROM Sales GROUP BY Product FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><H1>Daily Report</H1><body bgcolor=#000000><table border = 1>
<tr><th>Product</th><th>Amounts</th></tr>' SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =N'jack@myemail.com',
@body = @body,
@body_format ='HTML',
@subject ='MySubject',
@profile_name ='MyProfile'

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-09-12 : 02:28:11

Hello,

Thanks for reply.

Below solution works fine when there are values in the tables. Suppose if i dont have any values then it's not working fine.

See the below example. I do have vales for Products but at the same time i dont have any values for amounts. In this case i would like to display 'space' rather than showing blank sells......

____________________________________________________________________

Product Amounts

ARG
HYS6
_____________________________________________________________________

-- Thanks
Prashant Hirani

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources