sp_send_dbmail Error Formatting Query

Question

I have a SQL agent job that was working, but after adding a new left join to the dbmail query it's failing. The error is one I've seen all over that few people seem to have an answer for:

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 517
Query execution failed: Msg 105, Level 15, State 1, Server SQLSRV, Line 34

The message after Query Execution Failed tends to change and pick locations in the middle of words as the false culprit.

The query is as follows:

@Query = 'set nocount on 
USE [Epicor10]
select
  case when VoidOrder = 0 then "Not Void" else "Voided" end as ' + @Column1Name + ',
  case when Approve = 0 then "NA" else "A" end as Approved, 
   case when poheader.Confirmed = 0 then "UC" else "C" end as Confirmed, 
    POHeader.PONum, 
    podetail.POLine,
   pr.PORelNum,
   TG.GLAccount,
   convert(nvarchar,REPLACE(REPLACE(REPLACE(REPLACE( podetail.PartNum, CHAR(13), ""), CHAR(10), " "), CHAR(9), " "), ",", " "))  PartNum,
    convert(nvarchar,REPLACE(REPLACE(REPLACE(REPLACE( podetail.LineDesc, CHAR(13), ""), CHAR(10), " "), CHAR(9), " "), ",", " ")) LineDesc,
   OrderQty,
     ISNULL(CONVERT(nVarChar(max), pr.DueDate, 121), NULL) RelDueDate,
  ISNULL(CONVERT(nVarChar(max), PODetail.DueDate, 121), NULL)   LineDueDate,
   pr.ReceivedQty,
   pr.RelQty,
   (pr.RelQty - pr.ReceivedQty) BalanceDue,
   EntryPerson, 
   ShipName, 
   BuyerID, 
   POHeader.VendorNum, 
   Vendor.VendorID,
 convert(nvarchar(max),REPLACE(REPLACE(REPLACE(REPLACE(Vendor.Name, CHAR(13), ""), CHAR(10), " "), CHAR(9), " "), ",", "")) Name,
 REPLACE(REPLACE(REPLACE(REPLACE(ISNULL(CONVERT(nVarChar(max),ApprovedDate, 121), NULL), CHAR(13), ""), CHAR(10), " "), CHAR(9), " "), ",", "")   LineApprovedDate,
    ISNULL(CONVERT(nVarChar(max),OrderDate, 121), NULL) OrderDate,
 convert(nvarchar,REPLACE(REPLACE(REPLACE(case when ApprovedBy = "username" then "usersname" else ApprovedBy End, CHAR(13), ""), CHAR(10), " "), CHAR(9), " ")) ApprovedBy
 from [Epicor10].[Erp].[POHeader] 
inner join [Epicor10].[Erp].Vendor on POHeader.VendorNum = Vendor.VendorNum
left join  [Epicor10].[Erp].PODetail on poheader.company = podetail.company and poheader.PONum = podetail.PONUM
left join [Epicor10].[Erp].PORel pr on PODetail.Company = pr.company and PODetail.ponum = pr.PONum and PODetail.poline = pr.POLine
left join [Epicor10].[Erp].TranGLC TG on TG.Key1 = pr.PONum and TG.Key2 = pr.POLine and TG.Key3 = pr.PORelNum and TG.RelatedToFile = "PORel"
    where POHeader.OpenOrder = 1 and OrderDate >= "2016-7-1" and OpenLine = 1 and VoidLine = 0 and VoidRelease = 0 and OpenRelease = 1
    order by PONum, POLine, PORelNum'

Now this was working and sending fine until I was asked to add the GLAccount column, the last 'Left Join' on TranGLC is the trigger for the issue. If I remove that join again, it works, but I can't figure out what the problem with that is.

I've checked permissions on the agent (though I can't imagine why that table would've made a difference), I've done many redundant things to make sure its pointing to the database and schema. I've copied the query and run it standalone in another window and it works fine. Nothing I've found online has been helpful in pinpointing the problem.

UPDATE FOR COMMENTS:

Can I send it without the query?: Yes, I can even send the email with the query until I add the left join to TranGLC.

Permissions issue?: I also saw that a lot online as the usual cause, and that would make sense here. It doesn't seem to be the case though. No special permissions on this table that I can see. Its setup like the rest of the tables in the database>schema

Job Agent User: So here is where I'm a bit confused on the setup/relationship of everything. I'm running the sp under a Profile, which is set up under Management>Database Mail using Basic Authentication with an email we have for our domain. This email is actually the email for a domain user we have. I could try setting it to Windows Auth for the active user (me, since I'm an admin).

What I'm unclear about is how that works within SSAgent, most of what I've seen says to set up database mail under SSAgent>Properties>Alert System (Enable Mail profile is NOT checked here for us) but I'm unclear about the relationship. I feel like that's for something else entirely and shouldn't matter here.

Answer 1

From the looks online, everything points to some sort of permission issue somewhere in the process.

Questions to check would be: Are you able to send the email manually i.e. run the same exact job manually, not the query? Does that table or column have any special permissions? What happens if you change the user that the job agent runs as?