sp_send_dbmail - 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

General SQL Server Forums

New to SQL Server Programming

sp_send_dbmail

Author

Topic

tejicen
Starting Member

17 Posts

Posted - 2009-07-14 : 07:46:30

Hi,

Is it possible in any way use a varible in sp_send_dbmail

example:

declare @inValue varchar(255)
set @inValue = 'this is a test'
begin
exec msdb.dbo.sp_send_dbmail
@profile_name = 'info',
@recipients = 'info@mobill.se',
@importance ='High',
@query = '@inVarde',
@subject = 'TEST!',
@query_attachment_filename ='Results.txt'
end

kind regards, tejicen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-14 : 08:40:59

declare @inValue varchar(255),@sql varchar(8000)
set @inValue = 'this is a test'
begin
set @sql='
exec msdb.dbo.sp_send_dbmail
@profile_name = ''info'',
@recipients = ''info@mobill.se'',
@importance =''High'',
@query = '''+@inValue+''',
@subject = ''TEST!'',
@query_attachment_filename =''Results.txt'''
exec(@sql)
end

Madhivanan

Failing to plan is Planning to fail

tejicen
Starting Member

17 Posts

Posted - 2009-07-14 : 08:58:07

I can not get it right, should it be double apostrophe every where?

//tejicen

tejicen
Starting Member

17 Posts

Posted - 2009-07-14 : 09:06:07

I get this message when I execute the sp above.

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476
Query execution failed: Msg 156, Level 15, State 1, Server MOBILLSRV06, Line 1
Incorrect syntax near the keyword 'is'.

//tejicen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-14 : 09:12:52

declare @inValue varchar(255),@sql varchar(8000)
set @inValue = 'this is a test'
begin
set @sql='
exec msdb.dbo.sp_send_dbmail
@profile_name = ''info'',
@recipients = ''info@mobill.se'',
@importance =''High'',
@query = '''+@inVarde+''',
@subject = ''TEST!'',
@query_attachment_filename =''Results.txt'''
exec(@sql)
end

Madhivanan

Failing to plan is Planning to fail

tejicen
Starting Member

17 Posts

Posted - 2009-07-14 : 09:27:10

sorry, but can not get this right.

regards, tejicen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-14 : 09:36:40

Use

Print @sql

and see if it displays the code correctly

Madhivanan

Failing to plan is Planning to fail

tejicen
Starting Member

17 Posts

Posted - 2009-07-14 : 09:43:13

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476
Query execution failed: Msg 156, Level 15, State 1, Server MOBILLSRV06, Line 1
Incorrect syntax near the keyword 'is'.

exec msdb.dbo.sp_send_dbmail
@profile_name = 'info',
@recipients = 'info@mobill.se',
@importance ='High',
@query = 'this is a test',
@subject = 'TEST!',
@query_attachment_filename ='Results.txt'

can't understand why it gets rong?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-14 : 10:01:55

Note that the value of @query is 'this is a test'
What are you doing with that?

Madhivanan

Failing to plan is Planning to fail

tejicen
Starting Member

17 Posts

Posted - 2009-07-14 : 10:23:59

What I want is to put the varible @inValue in the @query and when it's executeed it will appear in the email info@mobill.se.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-14 : 10:49:22

Then, this should work

declare @inValue varchar(255),@sql varchar(8000)
set @inValue = 'this is a test'
begin
set @sql='
exec msdb.dbo.sp_send_dbmail
@profile_name = ''info'',
@recipients = ''info@mobill.se'',
@importance =''High'',
@query = '''+@inValue+''',
@subject = ''TEST!'',
@query_attachment_filename =''Results.txt'''
exec(@sql)
end

Madhivanan

Failing to plan is Planning to fail

tejicen
Starting Member

17 Posts

Posted - 2009-07-14 : 11:00:24

nope, did not work.

:(

tejicen
Starting Member

17 Posts

Posted - 2009-07-14 : 11:51:07

Can any guru help me,

I want to use sp_send_dbmail.

What I want is to put a string in to a variable and that string should be shown in the mail in this case info@mobill.se. Bur when I execute the sql statement below in gives me a message like this:

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476
Query execution failed: Msg 156, Level 15, State 1, Server MOBILLSRV06, Line 1
Incorrect syntax near the keyword 'is'.

declare @inValue varchar(255),@sql varchar(8000)
set @inValue = 'this is a test'
begin
set @sql='
exec msdb.dbo.sp_send_dbmail
@profile_name = ''info'',
@recipients = ''info@mobill.se'',
@importance =''High'',
@query = '''+@inValue+''',
@subject = ''TEST!'',
@query_attachment_filename =''Results.txt'''
exec(@sql)
end

please can anyone help me with this, tejicen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources