SQL Server - Connection Broken

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

Old Forums

CLOSED - General SQL Server

SQL Server - Connection Broken

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-03-24 : 08:41:30

Bobby writes "Hi,

I've come across a problem which causes a 'broken connection' from two different avenues.

Microsoft SQL Server 7.00 - 7.00.907 (Intel X86)
Sep 27 2000 19:31:48
Copyright (c) 1988-1998 Microsoft Corporation
Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)

The first is a simple select command from a view, for example:

select distinct
ind.iindividualid ,
ind.Title ,
ind.FName ,
ind.LName ,
ind.JobTitle ,
ind.Cmpny ,
ind.Addr1 ,
ind.Addr2 ,
ind.Addr3 ,
ind.City ,
ind.Rgn ,
ind.PostCd ,
ind.Cntry ,
ind.Phone ,
ind.Email ,
ind.Fax

from zdirectmailresults ind

where ind.iindividualid in (5501199 ,
5146532 ,
5146624, .......)

There are approx 25000 individual ID's in the where clause. This causes the following error message:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.

Connection Broken

I've checked in Books Online but the only suggestion I can find is set to the AutoReconnect property of the SQL Server to TRUE, will this help and how can this be done...? Is there a limit of the number of entries in the IN clause..? Hope you can help, many thanks.

Bobby"

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-24 : 09:08:35

If you are on SP4 for 7.0, shouldn't your version number be 7.00.1063?

I believe this is correct. See if installing the latest SP works. Also, you should make sure you have an up-to-date MDAC on the client or application. I'm assuming since you're running on 7.0, you're probably using an old RDO connection? Give us a little more details about your setup if this doesn't help.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-03-24 : 21:09:46

quote:
Originally posted by derrickleggett

I'm assuming since you're running on 7.0, you're probably using an old RDO connection?

Hmmm... this is an interesting assumption, Derrick. I used 7.0 for a while and have never used RDO, only ADO.

In regard to the original question... 25,000 IDs in an IN statement?! WOW! Wouldn't it be simpler, and perhaps faster, to put those in a table (temp or otherwise) and join to it?

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources