I have a job that fails only starting recently and I can't see any reason for it that makes sense.
The job is basically an SSIS package that runs a stored proc with some parameters passed in. It builds up some dynamic SQL (Please - I know it's not ideal and will work on changing it - but in this post, I'm seeking the cause for this failure).
The sql job agent is nice enough to give me the EXACT sql that tried to execute, and says:
Executing the query "EXEC dbo.ped_sp_ped_updates ?,?,?,?,?,?,?" failed with the following error: "Incorrect syntax near '########'
(I replaced the actual numbers with # signs for privacy).
And here is the SQL statement:
UPDATE PED_APPLICATIONS SET HICN_MEDICARE_NUMBER = '########',NOTES_GENERAL = ' SSIS | Jun 10 2015 5:17AM | Changing column: HICN_MEDICARE_NUMBER from ######## to ########, per Username request. Also changing any related DOCS/FAXES records to correspond' WHERE HICN_MEDICARE_NUMBER='#########' AND (CAST(ISNULL(DATE_RECEIVED,'1900-01-01') AS DATE)=CAST('06/06/2015' AS DATE))
It shows me that exact SQL that it tried to run. But guess what - that's a perfect valid sql statement, and if you start to ask, but we'd need to know the table structure, all data types, existing data being concatenated, etc. etc. etc.......... I can tell you this: When I paste the exact statement which SQL Job Agent failed on "incorrect syntax", into Management Studio, it runs perfectly, no errors, and does what it's supposed to.
What's the disconnect going on here? I swear I had made NO changes to this - it ran good for over 6 months - although not too often - and recently, it fails every time it gets triggered to run with essentially the exact same error in the exact same place.