How do I diagnose and fix "Error formatting query, probably invalid parameters, incorrect syntax" error?

Question

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 = 'mhiebing@utah.gov',
        @recipients = 'mhiebing@utah.gov ',
        @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.

API14	Entity Number	First Prod Date	Spacing Rule	Top Producing Interval Location	Bottom Hole Location	Well History Comments	Last Modified Date
43013534820000	100260	2019-01-09	139-134	Township 3S Range 1W Section 22 DUCHESNE County	Township 3S Range 1W Section 15 DUCHESNE County	Moved to CTB entity 100401	2020-07-15 17:27:00
43013534820000	100401	2019-01-09	139-134	Township 3S Range 1W Section 22 DUCHESNE County	Township 3S Range 1W Section 15 DUCHESNE County	Moved to CTB entity 100401	2020-07-15 17:27:00
43013534860000	100246	2019-01-09	139-134	Township 3S Range 1W Section 22 DUCHESNE County	Township 3S Range 1W Section 15 DUCHESNE County	Moved to CTB entity 100401.	2020-07-15 17:28:00

DECLARE "@"SearchYear AS VARCHAR(4) = 2020 DECLARE "@"SearchMonth AS VARCHAR(2) = 7 Why number in a variable that is string typed ? — SQLpro, Sep 28 '21 at 13:35
What happens if you instead omit the 3 part naming, and just use 2 part naming, and then define the database the query needs to be run in with the `@execute_query_database` parameter? — Thom A, Sep 28 '21 at 13:35
@SQLpro Eventually, I want this SP to run on the 1st of the month. I'll be replacing these two variables with dynamic numbers that will call the previous month in the current year. I tried removing them and putting the numbers directly in the query but it didn't make a difference. — Hiebs915, Sep 28 '21 at 13:46
@Larnu You mean instead of `UTRBDMSNET.dbo.tblAPDTracker` just put `UTRBDMSNET..tblAPDTracker`? I'm a little confused about your "`@execute_query_database`" comment. — Hiebs915, Sep 28 '21 at 13:48
`UTRBDMSNET..tblAPDTracker` is still 3 part naming, @Hiebs915 , you've just told the RDBMS to use the User's default schema. What about the latter part don't you understand? I can't really explain that more... Why are you using 3 part naming instead of using the parameterr? — Thom A, Sep 28 '21 at 13:50
@Larnu So would I add `@execute_query_database = 'UTRBDMSNET'` to my execution command and then list each of the table names with UTRBDMSNET or dbo? Like this: `FROM Well LEFT JOIN tblAPD Tracker ON...`? — Hiebs915, Sep 28 '21 at 13:57
@Larnu Ok so for 2-part naming I'd use `FROM UTRBDMSNET.Well LEFT JOIN UTRBDMSNET.tblAPDTracker ON...`? — Hiebs915, Sep 28 '21 at 14:06
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/237599/discussion-between-hiebs915-and-larnu). — Hiebs915, Sep 28 '21 at 14:16
@SolomonRutzky I need to do more research here. When Larnu said, "Always schema qualify", what does that mean exactly? — Hiebs915, Sep 28 '21 at 14:39
@Hiebs915 That means always specify a schema name for schema-bound objects. So, use `dbo.tblAPDTracker` instead of just `tblAPDTracker`. Your query already does this, Larnu was just referring to you asking if you should use just `tblAPDTracker` instead of `dbo.tblAPDTracker` when you misunderstood the original suggestion to use 2 part naming (i.e. `dbo.tblAPDTracker`) instead of 3 part naming which includes the DB name (e.g. `UTRBDMSNET.dbo.Lease`). Larnu suggested removing the first part--the DB name--to then pass the DB name in via the `@execute_query_database` param. — Solomon Rutzky, Sep 28 '21 at 14:44

Solomon Rutzky · Accepted Answer · 2021-09-28T14:33:08.250

2

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''

edited Sep 28 '21 at 14:33

answered Sep 28 '21 at 14:22

Solomon Rutzky

46,688
9
128
171

Wow, this worked! Thanks! I was close while looking this up yesterday, even looked up the differences between VARCHAR and NVARCHAR but never thought to change the number of characters... I tried `VARCHAR(4000)` and `NVARCHAR(MAX)`, both worked. I'll use `NVARCHAR(MAX)`. – Hiebs915 Sep 28 '21 at 14:26
@Hiebs915 You are welcome. And yes, I was hoping that by sharing what tipped me off as to the cause would help others more quickly identify such problems rather than waste hours making tiny modifications and retesting. Also, please see the additional notes I just added to the end of this answer. – Solomon Rutzky Sep 28 '21 at 14:34
I see your additional notes, thanks again. I changed all `VARCHAR`s to `NVARCHAR`. Great tip about the square brackets as well. From other posts, the single quotes were usually the issue so I spent a lot of time checking them. The square brackets are way easier to use in this case. – Hiebs915 Sep 28 '21 at 14:44
1

@Hiebs915 And, not only are square brackets easier when using Dynamic SQL, I believe using single quotes is a deprecated syntax (that will prolly work for the next 50 years but still there is a stated preference for a reason). – Solomon Rutzky Sep 28 '21 at 14:47
Looks like brackets are preferred. [Server-Deprecated Features-Column Alias](https://nakulvachhrajani.com/2012/06/18/0170-sql-server-deprecated-features-column-alias-defined-by-a-string-enclosed-in-quotation-marks/) However, I do like that MSSMS highlights the alias red when using single quotes. With brackets all the text is black. – Hiebs915 Sep 28 '21 at 14:53
1

@Hiebs915 Yes, though a better source of deprecation info is directly from Microsoft. Just go here: https://learn.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016#features-deprecated-in-a-future-version-of-sql-server and look for "column alias". – Solomon Rutzky Sep 28 '21 at 15:04

How do I diagnose and fix "Error formatting query, probably invalid parameters, incorrect syntax" error?

1 Answers1