Does the INSERT stored procedure not work?

Question

I am trying to insert from a single database called suspend into a table called Notification in the ANIMAL database. My stored procedure is as follows:

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 

Zero insertion is the completion of one column, which otherwise would not be filled, I tried different ways, for example, to use also the column names after the table name, and then indicate only the values ​​that I have received. I know that this is not a stored procedure problem because I executed it from sql server management studio and it works with parameter input. Then, I think the problem should 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 write the value to the database. I am debugging and getting angry about it because it works when I execute it manually, but not when I automate the process in my application. Does anyone see something wrong with my code?

thanks

EDIT: Added more code. This still does not work, it means that the procedure works if I execute it, so I don’t know what might be the error. In fact, I'm not mistaken. Could this be written in a table that is not in the database, where do you have the stored procedure?

Answer 1

ok, I finally found out that no one understood. It was a very stupid mistake, but I really got angry until I found the problem. This is not a permissions problem, the problem is that I did not execute this procedure from my application, so when 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(); 

since you see that I focused my efforts on much more complex things, and I didn’t even do it ... lol.

Thank you all for your answers :)

Answer 2

Specify column names:

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

Answer 3

I would indicate your column names and DONT did not use NULL for this column at all. Just let SQL Server handle this.

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

Answer 4

Launch the profiler when you try to run it from the application and see what values ​​it really sends. This will tell you if the application creates the correct exec expression to execute proc.

It could also be a permissions issue.

Answer 5

"Could this be written in a table that is not in the database, where do you have the stored procedure?"

This may be a problem. You can try adding the WITH WITH EXECUTE AS OWNER clause to your stored procedure so that it runs as the owner of the stored procedure. Or provide write permissions for the executing user to the table.

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