This is a bit quirky, I’ve tried to create a temp table with this script

Create table tempdb.dbo.Q_TempSum13Per (Prd1 varchar(20), Per varchar(2), Lbs numeric(18,0) COLLATE SQL_Latin1_General_CP1_CI_AS)

and I got this message:

Incorrect syntax near the keyword 'COLLATE'.

after messing around with the script for a while I trurns out that having a numeric column at the end of the script caused this error. When I change the numeric to run inbetween the varchars, it seems to work fine

Create table tempdb.dbo.Q_TempSum13Per (Prd1 varchar(20), Lbs numeric(18,0), Per varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS)

Not sure why, but it worked

It appears that it doesnt want to work if the last column is a date or a bit either.

anyone know why?