SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 UPDATE Not Triggered in Stored Procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bigsqldude
Starting Member

3 Posts

Posted - 11/14/2012 :  08:34:02  Show Profile  Reply with Quote
Hi All,

I have a Stored Procedure which is triggered to import/process a bunch of data from an import table. Within the SP is a UPDATE statement which it appears to not get triggered, however all the other statements within the stored procedure run fine.

At the moment if I want this UPDATE statement to run, I have to open the stored procedure within the SQL Server Management tool highlight the UPDATE statement and execute it. All checks show that the UPDATE statement has done what I expected it to do.

Any ideas why this could be happening?

PS. This relates to SQL Server 2008 Enterprise Edition.

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 11/14/2012 :  08:39:24  Show Profile  Reply with Quote
You'd have to show the stored procedure in order to get some help here...probably there is some kind of logic in the procedure that prevents it from running.

- Lumbago
My blog-> http://thefirstsql.com
Go to Top of Page

bigsqldude
Starting Member

3 Posts

Posted - 11/14/2012 :  08:46:42  Show Profile  Reply with Quote
quote:
Originally posted by Lumbago

You'd have to show the stored procedure in order to get some help here...probably there is some kind of logic in the procedure that prevents it from running.

- Lumbago
My blog-> http://thefirstsql.com



Here is the UPDATE code:

-- Errors
UPDATE Feed SET Feed.RowsRejected = e.Errors
FROM
(SELECT FeedId, SUM(num) Errors
FROM
(SELECT FeedId, ErrorDuplicate + CASE WHEN ErrorFormat = 1 OR ErrorQuery = 1 THEN 1 ELSE 0 END num
FROM ImportErrorSubscriberNumbers) e
GROUP BY FeedId) e
WHERE e.FeedId = Feed.Id
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/14/2012 :  09:13:14  Show Profile  Reply with Quote
Possibly some type of flow control statement within the stored procedure that causes the update statement to be bypassed.
Go to Top of Page

bigsqldude
Starting Member

3 Posts

Posted - 11/14/2012 :  10:20:49  Show Profile  Reply with Quote
quote:
Originally posted by bigsqldude

quote:
Originally posted by Lumbago

You'd have to show the stored procedure in order to get some help here...probably there is some kind of logic in the procedure that prevents it from running.

- Lumbago
My blog-> http://thefirstsql.com



Here is the UPDATE code:

-- Errors
UPDATE Feed SET Feed.RowsRejected = e.Errors
FROM
(SELECT FeedId, SUM(num) Errors
FROM
(SELECT FeedId, ErrorDuplicate + CASE WHEN ErrorFormat = 1 OR ErrorQuery = 1 THEN 1 ELSE 0 END num
FROM ImportErrorSubscriberNumbers) e
GROUP BY FeedId) e
WHERE e.FeedId = Feed.Id



Do I need to paste the whole procedure do you think ? I am reluctant to share it all.

Edited by - bigsqldude on 11/14/2012 10:27:06
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/14/2012 :  10:49:08  Show Profile  Reply with Quote
You don't have to; you can debug it yourself as follows: Insert a few print statements at points from the beginning of the stored proc to just before the update statement is called. Then run the stored proc from an SSMS window and see which of those get printed. I suspect the one just before the update statement won't get printed.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000