Sql-server – stored procedure with multiple statements does not execute all statements when run from job

sql-server-2012sql-server-agentstored-procedurest-sql

** 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
Server ULYSSES
Job Name CRM Reporting Dataset Updates
Step Name tblPhoneCall Insert
Duration 00:00:54
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
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;

Best Answer

The answer, it turned out, is something no one in forum land would have been able to guess at, no matter how good with SQL Server they were. It was application-level security that no one here was aware of.

The CRM database which was the source of the data for insert, contains a table of system users managed on the front end of the application. The vendor who developed the software set it up, but as they left no documentation, no one here was aware of the security layer on the back-end. It was confusing, largely, because the security only affects certain views - so all those other jobs that were working were calling for information from tables and views that were not influenced by the security.

When testing logged in as me, the procedure worked because I am set up in the CRM table as an administrator, but the credentials used to run SQL Server agent don't even exist in the CRM user table.

Had nothing to do with SQL Server security, but everything to do with permissions (didn't see that coming)...

Related Question