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

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at [email protected].

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