Sign In| Join
Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Sep 11, 2012 10:35 AM by mrao26
Member
9 Points
119 Posts
Aug 10, 2012 02:14 PM|LINK
Hello all.. I was wondering if anyone has ever used sql to run a piece of code export the result to excel and send it in email all in sql?
8 Points
16 Posts
Aug 10, 2012 02:33 PM|LINK
Hi,
I would like to know if anyone has managed to extract the data from sql and send as csv,,,
Aug 10, 2012 02:36 PM|LINK
No they don't have to manage the data it is just a list of db names and when it sends to txt its not readable..
Contributor
4181 Points
1041 Posts
Aug 10, 2012 02:45 PM|LINK
you can create a stored procedure and then run the job daily or weekly whenever you want using SQL agent.
See the working code below.
CREATE PROCEDURE [dbo].[CSVFileSend_Automatically] AS BEGIN DECLARE @File varchar(20), @Subject varchar(20), @Separator NCHAR(1) = ',', @SqlStatement NVARCHAR(MAX) = ' SET NOCOUNT ON SELECT ''ID'', ''Name'', ''Address'', ''Phone'', ''Email'' UNION ALL SELECT * from Customer SET @File = 'Customer.csv' SET @Subject = 'Customer File Send Automatically' EXEC msdb.dbo.sp_send_dbmail @profile_name='Your SQL profile', @recipients='abc@hotmail.com', @subject=@Subject, @query=@SqlStatement, @execute_query_database='Your Database Name', @query_result_separator=@Separator, @query_result_header=0, @query_result_no_padding=1, @attach_query_result_as_file=1, @query_attachment_filename=@File END
Aug 10, 2012 03:19 PM|LINK
so this procedure will get the data you want from sql database and convert it into csv and also send email with that attachement?
5068 Points
973 Posts
Aug 10, 2012 03:24 PM|LINK
Hi TheEisdame,
use this.
DECLARE @cvs nvarchar(MAX) DECLARE @separator nvarchar(1) SET @cvs = N'' SET @separator = ',' SELECT TOP 10 @cvs = @cvs + CAST(int_column AS nvarchar) + @separator + nvarchar_column + @separator + CAST(datetime AS nvarchar) + CHAR(13) FROM data_table WITH (NOLOCK) SELECT @cvs PRINT @cvs EXEC msdb.dbo.sp_send_dbmail @recipients = @mail_recipients, @profile_name = 'SO', @subject = @mail_subject, @body = @cvs
Aug 10, 2012 04:48 PM|LINK
Yes. Try and let me know if this does not work.
If you dont need the column header you can exclude the union all first part portion.
Aug 10, 2012 05:24 PM|LINK
Hi, i have tried and It only gave me one record do i need to loop thru and if so what would that look like?
Aug 10, 2012 05:26 PM|LINK
you dont need to loop through. You might have problem in your SQL query...check if any where clause....just run that query first and see the result.
paste your query here if you want to look me...
Aug 14, 2012 05:55 PM|LINK
sorry for the delay... here is the code
DECLARE @tableHTML NVARCHAR(MAX); SET @tableHTML = N'<h1>Unused Index</h1>' + N'<table border="1">' + N'<tr><th>Name:</th></tr>' + CAST((select top 1 name from sys.databases ) as varchar(max)) + N'</table>'; print @tableHTML EXEC msdb.dbo.sp_send_dbmail @profile_name = '1255869' , @recipients = 'me@myemail.com' , @subject = 'Database name' , @body = @tableHTML , @body_format = 'HTML'; Go
TheEisdame
Member
9 Points
119 Posts
SQL email send
Aug 10, 2012 02:14 PM|LINK
Hello all.. I was wondering if anyone has ever used sql to run a piece of code export the result to excel and send it in email all in sql?
mrao26
Member
8 Points
16 Posts
Re: SQL email send
Aug 10, 2012 02:33 PM|LINK
Hi,
I would like to know if anyone has managed to extract the data from sql and send as csv,,,
TheEisdame
Member
9 Points
119 Posts
Re: SQL email send
Aug 10, 2012 02:36 PM|LINK
No they don't have to manage the data it is just a list of db names and when it sends to txt its not readable..
rajsedhain
Contributor
4181 Points
1041 Posts
Re: SQL email send
Aug 10, 2012 02:45 PM|LINK
you can create a stored procedure and then run the job daily or weekly whenever you want using SQL agent.
See the working code below.
CREATE PROCEDURE [dbo].[CSVFileSend_Automatically] AS BEGIN DECLARE @File varchar(20), @Subject varchar(20), @Separator NCHAR(1) = ',', @SqlStatement NVARCHAR(MAX) = ' SET NOCOUNT ON SELECT ''ID'', ''Name'', ''Address'', ''Phone'', ''Email'' UNION ALL SELECT * from Customer SET @File = 'Customer.csv' SET @Subject = 'Customer File Send Automatically' EXEC msdb.dbo.sp_send_dbmail @profile_name='Your SQL profile', @recipients='abc@hotmail.com', @subject=@Subject, @query=@SqlStatement, @execute_query_database='Your Database Name', @query_result_separator=@Separator, @query_result_header=0, @query_result_no_padding=1, @attach_query_result_as_file=1, @query_attachment_filename=@File ENDRaj Sedhain
mrao26
Member
8 Points
16 Posts
Re: SQL email send
Aug 10, 2012 03:19 PM|LINK
so this procedure will get the data you want from sql database and convert it into csv and also send email with that attachement?
Ajay2707
Contributor
5068 Points
973 Posts
Re: SQL email send
Aug 10, 2012 03:24 PM|LINK
Hi TheEisdame,
use this.
DECLARE @cvs nvarchar(MAX) DECLARE @separator nvarchar(1) SET @cvs = N'' SET @separator = ',' SELECT TOP 10 @cvs = @cvs + CAST(int_column AS nvarchar) + @separator + nvarchar_column + @separator + CAST(datetime AS nvarchar) + CHAR(13) FROM data_table WITH (NOLOCK) SELECT @cvs PRINT @cvs EXEC msdb.dbo.sp_send_dbmail @recipients = @mail_recipients, @profile_name = 'SO', @subject = @mail_subject, @body = @cvsrajsedhain
Contributor
4181 Points
1041 Posts
Re: SQL email send
Aug 10, 2012 04:48 PM|LINK
Yes. Try and let me know if this does not work.
If you dont need the column header you can exclude the union all first part portion.
Raj Sedhain
TheEisdame
Member
9 Points
119 Posts
Re: SQL email send
Aug 10, 2012 05:24 PM|LINK
Hi, i have tried and It only gave me one record do i need to loop thru and if so what would that look like?
rajsedhain
Contributor
4181 Points
1041 Posts
Re: SQL email send
Aug 10, 2012 05:26 PM|LINK
you dont need to loop through. You might have problem in your SQL query...check if any where clause....just run that query first and see the result.
paste your query here if you want to look me...
Raj Sedhain
TheEisdame
Member
9 Points
119 Posts
Re: SQL email send
Aug 14, 2012 05:55 PM|LINK
sorry for the delay... here is the code
DECLARE @tableHTML NVARCHAR(MAX); SET @tableHTML = N'<h1>Unused Index</h1>' + N'<table border="1">' + N'<tr><th>Name:</th></tr>' + CAST((select top 1 name from sys.databases ) as varchar(max)) + N'</table>'; print @tableHTML EXEC msdb.dbo.sp_send_dbmail @profile_name = '1255869' , @recipients = 'me@myemail.com' , @subject = 'Database name' , @body = @tableHTML , @body_format = 'HTML'; Go