I have a sql stored procedure created which will be getting invoked for multiple tables and column sequence wise.There are multiple DDL scripts which have procedure call and these scripts will get executed via sqlcmd command.So there is a folder X which have Script1.SQL,Script2.SQL and so on.One by one the scripts from the path will be executed via sqlcmd.
Script1.SQL will have procedure call as example :
EXEC PROCEDURENAME 'TABLENAME', 'COLUMNNAME';
EXEC PROCEDURENAME 'TABLENAME', 'COLUMNNAME';
Script2.SQL will have :
EXEC PROCEDURENAME 'TABLENAME', 'COLUMNNAME';
EXEC PROCEDURENAME 'TABLENAME', 'COLUMNNAME';
The above are just examples so there will be multiple scripts with multiple lines of code having procedure call has above.
The procedure stops or is getting exited randomly.
There is no error or no error logs.
Below are the logs which are printed in procedure.
These logs are added in procedure to verify.
STARTS : MAPPING_LISTMASTERTABLES ColName : AUXPARAM
STARTS11
Jul 12 2024 6:19PM
Error Occurred1: Could not drop constraint. See previous errors.
STARTS12
Jul 12 2024 6:19PM
ENDS12
Jul 12 2024 6:19PM
STARTS13
Jul 12 2024 6:19PM
STARTS16
Jul 12 2024 6:19PM
Error Occurred3: Could not create constraint or index. See previous errors.
Execution Ends for \ddl\Master_List.sql
Executing\ddl\ModuleAndMenuTree.SQL
2024-07-12 18:19:58.150
(1 row affected)
STARTS11
Jul 12 2024 6:19PM
ENDS11
Jul 12 2024 6:19PM
STARTS12
Now if you see it stopped or exited dont know why.For other scripts it executed properly for next script it exited and this is random it exits for any script.
Now the logs are only till STARTS12 after that PRINT GETDATE() did not printed whereas for previous it worked.
Tried below things :
EXEC sp_configure 'query timeout', 1800; RECONFIGURE;
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'remote query timeout', 1800; RECONFIGURE;
Still the same issue.If you rerun the scripts again it executes fine.
The issue is while first execution.Checked
EXEC xp_readerrorlog; no relevant logs
What can be the problem?And how to figure it?