Nearly every week during the past few years has featured a headline about the latest data breach, malware attack, ransomware demand, or unrecoverable corporate data loss. Those stories are frequently followed by news that the CEOs at those companies were forced to resign.
Stored Procedure Executes but New Record is not Kept
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.
Any ideas?
Thanks
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
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[Tablename](Document, UploadDate, Label, PermitID)
VALUES(@Document, @UploadDate, @Label, @PermitID);
END
Any ideas?
Thanks
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
slight
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.
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.
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.
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.
slightwv
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
Ray
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
Ray
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(insertin
Is it possible you are looking at different databases/schemas(insertin
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).
Sincerely
Ray
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).
Sincerely
Ray
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 ?
https://blogs.msdn.microsoft.com/syedab/2009/09/16/how-to-use-sql-server-profiler-to-help-in-debugging-applications/
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 ?
https://blogs.msdn.microsoft.com/syedab/2009/09/16/how-to-use-sql-server-profiler-to-help-in-debugging-applications/
Greetings
Thanks you all for you assistance. I discover MY issue. When I was originally testing I would always click on the link "Home" after to wrote a record to the table in question. I tried clicking on other links after inserting the record and the record stayed until I clicked "Home".
I have forgotten that I wrote some maintenance code that removed old records based on a date from another table. I quick look at that store procedure and I discover my issue.
That you all for your assistance.
Thanks you all for you assistance. I discover MY issue. When I was originally testing I would always click on the link "Home" after to wrote a record to the table in question. I tried clicking on other links after inserting the record and the record stayed until I clicked "Home".
I have forgotten that I wrote some maintenance code that removed old records based on a date from another table. I quick look at that store procedure and I discover my issue.
That you all for your assistance.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trialIt's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Stored Proc
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
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?