I'm putting together a stored procedure that sends some query results attached as a CSV in an email. I'm testing the procedure and get this error:

Msg 22050, Level 16, State 1, Line 2 Error formatting query, probably invalid parameters Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 517 [Batch Start Line 2] Query execution failed: Msg 102, Level 15, State 1, Server NRWOGMSQL6ST\SQLSTD2012, Line 27 Incorrect syntax near 'tbl'.

I know the query that produces the results in the CSV works because I've tested it outside the SP. I believe all of the quotations are correct because I printed the query and double-checked the escape quotes. I tried using @query_result_no_padding = 1 but it gave me the same error in the attached CSV. I also know the emailing part of the SP works because I've been using a test query to test things(see commented out query that starts with "SELECT TOP 100").

I believe the error is referring to the tblAPDTracker table. I tried commenting out this line but the next error says the same thing about the "Well" table. I can't comment out the well table because it's the first table I use. Is there any way to get more information about what's going wrong and how I could fix it?

USE [UTRBDMSNET]
GO
/****** Object:  StoredProcedure [dbo].[TestProcedure] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[HiebingTestProcedure] 

AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @message VARCHAR(1000);
    DECLARE @subject VARCHAR(100);
    DECLARE @query VARCHAR(1000);
    DECLARE @tab char(1) = CHAR(9);
    DECLARE @query_attachment_filename VARCHAR(520);
    DECLARE @CRLF char(2);

    SELECT @CRLF = CHAR(13) CHAR(10)
    SELECT @subject = 'Well Information Report'
    SELECT @message = N'Dear Zeke, '
                       @CRLF  ''
                       @CRLF  N'Please refer to the attached spread sheet for the results of last month''s information report.'
                       @CRLF  ''
                       @CRLF  'Thanks,'
                       @CRLF  'Matt';

    --SELECT @query = 
    --   'SET NOCOUNT ON;
    --    SELECT TOP 100 WellID, Operator, WellStatus, ModifyUser
    --    FROM UTRBDMSNET.dbo.Well';

    SELECT @query =
    '
        SET NOCOUNT ON;
        DECLARE @SearchYear AS VARCHAR(4) = 2020
        DECLARE @SearchMonth AS VARCHAR(2) = 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 UTRBDMSNET.dbo.Well
            LEFT JOIN UTRBDMSNET.dbo.tblAPDTracker ON LEFT(tblAPDTracker.APINO,10) = Well.WellID
            LEFT JOIN UTRBDMSNET.dbo.Construct ON Construct.WellKey = Well.PKey
            LEFT JOIN UTRBDMSNET.dbo.ConstructReservoir ON ConstructReservoir.ConstructKey = Construct.PKey
            LEFT JOIN UTRBDMSNET.dbo.Lease ON Lease.Pkey = ConstructReservoir.LeaseKey
            LEFT JOIN UTRBDMSNET.dbo.WellHistory ON WellHistory.WellKey = Construct.WellKey
            LEFT JOIN UTRBDMSNET.dbo.ConstructDate ON ConstructDate.ConstructKey = Construct.PKey AND ConstructDate.Event = ''FirstProduction''
            LEFT JOIN UTRBDMSNET.dbo.Loc ON loc.ConstructKey = Construct.PKey AND Loc.LocType IN (''BH'',''TPI'')
            LEFT JOIN UTRBDMSNET.dbo.LocExt ON LocExt.LocKey = Loc.PKey
            LEFT JOIN UTRBDMSNET.dbo.RefCounty ON RefCounty.PKey = LocExt.County
        WHERE
                WellHistory.WorkType = ''ENTITY''
            AND WellHistory.ModifyUser = ''UTAH\rachelmedina''
            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 = 'TestingEmailAttachment.csv';

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'OilGasEmail',
        @from_address = '[email protected]',
        @recipients = '[email protected] ',
        @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
        --@query_result_no_padding = 1;
END

Below are a few rows of results that I would expect to see in the email CSV.

CodePudding user response：

The problem is so simple that it is being overlooked: string truncation due to @query variable not being declared with a large enough size to hold the query.

Change @query to be VARCHAR(4000) and it will work, though NVARCHAR(MAX) is the best / ideal choice.

The reason I started looking at truncation as the cause of the error is due to the error message:

| Incorrect syntax near 'tbl'.

On the surface that looks to be a table alias, but I searched and found that the string tbl is only ever used as a prefix for the table name. When you get an error message with only partial text in it, that usually indicates that the optimizer didn't see the rest of the query (either that or the error message was too long and got cut off, but that was clearly not the case here).

Additional Notes

Also, related to my recommendation to use NVARCHAR instead of VARCHAR, and seeing as how at least some of the string literals in the code are prefixed with N (making them NVARCHAR literals): you should declare all of the variables as NVARCHAR as that reduces potential for data loss in the future if any characters are used that are not part of the code page associated with the current database's default collation ("current" being where this stored procedure is lives). Likewise, the CHAR variables should be NCHAR.

Then, all string literals should be prefixed with N, not just some of them.

Finally, column name aliases (i.e. what comes after the AS) should use square brackets instead of single-quotes. For example:

Loc.LocType AS [Location Type]

instead of:

Loc.LocType AS ''Location Type''