Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Tables not clearing appropriately...

Author  Topic 

Spetty
Starting Member

25 Posts

Posted - 2007-03-05 : 14:58:36
Having issues with a table clearing out properly which is causing problems in the program.

By design there should only be one row in this table which updates with the most current record. However on this one system the table does not clear/update like it is supposed to, and instead starts adding multiple rows.

I have tried truncating the table but the problem does not resolve itself. Does anyone have any ideas as to why this might be happening?

Thanks!
Shawn

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-03-05 : 16:13:59
Somewhere, new rows are being inserted, instead of just updating the row that is there.

You could add a constraint to the table that limits it to a single row. If the table has a primary key, add a check constraint to limit the value of the primary key to a single value, and it will be impossible to add another row.

You could also prevent inserts or deletes by adding an INSTEAD OF INSERT, DELETE trigger on the table that just does a rollback and raises an error.











CODO ERGO SUM
Go to Top of Page

Spetty
Starting Member

25 Posts

Posted - 2007-03-05 : 16:26:18
Not sure what the actual "code" of system is, but it is hardcoded in to the program, and it's happening only on one system. Basically trying to rule out whether this is a SQL issue or something else. Essentially the code that is working on every other system is failing on this one.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-05 : 16:28:30
Run SQL Profiler to determine exactly what is happening.

Tara Kizer
Go to Top of Page

Spetty
Starting Member

25 Posts

Posted - 2007-03-15 : 11:46:38
New update on this issue:

Truncating the table again, does not resolve the issue as it just resurfaces. However a reinstall of SQL does cure this issue.

This is a pretty harsh solution to what I'm hoping it a minor issue. I did not get a chance to test dropping and recreating the table (this is essentially a temp table, so this is an ok solution) to see if that solved the problem.

Are there any SQL commands that would lock the table in such a fashion as to allow insert statements but not allow delete statements? How about anything that would keep SQL from doing what it needs to do when the statements are fed to it?

Thanks
Shawn
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-03-15 : 12:34:33
"Are there any SQL commands that would lock the table in such a fashion as to allow insert statements but not allow delete statements?"

MVJ already answered your question. Did you read his post?

"You could also prevent inserts or deletes by adding an INSTEAD OF INSERT, DELETE trigger on the table that just does a rollback and raises an error."

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

Spetty
Starting Member

25 Posts

Posted - 2007-03-15 : 12:53:33
Of course I read.

My response was that the results and how it's effecting the tables are hard-coded. Up until this recent occurrance the code tells the table to update itself with new values when those values change. Everything works great. However on two systems in particular this issue occurs where the values are not being allowed to update properly and instead throw it onto a different line.

The quick fix is an re-install of SQL (05 express to be exact), which then results in having the tables begin to update properly again. This is why I am asking if it is possible for SQL to on occasion lock down a table to a point where updating/or deleting a line is not possible but inserting is.

If that is the case I was hoping to find a SQL statement that would allow me the ability to fix this without reinstalling SQL. Dropping the table and re-creating is the next closest thing I can come up with at this point, but I am unable to attempt this right at the moment.

Go to Top of Page
   

- Advertisement -