2

The line cmd.ExecuteNonQuery();

cmd.CommandText

CREATE TRIGGER  subscription_trig_0  ON  subscription   AFTER INSERT  AS  UPDATE user_data SET msg_count=msg_count+1 FROM user_data
JOIN INSERTED ON user_data.id = INSERTED.recipient;

The exception:

Incorrect syntax near the keyword 'TRIGGER'.

Then using VS 2010, connected to the very same file (a mdf file) I run the query above and I get a success message. WTF!

Improve this question
1
  • @The edits: I prefer to have the WTF tag and i rather not fix spacing since i want to show the exact sql Commented May 19, 2010 at 19:59

3 Answers 3

Reset to default
2

Options

  • The 1st line of the actual SQL sent is not CREATE TRIGGER
  • CommandType is wrong (eg it's trying to add EXEC or some "prepare" commands)

Use SQL profiler to see exactly what you're sending on to the DB engine (you actually have Express edition that is hosting the MDF)

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

1 Comment

I'm confused. What do you mean by "The 1st line of the actual SQL sent is not CREATE TRIGGER"? CommandType is set to TEXT. How do i use/where is SQL profiler ? So far i had 0 luck and >5 threads about problems with MS, SQL and IDEs. But anyways, i still dont understand the solution and may not understand while looking at the profiler.
1

Do you have CommandType set wrong?

Improve this answer

7 Comments

it set to text. Should it not be for triggers?
Text is correct for what you are doing. Please post all the C# code that creates this trigger.
Its pretty ugly since there are regex.replace. and as i said the syntax works in visual studios. Maybe i'll try using a fresh sqlcommand object.
I havent completely tested it but it appears that does solve the problem. I cant imagine .parameters are affecting it when there are no @0 in the cmd text. OMG i just check it. It does. Theres nothing that refers to the param in the query and it explodes with a completely irrelevant error msg WTF.
The moment you add a @parameter to a command, its invocation changes from "T-SQL Batch" type to "RPC", which are different types on the TDS protocol (see freetds.org/tds.html#requests). You don't specify what cmd is, each client type treats the distinction differently (ODBC, OleDB, SqlClient). Most notably, the OleDB client adds an EXEC in front of the requests and the result is incorrect syntax usually. As obscure subject as this may be, a developer worth its salt knows it, if not from spec then from experience.
|
0

I am not sure why this is failing but if I were you I would use SMO for ddl queries. In this case you need Trigger.Create Method.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.