none
Getting "[Modify] Could not get required interface" Error on SQL MA Using INSTEAD OF Trigger RRS feed

  • Question

  • I have a SQL MA that is connected to two views (one for main view for normal import/export, the other for Delta import).  The main view joins two tables.  There is an INSTEAD OF trigger on the main view, to allow updates back to a specific base table (which is included in the join of the main view). 

    I had thought this would allow the SQL MA to write updates back to the base table. I verified I can update the view (and thus, the base table via the INSTEAD OF trigger) using the same credentials configured for the SQL MA in SQL Server Management STudio. 

    However, when I try to run an export from the SQL MA, I get cd-error for each entry it is trying to export.  Here are the errors:

    Connected data source error code: 0x80230808

    Connected data source error: [Modify] Could not get required interface

    I was reading some things about possible some issue with OLE DB (which I understand SQL MA is based on) not being able to "see INSTEAD OF triggers" or something like that.  Has anyone else implemented updateable views using INSTEAD OF triggers in conjunction with the SQL Management Agent?  I am on version 4.1.3441.0.

    Any insight here? Thanks!

    Tuesday, July 16, 2013 10:10 PM

All replies

  • I suggest looking at a custom ECMA based management agent for this. We have had to create MA's in past where the out of the box MA's did not fulfill our needs. You can look at http://technet.microsoft.com/en-us/library/hh859461(v=ws.10).aspx for a sample of a SQL MA based of ecma and adapt it to suit your needs.


    Visit My Blog: http://theidentityguy.blogspot.com/

    Wednesday, July 17, 2013 8:10 AM
  • Thank you...I'll have to do that unless anyone knows of a way to get a OOB SQL MA to work in my scenario (i.e. updateable view using INSTEAD OF trigger).

    Has anyone else been successful using the approach I am currently trying to implement?

    Wednesday, July 17, 2013 4:35 PM
  • Hi Jon,

    I have yet to get an update-able view work reliably.. My biggest issue is usually around adding new objects and updating the indexes...


    Visit My Blog: http://theidentityguy.blogspot.com/

    Thursday, July 18, 2013 1:28 AM