Connection Broken when executing select statement

Hi,
I have written a query that gives the following 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 have seen several posts with this problem but none seem to match mine.
here is the query
select distinct
case
when 'ct' = 'CT' then 'CTM'
when 'ct' = 'SD' then 'SDM'
when 'ct' = 'TFVT' then 'VTM'
end, null, 'L', 'AV-A196', 'AV-A196', null, null, Service_Area,
Locality, Location,
Segment_Ref, Vicinity, Map_Ref, Service_Status, Condition_Status,
Charge_Key,
Asset_Owner, Resp_Area, Resp_Position, Data_Changed, null, GIS_Tile,
Note_Text, Asset_Region, Asset_Ranking
from asset
where category = 'ct'
and asset_Id not in (60325, 60377,60274, 60397)
and ((len(local_id) < 6 and local_id = 'AV-A196')
or (len(local_id) >= 6 and substring(local_id, 1, len(local_id)-6) = 'AV-A196'))
I have tested this on a SQL Server 2000 machine by creating a new database,
importing the asset table of 66000 records. Then created new indexes on
Asset_id (clustered primary key)
Local_id (unique index)
Category (index)
If I remove any of the conditions in the where statement it runs fine.
However with them all in their it breaks.
I am running 8.00.859 (sp3)
Any Suggestions
BenBenJamin wrote:
> Hi,
> I have written a query that gives the following 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 have seen several posts with this problem but none seem to match
> mine. here is the query
> select distinct
> case
> when 'ct' = 'CT' then 'CTM'
> when 'ct' = 'SD' then 'SDM'
> when 'ct' = 'TFVT' then 'VTM'
> end, null, 'L', 'AV-A196', 'AV-A196', null, null, Service_Area,
> Locality, Location,
> Segment_Ref, Vicinity, Map_Ref, Service_Status, Condition_Status,
> Charge_Key,
> Asset_Owner, Resp_Area, Resp_Position, Data_Changed, null,
> GIS_Tile, Note_Text, Asset_Region, Asset_Ranking
> from asset
> where category = 'ct'
> and asset_Id not in (60325, 60377,60274, 60397)
> and ((len(local_id) < 6 and local_id = 'AV-A196')
> or (len(local_id) >= 6 and substring(local_id, 1, len(local_id)-6) => 'AV-A196'))
> I have tested this on a SQL Server 2000 machine by creating a new
> database, importing the asset table of 66000 records. Then created
> new indexes on Asset_id (clustered primary key)
> Local_id (unique index)
> Category (index)
> If I remove any of the conditions in the where statement it runs fine.
> However with them all in their it breaks.
> I am running 8.00.859 (sp3)
> Any Suggestions
> Ben
Maybe I'm reading the SQL statement incorrectly, but how would this
statement ever be true:
(len(local_id) < 6 and local_id = 'AV-A196')
That would seem to imply the lengh of the column must be 5 or less _and_
it must be equal to a 7 character string, at the same time.
Just curious.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" wrote:
> BenJamin wrote:
> > Hi,
> >
> > I have written a query that gives the following 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 have seen several posts with this problem but none seem to match
> > mine. here is the query
> > select distinct
> > case
> > when 'ct' = 'CT' then 'CTM'
> > when 'ct' = 'SD' then 'SDM'
> > when 'ct' = 'TFVT' then 'VTM'
> > end, null, 'L', 'AV-A196', 'AV-A196', null, null, Service_Area,
> > Locality, Location,
> > Segment_Ref, Vicinity, Map_Ref, Service_Status, Condition_Status,
> > Charge_Key,
> > Asset_Owner, Resp_Area, Resp_Position, Data_Changed, null,
> > GIS_Tile, Note_Text, Asset_Region, Asset_Ranking
> > from asset
> > where category = 'ct'
> > and asset_Id not in (60325, 60377,60274, 60397)
> > and ((len(local_id) < 6 and local_id = 'AV-A196')
> > or (len(local_id) >= 6 and substring(local_id, 1, len(local_id)-6) => > 'AV-A196'))
> >
> > I have tested this on a SQL Server 2000 machine by creating a new
> > database, importing the asset table of 66000 records. Then created
> > new indexes on Asset_id (clustered primary key)
> > Local_id (unique index)
> > Category (index)
> >
> > If I remove any of the conditions in the where statement it runs fine.
> > However with them all in their it breaks.
> >
> > I am running 8.00.859 (sp3)
> > Any Suggestions
> > Ben
> Maybe I'm reading the SQL statement incorrectly, but how would this
> statement ever be true:
> (len(local_id) < 6 and local_id = 'AV-A196')
> That would seem to imply the lengh of the column must be 5 or less _and_
> it must be equal to a 7 character string, at the same time.
> Just curious.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
Fair Call,
That part is just there as a means to stop the statement trying to process
Local_ID's less than 6 characters long with the second part of the condition
statement. It is meant to always be false.
I was gettting incorrect length to the substring function. I tried to get it
to process the conditions in the order that they are written to exclude the
unwanted rows. Maybe there is a way, I don't know.
But thanks for the quick response though...|||Hi
Check your SQL Server log. You may find that your process was terminated at
a server level. If it was, try to go to 8.00.0878 and if that does not solve
it, open a call with PSS.
Regards
Mike
"BenJamin" wrote:
>
> "David Gugick" wrote:
> > BenJamin wrote:
> > > Hi,
> > >
> > > I have written a query that gives the following 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 have seen several posts with this problem but none seem to match
> > > mine. here is the query
> > > select distinct
> > > case
> > > when 'ct' = 'CT' then 'CTM'
> > > when 'ct' = 'SD' then 'SDM'
> > > when 'ct' = 'TFVT' then 'VTM'
> > > end, null, 'L', 'AV-A196', 'AV-A196', null, null, Service_Area,
> > > Locality, Location,
> > > Segment_Ref, Vicinity, Map_Ref, Service_Status, Condition_Status,
> > > Charge_Key,
> > > Asset_Owner, Resp_Area, Resp_Position, Data_Changed, null,
> > > GIS_Tile, Note_Text, Asset_Region, Asset_Ranking
> > > from asset
> > > where category = 'ct'
> > > and asset_Id not in (60325, 60377,60274, 60397)
> > > and ((len(local_id) < 6 and local_id = 'AV-A196')
> > > or (len(local_id) >= 6 and substring(local_id, 1, len(local_id)-6) => > > 'AV-A196'))
> > >
> > > I have tested this on a SQL Server 2000 machine by creating a new
> > > database, importing the asset table of 66000 records. Then created
> > > new indexes on Asset_id (clustered primary key)
> > > Local_id (unique index)
> > > Category (index)
> > >
> > > If I remove any of the conditions in the where statement it runs fine.
> > > However with them all in their it breaks.
> > >
> > > I am running 8.00.859 (sp3)
> > > Any Suggestions
> > > Ben
> >
> > Maybe I'm reading the SQL statement incorrectly, but how would this
> > statement ever be true:
> >
> > (len(local_id) < 6 and local_id = 'AV-A196')
> >
> > That would seem to imply the lengh of the column must be 5 or less _and_
> > it must be equal to a 7 character string, at the same time.
> >
> > Just curious.
> >
> >
> >
> > --
> > David Gugick
> > Imceda Software
> > www.imceda.com
> >
> >
> Fair Call,
> That part is just there as a means to stop the statement trying to process
> Local_ID's less than 6 characters long with the second part of the condition
> statement. It is meant to always be false.
> I was gettting incorrect length to the substring function. I tried to get it
> to process the conditions in the order that they are written to exclude the
> unwanted rows. Maybe there is a way, I don't know.
> But thanks for the quick response though...|||Ben
Add option (loop join) in your select statement
"BenJamin" <BenJamin@.discussions.microsoft.com> wrote in message
news:81A67F85-4D1E-4109-8090-6AFFE437DA63@.microsoft.com...
>
> "David Gugick" wrote:
> > BenJamin wrote:
> > > Hi,
> > >
> > > I have written a query that gives the following 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 have seen several posts with this problem but none seem to match
> > > mine. here is the query
> > > select distinct
> > > case
> > > when 'ct' = 'CT' then 'CTM'
> > > when 'ct' = 'SD' then 'SDM'
> > > when 'ct' = 'TFVT' then 'VTM'
> > > end, null, 'L', 'AV-A196', 'AV-A196', null, null, Service_Area,
> > > Locality, Location,
> > > Segment_Ref, Vicinity, Map_Ref, Service_Status, Condition_Status,
> > > Charge_Key,
> > > Asset_Owner, Resp_Area, Resp_Position, Data_Changed, null,
> > > GIS_Tile, Note_Text, Asset_Region, Asset_Ranking
> > > from asset
> > > where category = 'ct'
> > > and asset_Id not in (60325, 60377,60274, 60397)
> > > and ((len(local_id) < 6 and local_id = 'AV-A196')
> > > or (len(local_id) >= 6 and substring(local_id, 1, len(local_id)-6) => > > 'AV-A196'))
> > >
> > > I have tested this on a SQL Server 2000 machine by creating a new
> > > database, importing the asset table of 66000 records. Then created
> > > new indexes on Asset_id (clustered primary key)
> > > Local_id (unique index)
> > > Category (index)
> > >
> > > If I remove any of the conditions in the where statement it runs fine.
> > > However with them all in their it breaks.
> > >
> > > I am running 8.00.859 (sp3)
> > > Any Suggestions
> > > Ben
> >
> > Maybe I'm reading the SQL statement incorrectly, but how would this
> > statement ever be true:
> >
> > (len(local_id) < 6 and local_id = 'AV-A196')
> >
> > That would seem to imply the lengh of the column must be 5 or less _and_
> > it must be equal to a 7 character string, at the same time.
> >
> > Just curious.
> >
> >
> >
> > --
> > David Gugick
> > Imceda Software
> > www.imceda.com
> >
> >
> Fair Call,
> That part is just there as a means to stop the statement trying to process
> Local_ID's less than 6 characters long with the second part of the
condition
> statement. It is meant to always be false.
> I was gettting incorrect length to the substring function. I tried to get
it
> to process the conditions in the order that they are written to exclude
the
> unwanted rows. Maybe there is a way, I don't know.
> But thanks for the quick response though...|||Check the traffic on your network, the query maybe
overloading it.
Peter
"A politician needs the ability to foretell what is going
to happen tomorrow, next week, next month, and next year.
And to have the ability afterwards to explain why it
didn't happen."
Winston Churchill
>--Original Message--
>
>"David Gugick" wrote:
>> BenJamin wrote:
>> > Hi,
>> >
>> > I have written a query that gives the following
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 have seen several posts with this problem but none
seem to match
>> > mine. here is the query
>> > select distinct
>> > case
>> > when 'ct' = 'CT' then 'CTM'
>> > when 'ct' = 'SD' then 'SDM'
>> > when 'ct' = 'TFVT' then 'VTM'
>> > end, null, 'L', 'AV-A196', 'AV-A196', null,
null, Service_Area,
>> > Locality, Location,
>> > Segment_Ref, Vicinity, Map_Ref, Service_Status,
Condition_Status,
>> > Charge_Key,
>> > Asset_Owner, Resp_Area, Resp_Position,
Data_Changed, null,
>> > GIS_Tile, Note_Text, Asset_Region, Asset_Ranking
>> > from asset
>> > where category = 'ct'
>> > and asset_Id not in (60325, 60377,60274, 60397)
>> > and ((len(local_id) < 6 and local_id = 'AV-A196')
>> > or (len(local_id) >= 6 and substring(local_id, 1, len
(local_id)-6) =>> > 'AV-A196'))
>> >
>> > I have tested this on a SQL Server 2000 machine by
creating a new
>> > database, importing the asset table of 66000 records.
Then created
>> > new indexes on Asset_id (clustered primary key)
>> > Local_id (unique index)
>> > Category (index)
>> >
>> > If I remove any of the conditions in the where
statement it runs fine.
>> > However with them all in their it breaks.
>> >
>> > I am running 8.00.859 (sp3)
>> > Any Suggestions
>> > Ben
>> Maybe I'm reading the SQL statement incorrectly, but
how would this
>> statement ever be true:
>> (len(local_id) < 6 and local_id = 'AV-A196')
>> That would seem to imply the lengh of the column must
be 5 or less _and_
>> it must be equal to a 7 character string, at the same
time.
>> Just curious.
>>
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>>
>Fair Call,
>That part is just there as a means to stop the statement
trying to process
>Local_ID's less than 6 characters long with the second
part of the condition
>statement. It is meant to always be false.
>I was gettting incorrect length to the substring
function. I tried to get it
>to process the conditions in the order that they are
written to exclude the
>unwanted rows. Maybe there is a way, I don't know.
>But thanks for the quick response though...
>.
>