asked on

sql 2005 email me results

I have a sql file that runs a query and emails me the table in the email.

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' ;

Open in new window

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?

Anthony Perkins

You need to use a combination of the parameters @attach_query_result_as_file and @query_attachment_filename

rivkamak

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.

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=',';

Open in new window

Anthony Perkins

First post your valid query, that you can execute successfully from SSMS.

rivkamak

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);

Open in new window

Anthony Perkins

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=',';

Open in new window

rivkamak

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.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

2. Try setting @query_result_separator=','

rivkamak

ASKER

It is there now.
but my file comes out with ----- dashes like 3 lines for each line seperator

Anthony Perkins

I am afraid I have no idea.