sqlbulkcopy with FIRE_TRIGGERS?

[Guest]

I am copying a large amount of data into SqlServer using the new SqlBulkCopy class with Whidbey. It works fine.

But -- the insert trigger on the table that SqlBulkCopy is sending data to is not running. If I insert with a regular INSERT query the trigger fires...but if I do the insert with SqlBulkCopy it does not.

I understand that one can normally add a 'hint' to SqlBulkCopy/Insert commands (FIRE_TRIGGERS) if writing the query out manually that will tell the SqlServer to run Insert triggers.

Is there a way to turn this hint on using the SqlBulkCopy class?

Thanks in advance!

 

[William Ryan eMVP]

There's an enum SqlBulkCopyOptions which has a .FireTriggers value (16) but
I haven't been able to find where to set it. I could have sworn I saw this
in intellisense but I can't find a property to set it to right now. Let me
look on the last Whidbey build I have b/c I remember reading the values from
intellisense.

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups

I am copying a large amount of data into SqlServer using the new

SqlBulkCopy class with Whidbey. It works fine.

But -- the insert trigger on the table that SqlBulkCopy is sending data to

is not running. If I insert with a regular INSERT query the trigger
fires...but if I do the insert with SqlBulkCopy it does not.

I understand that one can normally add a 'hint' to SqlBulkCopy/Insert

commands (FIRE_TRIGGERS) if writing the query out manually that will tell
the SqlServer to run Insert triggers.

 

[Guest]

Thanks a bunch -- I didn't notice that enum before.

You can set the SqlBulkCopyOptions enum value to SqlBulkCopy in one of the overloaded constructors. Looks like this should work fine.

Thanks again!
- Z