Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

? on running a sp in dbmail Expand / Collapse
Author
Message
Posted Monday, May 6, 2013 11:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:55 PM
Points: 301, Visits: 500
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
Post #1449825
Posted Monday, May 6, 2013 12:43 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 8:47 AM
Points: 839, Visits: 1,198
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/
Post #1449840
Posted Monday, May 6, 2013 2:57 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 8, 2013 7:16 PM
Points: 221, Visits: 132
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/
You can also follow my twitter account to get daily updates: @BLantz2455
Post #1449892
Posted Monday, May 6, 2013 3:06 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:55 PM
Points: 301, Visits: 500
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


Post #1449896
Posted Monday, May 6, 2013 3:14 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 8, 2013 7:16 PM
Points: 221, Visits: 132
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/
You can also follow my twitter account to get daily updates: @BLantz2455
Post #1449902
Posted Monday, May 6, 2013 3:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:35 PM
Points: 12,962, Visits: 32,498
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1449903
Posted Monday, May 6, 2013 3:48 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:55 PM
Points: 301, Visits: 500
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!!
Post #1449914
Posted Monday, May 6, 2013 4:12 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 8, 2013 7:16 PM
Points: 221, Visits: 132
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/
You can also follow my twitter account to get daily updates: @BLantz2455
Post #1449923
Posted Tuesday, May 7, 2013 8:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:55 PM
Points: 301, Visits: 500
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
Post #1450167
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse