executing stored procedures from within triggers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

executing stored procedures from within triggers

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-12 : 09:13:55

Jerry writes "I am running MS SQL 7 SP3 on a windows 2000 SP2 machine. I have written a stored procedure that sends emails when called. I am now trying to write a trigger that will run that procedure when a column is updated. I actually have this working on our development server. The code used is...

CREATE TRIGGER trUpdate
ON dbo.table FOR UPDATE
AS IF UPDATE(Table_Status)
BEGIN
DECLARE @status_old TINYINT
DECLARE @status_new TINYINT
DECLARE @Table_Id INT
SELECT @status_old = (SELECT Table_Status FROM deleted)
SELECT @status_new = (SELECT Table_Status FROM inserted)
SELECT @Table_Id = (SELECT Table_Id FROM deleted)
IF (@status_old <> 16 ) AND (@status_new = 16)
BEGIN
EXEC prc_MailProc @intID = @Table_Id
END
END

...This works fine on the development server, but not on production. In fact, any trigger I write that executes a stored procedure on our production server fails. I have searched in vain for a setting to change. Any help you could give would be great."

jackstow
Posting Yak Master

160 Posts

Posted - 2002-02-12 : 11:25:44

This could be someting to do with permissions.. i.e. does the owner of the table that the trigger is related to have the necessary rights to execute the stored procedures?

Jack

KoCo
Starting Member

4 Posts

Posted - 2004-04-06 : 07:13:52

quote:
Originally posted by AskSQLTeam

Jerry writes "I am running MS SQL 7 SP3 on a windows 2000 SP2 machine. I have written a stored procedure that sends emails when called. I am now trying to write a trigger that will run that procedure when a column is updated. I actually have this working on our development server. The code used is...

CREATE TRIGGER trUpdate
ON dbo.table FOR UPDATE
AS IF UPDATE(Table_Status)
BEGIN
DECLARE @status_old TINYINT
DECLARE @status_new TINYINT
DECLARE @Table_Id INT
SELECT @status_old = (SELECT Table_Status FROM deleted)
SELECT @status_new = (SELECT Table_Status FROM inserted)
SELECT @Table_Id = (SELECT Table_Id FROM deleted)
IF (@status_old <> 16 ) AND (@status_new = 16)
BEGIN
EXEC prc_MailProc @intID = @Table_Id
END
END

...This works fine on the development server, but not on production. In fact, any trigger I write that executes a stored procedure on our production server fails. I have searched in vain for a setting to change. Any help you could give would be great."

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-06 : 07:21:35

What are you trying to say here KoCo?

Duane.

KoCo
Starting Member

4 Posts

Posted - 2004-04-06 : 07:45:49

Oops. Previous reply without my comment.

I'm having a similar problem. Calling a procedure from within a trigger fails - when performed on the production server (remote sql server).
Calling from within another procedure causes no problem. Neither when using trigger on development system (local sql)

My exact situation:
(1) Access2000-project as GUI connecting to SQL database via ADO
(2) Development system: local SQL7.0 SP3 on W2K Pro SP4
(3) Production system: remote SQL7.0 SP3 on W2K Server SP4

All SQL-objects have dbo as owner.
No special rights defined; adding exec rights for user public for called procedure didn't help.

If anything isn't clear, please reply or email.

MatrixOne
Starting Member

11 Posts

Posted - 2004-04-06 : 10:16:41

Any differences in Collation?

I have had problems running distributed transactions-
Namely Insert / Execute statements using remote Stored Procs across 2 servers with differeing collations or between 7.0 and 2000.

Although that said, i never got an error msg, just query timed out (but infact the process still runs as an orphaned process which can only be killed by rebooting SQL or using Kill with a Work ID instead of SPID.)

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-04-06 : 16:56:32

I'm surprised nobody mentioned to the original poster that their trigger code does not handle the fact that many records can be updated by one statement, and thus they will mave multiple values returned from their SELECT statements, not just one.

Now, to KoCo's situation... Do you really want to call another stored procedure from within your trigger? Have you evaluated the performance impact and looked at other options. Using the example from the original Ask SQLTeam Post, I would suggest to that person they should just insert a row into a Mail Queue table and have another process, such as a scheduled job, that processes the contents of the Mail Queue outside of the initial transaction that triggered it all. Otherwise the initial transaction is not complete until the final sproc is completed.

Also, have you looked into whether the called sproc is making changes that are also firing triggers of its own? Is your server setup to allow nested and/or recursive triggers? Are you connecting to the dev and production servers using identical credentials with identical permissions inside?

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

KoCo
Starting Member

4 Posts

Posted - 2004-04-07 : 05:09:18

Mark,

My mean reason to call the sproc from the trigger is to avoid duplicating code that's used from multiple locations in the client application.
All the stored procedure does is updating an existing row in another table tblZ, based on a key-value in the triggering table tblX; or inserting a new row if the key doesn't exist yet in tblZ.
Would there be a significant performance gain copying the sproc code into the trigger?

I appreciate your suggestion about performance concerns and handling multiple rows in the trigger. I will keep them in mind on further development.
But it didn't answer my basic question: why everything works fine on a local sql installation while failing if called from a trigger on a remote server?

Concerning permissions: on the prod server all users are mapped to dbo user. I don't like this situation, but can't change it for the moment because an older application is still using the same database.
Anyway, all users being dbo, I tought they should be able to do about anything on the prod server?

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-04-07 : 19:05:14

Well, it depends on what you mean by "everyone is mapped to dbo user". Are you saying that there are multiple SQL Server logins that are all mapped to the a user named dbo within the database? Or are you saying that all users have been added to the db_owner or sysadmin (I hope not this one) roles? It's possible to have multiple users and yet have all of your objects owned by 'dbo', which is good practice. Have you logged into the production server as a sysadmin and tested it, that should do away with most any possible issue of permissions. If it works as a sysadmin, then you definitely need to look into permissions conflicts.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

KoCo
Starting Member

4 Posts

Posted - 2004-04-14 : 08:58:32

Mark,

To be clear: all sql server logins are mapped to a user in the database; every user is added to the db_owner role.
I tried logging into the production server using a login with sysadmin rights. The problem stays the same.

Hereafter you'll find the exact error popup screen:

> There was a problem accessing a property or method of the OLE object
>
> Try any of the following:
> * Verify that the OLE server was registerd correctly by reinstalling it
> * Make shure your computer is connected to the server on wich the OLE server application resides
> * Close the OLE server and restart it outside of {application name}. Then try the original operation again from within {application name}

I don't find how to correct, except by replacing the procedure-call by its t-sql code.
Can anybody help?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources