sp_send_dbmail passing parameter to @query
-
I am trying to pass a parameter into the @query argument of sp_send_dbmail for my first time. Here is some code as a simple example:
--drop table mydb.dbo.tmptable
create table mydb.dbo.tmptable (idno int, namefld varchar(20), color varchar(20))
insert into mydb.dbo.tmptable
select 1, 'elephant', 'gray' union all
select 2, 'bear', 'black' union all
select 3, 'cat', 'gray' union all
select 4, 'dog', 'brown' union all
select 5, 'rabbit', 'gray'
declare @fltr varchar(20), @Receipientlist varchar(8000), @subj varchar(100), @body varchar(1000), @filenm varchar(30), @qry nvarchar(max)
set @fltr = 'gray'
set @Receipientlist = 'test@email.com'
set @subj = 'subj param'
set @body = 'body param'
set @filenm = 'filenameparam.csv'
set @qry = 'select * from mydb.dbo.tmptable where color = ' + @fltr
exec msdb.dbo.sp_send_dbmail
@profile_name = 'SYSALERTS',
@body = @body,
@query = @qry,
@recipients = @Receipientlist,
@subject = @subj,
@attach_query_result_as_file = 1,
@query_result_no_padding = 1,
@query_attachment_filename = @filenm,
@query_result_separator = '' -- tabFirst of all, do I have the @qry set argument correct?
Second of all, when I execute this in SSMS, I get the following error: "Failed to initialize sqlcmd library with error number -2147467259."
When I research this error, I'm reading about possible permissions problems with a service account. But, I'm sorry, I'm not following the suggested solutions very well. I was hoping someone could point me in the right direction.
When I comment out the where clause of the @qry, it runs just fine.
-
have you used sp_send_dbmail before? i swear i've seen that error when the .net library 3.5 was not installed on the server, but dbmail depends on it:
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! -
Yessir, I have used sp_send_dbmail quite a lot on this server. This is the first time for passing a parameter to @query, and the first time I've encountered this error message.
-
Your select statement needs quotes wrapped around the @fltr parameter. Should look like this.
set @qry = 'select * from mydb.dbo.tmptable where color = ''' + @fltr + ''''
When you are using dynamic sql, print out your query and run it independently. In this case it will generate a real error for you. Also, you can skip all that and fire up sql Profiler to collect error messages too. Just make sure you include "User Error Message" under "Errors and Warnings". Saves me from developers who use ORMs and have no clue what queries their apps are building.
-
Thank you! My problem is solved. I installed .net library 3.5 on the server. Then I noticed the latest reply and fixed my select statement to include the quotes. Not sure which, or if both, fixed it. But it works now! Thanks!!
- askcoffman wrote:
Your select statement needs quotes wrapped around the @fltr parameter. Should look like this.
set @qry = 'select * from mydb.dbo.tmptable where color = ''' + @fltr + ''''
askcoffman: What would it look like if I wanted to add a second filter parameter to the query? All the quotations have me a little confused so just a bit more guidance should get me on my way. For example, what if I wanted to say this?
select * from mydb.dbo.tmptable where color = @fltr and idno > @fltr2
-
Sorry, I figured it out. Those quotes aren't THAT confusing. Ha
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply