Are you sure you matched the original data type and length?  For example, if the original columns was varchar(8000) and you specified varchar(4000), obviously data might get truncated.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER test_trg1
ON dbo.test
AFTER INSERT,UPDATE
AS
SET NOCOUNT ON;

IF UPDATE(field_key)
BEGIN
    INSERT INTO dbo.audit ( name, a_date, col, old_col, new_col )
  ...

I'd go with:

SELECT CONCAT(@Route, '(' + NULLIF(@USRoute, '') + ')', 
    CASE WHEN LEN(@Route) + LEN(@USRoute) = 0 THEN '' ELSE ', ' END,...

I think you would have to add a GO, otherwise if the table already exists SQL Server won't be able to "compile" the SQL:

drop table if exists...

CAST the column formula to the data type you want it to be, e.g.:

,CAST(Concat(Convert(varchar(10),Date,23) , ' ',REPLACE(REPLACE(RIGHT('0'+LTRIM(RIGHT(CONVERT(varchar(8),time,100),7)),7),'AM',' AM'),'PM',' PM')) AS datetime) as DateTime

Maybe this instead:

SELECT

DENSE_RANK() OVER (ORDER BY QuestionCategoryID) CategoryNumber,

...

SELECT whatever
FROM dbo.table_name
WHERE date_column BETWEEN CAST(YEAR(GETDATE()) - 1 AS varchar(4)) + '07' AND
    CAST(YEAR(GETDATE() AS varchar(4)) + '06'

If you need absolutely sequential numbers, no gaps, then you could use ROW_NUMBER() to generate a value to be added to the MAX() value determined before the INSERTs.

DECLARE @max_ID int

SELECT...

Select custnbr 
from #plist
group by custnbr
having sum(case when prlist like 'pli%' then 1 else 0 end) = 1 and
    sum(case when prlist like '[ac]b%'...

Do you mean you can't use a SEQUENCE?  I can't imagine why.  I use those all the time when for whatever reason an identity is not appropriate.  And there's a...

First I would think would be to try this:

GRANT SELECT ON dbo.view_name TO [domain\account];

GRANT SELECT ON dbo.view_name2 ...

Use a table to store the date ranges, then join to that.

DROP TABLE IF EXISTS #DATES;
CREATE TABLE #DATES (
    VARIABLEA date NOT NULL,
 ...

You're also going to need to do something about the:

ar.Account_B__c as Account_ID,

a.SalesOrg__c as SalesOrg,

columns.  They are not in the GROUP BY and they are not within an aggregate function, so...

Best would be not to shrink the main db at all.  If possible, add a secondary filegroup, create the temp tables in there, then shrink only the file(s) in that...

You're good there, so that's not part of the issue.