How do I configure Fire Triggers and Check Constraints in SqlBulkInsert?

ado.net sqlbulkcopy sql-server-2005

Question

I'm utilizing a database user with restricted access to bulk insert data into an MS SQL 2005 database from a C# function using an ADO.NET 2.0 SqlBulkCopy object. I see the following issue while attempting to conduct the operation:

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.

After reading some documentation, I used the constructor that allows me to provide these items to build the bulk copy object:

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

But nothing changes – I still get the same problem notice. Without success, I tried tinkering with some of the other SqlBulkCopyOptions variables. Am I missing anything since I honestly believed that this would solve the issue?

After giving my user permission to ALTER the table, I tried the method, and it worked. But in my case, it is not an option.

1
15
12/17/2008 3:15:58 AM

Accepted Answer

Finished it! It seems that I need to brush up on flag enums. When I should have been ORing the enum values, I was bitwise ANDing them.

SqlBulkCopyOptions.FireTriggers & SqlBulkCopyOptions.CheckConstraints

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

SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.CheckConstraints

worked as intended and enabled the bulk insert to finish.

25
12/17/2008 2:28:14 PM

Popular Answer

Only possibilities, I'm sorry.

Three situations in which ALTER TABLE is required are listed in the SQL documentation for BUCKING INSERT. You mentioned two of them. Is the KeepIdentity checkbox selected, even if it is not required?

Another possibility is that the trigger on the table is already deactivated, which would cause confusion. UseALTER TABLE dbo.SomeTable ENABLE TRIGGER ALL to guarantee that it is enabled.



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow