attach data to email - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

attach data to email

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-01-11 : 10:37:25

How is it possible to place the contents of a select query i.e.

select
field1, field2
from tblMain

into a variable such as @myRecords and then place these records into the contents of the email

This is what I am trying to do but not sure how to attached the contents of the data as you see here.
Any suggestions please?
Thanks

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Email',
@recipients = 'myname@company.com' ,
@body = 'Below listed data have been changed.\r\n' + @myRecords,
@subject = 'Invalid data. Details below'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 11:10:39

you can directly use @query argument of sp_send_dbmail for passing the query and getting results onto mail. no need of intermediate mail

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-01-11 : 11:29:06

DO you mean like the following?
It seems by single quotes are incorrect though.
...,
@query = 'SELECT
Cusip,[Description]
from
tblMain
where
ValueDate= '' + @IndexDate + ''
and ReturnSeq=1 and IndexID=' + @IndexID + '' + and IndexID =' + @IndexID + '

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 11:34:31

you cant form query dynamically like this
for this you need to use sp_send_dbmail inside dynamic sql

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-01-11 : 11:50:40

I get incorrect syntax near '+'
Do y ou see why please?

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Email',
@recipients = 'myname@company.com' ,
@body = 'Below listed have been changed.\r\n' ,
@subject = 'Invalid data. Details below',
@query = 'SELECT
field1,[Description]
FROM
tblMain
WHERE
MyDate = '' + @IndexDate + ''
--error is on below line
and qes=1 and IndexID=' + @IndexID + '

--error is on above line and field1 not in (select field1 from tblMain
where
MyDate='' + @MaxmyDate + ''
and IndexID =' + @IndexID + ' and qes=0)',
@attach_query_result_as_file =0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 12:35:30

I repeat
you cant pass dynamic query like this inside sp_send_dbmail. you need to use dynamic sql

something like


DECLARE @Sql varchar(3000)

SET @Sql='EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''Email'',
@recipients = ''myname@company.com'' ,
@body = ''Below listed have been changed.\r\n'' ,
@subject = ''Invalid data. Details below'',
@query = ''SELECT 
field1,[Description]
FROM 
tblMain
WHERE
MyDate = ' + @IndexDate + '
--error is on below line
and qes=1 and IndexID=' + @IndexID + ''''

EXEC (@Sql)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-01-11 : 15:36:40

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-12 : 00:17:27

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources