May 7, 2008 at 2:12 pm
I know the my query works standalone but it won't run when I put it in the @query section of an email job. It returns:
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '
AND p21_view_invoice_hdr.disputed_flag = '.
Here's my code:
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='Alerts',
@recipients=N'bjohnson@cummins-wagner.com',
@body='Message Body',
@subject ='Message Subject',
@query = 'select
p21_invoice_amt_remaining_view.invoice_no,
p21_invoice_amt_remaining_view.amt_remaining_frominv,
p21_view_invoice_hdr.customer_id,
p21_customer_view.customer_name,
p21_invoice_amt_remaining_view.order_no,
p21_view_invoice_hdr.invoice_date
from
p21_invoice_amt_remaining_view
left outer join p21_view_invoice_hdr on p21_invoice_amt_remaining_view.invoice_no = p21_view_invoice_hdr.invoice_no
left outer join p21_customer_view on p21_view_invoice_hdr.customer_id = p21_customer_view.customer_id
where
p21_view_invoice_hdr.paid_in_full_flag = 'N'
AND p21_view_invoice_hdr.disputed_flag = 'N'
AND p21_invoice_amt_remaining_view.amt_remaining_frominv > '4999.99'
AND p21_view_invoice_hdr.invoice_date < (select dateadd(d,-30,CURRENT_TIMESTAMP))
ORDER BY p21_view_invoice_hdr.invoice_date'
May 7, 2008 at 2:19 pm
Because your query is within a string you need to use 2 single-quotes in order to have one show in your string. So you need something like this:
@query = 'Select * from sys.databases where name = ''msdb'' and compatibility_level = 90'
msdb is surrounded by 2 single quotes.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 7, 2008 at 2:29 pm
So do you mean that the where section should be:
where
p21_view_invoice_hdr.paid_in_full_flag = ''N''
AND p21_view_invoice_hdr.disputed_flag = ''N''
AND p21_invoice_amt_remaining_view.amt_remaining_frominv > ''4999.99''
AND p21_view_invoice_hdr.invoice_date < (select dateadd(d,-30,CURRENT_TIMESTAMP))
Where anything with single quotes needs to be 2 single quotes? I noted in your example that you didn't place 90 in single quotes. I tried running this and it didn't like it.
May 7, 2008 at 2:41 pm
Your where clause looks fine to me now, except that if p21_invoice_amt_remaining_view.amt_remaining_frominv is numeric you do not need the quotes .
When you say it did not like it, did you get an error?
Try testing the query in SSMS by doing:
[font="Courier New"]DECLARE @query VARCHAR(MAX)
SET @query = 'select
p21_invoice_amt_remaining_view.invoice_no,
p21_invoice_amt_remaining_view.amt_remaining_frominv,
p21_view_invoice_hdr.customer_id,
p21_customer_view.customer_name,
p21_invoice_amt_remaining_view.order_no,
p21_view_invoice_hdr.invoice_date
from
p21_invoice_amt_remaining_view left outer join
p21_view_invoice_hdr on
p21_invoice_amt_remaining_view.invoice_no = p21_view_invoice_hdr.invoice_no left outer join
p21_customer_view on
p21_view_invoice_hdr.customer_id = p21_customer_view.customer_id
where
p21_view_invoice_hdr.paid_in_full_flag = ''N'' AND
p21_view_invoice_hdr.disputed_flag = ''N'' AND
p21_invoice_amt_remaining_view.amt_remaining_frominv > ''4999.99'' AND
p21_view_invoice_hdr.invoice_date < (select dateadd(d,-30,CURRENT_TIMESTAMP))'
EXEC(@query)[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 12, 2008 at 9:18 am
That query worked - so if we set it up that way, how do I handle the @query part of the execution of the email?
May 12, 2008 at 9:22 am
I'm now attempting:
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='Alerts',
@recipients=N'bjohnson@cummins-wagner.com',
@body='Message Body',
@subject ='Message Subject',
@query = 'select
p21_invoice_amt_remaining_view.invoice_no,
p21_invoice_amt_remaining_view.amt_remaining_frominv,
p21_view_invoice_hdr.customer_id,
p21_customer_view.customer_name,
p21_invoice_amt_remaining_view.order_no,
p21_view_invoice_hdr.invoice_date
from
p21_invoice_amt_remaining_view left outer join
p21_view_invoice_hdr on
p21_invoice_amt_remaining_view.invoice_no = p21_view_invoice_hdr.invoice_no left outer join
p21_customer_view on p21_view_invoice_hdr.customer_id = p21_customer_view.customer_id
where
p21_view_invoice_hdr.paid_in_full_flag = ''N'' AND
p21_view_invoice_hdr.disputed_flag = ''N'' AND
p21_invoice_amt_remaining_view.amt_remaining_frominv > ''4999.99'' AND
p21_view_invoice_hdr.invoice_date < (select dateadd(d,-30,CURRENT_TIMESTAMP))';
and am getting this as an error:
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 357
Query execution failed: Msg 208, Level 16, State 1, Server MD-SQL1, Line 1
Invalid object name 'p21_invoice_amt_remaining_view'.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
May 12, 2008 at 9:36 am
Because the query is being executed in the context of DBMail it is executed within the master database so you need to fully qualify your object names in the query, dbname.schema.object.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 18, 2009 at 11:29 am
Hi, i have a similar problem, my difference is that i am using a temp table
i have declared and insert values into this temp table in the same stored procedure.
,@query ='select * from #temp'
,@attach_query_result_as_file = 1 ;
but i keep getting this error:
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 495
Query execution failed: ?Msg 208, Level 16, State 1, Server ****, Line 1
Invalid object name '#temp'.
Any ideas?
August 18, 2009 at 11:34 am
the scope of your temp table probably isn't sufficient.
you haven't really presented much information, but i'd assume what you're doing is something like this:
CREATE TABLE #Temp
(
stuff
)
INSERT INTO #Temp(stuff)
SELECT values
exec sp_send_db_mail
,@query ='select * from #temp'
which won't work, because #temp is outside the scope of sp_send_db_mail.
you'll probably have to create #temp within the @query parameter, which kinda invalidates your purposes.
not sure if there's a way to create a temp table with scope sufficient for what you're trying to accomplish.
August 18, 2009 at 11:42 am
yes that's exactly what i am doing, ok i will try to find another way to get the values without using a temp table. Thank you.
August 18, 2009 at 1:02 pm
It might work with a global temp table. You define a global temp table using two octothorpes instead of just one
Create Table ##temp
The global temp table can be used from multiple connections and remains valid until all connections that are using it are closed or until it is dropped.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 18, 2009 at 2:27 pm
yes it does work with ##temp!! thank you!!
August 19, 2009 at 6:03 am
drew.allen (8/18/2009)
It might work with a global temp table. You define a global temp table using two octothorpes instead of just oneCreate Table ##temp
The global temp table can be used from multiple connections and remains valid until all connections that are using it are closed or until it is dropped.
Drew
what sort of conflicts could occur with global temp tables? is it a single instance of the table for all connections, or does each connection get it's own table? also, if it's each connection, if you execute the procedure twice, does it cause a conflict in existence of the table? what if you execute it twice without waiting for it to finish the first time?
August 19, 2009 at 7:15 am
You need to be wary of global temp tables. Is there any possibility of multiple requests occurring concurrently? If there is one will fail on the create table statement or if you have If exists Drop table then the first will fail. Global temp tables are usually a bad idea.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 20, 2009 at 12:41 pm
ok thank you i will keep that in mind. 🙂
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply