SqlCommand cannot drop triggers

Question

I am creating test methods in .Net that needs to alter a database for setting testing scenarios. We use SqlCommands to send queries to alter a database, the connection strings for this are trusted connection strings. All DROPS and ALTER queries have been working fine for Tables, Columns, Constraints, etc. But when I try to DROP a Trigger, I get the following error.

SQL EXCEPTION: 'Cannot drop the trigger 'dbo.TRIG_Application_Insert', because it does not exist or you do not have permission.'

The query is DROP TRIGGER [dbo].[TRIG_Application_Insert] and it works because it has been tested directly in SQL Management Studio, and is used inside a script that adds the triggers to the database, which is invoked via sqlcmd tool.

Could this be a permission issue or does the query needs to be different when used from the .Net SqlCommand?

I have tried to use a standard security connection string for sa user and for a db user with high privileges and still getting the same error.

Answer 1

Since your Initial Catalog is master your command is running against the master database. You're trigger is not in that database your command fails. You need to either set the Initial Catalog to the database you want to connect to our specify the database in your command. So if you database is MyDatabase use either:

Data Source=(local)\SQLEXPRESS2012;Initial Catalog=MyDatabase;Integrated Security=True 

Or

DROP TRIGGER [MyDatabase].[dbo].[TRIG_Application_Insert]