Q
Problem solve Get help with specific problems with your technologies, process and projects.

Problem attaching stored procedure and SQL Server trigger

Learn why using the sp_OACreate stored procedure with a SQL Server 2005 trigger, is likely to result in a permissions problem.

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?

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 last published in December 2007

Dig Deeper on Microsoft SQL Server 2005

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close