Query Result in Mail

Question

Query Result in Mail

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

K - how about the send_db_mail query that you are using?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 2

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'My Profile' ,

@recipients = 'My email ADD' ,

@subject = 'Job Summary'

,@query = 'Exec dbo.JobSummaryUtil'

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Does JobSummaryUtil exist in msdb?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 4

Yes it is there..i executed and working fine in SSMS

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Are you able to send a test email through dbmail?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 6

yes my dbmail profile works fine...you can check the error message in above posts.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Using your proc and the email query it works fine for me under the following circumstances.

The database the query is executing from is msdb or I use the three part naming standard (as suggested) which would be msdb.dbo.JobSummaryUtil.

It will fail when not executing from msdb or when not using the three part naming standard.

Have you tried using the three part naming standard?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 8

Yes it works for me too now ..Thanks RNNR... but result in e-mail so weired format..

Can we do in excel format ?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Use the file attachment option

, [ @attach_query_result_as_file = ] attach_query_result_as_file ]

@attach_query_result_as_file = yourfile.xls or csv

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 10

worked with this options

,@attach_query_result_as_file = 1

,@query_attachment_filename ='Results.xls'

but data in file is not in particular order or the result in excel file can not see like SSMS Result pane.

LutzM SSC Guru Points: 107049 More actions · Answer 11

The data in the file will be in the same order as specified in the original query. If there's no ORDER BY in the SELECT statement then you can't expect any particular order.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 12

The output in the attachment is the same as if you ran the query with "results to text" instead of to "grid" and that's why it looks the way it does, regardless of the file extension you specify. If you want something "prettier" I believe your only option is to use the SSRS idea (with a subscribed report) as mentioned above

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Nah - you can delimit the results in the mailed file using

@query_result_separator = ','

That said, you can replace the separator value with a |, tab, or something else if you deem it necessary.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

JeffRush SSC Eights! Points: 855 More actions · Answer 14

The problem with presenting data is that inevitably the presentation of the data comes into play 🙂

SQLRNNR SSC Guru Points: 281334 More actions · Answer 15

JeffRush (12/30/2011)
The problem with presenting data is that inevitably the presentation of the data comes into play 🙂

How true that is.

Depending on the audience, the far better solution should probably be an SSRS report.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events