Is it possible to somehow “echo” messages in SQL scripts in SQL Server, so they can get recorded in the execution log?

Due to strong security policies the developers can only run database scripts in the dev environment, and they can see the result of them right away.

However, to perform changes in the test, staging, or prod databases they need to submit SQL scripts, get them reviewed, approved, and finally ran. Once they are run the DBA sends an execution log that looks like:

(12 rows affected)
(1 row affected)
(141 rows affected)
Msg 8114, Level 16, State 5, Line 21
Error converting data type varchar to bigint.
(5 row affected)
(1 row affected)

In the example above 6 queries were executed and it’s simple enough to get that the fourth had issues. Going back to the SQL script is easy to pinpoint the bad query.

It’s not easy anymore, when there are 250 queries ran on a script. It would be great to get something like:

(12 rows affected)
(1 row affected)
-- Step #1 completed
(141 rows affected)
Msg 8114, Level 16, State 5, Line 21
Error converting data type varchar to bigint.
(5 row affected)
-- Step #2 completed
(1 row affected)