rivkamak
asked on
sql 2005 email me results
I have a sql file that runs a query and emails me the table in the email.
Is there a way to set it up in SQL 2005 that it should email me the results with an attached csv file with the results?
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'*****.com',@subject = 'Error Logs' ,
@body=N'',@query = 'select top (25) num = COUNT(*),[filename] from web_logs
group by [site],[filename]
order by num desc' ;
Is there a way to set it up in SQL 2005 that it should email me the results with an attached csv file with the results?
You need to use a combination of the parameters @attach_query_result_as_fi le and @query_attachment_filename
ASKER
So if I'm trying to do this in the email query, the single quote in the case is throwing an error. How do I adjust that?
also the when email = '' also throws an error.
also the when email = '' also throws an error.
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'******',@subject = 'Error Logs' ,
@body=N'',@query = 'use oorahCMS
SELECT ID, First, Last,E_mail as email,
CASE Make
WHEN '' THEN ''Car''
WHEN ', , , , ' THEN ''Car''
ELSE LTRIM ( REPLACE(Make, ',' ,'') )
END as Make,
dateEntered, DATEDIFF(minute, dateEntered , getdate())as timedifference,
DATENAME(weekday, DATEADD(day,2, dateEntered ))as daytosend from OnlineUnfinishedDonations
where DATEDIFF(minute, dateEntered, getdate()) < @startnum and DATEDIFF(minute, dateEntered, getdate()) >@endnum and [E_mail] <>'' and donated is null and sentEmail <> 1
AND E_mail NOT IN (SELECT KAOCD_Email FROM tKAOCD);',
@attach_query_result_as_file = 1 , @query_result_separator=',';
First post your valid query, that you can execute successfully from SSMS.
ASKER
SELECT ID, First, Last,E_mail as email,
CASE Make
WHEN '' THEN 'Car'
WHEN ', , , , ' THEN 'Car'
ELSE LTRIM ( REPLACE(Make, ',' ,'') )
END as Make,
DATENAME(weekday, DATEADD(day,2, dateEntered ))as daytosend ,
dateEntered, DATEDIFF(minute, dateEntered , getdate())as timedifference
from OnlineUnfinishedDonations
where DATEDIFF(minute, dateEntered, getdate()) < @startnum and DATEDIFF(minute, dateEntered, getdate()) >@endnum and [E_mail] <>'' and donated is null and sentEmail <> 1
AND E_mail NOT IN (SELECT KAOCD_Email FROM tKAOCD);
See if this works for you:
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'******',
@subject = 'Error Logs' ,
@body=N'',
@query = 'use oorahCMS;
SELECT ID,
First,
Last,
E_mail AS email,
CASE Make
WHEN '''' THEN ''Car''
WHEN '', , , , '' THEN ''Car''
ELSE LTRIM(REPLACE(Make, '','', ''''))
END AS Make,
DATENAME(WEEKDAY, DATEADD(DAY, 2, dateEntered)) AS daytosend,
dateEntered,
DATEDIFF(MINUTE, dateEntered, GETDATE()) AS timedifference
FROM OnlineUnfinishedDonations
WHERE DATEDIFF(MINUTE, dateEntered, GETDATE()) < @startnum
AND DATEDIFF(MINUTE, dateEntered, GETDATE()) > @endnum
AND [E_mail] <> ''''
AND donated IS NULL
AND sentEmail <> 1
AND E_mail NOT IN (SELECT KAOCD_Email
FROM tKAOCD);',
@attach_query_result_as_file = 1,
@query_result_separator=',';
ASKER
i want to use a variable outside the exec, but I'm getting this error.
1. Must declare the scalar variable "@startnum".
2. also, this returns a txt file. Any way to get the data in a csv comma delimited file.
1. Must declare the scalar variable "@startnum".
2. also, this returns a txt file. Any way to get the data in a csv comma delimited file.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
2. Try setting @query_result_separator=', '
ASKER
It is there now.
but my file comes out with ----- dashes like 3 lines for each line seperator
but my file comes out with ----- dashes like 3 lines for each line seperator
I am afraid I have no idea.