** see updates below
I have a stored procedure (SQL Server 2012) that deletes old records from a table, then inserts new records from a view to synchronize the table with the view. When I execute the procedure from the command line, it works as intended, so I put it in a job to run every morning. When the procedure is run from the job, no errors arise, the job completes "successfully" in a fraction of the time I would expect, but no records have been inserted into the table. The delete works, but nothing gets inserted.
In trying to figure what is happening, I added some print messages on either side of each statement, "delete started", "delete ended", etc - and all of the messages show up in the job log - so the code is stepping through, it's just not inserting any records into the table.
My first inclination was it was some kind of permissions problem, but the delete statement works fine, and I have other jobs that are configured with the same owner that insert with no problem. I double checked the "mirror" schema, and the owner listed on the job has insert, update, delete, select privileges all enabled. (Still might be a permissions problem, I haven't done much configuration on that front, insights welcome.)
The actual statement is quite long (though uncomplicated), so here's a shortened version of what it's doing with identical syntax. I actually don't think the problem is with the code, as when executed from the command line, it works fine, but here it is anyway - maybe it will prompt just the right Q/A that I need.
Any ideas as to where to look would be appreciated:
**** 2014-03-27 a few updates: it occurred to me to try removing the delete part and just have an insert, just to isolate the problem. Same result, insert didn't happen, job succeeded. So I guess it's not about having more than one statement, it's the insert that is not working, flat out.
Also tried changing owner to myself as well as another user, but those resulted in failed job due to
"Unable to determine if the owner (PRELIANT\reports) of job CRM Reporting Dataset Updates has server access"
For reference, here is the exact message from the job history message window when the job "succeeds": Date 3/27/2014 7:33:11 AM Log Job History (CRM Reporting Dataset Updates)
Step ID 2
Job Name CRM Reporting Dataset Updates
Step Name tblPhoneCall Insert
Sql Severity 0
Sql Message ID 0
Operator Net sent
Retries Attempted 0
Message Executed as user: PRELIANT\reporting. begin insert [SQLSTATE 01000] (Message 0) finish insert [SQLSTATE 01000] (Message 0). The step succeeded.
/* remove all records from mirror that are not 'complete' (complete records are read only and will not change) */ delete MyDB.mirror.tblPhoneCall where statecode <> 1 ; /* remove records from mirror that were deleted from CRM */ delete tbl from MyDB.mirror.tblPhoneCall tbl left join AnotherDB.dbo.PhoneCall vw on vw.ActivityId = tbl.activityid where vw.ActivityId is null; /* insert all records from CRM where there is no match on mirror table, which now includes new records, or records that were not previously completed */ insert MyDB.mirror.tblPhoneCall ( activityid , field1 , field2 ) select vw.activityid , vw.field1 , vw.field2 from AnotherDB.dbo.FilteredPhoneCall vw left join MyDB.mirror.tblPhoneCall tbl on tbl.activityid = vw.activityid where tbl.activityid is null;