Solved

SQL 2000 update failed

Posted on 2011-09-08
10
229 Views
Last Modified: 2012-08-14
I am updating a SQL table and get an update failed error, this worked OK yesterday so I assume there is something wrong with SQL (see attached error)
Can anyone help?
SQL-error.bmp
0
Comment
Question by:HKFuey
  • 5
  • 4
10 Comments
 
LVL 5

Expert Comment

by:zvytas
Comment Utility
Are you using linked server in your query? If so, check connection to the linked server, it seems it's down.
0
 

Author Comment

by:HKFuey
Comment Utility
Access tables linked by ODBC, I can open a query on the table and try to manually edit the records and I get the error. This was working OK yesterday.
0
 

Author Comment

by:HKFuey
Comment Utility
If I go directly to the table and try editing the field in question I get the same problem (it is not a key field)
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
Comment Utility
Have you dropped a primay key from any of the tables - this makes the tables read only from Access. Delete and relink the tables
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
Comment Utility
You're not by any chance linking to a view - they don't have primary keys. At the Access side you must create a unique index on the link. Normally you are asked what fields are used for the unique records when linking. It is soimetimes easy to forget when relinking that tables - or if you do it through code.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:HKFuey
Comment Utility
I have a copy of the database I did last week and this works OK
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
Comment Utility
The query you are using. Do it have one or more tables in it, and are any of those linked tables linking to a view?
0
 

Assisted Solution

by:HKFuey
HKFuey earned 0 total points
Comment Utility
OK, its WORKING!

I had left the Access app open on my PC overnight, I assume this may have locked the data somehow?

Closing the app sorted it.
0
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 500 total points
Comment Utility
Ah, and SQL Server probably closed the connection while running overnight maintenance - strange you could still edit via tables though - mind you, stranger things have happened!!

Good luck

Kelvin
0
 

Author Closing Comment

by:HKFuey
Comment Utility
Thanks for the help!!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now