October 14, 2014 at 2:31 am
I'm currently trying to set up an job to run a set of procs. I'm seeing some pretty baffling behaviour though. Two of the procs are to load a staging table and a fact table and they include a line to truncate their respective tables before re-loading them. The problem I'm having is that when I run the procs through the job, the truncate happens and nothing else. When I run the procs on their own, one after the other, everything works as it should. Is this a quirk of SQL Server Agent or a problem with the procs? If you need to see any code just ask, I've not posted any so far because as I said, it works fine in isolation.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
October 14, 2014 at 4:31 am
Who is the owner of the job?
When the job is executed who is it executed by(which account).
October 14, 2014 at 4:38 am
The owner of the job is me and the job is run by svc_xxxxr2Agent. Both accounts have sa access and datareader and datawriter enabled.
It also seems that it's one step that's causing the problem. When run on its own, the final fact load works i.e. truncates then inserts but the staging load seems to be the one that's failing. However when I run them one after the other using EXEC proc1, EXEC proc2 etc it works as it should.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
October 14, 2014 at 6:17 am
Are you using a SSIS package to do this.
October 14, 2014 at 6:21 am
I wish I was. It's not installed on this server and neither can I actually install it. I'm doing this direct into SQL Server Agent.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
October 15, 2014 at 4:56 am
I seem to have solved the problem but it has raised more questions than it's answered.
It appears that it wasn't necessarily anything to do with the Job I was running. What transpired was that if I ran the package when the staging table was empty to start with, the fact table wouldn't load\ or would load nothing. If the staging table was populated when I ran the job it would load the fact table with no problems. Oddly if the fact table was empty and the staging table populated then it would work OK. Can anybody suggest any reasons for this?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply