I've been writing 'driver' sql scripts (is that the correct name?) to run multiple sql 'sub' scripts in order, writing errors to log file etc. My Oracle version seems to be more or less ok, but my sql server one I'm struggling with. Specifically, when a script fails, I'm getting line numbers bigger than files for the error location. Is there a best practice 'template' for this? Should I be using dacpacs instead? Any tips appreciated here. Thanks.
SET NOCOUNT ON
GO
PRINT 'upgrading something'
echo 'do this first'
:r 'this.sql'
echo 'now do that'
:r 'that.sql'
PRINT 'done'
GO