We help IT Professionals succeed at work.

Stored Procedure Executes but New Record is not Kept

Last Modified: 2018-09-18
SQL Server 2008 Insert Stored Procedure Executes but the record does not stay.

1. Application calls the stored procedure, passing the data.
2. I see that the new record is created and is in the table.
3. Moved off of the page in the application.
4. The new record is gone.

ALTER PROCEDURE [dbo].[Procedure]
	@Document nvarchar(max),
	@UploadDate  DateTime,
	@Label nvarchar(max),
	@PermitID int
	INSERT INTO [dbo].[Tablename](Document, UploadDate, Label, PermitID)
	VALUES(@Document, @UploadDate, @Label, @PermitID);


Open in new window

Any ideas?

Watch Question

Dustin SaundersCo-Founder and Chief Architect
Top Expert 2016

Is there code in the application that saves back an original copy of the tables when navigating away?

If you set up a SQL Server Profiler trace, so you see an execution that removes the table?  Are there any triggers in the database that would delete the new rows?
Most Valuable Expert 2012
Distinguished Expert 2019

Does the transaction not get committed?  Moving off the page might rollback.
RayBakkerApplication Developer Analyst



I am not using transaction. I am using a try/catch in my code. No errors are raised.

The page in question, allows the user to upload a file. Inserts a record that reflex the upload.

File gets uploaded, record gets created. When I click to go to another page, the record disappears. I can see that the sequence has incremented.
Most Valuable Expert 2012
Distinguished Expert 2019

sequences get incremented even if DML is rolled back.

I'm not a SQL Server person so I'm not sure how it handles things but what you describe seems to be the insert getting rolled back.  Try an explicit commit and see if the row stays after you move off the page.
RayBakkerApplication Developer Analyst



Thanks for the feedback. I agree that it seems like a rollback type issue. I did do an explicit commit but it make no difference. I am asking my DBA to look into how they created the database and the account that the application uses to connect to the DB.

I think it is a permission issue on the table.

I wrote some code, early in the project,that imports the data from the old oracle DB in to this SQL DB. I wrote the SQL statements (inserts, selects, updates) in code. They all worked.  It used that same account to connect to the SQL DB.

 I am at a loss to explain how the SQL statements in code and the stored procedure connecting to the DB with the same account give different results.

Does that spark anything with you?

Tanks for your time

Most Valuable Expert 2012
Distinguished Expert 2019

I don't see how it can be a permission issue.  I've never seen a permission that would allow a "temporary" insert but not commit without any error.

Is it possible you are looking at different databases/schemas(inserting into one and looking in another)?
RayBakkerApplication Developer Analyst


I am calling the store proc from code, using the connection.

1. Run the app that calls procedure and insert.
2. I log into SQL Server Management Studio and see the new record in the table.
3. In the app I go to the new page.
4. In SQL Server Management Studio and rerun that select and the record is gone.
5. Of course I am logging into Studio with my personal account.
6. My Selects work from my app.

Just tested my update and it worked (different table).


Most Valuable Expert 2012
Distinguished Expert 2019

You can investigate Dustin's ideas.

Hopefully a SQL Server Expert will be along later.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Sounds like there isnt any write permission. That would be specific for the User (and table)

It is suspiciously rolling back or contravening constraints / referential integrity issues. That should be applicable to all, and generate an error.

I would be using a BEGIN TRANSACTION ....  COMMIT TRANSACTION within the SP as we see it. But you also mention a TRY CATCH block which I cannot see.... a BEGIN TRANS without a COMMIT will also give you that type of behaviour.

Can you show the (full) trigger and the SP ?

Whats the connection string ?

Have you checked permissions in SSMS for the User / Role / Table ?

Have you tried (or ever used) sql profiler before ?

Application Developer Analyst
This one is on us!
(Get your first solution completely free - no credit card required)
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Ouch :)

Thanks for letting us know....

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.