CODEWITHSUNDEEP
sqlsql-serveremail
Duluth
Duluth

Reputation: 65

SQL send query to email via msdb.dbo.sp_send_dbmail: single quotes error.

Within the below's @query field, I specify my query by using ' at the start and end. The block below works. 

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SendCallCenter',
    @recipients = '[email protected]',
    @subject = 'Previous_No_Shows',
    @query = 
    N'Select
        [NETEWLifePRODDBV1.8.7].[dbo].[tblClient].[fldFirstName],[fldLastName],[fldLocation],[fldJHEDId], 
        [NETEWLifePRODDBV1.8.7].[dbo].[tblClientSchedule].[fldDuration],[fldDate]
    FROM 
        [NETEWLifePRODDBV1.8.7].[dbo].[tblClientSchedule] Inner Join [NETEWLifePRODDBV1.8.7].[dbo].[tblClient]
    on
        [NETEWLifePRODDBV1.8.7].[dbo].[tblClientSchedule].[fldClientId]= [NETEWLifePRODDBV1.8.7].[dbo].[tblClient].[fldClientId]
   WHERE
        [fldApptOutcomeId] = 4;',
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'Data.csv'

However when I introduce two new datetime parameters in WHERE, the ' within these lines interrupts the string at the numerical values.

enter image description here

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '17530101'.

What can I use to keep the entire query as a continuous string?

I used this tutorial

Upvotes: 1

Views: 781

Answers (1)

S3S
S3S

Reputation: 25112

You have to escape your single quotes.

`..dd,''17530101'',getdate()....`

When you do it correctly, you won't see the dates in blue. Another great debugging tip is to set your query to a parameter

declare @sql varchar(max) = 'select ...'

And then you can print it out, to make sure your concatenations, converts, etc worked.

print @sql

For example:

declare @sql varchar(max) = 'select datediff(dd,''17530101'',getdate())'
print @sql

You can also use the @sql in your proc execution by setting @query = @sql once you know that query string is correct.

Upvotes: 2

Related Questions