INSERT stored procedure does not work?

Question

Hello, I'm trying to make an insertion from one database called suspension to the table called Notification in the ANimals database. My stored procedure is this:

       ALTER PROCEDURE [dbo].[spCreateNotification] 
        -- Add the parameters for the stored procedure here
        @notRecID int,
        @notName nvarchar(50),
        @notRecStatus nvarchar(1),
        @notAdded smalldatetime,
        @notByWho int
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        -- Insert statements for procedure here
        INSERT INTO Animals.dbo.Notification 
(
NotRecID, 
NotName, 
NotRecStatus, 
NotAdded, 
NotByWho
)
values (@notRecID, @notName, @notRecStatus, @notAdded, @notByWho);
    END

The null inserting is to replenish one column that otherwise will not be filled, I've tried different ways, like using also the names for the columns after the name of the table and then only indicate in values the fields I've got. I know it is not a problem of the stored procedure because I executed it from the sql server management studio and it works introducing the parameters. Then I guess the problem must be in the repository when I call the stored procedure:

public void createNotification(Notification not)
        {
            try
            {
                DB.spCreateNotification(not.NotRecID, not.NotName, not.NotRecStatus,
                                        (DateTime)not.NotAdded, (int)not.NotByWho);

            }
            catch
            {
                return;
            }
        }

And I call the method here:

public void createNotifications(IList<TemporalNotification> notifications)
        {

            foreach (var TNot in notifications)
            {
                var ts = RepositoryService._suspension.getTemporalSuspensionForNotificationID(TNot.TNotRecID);
                Notification notification = new Notification();
                if (ts.Count != 0)
                {
                    notification.NotName = TNot.TNotName;
                    notification.NotRecID = TNot.TNotRecID;
                    notification.NotRecStatus = TNot.TNotRecStatus;
                    notification.NotAdded = TNot.TNotAdded;
                    notification.NotByWho = TNot.TNotByWho;

                    if (TNot.TNotToReplace != 0)
                    {
                        var suspensions = RepositoryService._suspension.getSuspensionsAttached((int)TNot.TNotToReplace);
                        foreach (var sus in suspensions)
                        {
                            sus.CtsEndDate = TNot.TNotAdded;
                            sus.CtsEndNotRecID = TNot.TNotRecID;
                            DB.spModifySuspensionWhenNotificationIsReplaced((int)TNot.TNotToReplace, (int)sus.CtsEndNotRecID, (DateTime) sus.CtsEndDate);
                        }
                        DB.spReplaceNotification((int)TNot.TNotToReplace, DateTime.Now);
                        createNotification(notification);
                    }
                    else
                    {
                        createNotification(notification);
                    }
                }
            }
            deleteTemporalNotifications(notifications);
        }

It does not record the value in the database. I've been debugging and getting mad about this, because it works when I execute it manually, but not when I automatize the proccess in my application. Does anyone see anything wrong with my code?

Thank you

EDIT: Added more code. It still doesn't work changing that, I mean, the procedure works if I execute it, so I don't know what could be the error. In fact, I don't get any error. Could it be a matter of writin in a table that is not in the database where you have your stored procedure?

Answer 1

Specify your column names:

INSERT INTO Animals.dbo.Notification 
(RecID, Name, RecStatus, Added, ByWho)
VALUES 
(@notRecID, @notName, @notRecStatus, @notAdded, @notByWho);

Answer 2

I would specify your column names and DONT incude the NULL at all for that column. Just let SQL Server deal with it.

INSERT INTO Animals.dbo.Notification
(
 RecID,
 [Name],
 RecStatus,
 Added,
 ByWho 
)
values (@notRecID, @notName, @notRecStatus, @notAdded, @notByWho); 

Answer 3

Run profiler when you try to run it from the application and see what values it realy is sending. That will tell you if the application is creating the correct exec statment to exec the proc.

Also it may be a permissions problem.

Answer 4

"Could it be a matter of writin in a table that is not in the database where you have your stored procedure?"

That may be the problem. You could try adding the "WITH EXECUTE AS OWNER" clause to your stored procedure so that it executes as the owner of the stored procedure. Or grant write permissions for the executing user to the table.

http://msdn.microsoft.com/en-us/library/ms188354.aspx

Answer 5

ok, I finally found out what noone realized lol. It was a very stupid error but got me really mad till I found the problem. It wasn't a problem of permissions, the problem was that I was not executing the procedure from my application, so where I wrote this:

DB.spCreateNotification(not.NotRecID, not.NotName, not.NotRecStatus,
                                        (DateTime)not.NotAdded, (int)not.NotByWho);

When I had to write:

DB.spCreateNotification(not.NotRecID, not.NotName, not.NotRecStatus,
                                        (DateTime)not.NotAdded, (int)not.NotByWho).Execute();

so as you see I was focusing my efforts in much more complex things and I wasn't even executing it...lol.

Thank you all for your answers anyway:)