Translating a query from MS Access to SQL Server
I think Binaya Regmi is correct. When deleting in SQL-Server explicitly name the table you want to delete the rows from - especially necessary when using a join. 

So in your case change the top row from 

DELETE category.*


to

DELETE category