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.

Not that this answers your question, but SQuirrel SQL is not nearly as good as Quest Toad (also has a freeware version) IMO. You may want to give it a crack to save the headache! toadsoft.com/toadsqlserver/toad_sqlserver.htm — Aaron Alton
– Aaron Alton, Commented May 28, 2009 at 4:43

Beto · Accepted Answer · 2009-06-04 17:26:14Z

2

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 ('').

answered Jun 4, 2009 at 17:26

Beto

Sign up to request clarification or add additional context in comments.

1 Comment

BordrGuy108 Over a year ago

Awesome. Thank you very much for your answer and the precise summary (it worked just like you said).

Cade Roux · Accepted Answer · 2009-05-28 03:59:35Z

0

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.

answered May 28, 2009 at 3:59

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

2 Comments

BordrGuy108 Over a year ago

I read about that when I first received the described error message, but as the query I was trying to execute was literally just that query I posted above, shouldn't it execute fine as the CREATE TRIGGER statement is the first (and only) statement in the batch?

Cade Roux Over a year ago

I think SQuirrelSQL is putting something else in the batch first. Use the SQL Profiler to see what it's actually sending. And then see if you can disable that in SQuirrel SQL or submit a bug report.

Ed Harper · Accepted Answer · 2009-05-28 11:29:13Z

0

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).

answered May 28, 2009 at 11:29

Ed Harper

21.6k5 gold badges60 silver badges85 bronze badges

1 Comment

BordrGuy108 Over a year ago

Interesting. I am also using SQuirrel SQL v2.6.8 and SQL Server 2005, but instead of the driver you mentioned I am using the Microsoft SQL Server JDBC Driver 2.0. Maybe the driver is to blame?

Collectives™ on Stack Overflow

Issues Creating SQL Server triggers using SQuirreL SQL Client

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related