| Author |
Topic |
|
peace
Constraint Violating Yak Guru
420 Posts |
 Posted - 2012-04-20 : 04:22:32
|
| How to send sql query result which is huge as attachment to email? |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2012-04-20 : 05:44:31
|
| i get this error:File attachment or query results size exceeds allowable value of 5000000 bytes. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-20 : 14:50:53
|
| Error message is obvious. Why not export it to multiple files and then send it via multiple emails?Whats the data you're trying to send? Also whats the need to sending such high amount of data over mail?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-04-21 : 06:13:03
|
| As part of the process - consider zipping the file prior to attaching or if conveniant, place on a network path and email a path to the fileJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2012-04-22 : 21:47:45
|
| Im populating a query for a list of mobile number.Is scheduling process.That's why im thinking to schedule it instead doing it manually.But seems like I can't sent out huge data.Any idea? as the 3rd party couldnt connect/have the access to connect to the path as you suggested. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-22 : 21:51:38
|
| did you try zipping and sending it?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2012-04-22 : 21:59:00
|
| can u give example on zipping query?Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-22 : 22:01:28
|
| you can easily do it in ssis using j# distributable library functions or even use third party free zipping software and execute it by adding a execute process task inside ssis package------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2012-04-22 : 22:06:22
|
| SSIS??? oh my.. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-22 : 22:08:43
|
quote:
Originally posted by peace
SSIS??? oh my..
you can also execute it from t-sql query using xp_cmdshell and calling third party exe (if you've one) that does zipping but its not recommended------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2012-04-22 : 22:11:13
|
| mind to show me step by step. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2012-04-23 : 00:03:19
|
| where should i actually apply the zip?Dont really understand the spEXEC msdb.dbo.sp_send_dbmail @recipients='test@test.com', @body='test', @subject ='test', @profile_name ='test', @query ='select * from db.test', @attach_query_result_as_file = 1 ; |
|
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2012-04-23 : 03:42:23
|
| this is useful[url]http://sqlworkday.blogspot.com/2010/12/how-to-send-csv-or-xls-file-as.html#!/2010/12/how-to-send-csv-or-xls-file-as.html[/url] |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-04-24 : 01:36:31
|
| Most zip packages have a command line option.For example, check PKZip command line utility. As long as SQL Server could see the libraries , use xp_cmdshell to zip the backup file. Ensure you fit in with the organisations security policy around usinng xp_cmdshellJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-24 : 21:43:08
|
| if you dont want to use third party exe use j# distributable functions------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2012-04-25 : 03:16:00
|
| Sending large files using email is not a very good design choice. It's unsecure, unreliable and it often takes manual handling. Use FTP or SFTP instead- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
|
|
Result to email