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?
rivkamakAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
You need to use a combination of the parameters @attach_query_result_as_file and @query_attachment_filename
0
rivkamakAuthor Commented:
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

0
Anthony PerkinsCommented:
First post your valid query, that you can execute successfully from SSMS.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

rivkamakAuthor Commented:
 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

0
Anthony PerkinsCommented:
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

0
rivkamakAuthor Commented:
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.
0
Anthony PerkinsCommented:
1.  Good point, I missed that.  Try it this way (assuming that @startnum and @endnum are varchar(8) and have the format YYYYMMDD:

DECLARE @query nvarchar(max)

SET @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);'

EXEC msdb.dbo.sp_send_dbmail
    @recipients=N'******',
    @subject = 'Error Logs' ,
    @body=N'',
    @query = @query,
    @attach_query_result_as_file = 1, 
    @query_result_separator=',';

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
2.  Try setting @query_result_separator=','
0
rivkamakAuthor Commented:
It is there now.
but my file comes out with ----- dashes like 3 lines for each line seperator
0
Anthony PerkinsCommented:
I am afraid I have no idea.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.