N/APosted on - 09/30/2011
I have created A trigger using SQL server 2000′s Enterprise Manager (which does not suffer from the bug).
When I try to open the trigger much later using Microsoft’s SQL Server Management Studio, the trigger fails to open and I get the following message:
Script failed for Trigger ‘TEST’. (Microsoft.SqlServer.Smo)
Syntax error in TextHeader of Trigget ‘TEST’. (Microsoft.SqlServer.Smo)”
I am looking for some help in this regard which will be fully appreciated.
Syntax Error in Microsoft SQL Server
This error is caused by a documented bug in SQL. It is triggered by nested comment blocks. But you can go around this problem. You just need to get to the trigger without opening it from the Management Studio so you would not get caught up or stuck in the error. Triggers are stored in SYSCOMMENTS table and if they are longer, they can have multiple entries. You can use this script to display all tables:
SELECT a.[Name] as FunctionName
FROM sysobjects a
WHERE a.xtype = 'U'
from here, you can get to your trigger and you can print it if it is longer. When you get to the trigger, you can edit it and remove nested comment blocks You can also just simply change your header since it could be the real cause of the problem.