asked
SQL DB Mail @Query Issue
Hi, I am trying to run some SQL Code within a db send mail heading so that the results are emailed as CSV. I am having nightmares getting this working, for some reason it doesn't like my query after the @Query = command, it just says the syntax is incorrect. It is quite a long piece of code split into different sections. The first part of the code is as below: EXEC msdb.dbo.sp_send_dbmail @profile_name = 'XX', @recipients = 'XX', @subject = 'Test', @attach_query_result_as_file = '1', @query_attachment_filename = 'test.csv', @query = use DB go declare @DefTime int =2 Declare @Brandid Varchar(20) Declare @fromdate datetime Declare @todate datetime DECLARE @HeaderIdentifier VARCHAR(20) = RIGHT(REPLICATE(' ', 20) + 'HEADER', 20) DECLARE @SourceCode VARCHAR(3) = '000' --681 If (SELECT COUNT(*) FROM BrandConfig WHERE Bco_ConfigID = 4461 AND (@BrandID IS NULL OR Bco_BrandID = @BrandID)) <> 0 BEGIN Set @SourceCode = LEFT(ISNULL(NULLIF(LTRIM((SELECT TOP 1 Bco_ConfigValue FROM BrandConfig WHERE Bco_ConfigID = 4461 AND (@BrandID IS NULL OR Bco_BrandID = @BrandID))), '') ,'000') + REPLICATE('0', 3), 3) END DECLARE @DateCreated VARCHAR(8) = RIGHT(REPLICATE('0', , @append_query_error = 1
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
@query is a string data type so you'll need to enclose it in single quotes. Any single quotes in the query need to be doubled up. For example: @query = 'DECLARE @SourceCode VARCHAR(3) = ''000'''. You also need to look at the end of your query. You haven't finished that RIGHT(REPLICATE( statement. I would also get rid of the GO after the USE DB. It's not needed. I can't tell without testing but it might cause a problem so it's best to get rid of it.
Hi David, Thanks a lot for this, that is helpful. What I am not sure about is at what point do I start and end the single quotes, so after @query = 'DECLARE @SourceCode VARCHAR(3) = "000"". Do I then need to put quotes round the next line? or statement? Unfortunately I am more familiar with the administration side of SQL rather than coding! Yes I only put in 1 part of the SQL Script, as it is quite long. I can message you the script if you want? Matt
Hi Matt Sorry, that wasn't the clearest example now that I look at it again! Treat the whole query as a single string. So take your query, add a single quote at the start and another at the end, abd then replace all the other single quotes inside with two single quotes. Test it first in a query window and make sure it runs. Then change that into a string that contains the query and test it again by executing it with sp_executesql. Here's how that looks with the first few lines of your query: declare @sql nvarchar(max); set @sql = ' use DB declare @DefTime int =2 Declare @Brandid Varchar(20) Declare @fromdate datetime Declare @todate datetime DECLARE @HeaderIdentifier VARCHAR(20) = RIGHT(REPLICATE('' '', 20) + ''HEADER'', 20) '; exec sp_executesql @sql; Once it's working, use the query in your code like this: declare @sql nvarchar(max); set @sql = '...'; exec msdb.dbo.sp_send_dbmail @profile_name = 'XX' , @recipients = 'XX' , @subject = 'Test' , @attach_query_result_as_file = '1' , @query_attachment_filename = 'test.csv' , @query = @sql , @append_query_error = 1;
20 People are following this question.
