SqlBulkInsert - How to set Fire Triggers, Check Constraints?

Question

I'm performing a bulk insert with an ADO.NET 2.0 SqlBulkCopy object from a C# method into a MS SQL 2005 database, using a database user with limited permissions. When I try to run the operation, I get the error message:

Bulk copy failed. User does not have ALTER TABLE permission on table 'theTable'. ALTER TABLE permission is required on the target table of a bulk copy operation if the table has triggers or check constraints, but 'FIRE_TRIGGERS' or 'CHECK_CONSTRAINTS' bulk hints are not specified as options to the bulk copy command.

I read some documentation and created the bulk copy object with the constructor that lets me specify such things:

    SqlBulkCopy bc = new SqlBulkCopy(
        System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"],
        SqlBulkCopyOptions.FireTriggers & SqlBulkCopyOptions.CheckConstraints);

But this doesn't change anything - I get the same error message as before. I tried fiddling with some of the other SqlBulkCopyOptions values but no luck. I really thought this would fix the problem, am I missing something?

I tested the procedure after granting ALTER on the table to my user, and the operation succeeded. However this is not an option for my situation.

Answer 1

Possibilities only, I'm sorry

SQL documentation for BULK INSERT specifies 3 cases where ALTER TABLE is needed. You listed 2 of them. Is the KeepIdentity option being set, even if not needed?

Another option is that the trigger on the table is disabled already, confusing the issue. Use ALTER TABLE dbo.SomeTable ENABLE TRIGGER ALL to ensure enabled.

Answer 2

Solved it! Looks like I need a refresher on flags enums. I was bitwise ANDing the enum values when I should have been ORing them.

SqlBulkCopyOptions.FireTriggers & SqlBulkCopyOptions.CheckConstraints

evaluates to zero (which is equivalent to SqlBulkCopyOptions.Default.)

SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.CheckConstraints

Worked correctly and allowed the bulk insert to complete.

Answer 3

Thanks for this solution... but insert trigger defined on my target table - should fire for EACH record and this with this solution - trigger fires after ALL records inserted to that table - finally get the error - more than one rows returned! so how can I get bulk insert and trigger fires for each row!

regards Jeet