SqlBulkCopy error in staging only

We are getting the following error (only on the staging server) when we use SqlBulkCopy. The stored procedure has execute permissions. And they are working properly in our test environment. So what is the setting to be corrected in stage environment to work开发者_开发技巧 it properly? Please share your thoughts.

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.

It is hard to tell exactly since you don't provide any SP/Table definition but it seems that you need to specify SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.CheckConstraints to get it to work... and/or give the user ALTER TABLE permission... if this works in one environment and not in another one then there must be a difference either in the SP/Table definitions and/or the user permissions...

Root cause for the given problem is ALTER TABLE permission is required. We can resolve the problem by one of the below 2 solutions.

Either simply grant ALTER TABLE permission to the specified user group or follow the instructions

• Constraints are disabled, which is the default behavior. To keep constraints enabled, use the -h option with the CHECK_CONSTRAINTS hint
• Triggers are disabled, which is the default behavior. To fire triggers, use the -h option with the FIRE_TRIGGERS hint.
• Use the -E option to import identity values from a data file.

The error seems pretty clear to me. In your staging environment the user that you are executing the SQL commands as does not have ALTER TABLE permission on the table you are bulk loading into. Based on the phrasing of your question, presumably there is some other environment(s) which are working. In those environments the user does have ALTER TABLE access.

Alternatives, as specified in the error message, include issuing FIRE_TRIGGERS and/or CHECK_CONSTRAINTS options with your bulk load command. See here for more info on how to do that: http://msdn.microsoft.com/en-us/library/aa225968(v=sql.80).aspx