database email

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » database email

17 posts, Page 1 of 2

1 2 »»

database email

Rate Topic

Display Mode

Topic Options

Author

Message

prathibha_aviator

Posted Wednesday, January 16, 2013 10:52 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:12 AM
Points: 95, Visits: 223

Hello Again...I am at my new intern place and the job my manager gave me is
I have to run a query for example "select * from organization" and then the results i get from the result tab should be send to an email id (want to try wid my own).
So I have reached a point where i can send emails from the database. I have created a new profile and account associated with that using database mail node. But I dont know what to do next.. Also the mails are too slow.. I haven't find any solution till now..Its been a couple days since I am trying this..Any help is appreciated. I am open to learn any new thing.

--Pra Smile

--------------------------------------------------------------------------------

Post #1408001

SQLRNNR

Posted Wednesday, January 16, 2013 10:56 AM

SSCoach

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 18,736, Visits: 12,333

We would need more details. The info is pretty limited in order to provide a viable solution.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1408004

prathibha_aviator

Posted Wednesday, January 16, 2013 11:02 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:12 AM
Points: 95, Visits: 223

So My data base is DB1
I have some 120 tables in that
I have one particular table which is oraganization

My job is to run a query "select * from organization"
and the results that i get, I have to send it to my email ID.. but not manually by exporting a file,

It should be automated. It’ll need to run every night automatically without anyone doing anything. Is that possible?

--Pra Smile

--------------------------------------------------------------------------------

Post #1408006

SQLRNNR

Posted Wednesday, January 16, 2013 11:07 AM

SSCoach

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 18,736, Visits: 12,333

That could be a rather large email.

But yes you can do that.

start with a little reading on the procedure that sends emails.

http://msdn.microsoft.com/en-us/library/ms190307.aspx

Post #1408007

prathibha_aviator

Posted Wednesday, January 16, 2013 11:47 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:12 AM
Points: 95, Visits: 223

USE msdb
GO
EXEC sp_send_dbmail @profile_name='prathibha',
@recipients='prathibha_aviator@abc.com',
@subject='Unbalanced Accounts',
@body='',
@query='select * from CNAccounting_Joliet_2013.dbo.employee',
@execute_query_database= 'CNAccounting_Joliet_2013',
@attach_query_result_as_file= 1,
@query_attachment_filename= 'Results'

Is this right??? My intention is just to give the query results as an attached file

--Pra Smile

--------------------------------------------------------------------------------

Post #1408025

SQLRNNR

Posted Wednesday, January 16, 2013 12:04 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 18,736, Visits: 12,333

Looks correct to me.

Post #1408028

prathibha_aviator

Posted Wednesday, January 16, 2013 12:09 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:12 AM
Points: 95, Visits: 223

Now How do I do it automatically evrynight.. I dont have to run the query again and again
It should run by its self at the end of the day and send me an email with the query results... Please................????

--Pra Smile

--------------------------------------------------------------------------------

Post #1408030

SQLRNNR

Posted Wednesday, January 16, 2013 12:11 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 8:43 AM
Points: 18,736, Visits: 12,333

Add the query to a SQL Agent Job. Schedule the job to run nightly.

Post #1408031

Ed Wagner

Posted Wednesday, January 16, 2013 12:45 PM

SSCommitted

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 1,877, Visits: 550

I don't know if you care, but database mail sends outgoing mail only. The recipients need to understand that if they try to reply, it won't be deliverable unless you're sending it from a profile using an account that just happens to have the same email address as one on an Exchange (or other email) server. Either way, it won't go to the sender because there's nothing there to receive incoming email.

Also, be aware that sending the result of a query can get very big very quickly. Any mail rules the incoming mail server has in place will be applied and may prevent the email from even arriving.

Post #1408041

prathibha_aviator

Posted Wednesday, January 16, 2013 12:54 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:12 AM
Points: 95, Visits: 223

Thanks for the information. Yes the recipients are all aware, This is just to send them one or two column query results so that they can work on the effected parts. But Ya, The size can be big some times. I totally agree and will keep that in mind. I'll think of any other alternative later. But presently focusing on automation part, I mean "Adding the query to a SQL Agent Job"

--Pra Smile

--------------------------------------------------------------------------------

Post #1408045

« Prev Topic | Next Topic »

17 posts, Page 1 of 2

1 2 »»

Permissions