? on running a sp in dbmail

Question

Post reply

? on running a sp in dbmail

jbalbo

SSCertifiable

Points: 7580
More actions
May 6, 2013 at 11:58 am

#272230

Hi ,
I am trying to figure out how I can attach a file as an XML in dbmail and run a query that would produce the XML file
I found this example to get some out put and put it in an SP
USE [Test]
GO
/****** Object: StoredProcedure [dbo].[XML_Test_3] Script Date: 05/06/2013 13:53:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[XML_Test_3]
-- Report Parameters
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SELECT ( SELECT 'White' AS Color1,
'Blue' AS Color2,
'Black' AS Color3,
'Light' AS 'Color4/@Special',
'Green' AS Color4,
'Red' AS Color5
FOR
XML PATH('Colors'),
TYPE
),
( SELECT 'Apple' AS Fruits1,
'Pineapple' AS Fruits2,
'Grapes' AS Fruits3,
'Melon' AS Fruits4
FOR
XML PATH('Fruits'),
TYPE
)
FOR XML PATH(''),
ROOT('SampleXML')
I want the attachment to be an XML file with this output
<SampleXML>
<Colors>
<Color1>White</Color1>
<Color2>Blue</Color2>
<Color3>Black</Color3>
<Color4 Special="Light">Green</Color4>
<Color5>Red</Color5>
</Colors>
<Fruits>
<Fruits1>Apple</Fruits1>
<Fruits2>Pineapple</Fruits2>
<Fruits3>Grapes</Fruits3>
<Fruits4>Melon</Fruits4>
</Fruits>
</SampleXML>
Thanks
Joe

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 1

Not much sure what the question is.. but the below url might slow.

http://guozspace.wordpress.com/2012/05/31/create-htmlxml-emails-using-sql-server/

Calibear Ten Centuries Points: 1377 More actions · Answer 2

You will need to use the following variables from the sp_send_dbmail procedure to include a query in your email:

[ , [ @query = ] 'query' ]

[ , [ @execute_query_database = ] 'execute_query_database' ]

You can also use the following to attach your file to the email instead of within the email:

[ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]

http://msdn.microsoft.com/en-us/library/ms190307.aspx

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455

jbalbo SSCertifiable Points: 7580 More actions · Answer 3

Thanks for getting back ,

so something like this ?

EXEC msdb.dbo.sp_send_dbmail

@recipients = 'me@email.org',

@query = 'select FName as first,

LName as last

from client where LName = ''wilson'' ''

for xml path(''Referral'')

ROOT(''CLUB'')',

@execute_query_database = 'execute_query_database', <-- NOT SURE what goes here ???

@attach_query_result_as_file = 1,

@body = 'The stored procedure finished successfully.',

@subject = 'Automated Success Message'

@attach_query_result_as_file = 1 makes it an attachment, what if I wanted to name it?

Thanks

Joe

Calibear Ten Centuries Points: 1377 More actions · Answer 4

jbalbo (5/6/2013)
Thanks for getting back ,
so something like this ?
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'me@email.org',
@query = 'select FName as first,
LName as last
from client where LName = ''wilson'' ''
for xml path(''Referral'')
ROOT(''CLUB'')',
@execute_query_database = 'execute_query_database', <-- NOT SURE what goes here ???
@attach_query_result_as_file = 1,
@body = 'The stored procedure finished successfully.',
@subject = 'Automated Success Message'
@attach_query_result_as_file = 1 makes it an attachment, what if I wanted to name it?
Thanks
Joe

You will need to specify @profile_name, which is the mail profile set up for database mail for these mail notifications. @execute_query_database is the database the query should be run on. Use @query_attachment_filename for the file name you want to use.

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455

Lowell SSC Guru Points: 323504 More actions · Answer 5

sp_send_dbmail runs in the msdb database...so either your query needs to explicitly state teh database name, or the @execute_query_database must be supplied.

example :

@query = 'select FName as first,
LName as last
from PRODUCTION.dbo.client where LName = ''wilson'' ''
for xml path(''Referral'')
ROOT(''CLUB'')',

or

@query = 'select FName as first,
LName as last
from client where LName = ''wilson'' ''
for xml path(''Referral'')
ROOT(''CLUB'')',
@execute_query_database = 'PRODUCTION',
something like this might help a little:
EXEC msdb.dbo.sp_send_dbmail
@profile_name='GMail Profile',
@recipients = 'me@email.org',
@query = 'select FName as first,
LName as last
from PRODUCTION.dbo.client where LName = ''wilson'' ''
for xml path(''Referral'')
ROOT(''CLUB'')',
@body = 'The stored procedure finished successfully.',
@subject = 'Automated Success Message'
@query_result_header = 1,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'qry.txt',

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jbalbo SSCertifiable Points: 7580 More actions · Answer 6

Thanks for the info so now I have this

EXEC msdb.dbo.sp_send_dbmail

@profile_name='DBMail',

@recipients = 'mygmail@gmail.com',

@query = 'select FName as first,

LName as last

from Test.dbo.client where LName = ''wilson'' ''

for xml path(''Referral'')

ROOT(''CLUB'')',

@body = 'The stored procedure finished successfully.',

@subject = 'Automated Success Message',

@query_result_header = 1,

@exclude_query_output = 1,

@append_query_error = 1,

@attach_query_result_as_file = 1,

@query_attachment_filename = 'qry.xml'

But still getting an error of:

db01_ssagent. profile name is not valid [SQLSTATE 42000] (Error 14607

Account name is DBMail

Sent a test and received it??

Thanks yet again!!

Calibear Ten Centuries Points: 1377 More actions · Answer 7

Error online shows incorrect profile name. Check to make sure your profile name is correct:

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/265151f8-1ea5-43fb-bb8d-1092bc104a67/

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455

jbalbo SSCertifiable Points: 7580 More actions · Answer 8

Now that I can spell correctly, it is running... Thank you

Of Course I have another problems with the out put but will start a new thread..

Thanks Again