Hello everyone,

i have stumbled across an odd behavior. I am deleting data in 60 tables in 1 direct sql statement.
For some reason sometimes records just dont get deleted ... Every time records from 2 tables
dont get deleted. And sometimes its even more than those 2 tables. If i run the same sql in sql
management studio everything works as it should. The funny part is that these two tables are
around in the middle of the sql statement ... 

What could be wrong? Should i split my sql in smaller ones? Should i create a procedure and use
exec to call it? 

Sometimes 1 apple + 1 apple = green east 

Regards,
A

Can you supply you DSQL statement?

On Tue, Mar 30, 2021 at 02:28 PM, Adrian Wick wrote:

Should i create a procedure and use
exec to call it? 

+1

That's what I would suggest, yes.

Steven G. Blank
SGBlank Consulting

Yes, definitely stored procedure.
Direct SQL should be used only if you don't have a better approach.
With direct sql there is an additional issue of transaction processing related to Magic/xpa.
It is much easier and more safe to put that SQL code inside procedure.

Take care: If you are truncating tables / deleting data in 60 tables, you can't be sure if you are violating referential integrity.
The order of truncate statements could be an issue.

To safe on the safe side, I'd create procedure with return value - just to be 100% sure everything is OK.

Regards - Kenan