Issues Creating SQL Server triggers using SQuirreL SQL Client

Question

Recently I have been working with a SQL Server database and I was trying to create some triggers for some tables using SQuirreL SQL Client and for some reason I was unable to get rid of the following pesky error message:

"Error: 'CREATE TRIGGER' must be the first statement in a query batch. SQLState: S0001 ErrorCode: 111"

The query I was attempting to execute was the following (I started out with a really simple trigger to make sure the syntax was correct):

CREATE TRIGGER meeting_overlap on adhoc_meeting
FOR INSERT
AS
DECLARE
    @myvar INT

When I attempted to execute my exact same query in Microsoft SQL Server Management Studio Express it executed fine. My question is: has anyone else run into similar problems using SQuirreL SQL Client and if so, what did you do to get rid of this error?

EDIT:

I am using SQuirrel SQL v2.6.8 with the Microsoft SQL Server JDBC Driver 2.0 and I am connecting to SQL Server 2005.

Answer 1

Usually you get that error when attempting to run multiple statements in the same batch that are not allowed to be anything but the first statement in a batch. You may need a GO in between them. GO is not a SQL command but really a directive to the client tool to separate batches of commands.

Answer 2

I couldn't replicate this on SQuirrel SQL v2.6.8 using v1.2.2 of the jTDS jbdc driver against SQL 2005 SP3 (developer edition).

Answer 3

I was having the same problem. After some Googling, I found this article:

http://vsingleton.blogspot.com/2009/04/error-create-view-must-be-first.html

In short, wrap exec('') around your create trigger statement. In addition, any single quotes (') you have in the trigger statement need to be changed double single quotes ('').