I am beating my head against a wall, I am trying to set up a job to run and produce an xls to send to certain members.
If I take the "--" away from "Use DBName" and "Execute as user" I get a MSG 229,Lvl 14,State5,Procedure sp_send_DBmail Line 1 - Execute permission was denied on object
If I put in the "--" for "Use DBName" and keep "Execute as user" I get a MSG 15517,Lvl 16,State 1, Line 2 - Cannot execute as the db principal because the "" doesn't exsist.
I've tried changing/adding/permissions in all dbs, unchecking collation in OE, Checking the SIDs match and I feel I am at a loss. Someone help
Below is my query:
--Use DBNAME
--Execute as user = 'DOMAIN\svc_ACCTNAME'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBNAME@ABC.com',
@recipients = 'Name@ABC.com',
@subject = 'Report',
@body='testmail',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'REPORTNAME.xls',
--DECLARE @msg VARCHAR(250);
--declare @query varchar(2048);
--SELECT @msg = 'Please refer to the attached spread sheet for the report.';
@query='Use DBNAME
SELECT "JobOperations"."jmoProcessID" as "Resource ID",
"JobOperations"."jmoProcessShortDescription" as "Resource Description",
"Employees"."lmeEmployeeName" as "Employee Name",
"Jobs"."jmpPartID" as "Part ID",
"Jobs"."jmpJobID" as "Job ID",
"JobOperations"."jmoJobOperationID" as "Seq",
"Jobs"."jmpOrderQuantity" as "WO QTY",
"JobOperations"."jmoQuantityComplete" as "LBR QTY",
("JobOperations"."jmoOperationQuantity" - "JobOperations"."jmoQuantityComplete") as "QTY Delt",
"JobOperations"."jmoEstimatedProductionHours" as "Standard Run Hours",
"JobOperations"."jmoActualProductionHours" as "Actual Hours"
FROM (("DBNAME"."dbo"."Jobs" "Jobs"
LEFT OUTER JOIN "DBAME"."dbo"."JobOperations" "JobOperations" ON "Jobs"."jmpJobID"="JobOperations"."jmoJobID")
LEFT OUTER JOIN "DBNAME"."dbo"."TimecardLines" "TimecardLines" ON (("JobOperations"."jmoJobID"="TimecardLines"."lmlJobID") AND ("JobOperations"."jmoProcessID"="TimecardLines"."lmlProcessID")) AND ("JobOperations"."jmoWorkCenterID"="TimecardLines"."lmlWorkCenterID"))
LEFT OUTER JOIN "DNAME"."dbo"."Employees" "Employees" ON "TimecardLines"."lmlEmployeeID"="Employees"."lmeEmployeeID"
WHERE "timecardlines"."lmlCreatedDate" <= DATEADD(day, DATEDIFF(dd, 0, getdate()-7), 0) AND
"timecardlines"."lmlCreatedDate" > DATEADD(dd, DATEDIFF(dd, 0, getdate()-14), 0)'