0

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
Hiebs915
  • 666
  • 1
  • 7
  • 22
  • @Larnu You want the whole query? – Hiebs915 Oct 04 '21 at 17:24
  • The error of a “invalid for divide operator” is not caused in that code. It would only result if the / **was not** in a string literal. There is no escaping problem here and literal / characters need bo escaping in strings. – user2864740 Oct 04 '21 at 17:24
  • Yes, @Hiebs915 . – Thom A Oct 04 '21 at 17:25
  • @Larnu Is it possible to include `/` in the filename? If not, I'll use a period instead. – Hiebs915 Oct 04 '21 at 17:26
  • 1
    A *file's* name cannot have a `/` in it, no @Hiebs915 . I recommend against `.` too though (that's normally to denote the extension). Use a hyphen perhaps. – Thom A Oct 04 '21 at 17:26
  • (note: Any invalid character in a file name would be represented as a different error later. This is a SQL Server *parsing* error.) – user2864740 Oct 04 '21 at 17:26
  • The error is likely caused by a different version of code; or if this code was extracted from a string in a programming language, it is not the *actual command*; or it is preceded by invalid SQL.. – user2864740 Oct 04 '21 at 17:30
  • @Larnu Ok. Thanks for the tip on using a period, didn't think of that in my last response to you. I guess I might have to resort to using `-` instead. – Hiebs915 Oct 04 '21 at 17:32
  • @user2864740 I'm a little confused then. When I use a `-` instead of a `/` the filename shows as "SITLAMonthlyEntityReport 9-2021". – Hiebs915 Oct 04 '21 at 17:38
  • I’m confused as well. What happens if using the expected literal directly? – user2864740 Oct 04 '21 at 17:41
  • The way that one would normally get such an error is, `select 'a' / 'b'` — this results in the error message shown, and it occurs because the / is outside a string. – user2864740 Oct 04 '21 at 17:43
  • @user2864740 If I use a `/` directly without single quotes around it I get, "Incorrect syntax near '/'. – Hiebs915 Oct 04 '21 at 17:45
  • While invalid when executed (as the second arguments conversion to fails), the following parses correctly: `select 1 / 'foo/bar'` – user2864740 Oct 04 '21 at 17:45
  • A / *must* be quoted if it’s meant to be part of a string (because out of quotes it’s not a string..).. so again, back to some larger context. – user2864740 Oct 04 '21 at 17:46
  • @user2864740 If use `/` or `'/'` the filename is 2021.csv. It only shows me the part after the escape. – Hiebs915 Oct 04 '21 at 17:47
  • That behavior **has nothing to do with the reported error**. And that seems logical. – user2864740 Oct 04 '21 at 17:49
  • So this entire run-around can be summarized as “how can I attach a file name containing a / with sp_send_mail”? And then all the *incorrect claims* can be omitted entirely. – user2864740 Oct 04 '21 at 17:50
  • The minimal SSCCE would be merely the sp_send_mail call with relevant literals used directly as arguments. None of the other code is relevant. – user2864740 Oct 04 '21 at 17:52
  • @user2864740 I was asked for more code so I added it. All I wanted to do was use a `/` in an emailed filename from sp_send_mail. Larnu said I couldn't so I figured it was over at that point. I'll just a `-` instead. If you want, you can suggest an edit to the title if you think it needs to be more clear. I'd like my question to be clear and undertandable for others. – Hiebs915 Oct 04 '21 at 17:54
  • The run-about appears to occur at the “things tried” bit. These attempts include invalid syntax (not shown in the snippet above), eg. `select ''/''` is trivially invalid because it is the same as `select '' / ''`. Going back to a minimal SSCCE with literals (showing the given input and describing the behavior (that of the file name only including the last segment)) would avoid all of such, eg. `EXEC msdb.dbo.sp_send_dbmail .. @query_attachment_filename = 'foo\bar.txt' ..` .. “attaches the filename as bar.txt”. – user2864740 Oct 04 '21 at 19:57
  • Then for any case with a syntax error (if relevant), show the full changes in the full (yet minimal SSCCE) context as a stand-alone query. – user2864740 Oct 04 '21 at 20:02

0 Answers0