Stored Procedure Does Not Fire Last Command

Question

On our SQL Server (Version 10.0.1600), I have a stored procedure that I wrote.

It is not throwing any errors, and it is returning the correct values after making the insert in the database.

However, the last command spSendEventNotificationEmail (which sends out email notifications) is not being run.

I can run the spSendEventNotificationEmail script manually using the same data, and the notifications show up, so I know it works.

Is there something wrong with how I call it in my stored procedure?

[dbo].[spUpdateRequest](@packetID int, @statusID int output, @empID int, @mtf nVarChar(50)) AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @id int
    SET @id=-1
    -- Insert statements for procedure here
    SELECT A.ID, PacketID, StatusID
    INTO #act FROM Action A JOIN Request R ON (R.ID=A.RequestID)
    WHERE (PacketID=@packetID) AND (StatusID=@statusID)

    IF ((SELECT COUNT(ID) FROM #act)=0) BEGIN -- this statusID has not been entered. Continue

        SELECT ID, MTF
        INTO #req FROM Request
        WHERE PacketID=@packetID

        WHILE (0 < (SELECT COUNT(ID) FROM #req)) BEGIN
            SELECT TOP 1 @id=ID FROM #req
            INSERT INTO Action (RequestID, StatusID, EmpID, DateStamp)
            VALUES (@id, @statusID, @empID, GETDATE())
            IF ((@mtf IS NOT NULL) AND (0 < LEN(RTRIM(@mtf)))) BEGIN
                UPDATE Request SET MTF=@mtf WHERE ID=@id
            END
            DELETE #req WHERE ID=@id
        END
        DROP TABLE #req

        SELECT @id=@@IDENTITY, @statusID=StatusID FROM Action

        SELECT TOP 1 @statusID=ID FROM Status
        WHERE (@statusID<ID) AND (-1 < Sequence)

        EXEC spSendEventNotificationEmail @packetID, @statusID, 'http:\\cpweb:8100\NextStep.aspx'

    END ELSE BEGIN

        SET @statusID = -1

    END

    DROP TABLE #act

END

Idea of how the data tables are connected:

my database

You could probably rewrite almost all of it in a set based way. Also `TOP 1` with no order by and `@@IDENTITY` look suspect. What does `spSendEventNotificationEmail` do? Does it end up calling `sp_send_dbmail` if so could it be a permissions issue? Are you checking the return value of that procedure? — Martin Smith, Jun 21 '12 at 21:39
It seems correct. Could it be a permissions issue due to the user calling the outer stored procedure? — Pablo Romeo, Jun 21 '12 at 21:39
OK, I'm a C# developer and not an SQL guy. I don't really know what this *set based way* is, but I'm open to input. Also, how do I check the permissions? Both procedures are located on the same server and in the same database. — , Jun 21 '12 at 21:42
If you do a lot of imperative programming (c#) you think in loops (while/for/..) Sql is a declarative language meaning that you tell *what* you want instead of telling *how*. There is no fine distinction between imperative and declarative though, its a spectrum. First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column. More info if you google on RBAR which stand for 'Row By Agonizing Row'. I'm trying to clarify set based thinking here and not commenting on your sproc in casu. — buckley, Jun 22 '12 at 21:06
Some good links (more on google) http://www.sqlservercentral.com/Forums/Topic642789-338-1.aspx http://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/ http://stackoverflow.com/questions/1687512/rbar-vs-set-based-programming-for-sql — buckley, Jun 22 '12 at 21:06

buckley · Accepted Answer · 2012-06-22T21:13:45.847

From your comments I get you do mainly C# development. A basic test is to make sure the sproc is called with the exact same arguments you expect

PRINT '@packetID: ' + @packetID
PRINT '@statusID: ' + @statusID
EXEC spSendEventNotificationEmail @packetID, @statusID, 'http:\\cpweb:8100\NextStep.aspx'

This way you 1. know that the exec statement is reached 2. the exact values

If this all works than I very good candidate is that you have permission to run the sproc and your (C#?) code that calls it doesn't. I would expect that an error is thrown tough.

A quick test to see if the EXEC is executed fine is to do an insert in a dummy table after it.

Update 1

I suggested to add PRINT statements but indeed as you say you cannot (easily) catch them from C#. What you could do is insert the 2 variables in a log table that you newly create. This way you know the exact values that flow from the C# execution.

As to the why it now works if you add permissions I can't give you a ready answer. SQL security is not transparent to me either. But its good to research yourself a but further. Do you have to add both guest and public? It would also help to see what's going inside spSendEventNotificationEmail. Chances are good that sproc is using a resource where it didn't have permission before. This could be an object like a table or maybe another sproc. Security is heavily dependent on context/settings and not an easy problem to tackle with a Q/A site like SO.

I call the stored procedure `spUpdateRequest` from my code, so I'm not sure how to see what these `PRINT` statements return. If it helps, my code was originally calling `spSendEventNotificationEmail` after making the update request, but it seemed more logical to have the original call make the secondary call. If I can't get it to work, I'll go back to my old technique. — , Jun 21 '12 at 22:01
I don't know how to do some of the things you're suggesting, buckley, and I don't understand why the update procedure works when it does not have any profiles granted to execute it. However, I added the two accounts `guest` and `public` to the email script, and it now works. I just don't understand **why**. — , Jun 22 '12 at 17:53
Added an update to my answer. Not a clear answer but maybe it helps. — buckley, Jun 22 '12 at 21:14

Stored Procedure Does Not Fire Last Command

1 Answers1