Error: 'CREATE VIEW' must be the first statement in a query batch.or maybe
SQLState: S0001
ErrorCode: 111
Error: 'CREATE TRIGGER' must be the first statement in a query batch.Well, do not worry. Here is how you can solve this issue: Just wrap your query with exec(''). For example, lets say your view is defined as
CREATE VIEW theView AS SELECT tableA.id + '_' tableA.name AS ID_NAME, tableB.* FROM tableA a INNER JOIN tableB b ON a.a_id = b.b_IdWell then just change your call as follows:
exec('CREATE VIEW theView AS SELECT tableA.id + ''_'' tableA.name AS ID_NAME, tableB.* FROM tableA a INNER JOIN tableB b ON a.a_id = b.b_Id')Notice that single quotes origninally in my CREATE statement are escaped as two consecutive single quotes after it is wrapped in the call to exec('').
You will get the error above, as I did, when executing the CREATE from within SQuirreL SQL, so apparently SQuirreL is attempting to do some 'innocent' things before the CREATE statement. Wrapping the CREATE in an exec forces SQuirreL to put the CREATE statement first in a separate execution plan.
Hope that helps you.
thanks this helped
ReplyDeleteIt helped me too. Thanks.
ReplyDeleteDitto.
ReplyDeletealso you could just add a GO before so that it becomes the first statment in the querry batch
ReplyDeleteInserting the GO can work, but can also cause problems in a larger deploy script (like that created by .database projects in VS) as it can cause unexpected ends to scripts generated assuming that there are not GOs in the code.
ReplyDelete