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:
DELETE FROM mytable
INSERT INTO mytable
SELECT * FROM VW_mytable
UPDATE mytableset set month= RIGHT('0' + CONVERT(VARCHAR, month), 2)
UPDATE mytableset set period = CONCAT(year,month)
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
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON
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.
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)