Incorrect syntax near ')'.

Question

I have a BasicMSI project (Installshield 2009) that runs a SQL script during the installation process. During the installation I receive the following error.

Error 27506.Error executing SQL script {SCRIPTNAME}. Line 352. Incorrect syntax near ')'. (102)

The problem is that I don't have any ')' at line 352 of the script and also the script works without any problems if I run it with SQL Management Studio Express.

Can anyone tell me what is the problem and how can I fix it? Thanks.

PS. I cannot set the script error handling option to "On Error, Goto Next Statement" because therefor it will not create some of my foreign keys.

IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TRIGGER_NAME]'))
EXEC dbo.sp_executesql @statement = N'
CREATE TRIGGER [dbo].[TRIGGER_NAME]
ON [dbo].[TABLE_NAME]                  -- LINE: 352
INSTEAD OF INSERT
AS
BEGIN 
DECLARE @Count INT;
SET @Count = (SELECT COUNT([Name]) 
    FROM TABLE_NAME
    WHERE IsDeleted = 0 AND [Name] IN (SELECT [Name] FROM INSERTED));

IF @Count > 0
BEGIN
 RAISERROR (''Error Message.'', 16, 1);
 Rollback; 
END
ELSE
BEGIN
 INSERT INTO dbo.TABLE_NAME SELECT {Columns} FROM INSERTED;
 SELECT CONVERT(BigInt,SCOPE_IDENTITY()) AS [value]
END
END
' 
GO

Answer 1

It seems reasonable that this error might occur if you've written an IN statement, which you populate programmatically, only at runtime some values are missing, resulting in a statement saying "... WHERE x IN()", which is invalid.

This would generate that error, and also, it is an error that could easily appear in one environment but not another. It is hard to give more detail than that without actually seeing the script.