2

** 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;
  • What account is SQL Server Agent running as? Have you tried logging in as that user and running the command line to see what happens? (You running a command from the command line might not be the same as the SQL Server Agent account doing the same thing.) Though I would expect if permissions were an issue you'd see error messages in the job history. Is MyDB/AnotherDB really a database on the same server, or are you using linked servers? – Aaron Bertrand Mar 26 '14 at 19:16
  • I know, right? I would have expected it to fail, or at least show some kind of warning... When you say "running as" do you mean the job owner? In the advanced properties of the individual step, the run as user option is blank, as it is with other jobs on the same server. The owner is a login that predates my time here, a generic account used for all our jobs. I actually don't know how to execute as that user (I don't have the credentials for that account), but I'll poke around and see if I can't track them down and try. Both databases are on the same server. – liver.larson Mar 26 '14 at 22:54
  • @AaronBertrand - update: 1) we need a DBA - this is not really my realm 2) definitely permissions-oriented. Figured out how to execute as Reporting (user) and can't even access either database. Looked at roles and permissions on that user, and I have no idea why it's not being able go, as there are all sorts of jobs set up with this user. I will now be getting a crash course on security, so I can figure out how the roles/permissions/agent all interact and try to configure it. i still find it very strange that not being able to access the database did not cause the job to fail with an error... – liver.larson Mar 28 '14 at 16:56
  • still at a complete loss here. Can't figure out why other jobs run just fine from agent, and this one doesn't. Can't see any configuration differences. "AnotherDB" in example is a CRM database. In order to most closely reflect other procedures that do work, I set up a view through MyDB as "select * from AnotherDB..FilteredPhoneCall and redirected the procedure to the view. No beans, now when I run the procedure from the job, it deletes every record. When I run the procedure manually, still works as intended. WTH? pointers on how to troubleshoot, please? – liver.larson Apr 1 '14 at 16:26
  • You've provided a lot of information, but not a lot of it is usable by people who can't get their hands directly on your system. :-( – Aaron Bertrand Apr 1 '14 at 16:29
1

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)...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.