Solved

Error when attempting to change value of INT field in SQL table.  Help!

Posted on 2016-08-03
23
46 Views
Last Modified: 2016-10-08
I have a very strange situation.  When trying to update the int value in a field, I get the error below.  The strange part is that I only get the error if the original value is certain numbers.  For example, in this case, I'm trying to change the value from 15 to 3, or really to any other number.  It will not let me change it.  If I pull up other fields with different initial values, say 3, then it changes no problem.  The field is an index, but other than that, nothing special.

Any thoughts? Let me know what other info you need.  Thanks!

Error:

"No row was updated.

The data in row 1 was not committed.
Error Source: .NetSqlClient Data Provider.
Error Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.

Correct the errors and retry or press ESC to cancel the change(s)."
0
Comment
Question by:tommyboy115
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 8
23 Comments
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41741344
Where are you making the edit ?
Can you run an update statement sucessfully ?
Are there any triggers attached to the table ?
Is referential integrity involved ?

If you restore the db to another server so you get the same problem ?

Is this a production system ?
0
 

Author Comment

by:tommyboy115
ID: 41741380
I'm attempting to the make the edit in a View.  I did just notice that I CAN change the values when editing the table directly (Edit top 200 rows).  The problem seems to revolve around updating it when queried.

I can not run an update query successfully.  It just hangs.

No triggers.

No referential integrity.

I did not restore to another server yet.

This is a production system and just started happening.
0
 
LVL 13

Expert Comment

by:Dustin Saunders
ID: 41741381
Can you script the table out and paste the result so we can see the layout of the columns?
sqlTable.png
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41741388
can u drop the index and try your update.

I would drop the view and recreate it ir create another with same definition and try ur edit.

If you run the update what error do you get ?
Is there a primary key defined ?
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41741389
can u drop the index and try your update.

I would drop the view and recreate it ir create another with same definition and try ur edit.

If you run the update what error do you get ?
Is there a primary key defined ?
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41741390
can u drop the index and try your update.

I would drop the view and recreate it ir create another with same definition and try ur edit.

If you run the update what error do you get ?
Is there a primary key defined ?
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41741391
can u drop the index and try your update.

I would drop the view and recreate it ir create another with same definition and try ur edit.

If you run the update what error do you get ?
Is there a primary key defined ?
0
 

Author Comment

by:tommyboy115
ID: 41741402
Since this is a production server, would dropping the index while live create any adverse issues?
0
 

Author Comment

by:tommyboy115
ID: 41741416
Here is the table config.  DEAL_STSID is the field at issue.
table.txt
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41741481
If you can edit the table directly then the problem is with the view.

Can u post the definition ?

Are you sure the row is not locked by another process.
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41741482
If you can edit the table directly then the problem is with the view.

Can u post the definition ?

Are you sure the row is not locked by another process.
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41741483
If you can edit the table directly then the problem is with the view.

Can u post the definition ?

Are you sure the row is not locked by another process.
0
 

Author Comment

by:tommyboy115
ID: 41741486
It's multiple rows with the issue, but how can I tell if a row is locked or not?
0
 

Author Comment

by:tommyboy115
ID: 41741488
And also, I can change the value of other fields in the row, so it's probably not locked.  Here's the view:

SELECT     DEALID, DEAL_STSID
FROM         dbo.DMS_DEALS
WHERE     (DEALID = 240364)
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41741524
If you run

update dbo.DMS_DEALS set DEAL_STSID =1
WHERE     (DEALID = 240364)
go
update dbo.DMS_DEALS set DEAL_STSID =2
WHERE     (DEALID = 240364)
go
update dbo.DMS_DEALS set DEAL_STSID =3
WHERE     (DEALID = 240364)
go

what happens ?

you can run sp_lock to view current locking info.

are you logging in as the sa?
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41741526
Is the error only related to record 240364 ?
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41742662
Were you able to confirm that there are no other processes interacting with the record when you try to edit ?
0
 

Author Comment

by:tommyboy115
ID: 41748046
So, the records suddenly became editable again.  I can only assume this had to do with locking.  I just ran into another issue where there no record updates were being allowed.  I'm not a SQL expert by any stretch.  How can I determine what is happening with regards to locking with the database?
0
 

Author Comment

by:tommyboy115
ID: 41748073
In this latest episode, I stopped SQL server and started it again to fix the problem.
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41748396
You can use sp_who and sp_who2 along with sp_lock as a staring point.
0
 

Accepted Solution

by:
tommyboy115 earned 0 total points
ID: 41783397
The problem ended up being table-level locking.
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41834925
I feel I was on the right track with this.

No worries if you dont\cant award any points in my favor.

Cheers,
Datrias
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question