SQL Procedure getting exit upon called

Ruchita 40

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';

Script2.SQL will have :

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?

LiHongMSFT-4306 26,611 Reputation points

2024-07-15T01:50:40.6066667+00:00

Hi @Ruchita

Is this issue solved now?

If any answer is helpful to your question, you can accept it as an answer so that other users with similar questions can see it more easily. :)

Accepted answer

Erland Sommarskog 109.3K Reputation points MVP

2024-07-12T22:25:19.4066667+00:00

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;

Just to note: these are completely irrelevant to your problem.

As Olaf noted, you dumped a bunch of T-SQL code, which is not exactly easy to read. You can try to clear it by using the View Markdown button and make sure that the code block is in a single set of three backticks.

Then again, as Naomi suggests, the issue is most likely that your the DDL commands you generate run into errors. You have some debug output. You may have to improve that. I would recommend that you have a PRINT for every dynamic SQL statement you produce. Presumably the last one executed is the one that fails.
Please sign in to rate this answer.
Ruchita 40 Reputation points

2024-07-13T06:35:17.7733333+00:00

Thats ok since procedure doesnt stop because of that constraint errors it moves ahead for the execution but not able to figure it out why it stops for other ddl scripts because PRINT STARTS12 is printed after that PRINT GETDATE is not printed which is on the next line itself so if last query would have taken time then next PRINT statement would not have executed

Also if you rerun all the scripts again it executes fine.

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Erland Sommarskog 109.3K Reputation points MVP

2024-07-13T09:30:03.21+00:00

Permit me to point out that what you are doing is to ask others to debug your code. Because it is quite clear that the procedure exits because of some error condition.

You have a long and complex stored procedure, and reading that code takes some time. And it does not help that you post it a format that is difficult to read. Double line-spacing and in your latest dump the indentation is lacking. Given the length of the code, it would probably be better to attach it in a file instead. But it still a long and complex procedure...

However, I can give some tips about error handling, which is a very sore point in SQL Server. On some errors, SQL Server continues the execution on the next statement, and on others it aborts execution entirely, and there is not really any good pattern for which one or the other occurs.

Furthermore, when you trap an error with TRY-CATCH and retrieve the error message with error_message() and other functions, you only get one error message. But many DDL errors result in two error messages, and the one you get in error_message() is the less interesting one. The one that has the real error information is lost.

On my web site I have a series of articles, Error and Transaction Handling in SQL Server, in total three parts and three appendixes, that can help you to understand error handling in SQL Server better. In chapter 7 in Part Three I cover the challenge about double error messages and suggests som horrible kludges of a workaround.

You say that the procedure works on a re-run. I would assume that this is because when you re-run, the starting point is not the same as when you tried the first time. Or are you restoring a backup in between?

Ruchita 40 Reputation points

2024-07-13T10:03:29.37+00:00

This procedure is about converting table varchar column to nvarchar.So i have a temp table maintained where i check if proc has been executed for that table and column which is called if yes then it will exit if no then it will continue the execution.

So i truncate that table and rerun the scripts again and then it works perfectly inspite of errors in earlier scripts.

I will still try by correcting the errors if it executes.

Erland Sommarskog 109.3K Reputation points MVP

2024-07-13T10:28:32.8266667+00:00

For that purpose, I can offer on more tip from my web site: https://www.sommarskog.se/collation-change.html

This article cover a different task from yours, to wit: change the collation of many columns in a database. However, many of the challenges you face are the same with both. You need to drop foreign keys, indexes etc and then restore them.

The article comes with two scripts, and particularly the second one could be of interest for you. This script is for generating all the ALTER TABLE ALTER COLUMN statements together with all that is needed to be able to execute them. Or, well "all" - that depends on exactly what features you are using.

Ruchita 40 Reputation points

2024-07-17T05:53:58.0566667+00:00

I have corrected all the errors.

I have try catch block where i am checking if any error messages are present.

For debugging purpose i have added try catch for each alteration statement.

I have added PRINT statements for all the queries getting executed.

Below is my catch block :

BEGIN CATCH

SET @ErrorMessage = ERROR_MESSAGE();

SET @ErrorSeverity = ERROR_SEVERITY();

SET @ErrorState = ERROR_STATE();

THROW; //ADDED FOR DEBUGGING PURPOSE TO CHECK IF ANY ERROR THROW

-- Print or log the error information

RAISERROR (@ErrorMessage, 16, 1);

PRINT 'Error Occurred4: ' + @ErrorMessage;

END CATCH;

Now no errors are there.

But still the same issue.

Erland Sommarskog 109.3K Reputation points MVP

2024-07-17T08:51:28.8366667+00:00

So you have made substantial changes to the error handling, but you still see the same issue. Can you attach a copy of the stored procedure and an example of the output when procedure exits, as you think, prematurely?

Ruchita 40 Reputation points

2024-07-17T09:20:32.1933333+00:00

CHANGECOLUMNTYPETONVARCHAR1_ReadableProc.txt

Ruchita 40 Reputation points

2024-07-17T09:20:57.1533333+00:00

CHANGECOLUMNTYPETONVARCHAR_ProcWithPrintStatement.txt

Ruchita 40 Reputation points

2024-07-17T10:04:20.1933333+00:00

Execution_Logs.txt

All the 3 files attached.

This file has debugged print statements from _ProcWithPrintStatement proc.

Ruchita 40 Reputation points

2024-07-17T10:06:19.3966667+00:00

The above log file Execution_Logs.txt is from this proc CHANGECOLUMNTYPETONVARCHAR_ProcWithPrintStatement.txt as PRINT statements are added there.

Ruchita 40 Reputation points

2024-07-17T10:06:58.54+00:00

All the 3 files attached.

The above log file is from this proc CHANGECOLUMNTYPETONVARCHAR_ProcWithPrintStatement.txt as PRINT statements are added there.

Erland Sommarskog 109.3K Reputation points MVP

2024-07-17T19:33:58.1366667+00:00

First, if you replace PRINT with RAISERROR WITH NOWAIT, output will be unbuffered. Then we might see more output at the end.

When you get these seemlingly random exists, does it always end with STARTS12?

You said that there multiple scripts that are executed with SQLCMD. What is driving this execution? A PowerShell script or similar? How this script/program executed? Manually through the command line, or as an Agent job? Or something else?

As I understand it, it is not that the procedure exits, is it? But the entire execution stops, doesn't? I mean, if it was only the procedure, execution would continue with the next call, or at least the next script. Or am I missing something?

Ruchita 40 Reputation points

2024-07-18T02:57:26.16+00:00

Yes correct.The execution stops as in it doesn't proceed ahead after this STARTS12.

Nothing is executed later not even next script.

The pattern is similar most of the times checked till now.

I have a DDL folder where 36 .SQL scripts are present which invokes this procedure.

This is executed via dbexe which is a python program which will run the scripts via sqlcmd.

Just to ensure if this is causing the issue I have tried running this using bat file which will consider the .SQL file from filepath and bat file will execute these scripts internally using sqlcmd command.This bat file we are executing manually.

eg: sqlcmd -U xxxxxxxxxx -P yyyyyy -S indra,1442 -d sahibase_newsql1 -i "%tmpFile%" >> "%logFile%" 2>&1

When this was executed the execution stopped.

But at this time it stopped for another script.

Ruchita 40 Reputation points

2024-07-18T05:29:38.6166667+00:00

I replaced all the PRINT statements with RAISEERROR with NOWAIT.

Now the statements for eg : PRINT STARTS12 there was nothing printed after that.

But after adding RAISEERROR with NOWAIT i got the next statements.

So the logs are as below :

2024-07-18 10:22:41.950

ALTER TABLE sahibase_newsql1.sahibase_newsql1.LISTMASTER ALTER COLUMN CODE NVARCHAR(20)

STARTS16

STARTS17

STARTS171 1

STARTS17UC

ALTER TABLE sahibase_newsql1.sahibase_newsql1.LISTMASTER ADD CONSTRAINT UN_LISTMASTER UNIQUE (OWNERTYPE, OWNERID, LISTTYPE, CODE)

Now the above ALTER statement after STARTS17UC did not execute.

It stopped.

The same alter statement if you execute manually on same db it executes fine and constraint gets created.

Also this statement is within try catch block :

Code snippet :

RAISERROR('STARTS17UC', 0, 1) WITH NOWAIT;

BEGIN TRY

SET @sql = 'ALTER TABLE sahibase_newsql1.sahibase_newsql1.' + @tableName + ' ADD CONSTRAINT ' + @index_name + ' UNIQUE (' + @index_columns + ')';

RAISERROR(@sql, 0, 1) WITH NOWAIT; //This got printed as above.

EXEC(@sql);

END TRY

BEGIN CATCH

SET @ErrorMessage = ERROR_MESSAGE();

SET @ErrorSeverity = ERROR_SEVERITY();

SET @ErrorState = ERROR_STATE();

THROW;

-- Print or log the error information

RAISERROR (@ErrorMessage, 16, 1);

END CATCH;

Erland Sommarskog 109.3K Reputation points MVP

2024-07-18T09:19:10.94+00:00

Let's think more about this. So you had a .BAT file that set up a loop over the files in the folder and you ran SQLCMD for each file. And in the midst of one file, execution of the entire BAT file stopped? And that file was not the last file?

Then quite obviously, it has nothing to do with the stored procedure as such, because if the procedure would for some reason exit randomly, the BAT file would continue on the next file. And in that case, you would see one more incomplete files in the log. But as I understand, it is only at the very end, where things stop.

And furthermore, this seems to happen, no matter if you run a .BAT file or your Python script. It's like there is some policy on your machine "This process has been running for 15 minutes, that's enough. Let's stop it." No, I don't what sort of policy that may be. But I just wanted to turn your attention to where the problem might be.

To test this hypothesis, you could change the procedure to simply read

PRINT 'STARTS : ' + @tableName + ' ColName : ' + @columnName PRINT CONVERT(VARCHAR(30), GETDATE(), 121); WAITFOR DELAY '00:01:00' PRINT 'ENDS: ' + @tableName + ' ColName : ' + @columnName PRINT CONVERT(VARCHAR(30), GETDATE(), 121);

And see if you get through all 36 files.

Ruchita 40 Reputation points

2024-07-18T10:11:12.49+00:00

It seems it's CPU spike issue.CPU consumption is more when the scripts are executed.

My sincere thanks for your help in resolving this issue.Your assistance was invaluable, and I truly appreciate the effort you put into resolving it.Your support not only solved the problem but also helped me learn more about SQL.

Thanks a ton!

Erland Sommarskog 109.3K Reputation points MVP

2024-07-18T10:26:28.2333333+00:00

So you run the scripts on the machine where SQL Server is installed? It's sounds funny that it would be related to the CPU consumption, since it is the script that is aborted, and the script should consume a lot of CPU, since the hard work is in SQL Server.

Ruchita 40 Reputation points

2024-07-18T11:49:39.7+00:00

No the scripts are run from machine1 and SQL Server exists on machine2.

CPU consumption of machine2 is high because the scripts will modify varchar columns to nvarchar across tables.

The scripts executed fine without any exit on another machine.

So once that is resolved we will check again if any issue then will get back AGAIN!! :)
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

2 additional answers

Olaf Helper 43,896 Reputation points

2024-07-12T17:50:03.2733333+00:00

The procedure stops or is getting exited randomly. There is no error or no error logs.

"Thanks" for posting tons of SQL code. We don't have your database to test anything and without an error message we can't guess anything.

Please post an reproducable example, otherwise it's mor the difficult to assist you.
Please sign in to rate this answer.
Ruchita 40 Reputation points

2024-07-13T06:48:07.4466667+00:00

Ok will check
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Naomi Nosonovsky 565 Reputation points

2024-07-12T20:41:25.0866667+00:00

Well, in the output you posted I can see some errors like this Error Occurred1: Could not drop constraint. See previous errors.

Can you investigate these errors?
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

SQL Procedure getting exit upon called

2 additional answers

Your answer