Problem attaching stored procedure and SQL Server trigger

I have some questions about triggers and stored procedures. I've created a stored procedure in my database (not master) which calls a .dll file using the method sp_OACreate,sp_OAGetErrorInfo,sp_OAMethod,sp_OADestroy.

All goes well when I execute it in SQL Analyzer. So, I tried to attach this stored procedure to the trigger. When a certain column in one table changes, I want the trigger to call this stored procedure. It seems easy, but when I tried it, the SQL Server instance blocked. I got a message saying there was a permissions problem of using the method sp_OACreate. I've modified the authorization, but it still won't work.

When I execute the stored procedure and trigger separately, there's no problem. Is there any difference between these two ways, or did I lose something in my test?

    Requires Free Membership to View

    Login

If you are using SQL Server 2005, you should look to avoid using sp_OACreate, etc. Try to replace this code with managed CLR-based code instead. In terms of permissions to the object, you will need to consider the identity of the running code as well.

This was first published in December 2007

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top