Matt
Matt

Reputation: 816

How can I attach a file name containing a / with sp_send_mail?

I have a query that sends an email to me each month for the previous month's data that we receive. I'd like to include the last month and current year in the filename. I got the month and year to work but when I try to show them in the filename, SQL tries to escape the line because I'm using a forward slash to separate month and year.

For example, the code below returns "2021.csv" as the filename. How do I use a forward slash in the filename?

SELECT @query_attachment_filename = 
  N'SITLAMonthlyEntityReport'
 +N' '
 +CAST(DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())) AS NVARCHAR(12))
 +N'/' -- This is the problem area.
 +CAST(YEAR(GETDATE()) AS NVARCHAR(4))
 +N'.csv';

I've tried using all of these methods without luck:

  1. N''/'' -- I get, "Operand data type nvarchar is invalid for divide operator".
  2. ''/'' -- I get, "Operand data type varchar is invalid for divide operator".
  3. ['/']
  4. / -- I get, "Incorrect syntax near '/'".

I'd like to see this as an example filename: "SITLAMonthlyEntityReport 9/2021".

EDIT: I was asked for the entire query that included the snippet from above. Below is the full query, minus email information.

BEGIN
    DECLARE @message NVARCHAR(1000);
    DECLARE @subject NVARCHAR(100);
    DECLARE @query NVARCHAR(MAX);
    DECLARE @tab NCHAR(1) = NCHAR(9);
    DECLARE @query_attachment_filename NVARCHAR(520);
    DECLARE @CRLF NCHAR(2);

    SELECT @CRLF = NCHAR(13)+NCHAR(10)
    SELECT @subject = 'Well Information Report'
    SELECT @message = N'Dear ###, '
                      +@CRLF+ N''
                      +@CRLF+ N'Please refer to the attached spread sheet for the results of last month''s information report ('
                      +CAST(DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())) AS NVARCHAR(12))
                      +N'/'
                      +CAST(YEAR(GETDATE()) AS NVARCHAR(4))
                      +N').'
                      +@CRLF+ N''
                      +@CRLF+ N'If there aren''t any results in the attached CSV, the query didn''t find any information for the last month,'
                      +@CRLF+ N''
                      +@CRLF+ N'Thanks,'
                      +@CRLF+ N'###';

    SELECT @query =
    '
        SET NOCOUNT ON;
        --DECLARE @SearchYear AS NVARCHAR(4) = YEAR(GETDATE())
        --DECLARE @SearchMonth AS NVARCHAR(9) = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE()))
        DECLARE @SearchYear AS NVARCHAR(4) = 2021
        DECLARE @SearchMonth AS NVARCHAR(9) = 7

        SELECT
            API14,
            [Entity Number],
            [First Prod Date],
            [Spacing Rule],
            TPI AS [Top Producing Interval Location],
            BH AS [Bottom Hole Location],
            [Well History Comments],
            [Well History Modify Date] AS [Last Modified Date]
        FROM
        (
        SELECT
            dbo.BuildAPI14(Well.WellID, Construct.SideTrack, Construct.Completion) AS [API14],
            CAST(ConstructDate.EventDate AS DATE) AS [First Prod Date],
            Loc.LocType AS [Location Type],
            CONCAT(''Township '',LocExt.Township,LocExt.TownshipDir,'' '',''Range '',LocExt.Range,LocExt.RangeDir,'' Section '',LocExt.Sec,'' '',RefCounty.CountyName,'' County'') AS ''Location'',
            tblAPDTracker.SpacingRule AS [Spacing Rule],
            Lease.Number AS [Entity Number],
            WellHistory.WHComments AS [Well History Comments],
            WellHistory.ModifyDate AS [Well History Modify Date]
        FROM dbo.Well
            LEFT JOIN dbo.tblAPDTracker ON LEFT(tblAPDTracker.APINO,10) = Well.WellID
            LEFT JOIN dbo.Construct ON Construct.WellKey = Well.PKey
            LEFT JOIN dbo.ConstructReservoir ON ConstructReservoir.ConstructKey = Construct.PKey
            LEFT JOIN dbo.Lease ON Lease.Pkey = ConstructReservoir.LeaseKey
            LEFT JOIN dbo.WellHistory ON WellHistory.WellKey = Construct.WellKey
            LEFT JOIN dbo.ConstructDate ON ConstructDate.ConstructKey = Construct.PKey AND ConstructDate.Event = ''FirstProduction''
            LEFT JOIN dbo.Loc ON loc.ConstructKey = Construct.PKey AND Loc.LocType IN (''BH'',''TPI'')
            LEFT JOIN dbo.LocExt ON LocExt.LocKey = Loc.PKey
            LEFT JOIN dbo.RefCounty ON RefCounty.PKey = LocExt.County
        WHERE
                WellHistory.WorkType = ''ENTITY''
            AND WellHistory.ModifyUser = ''UTAH\###''
            AND YEAR(WellHistory.ModifyDate) = @SearchYear
            AND MONTH(WellHistory.ModifyDate) = @SearchMonth
        GROUP BY
            Well.WellID,
            Construct.SideTrack,
            Construct.Completion,
            ConstructDate.EventDate,
            Loc.LocType,
            LocExt.Township,
            LocExt.TownshipDir,
            LocExt.Range,
            LocExt.RangeDir,
            LocExt.Sec,
            RefCounty.CountyName,
            tblAPDTracker.SpacingRule,
            Lease.Number,
            WellHistory.WHComments,
            WellHistory.ModifyDate
        ) AS BasicQuery
        PIVOT
        (
        MIN(BasicQuery.Location) FOR [Location Type] IN ([TPI], [BH])
        ) AS PivotedQuery
        ORDER BY
            API14,
            [Well History Modify Date];
    '
    
    SELECT @query_attachment_filename = N'SITLAMonthlyEntityReport'
                                        +N' '
                                        +CAST(DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())) AS NVARCHAR(12))
                                        +'/'
                                        +CAST(YEAR(GETDATE()) AS NVARCHAR(4))
                                        +N'.csv';

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = '###',
        @from_address = '###',
        @recipients = '###',
        @reply_to = '###',
        @body = @message,
        @query = @query,
        @query_attachment_filename = @query_attachment_filename,
        @attach_query_result_as_file = 1,
        @query_result_header = 1,
        @query_result_width = 32767,
        @query_result_separator = @tab,
        @append_query_error = 0,
        @execute_query_database = 'UTRBDMSNET'
END

Upvotes: 0

Views: 294

Answers (0)

Related Questions