I have a query that is giving me the message "Error formatting query,
probably invalid parameters [SQLSTATE 42000] (Error 22050). The step
failed.", when running within a job using sp_send_dbmail, even though the
select statement ran fine when ran in SQL Management Studio. Has anyone
encountered this before, and if so, what did they do to correct it?
The query is as follows:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients = '[Email address protected]
@query = 'SELECT top 20 count(a.id) total, b.name name FROM
Log a inner join names b on a.nameid = b.id where createdon > =
dateadd(day,-1,getdate()) group by b.name order by total desc',
@subject = 'Top 20 by name.';
Thanks.
-----Original Message-----
From: Curtis [mailto:mssqldba-ezmlmshield-x80058396.[Email address protected]
Sent: Friday, April 27, 2007 10:57 AM
To: LazyDBA Discussion
Subject: RE: Save query results to File
To send the query result in a file add "@attach_query_result_as_file = 1"
If you want to name the attachment add "@query_attachment_filename =
'filename'"
See: http://msdn2.microsoft.com/en-us/library/ms190307.aspx
-----Original Message-----
From: Bowman Rita A.
[mailto:mssqldba-ezmlmshield-x77635153.[Email address protected]
Sent: Friday, April 27, 2007 9:04 AM
To: LazyDBA Discussion
Subject: RE: Save query results to File
Emil,
I use a similar process (shown below), except I do not save the results
to a text file.
Exec @return = master..xp_sendmail
@subject = 'Query Subject',
@query = '<database>.<owner>.<sproc>',
@recipients = '<email recipients>',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'filename'
If you need to export the file, maybe DTS would be a better way? A DTS
job that creates a file and emails it can easily be scheduled.
TGIF+1/2
-----Original Message-----
From: Kackos Emil
[mailto:mssqldba-ezmlmshield-x69257126.[Email address protected]
Sent: Friday, April 27, 2007 9:42 AM
To: LazyDBA Discussion
Subject: Save query results to File
Hello and happy Friday!
I want to run a scheduled task that produces a list, save the results to
a file and send that file to a person via email all in one shot.
I can already send emails via scheduled tasks.
Is it possible to run a query, save the result to a text file and send
that text file via email?
It's going to run as a scheduled task, so it can't prompt for path and
filename.
Thanks!
Emil
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page