Hi all,
I've a query running under MS SQLServer2000 (Stored Procedures).
I'd like to send it's result via e-mail, formatting the output in txt form
or html form.
Someone knows how to do it ? Have you got examples of code ?
Thanks for the advice
Marco Garutti
On May 24, 12:22 pm, "Marco Garutti"
wrote:
> Hi all,
> I've a query running under MS SQLServer2000 (Stored Procedures).
> I'd like to send it's result via e-mail, formatting the output in txt form
> or html form.
> Someone knows how to do it ? Have you got examples of code ?
> Thanks for the advice
>
> Marco Garutti
It may be better you can send through excel . Just run the SP in QA
and copy and paste to excel . Zip the excel file and send it to email
Note: Excel has limitation of 65,000 odd rows
If you want in text format , in QA , set output to text with delimiter
of your choice (,or |) and copy and paste to notepad .
One way is to use the extended stored procedure xp_sendmail. It does require
that you have a MAPI profile and SQL Mail configured. Then it can be used
like this:
xp_sendmail
@recipients = 'email@domain.com',
@subject = 'Query Results',
@query = 'SELECT fname, lname FROM Employees WHERE empid = 9'
In the above example the results of the query will be included in the e-mail
message. Read in SQL Server Books OnLine more about the additional
parameters for xp_sendmail. They control how the query is executed (database
context, database user) and how it is displayed (headers, formatting, attach
query results in a file).
Here is more info on configuring SQL Mail:
http://support.microsoft.com/kb/q263556/
HTH,
Plamen Ratchev
http://www.SQLStudio.com
In addition to the other suggestion to use xp_sendmail, you could use
the job scheduler to set it up to run at a specific time each day.
I've got some sample code below that was used for exactly the same
thing. In your stored procedure you'd have to do the formatting in
the Select clause.
DECLARE @rlist varchar(1000)
Declare @Sub nvarchar(60)
Declare @Q nvarchar(100)
Declare @Date1 nvarchar(20)
Declare @Date2 nvarchar(20)
set @Date1 = Convert(nvarchar(12), GetDate() - 2, 101)
set @Date2 = Convert(nvarchar(12), GetDate() - 1, 101)
set @Q = 'EXEC [WIN Support Database]..RebootsReport ''' + @Date1 +
''', ''' + @Date2 + ''''
Select @Sub = 'Reboot Report for ' + @Date1
SELECT @rlist = 'xxx@xx.xxx'
exec master..xp_sendmail @recipients=@rlist,@query=@Q,@subject=@Sub
On May 24, 2:22 am, "Marco Garutti"
wrote:
> Hi all,
> I've a query running under MS SQLServer2000 (Stored Procedures).
> I'd like to send it's result via e-mail, formatting the output in txt form
> or html form.
> Someone knows how to do it ? Have you got examples of code ?
> Thanks for the advice
>
> Marco Garutti