SQL send_dbmail Question

mcdchr
mcdchr used Ask the Experts™
on
I am trying to use the send_dbmail function to send the results of query. I can create the function fine and it works. I can create the query fine and it works. However, when I try to add the query to the DB Mail function I am getting this error:

>>>>>>>>>>>
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'True'.
>>>>>>>>>>>

I have tested the query and it works fine, so I am not sure what I am doing wrong with the db_mail function. Any help woudl be greatly appreciated!

If I run this it is fine (there are some more things to add.. but right now I am just focused on the query)
 
EXEC msdb.dbo.sp_send_dbmail 
@Query = '',
@profile_name = 'SP Mail', 
@recipients = 'xxx@xxxxxxxx.com, 
@subject = 'MSP No Images report',
@body = 'This is the no images report',
@body_format = 'Text',
 
When I add my query I get the error.
 
EXEC msdb.dbo.sp_send_dbmail 
@Query = 'SELECT     MSP_Enterprise_Client.name, Client_Order.order_no, Client_Order.order_id, CONVERT(varchar, Client_Order.order_date, 101) AS OrderDate
FROM         Client_Order INNER JOIN
                      MSP_Client ON Client_Order.msp_client_id = MSP_Client.msp_client_id INNER JOIN
                      MSP_Enterprise_Client ON MSP_Client.msp_enterprise_client_id = MSP_Enterprise_Client.msp_enterprise_client_id LEFT OUTER JOIN
                      Item INNER JOIN
                      Client_Order_Detail ON Item.item_id = Client_Order_Detail.item_id ON Client_Order.order_id = Client_Order_Detail.order_id
WHERE     (Client_Order.Order_No <> '') AND (Client_Order_Detail.image_name = '') AND (Item.is_package <> 'True') AND (Item.item_description <> 'Additional Pose') AND (Item.item_description <> 'Additional Poses')AND (item.item_description <> 'Pose Change Fee') AND (item.supports_image_selection <> 'False') AND (DATEPART(yy, Client_Order.order_date) 
                      = DATEPART(yy, GETDATE())) AND (DATEPART(dy, Client_Order.order_date) >= DATEPART(dy, DATEADD(dy, -1, GETDATE())))
GROUP BY Client_Order.order_id, CONVERT(varchar, Client_Order.order_date, 101), MSP_Enterprise_Client.name, Client_Order.order_no
ORDER BY MSP_Enterprise_Client.name, Client_Order.order_no',
@profile_name = 'SP Mail', 
@recipients = 'xxx@xxxxxxx.com', 
@subject = 'MSP No Images report',
@body = 'This is the no images report',
@body_format = 'Text',
@File_Attachments = 'C:\SQLData\noimage.csv'

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
look at this:
If I run this it is fine (there are some more things to add.. but right now I am just focused on the query)
 
EXEC msdb.dbo.sp_send_dbmail 
@Query = '',
@profile_name = 'SP Mail', 
@recipients = 'xxx@xxxxxxxx.com', 
@subject = 'MSP No Images report',
@body = 'This is the no images report',
@body_format = 'Text',
 
When I add my query I get the error.
 
EXEC msdb.dbo.sp_send_dbmail 
@Query = 'SELECT     MSP_Enterprise_Client.name, Client_Order.order_no, Client_Order.order_id, CONVERT(varchar, Client_Order.order_date, 101) 
AS OrderDate
FROM         Client_Order INNER JOIN
                      MSP_Client ON Client_Order.msp_client_id = MSP_Client.msp_client_id INNER JOIN
                      MSP_Enterprise_Client ON MSP_Client.msp_enterprise_client_id = MSP_Enterprise_Client.msp_enterprise_client_id LEFT OUTER JOIN
                      Item INNER JOIN
                      Client_Order_Detail ON Item.item_id = Client_Order_Detail.item_id ON Client_Order.order_id = Client_Order_Detail.order_id
WHERE     (Client_Order.Order_No <> '') AND (Client_Order_Detail.image_name = '') AND (Item.is_package <> ''True'') AND 
(Item.item_description <> ''Additional Pose'') AND (Item.item_description <> ''Additional Poses'')AND (item.item_description <> ''Pose Change Fee'') 
AND (item.supports_image_selection <> ''False'') AND (DATEPART(yy, Client_Order.order_date) 
                      = DATEPART(yy, GETDATE())) AND (DATEPART(dy, Client_Order.order_date) >= DATEPART(dy, DATEADD(dy, -1, GETDATE())))
GROUP BY Client_Order.order_id, CONVERT(varchar, Client_Order.order_date, 101), MSP_Enterprise_Client.name, Client_Order.order_no
ORDER BY MSP_Enterprise_Client.name, Client_Order.order_no',
@profile_name = 'SP Mail', 
@recipients = 'xxx@xxxxxxx.com', 
@subject = 'MSP No Images report',
@body = 'This is the no images report',
@body_format = 'Text',
@File_Attachments = 'C:\SQLData\noimage.csv'

Open in new window

BTW, don't forget to remove 1st and 11th line as it is not part of the code

Author

Commented:
Thanks for being so quick!

I have made the changes (attached). Now I get the following error:

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476
Query execution failed: Msg 208, Level 16, State 1, Server SNIPEHUNT, Line 1
Invalid object name 'Client_Order'.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

I noticed from some other posting that I should put databasename.dbo in front of my table names in the FROM. I took your changes and added my other changes and now it seems to work (it is attached). Thanks for the help!

EXEC msdb.dbo.sp_send_dbmail 
@Query = 'SELECT     MSP_Enterprise_Client.name, Client_Order.order_no, Client_Order.order_id, CONVERT(varchar, Client_Order.order_date, 101) 
AS OrderDate
FROM         MyStudioPlus.dbo.Client_Order INNER JOIN
                      MyStudioPlus.dbo.MSP_Client ON Client_Order.msp_client_id = MSP_Client.msp_client_id INNER JOIN
                      MyStudioPlus.dbo.MSP_Enterprise_Client ON MSP_Client.msp_enterprise_client_id = MSP_Enterprise_Client.msp_enterprise_client_id LEFT OUTER JOIN
                      MyStudioPlus.dbo.Item INNER JOIN
                      MyStudioPlus.dbo.Client_Order_Detail ON Item.item_id = Client_Order_Detail.item_id ON Client_Order.order_id = Client_Order_Detail.order_id
WHERE     (Client_Order.Order_No <> '') AND (Client_Order_Detail.image_name = '') AND (Item.is_package <> "True") AND 
(Item.item_description <> "Additional Pose") AND (Item.item_description <> "Additional Poses")AND (item.item_description <> "Pose Change Fee") 
AND (item.supports_image_selection <> "False") AND (DATEPART(yy, Client_Order.order_date) 
                      = DATEPART(yy, GETDATE())) AND (DATEPART(dy, Client_Order.order_date) >= DATEPART(dy, DATEADD(dy, -1, GETDATE())))
GROUP BY Client_Order.order_id, CONVERT(varchar, Client_Order.order_date, 101), MSP_Enterprise_Client.name, Client_Order.order_no
ORDER BY MSP_Enterprise_Client.name, Client_Order.order_no',
@profile_name = 'SP Mail', 
@recipients = 'xxx@xxxxxxx.com', 
@subject = 'MSP No Images report',
@body = 'This is the no images report',
@body_format = 'Text',
@File_Attachments = 'C:\SQLData\noimage.csv'

Open in new window

glad to help!!!

Ritesh Shah

www.SQLHub.com

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial