Fatal error 625 - Connection broken problem (Help needed)

Question

Fatal error 625 - Connection broken problem (Help needed)

Ud4inBrampton

SSC-Addicted

Points: 408
More actions
January 30, 2007 at 2:33 pm

#172012

Hello everyone,
When I execute the following statement in the query analyzer on a msSQL2000 database...
delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where Userid=78 and DVDID=8 and onhold=0);
I get the following error...
Server: Msg 21, Level 20, State 1, Line 1
Warning: Fatal error 625 occurred at Jan 30 2007 2:15PM
Connection Broken
If I execute a simple SELECT statement, or any other statement, it runs ok and doesn't cause an error.
Is there anything wrong with the above statement? and how can it be causing that error message?
Thanks in advance for all help received!
Regards, Robert

Phone a Babysitter.com
Connecting Parents and Babysitters Nationwide!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

connection broken is typically a one time kind of error, where you lost your connection to your database, whether a cable was kicked out, an admin killed your spid, or any other networking issue.

try again, and then let us know if you get kicked out every time you run the delete statement; i don't think this will repeat, however.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

JDixon-385826 SSCrazy Points: 2723 More actions · Answer 2

Is there more then 1 ID that matches the where clause in the sub query?

I ask because I am wondering why you have such a complicated script for what seems like a simple task.

Either way I would simplify the query OR Upgrade your version to the latest Service pack. This connection broken problem was supposed to be fixed in service pack 4 I believe.

I would >

instead of

delete from DVD_I_Have where id in(select top 1 id from DVD_I_Have where Userid=78 and DVDID=8 and onhold=0)

TRY >

delete from DVD_I_Have where id = ?Result from the subquery?

OR

DECLARE @result numeric (18,0)

SET @result = (select top 1 id from DVD_I_Have where Userid=78 and DVDID=8 and onhold=0)

delete from DVD_I_Have where id = @Result

JDixon-385826 SSCrazy Points: 2723 More actions · Answer 3

Lowell - There is actually a known issue with the connection breaking. many times caused by a cursor but other things too. It will break your connection over and over again.

Ud4inBrampton SSC-Addicted Points: 408 More actions · Answer 4

Thank you for the quick replies!

Lowell, the error message is consistent - I get it every time I execute the above statement.

As far as the complexity of the statement goes, its my developers work, so I presume he has a reason for it. Personally I'm not to well versed in this.

As mentioned, quite oddly, its only this statement causing the error. If I try other statements, they execute ok. Based on your replies, and the fact i don't receive a syntax error in the query analyzer, it should run ok.

In regards to the server, it is provided by a third party (webhost) so I don't have access to it, outside of using enterprise manager. I'll mentioned server pack 4 to my host to see if its been installed.

Any other ideas why this specific sql statement is causing the error?

Thanks, Robert

Phone a Babysitter.com
Connecting Parents and Babysitters Nationwide!

SQLBill SSC Guru Points: 51440 More actions · Answer 5

Run this query:

SELECT ServerProperty('Edition'),

ServerProperty('ProductVersion'),

ServerProperty('ProductLevel')

Then let us know what the results are. It will tell you what Service Pack level you have installed along with a couple of other things).

-SQLBill

SQLBill SSC Guru Points: 51440 More actions · Answer 6

Have you checked the Windows Event Viewer Logs for related error messages?

Can you run these and see what happens:

select top 1 id from DVD_I_Have where Userid=78 and DVDID=8 and onhold=0;

SELECT * from DVD_I_Have where id in(select top 1 id from DVD_I_Have where Userid=78 and DVDID=8 and onhold=0);

I'm wondering if the problem is with the SELECT TOP ....? You don't have an ORDER BY.

-SQLBill

Ud4inBrampton SSC-Addicted Points: 408 More actions · Answer 7

Hi SQLBill,

Thanks for the reply! here's what I got...

Run this query:

SELECT ServerProperty('Edition'),

ServerProperty('ProductVersion'),

ServerProperty('ProductLevel')

RESPONSE: STANDARD EDITION 8.00.2039 - SP4

Have you checked the Windows Event Viewer Logs for related error messages?How do I access the event viewer in enterprise manager? Or is this something i would find on the web server?

Select top 1 id from DVD_I_Have where Userid=78 and DVDID=8 and onhold=0;

RESPONSE: ID 167 (which is correct)

SELECT * from DVD_I_Have where id in(select top 1 id from DVD_I_Have where Userid=78 and DVDID=8 and onhold=0);

RESPONSE: RETURNS COMPLETE DATA FOR THAT ROW - (from all columns - which is correct)

I'm wondering if the problem is with the SELECT TOP ....? You don't have an ORDER BY.

I'm not well versed in sql, but would a delete statement have a order by clause?

Robert

Phone a Babysitter.com
Connecting Parents and Babysitters Nationwide!

dbuchan SSCrazy Points: 2328 More actions · Answer 8

One nasty horrible little program I came across can cause this - apart from the suggestions supplied.

It's called Entercept (or was, I think they renamed it) from McAfee and has the nasty ability to break connections when you try some standard SQL Server tasks it thinks you shouldn't be doing. It does not write to the event log or provide any user notification but will log it's own events - if you are the Entercept/whatever Admin you can see these events.

The event logs you are interested in would be located on the server where SQL Server is installed. Check your own event logs also. (My Computer-right click-Manage)

A delete statement does not have an order by clause but the subquery should - or else, logically, what is the TOP 1 the top of ?

And I must ask - is DVD_I_Have a read only table ? Do updates to a row/column work ?

DB

The systems fine with no users loggged in. Can we keep it that way ?br>

JDixon-385826 SSCrazy Points: 2723 More actions · Answer 9

try running it as - assuming that the result from the subquery is numeric.

DECLARE @result numeric (18,0)

SET @result = (select top 1 id from DVD_I_Have where Userid=78 and DVDID=8 and onhold=0)

delete from DVD_I_Have where id = @Result

Since you are at service pack 4, most likely there is not a hot fix that deals with this directly.

Or have your developer rewrite the query - things can pass the snytax check and still not be correct.

Ud4inBrampton SSC-Addicted Points: 408 More actions · Answer 10

Hello everyone,

My developer changed the delete statement to the following, which has cleared up the fatal error problem. (followed JDixon's suggestion)

string SomeID="0";

strsql="select id from DVD_I_Have where userid="+Session["userid"].ToString()+" and DVDID="+DVDID+" and onhold=0";

dr1=(SqlDataReader)DL.ExecuteCommandWithTransaction(strsql,ExecutionType.ExecuteReader,objcon,myTrans);

if(dr1.Read())

{

SomeID=dr1["id"].ToString();

}

dr1.Close();

strsql="delete from DVD_I_Have where id="+SomeID+"";

This was a strange situation, as the old statement ran perfectly on another sql2000 server for over 6 months without ever causing an error.

Any comments on the new statement would be appreciated.

Thanks to everyone for your help!

Robert

Phone a Babysitter.com
Connecting Parents and Babysitters Nationwide!

SQLBill SSC Guru Points: 51440 More actions · Answer 11

Though you solved it...here's the answers to your questions:

Have you checked the Windows Event Viewer Logs for related error messages?How do I access the event viewer in enterprise manager? Or is this something i would find on the web server?

This is on the server hosting SQL Server. Right click on My Computer icon and select Manage. Then you will see the Event Viewer Logs (Application, Security, System)

I'm wondering if the problem is with the SELECT TOP ....? You don't have an ORDER BY.

I'm not well versed in sql, but would a delete statement have a order by clause?

As dbuchan said...the delete doesn't need an ORDER BY, but the sub-select does since there is a TOP statement. I believe SQL Server 2000 defaults to ORDER BY everything ASCending. Maybe SQL Server 2005 does not default and needs ORDER BY set explicitly.

-SQLBill