SQL email send RSS

• • Reply
  

  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?

• • Reply
  

  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,,,

• • Reply
  

  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..

• • Reply
  

  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
         
  END

  
  
  

  Thanks,
  Raj Sedhain
  
  • Edited by rajsedhain on Aug 10, 2012 02:46 PM
• • Reply
  

  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?

• • Reply
  

  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 = @cvs

  
  
  

  Please, Mark as Answer if this reply helped you.
• • Reply
  

  rajsedhain

  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.

  Thanks,
  Raj Sedhain
  
  • Edited by rajsedhain on Aug 10, 2012 04:49 PM
• • Reply
  

  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?

• • Reply
  

  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...

  Thanks,
  Raj Sedhain
  
• • Reply
  

  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