SQLTeam.com | Weblogs | Forums

Scheduled stored procedure and empty table (working manually!)




I created a stored procedure, scheduled every night, that delete multiple tables and then re-insert rows in them from views.
All the tables are populated but one that remains empty.
The strange thing is that if I run the procedure MANUALLY, the table is populated.

My code is like this:


SELECT * FROM VW_mytable

    UPDATE mytableset set month= RIGHT('0' + CONVERT(VARCHAR, month), 2)
UPDATE mytableset set period = CONCAT(year,month)

Anyone had the same issue?



Some error causing it to roll back, perhaps?

Permissions on the VIEW perhaps?


The stored procedure doesn't throw any error.
And the strange thing is that all the other tables are deleting and updating successfully from views.


What is the definition of the view?


Would you see them? (i.e. on a scheduled task, rather than when you run it manually). It will probably run in a different context - e.g. SQL_Service or similar - rather than your logon. If there was an error there might be something in the Error Logs

Also, there isn't any error handling shown in your code sample above. Ordinarily I would expect SQL to terminate the SProc if an error is encountered, but there are conditions where it might not. For example, it might silently fail to retrieve anything from the VIEW

Adding this at the top of the SProc (just after the "AS") may help to force errors to abort


its a longshot though, errors would probably terminate the Sproc, but the problem that you cannot see them (when run as a scheduled task) remains.

  1. what kind of schedule is it?
  2. what user is used on that scheduled job?



i made some tests.
Maybe I found the problem.
I have 2 scheduled jobs (on different db) running at same hour.

Running them at different time, from a first test, appears to populate correctly all the table.

So It could be a problem running multiple scheduled stored procedure at the same time?



so both scheduled jobs call the same stored procedure?


No, they are 2 different stored procedure, dropping tables from different db and regenerating from views.
But I think the issue was that the failing stored procedure, were reading from the other db table maybe when the tables were regenerating.

Now running in different times, it seems working without problems (2 nights in a row)


Might be better to run them sequentially, in a single job, than two different jobs (scheduled at different times)?

I'm just thinking that some kind person, at some time!, might change the Schedule - without realising that they should not run at the same time. Also possibility that the first one will BLOCK, for some reason, and then over-run the start time of the second job - or the second job will start before the first (because there was a power cut / scheduled downtime at the time that the first job was scheduled)