delete a field help?

I have a user who has submitted a survey twice, and I have to delete the second entry.
But every user has a UserID and the data for the second submission has the same UserID (the only thing different is the fact she submitted data 5 seconds later)as this is one of the unique values used in many tables.
So I have deleted data in other tables but If I try to delete it from this particulat table I can't as I am really trying to delete that ID, so how can I get rid off that record?You don't have a primary key on that field with a unique constraint? (something like an identity column?)

If you don't have anything to uniquely identify the record, you will have to select one of the records into a temp table, delete the record, then insert the record back. There really isn't any other way to do it.|||UserId has an indentity set, but not in the table that it references to,it is not the primary key.

Could you explain a bit more what would I need to do, as I have only done regular delitions,where things go smooth.

I will attach a file with the error eventhough you probabaly know what it is

Thanks for help

I forgot to tell you that the record I am trying to delete it from is in a view,don't konw if that makes any difference?|||Can you post the table structure of the table you're trying to delete from and the table that's not letting you delete?|||ok,viewSurveySubmission is where the double record is and RelocateeServices will not let me delete it.|||You need to find where the problem is in the base tables and delete the record from there. The view will try to delete from both tables, which it cannot do because another record is still using one of the records from a table you are trying to delete from. Make sense?

In this case find the duplicate in the base table and delete that record.|||Thanks to all willing to help,but I have actually been able to do it myself.

Thanks

degraded stored procedure performance

Hi,

I have been experiencing an intermittent problem that I hope someone can help me with.

Twice over the last month or so, we have experienced a problem where the performance of a single stored procedure degrades dramatically very quickly, possibly instantaneously from ~1sec to ~15-25 secs. What's throwing me is there are hundreds of other stored procedures, but none of them has been affected. Is there a possibility that our Maintenance plan which is scheduled to reorganize the data and index pages on Sunday night be the culprit?? Both times the problem reared it's ugly head on Monday that's why I'm wondering if it is causing some problem with the sp's execution plan or something, because the problem goes away if I recompile the stored procedure. If it is the problem, I have a couple more questions, what can I do to help prevent it from occurring in the future, and why does it only affect one sp?? If however you don't think it is the cause any other thoughts would be greatly appreciated.

The stored procedure in question has not been modified for 2 months and generally performs fine.

SQL Server 2000 Enterprise Edition 8.00.679

Thanks in AdvanceWhat you have describe is not that uncommon. This would most likely happen if the size of the underlying table(s) grew or shrank significantly after the sp was compiled or if the data distibution drastically changed after the sp was compiled.

One thing you might want to do is force a recompile of all sp and triggers after the maintinance plan runs check out 'sp_recompile' in BOL.|||to step though all tables you can use:

Microsofts code:
sp_MSforeachtable 'sp_recompile ''?'''

Or your own:
declare @.Tbl sysname
select @.Tbl = min(name) from sysobjects where type = 'U'
while (@.Tbl is not null) begin
exec('sp_recompile ' + @.Tbl)
select @.Tbl = min(name) from sysobjects where type = 'U' and name > @.Tbl
end

DEFINITIVE ANSWER PLEASE -- can you UPDATE ntext datatype field??

Hi, I've read conflicting articles on updating an ntext field in a
column.

My ntext field will exceed 8,000 characters (typically twice that size
-- but just a text string).

One article (I think from MicroSoft) said you could NOT use ntext in
an UPDATE statement, but I've seen examples from other people using
it...but don't know if it's related to the size/characters issue.

Is this true or not?

Thanks very much...KathyKathyB (KathyBurke40@.attbi.com) writes:
> One article (I think from MicroSoft) said you could NOT use ntext in
> an UPDATE statement, but I've seen examples from other people using
> it...but don't know if it's related to the size/characters issue.

Yes, you can update an ntext column directly in an UPDATE statement.
I dont think there is a limitation, but it may be unpractiable if
you have a string which is million characters long.

There is also UPDATETEXT which permits you change parts of an ntext
column, but this function is certainly more complex to use, so as long
as you can do it with plain UPDATE stay with it.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, Erland, that's what I wanted to hear!

I don't need to save a million, just up to 50,000!

Kathy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!