Cant delete single record? HELP!

OK,

This one is driving me nuts. I've issued a very simple statement to delete a single row from a table. It appears that when I execute it in SQL Query Analyzer the CPUTime spikes and holds one of the CPUs on the box pegged at 100%. I've let this thing run for over a day, and it's not deleting the one damn record. Any thoughts? :confused: :confused: Here's the command I'm executing:

DELETE FROM Invoices WHERE InvoiceID = 153345

Running SELECT * FROM Invoices WHERE InvoiceID = 153345 returns only a single record as it should. InvoiceID is the PK in this table. Any and all help is greatly appreciated. I've rebooted the server, but to no avail. Same thing happens after a reboot.

TIAAny triggers on the table?|||Any triggers on the table?
No, no triggers on the table.|||Any triggles on this table?
No triggles either ;)|||what does the execution plan looks like for the delete statement?

Are there foreign key constraints on Invoices table ?
run something similar to the following:

select object_name(constid) as FkeyName,
object_name(fkeyid) as DependentTable from sysforeignkeys where rkeyid=object_id('Invoices')

Is anything locking Invoices table when you try the delete?

simas|||It won't even pull up an estimated execution plan. It just sits there doing nothing. There are several foreign keys, but I'm deleting from those tables prior to this delete, and they all delete without a hitch. It's just this table. I don't see anything locking the Invoices table.|||Have you ran a DBCC CHECKDB to see if there are any integrity issues?|||use <Your_Database_Name>
go
dbcc opentran
go

You have to have an open transaction that references this table. Oh, and do KILL your DELETE before you issue the DBCC. Make sure that any rollback (doubt it) completes before you fire DBCC.|||CHECKDB found 0 allocation errors and 0 consistency errors in database 'Accounting'.

Nice thought though.|||This is trying to pull a rabit out of the hat, but try to run UPDATE STATISTICS on the table. Also, try to drop and recreate the PRIMARY KEY index on that table. Then, see if you can delete the rows.

Also, I don't know why in the world I didn't suggest this earlier. In Query Analyzer before you do anything else, run: SELECT @.@.SPID. Set up a profiler to capture all activity using that SPID as a filter. In particular, look for SQL:Batch Started; SQL:Batch Completed; all errors, all recompiles, all locks and blocks; RPC:Started; RPC:Completed. See if you can see anything in the profiler that would lead to this problem.

Also, when this is running, if you run this in Query Analyzer do you get any results?

SELECT * FROM master..sysprocesses WHERE blocked <> 0|||UPDATE: DELETING THE PRIMARY KEY AND RECREATING IT WORKED. (no comment on how bad I think it is that this should ever have to be done, but it's fixed so I won't complain)

Mad Thanks go out to those that helped me out with this!

Special Thanks to Derrick! :D|||And you're sure you're on the latest service packs, critical updates, etc for Windows and SQL Server? Make sure the machine that has Query Analyzer on it has also been updated.|||SP3a on both, I'll have to check on the critical updates (since my boss thinks the internet is a fad). Thanks again for everything!

Cant connect to remote SQL Server from my ASP.NET 2.0

I’m going nuts and need some help with my ASP.NET 2.0 application which has trouble talking to a remote SQL Server 2000. First, before I describe my problem let me say that everything works perfectly on my XP development workstation when I point to the local SQL Server database. Screens load fine, saves and edits all work perfectly.

Problem is when I connecting to SQL Server on a remote windows 2003 server I get

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Now, I’ve researched this error and have successfully made the following changes to the remote windows 2003 server where the database resides:

1) Added logins for “ASPNET”, “NT AUTHORITY\NETWORK SERVICE”, and “IIS_WPG”. The reason I added NT AUTHORITY is because we plan on deploying this web site to the same windows 2003 server and I understand that IIS6 (on this server) has ASP.NET running as NT AUTHORITY acct.

2) For all the logins above I’ve added them as users to the database and given dbo role.

I still get same login for user null error when attempting to connect from my XP workstation.

Can anyone help?

Hi,

You need to authenticate against the remote SQL server. Do that in your connectionstrong.

Check www.connectionstrings.com

Regards

|||

Thanks, I'm still getting error. I also want to state I am using windows authentication for this web application, and my understanding is ASP.NET is running as NT AUTHORITY (if windows 2003 and IIS 6) or ASPNET(if XP ) is this a correct assumption?My web.config is set to windows authentication.

I looked at connectionstrings.com as you suggested. This is the connection stringI auto created (it seems very consistent w/ examples on connectionstrings.com) using VisualStudio by dragging a table onto a form from the remote server "TESTSERVER" and using the Configure Data Source dialog which creates the connection string automatically. The SQL Database on TESTSERVER is set to mixed mode for both SQL and Windows authentication. Here is the string, do you see any issues?

Data Source=TESTSERVER;Initial Catalog=CompanyDB1;Integrated Security=True.

I get the same error :Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Do you have any ideas on what I'm doing wrong? Why is user null? Should the user be ASPNET or NT AUTHORITY coming from my XP based web server?

|||Where is the SQL server located in?|||

The SQL Server 2000 database is located remotely on machine called TESTSERVER. This is what is in the connection string

(PS - When I want to point locally to my machine "PUU304" I use this connection string which works fine on my machine

connectionString="Data Source=PUU304;Initial Catalog=JBLocalDatabase;Integrated Security=True")

|||Well, using windows authentication for your winxp login is not working. Try to create a user/pass for your database on the remote server|||check the permission of your database|||NT AUTHORITY has dbo rights in the database. Is this the permission you are referring to?