question

Manuel Coca avatar image
Manuel Coca asked

clr store procedure call from trigger.

I have a problem with CLR stored procedure on SQL Server 2005. I create a stored procedure that opens an ODBC connection and inserts a record in other db (informix). It works fine when I call the store procedure directly. But I put it inside a trigger, and don't work (also make hardcode call of the sp), the problem is in connection.Open(). I probe it on another server and all works fine. The only difference between the servers is that one is 32 bits and the other 64bits (of course the Informix's driver too). Both sql server version are 2005.

sql-server-2005stored-procedurestriggerclr
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Matt Whitfield avatar image
Matt Whitfield answered

It's entirely likely that you need to specify an EXECUTE AS clause for your trigger. For more information, check out the MSDN documentation on EXECUTE AS.

Basically, EXECUTE AS specifies under what security context a trigger will run. It defaults to CALLER - which means that whichever database principal executes the statement which causes the trigger to run would give their security context to the trigger. You haven't stated how the operation which fires the trigger is being run, so this may not apply to you, apologies if it doesn't.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.