3

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.

Mauricio Quintana
  • 391
  • 1
  • 4
  • 13
  • 1
    No you do not need a different statement. Are you sure that you have an Initial_Catalog set on your connection string? If not your command isn't running against the correct database. – shf301 Aug 06 '15 at 17:58
  • When you run the script to add the trigger, do you then go into SQL Management Studio and see the created trigger or is this in a transaction that gets rolled back? – JeffO Aug 06 '15 at 18:06
  • @shf301 my connection string is Data Source=(local)\SQLEXPRESS2012;Initial Catalog=master;Integrated Security=True – Mauricio Quintana Aug 06 '15 at 18:19
  • @JeffO each test regenerates the database to have a clean instance on each tests and then this instance is modified; I try my queries directly on the SQL Management Studio (in a clean db instance) before writing them on the .Net code . in the Management Studio, the queries work just fine, they do drop the Trigger as expected – Mauricio Quintana Aug 06 '15 at 18:23
  • Running the DROP TRIGGER on SQL Management Studio works since the required database is selected for the new query. In the lower right of the query window appears the database against the query is being run. – Mauricio Quintana Aug 06 '15 at 19:01

2 Answers2

4

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]
shf301
  • 31,086
  • 2
  • 52
  • 86
  • Specifying the database for the drop trigger throws the exception: SQL EXCEPTION: ''DROP TRIGGER' does not allow specifying the database name as a prefix to the object name.' – Mauricio Quintana Aug 06 '15 at 18:30
  • Setting initial catalog to MyDatabase (the corresponding name in my case) solves the problem. Although i think this might generate troubles if trying to use the same connection string to alter a different database (which I happen to do in some other tests), but that can be sorted out. – Mauricio Quintana Aug 06 '15 at 18:35
2

It looks like you may be hitting the wrong Database since you are saying you use accounts with elevated rights. I would reccomend adding a USE to your script to make it hit the correct Db. It is good practices anyway.

USE <database name>
DROP TRIGGER [dbo].[TRIG_Application_Insert]
Wes Palmer
  • 880
  • 4
  • 15
  • This is a simpler solution, at least in my specific case. I still need to use my initial catalog or database as master for the rest of my code to work and it does not require a lot of changes into my code. – Mauricio Quintana Aug 06 '15 at 18:55