Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Sep 11, 2012 10:35 AM by mrao26
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?
Aug 10, 2012 02:33 PM|LINK
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..
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]
@Separator NCHAR(1) = ',',
@SqlStatement NVARCHAR(MAX) = '
SET NOCOUNT ON
SELECT * from Customer
SET @File = 'Customer.csv'
SET @Subject = 'Customer File Send Automatically'
@profile_name='Your SQL profile',
@execute_query_database='Your Database Name',
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?
Aug 10, 2012 03:24 PM|LINK
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)
data_table WITH (NOLOCK)
@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'<table border="1">'
+ CAST((select top 1 name from sys.databases ) as varchar(max))
@profile_name = '1255869'
, @recipients = 'email@example.com'
, @subject = 'Database name'
, @body = @tableHTML
, @body_format = 'HTML';