general network error

Please help!

Getting this error intermittently:

System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding.
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

After getting a fair number of these errors I get this one also:

System.Data.SqlClient.SqlException: General network error. Check your
network documentation.
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

SQL connection pooling is turned off. All sql parameters passed to the
stored procedure and all the data types are correct.

*** Sent via Developersdex http://www.developersdex.com ***Assuming that that there are no basic network connectivity issues,
there are several articles in the MS Knowledge Base about specific
errors or bugs which might produce that error message, so you should
also go through them to see if they might apply to your situation (eg.
827452, 826829 and others).

Simon|||
I checked all the articles i can find with no avail...

*** Sent via Developersdex http://www.developersdex.com ***|||Maybe it's the SQL statement that is timing out. In how many
seconds approximately do you get the timeout message?

> Getting this error intermittently:
> System.Data.SqlClient.SqlException: Timeout expired. The timeout period
> elapsed prior to completion of the operation or the server is not
> responding.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
>
> After getting a fair number of these errors I get this one also:
> System.Data.SqlClient.SqlException: General network error. Check your
> network documentation.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
>
> SQL connection pooling is turned off. All sql parameters passed to the
> stored procedure and all the data types are correct.|||
It only happends a couple times a day, so it is intermittent error - it
is not timing out every time the sql is called. It can run for hours or
even days without a problem and then suddenly it starts getting these
timeouts...

*** Sent via Developersdex http://www.developersdex.com ***|||Like Simon pointed out, you should also search the MS KB.
A quick search lead me to this:

http://support.microsoft.com/defaul...kb;en-us;827393

I don't know what SQL statement is causing you this error but
I suggest what the KB article says, which is to check for any
Locks in SQL EM when you get the error.
You could also run the SQL Profiler the whole day and see
on which statement you are getting the Timeout error.
At least you might see a SQL Statement that is running for
a long period in the SQL Profiler.

"Additionally, when you view the current locks in SQL Server
Enterprise Manager (expand Management, expand Current
Activity, and then expand Locks/Process ID), you may find
that the sps_rCheckTreeForDelete object is blocking other
connections."

There are a ton of posts on others experiencing the same error:

http://groups-beta.google.com/group...m eout+expired

Maybe you could find some hints when you go through those
posts.

> It only happends a couple times a day, so it is intermittent error - it
> is not timing out every time the sql is called. It can run for hours or
> even days without a problem and then suddenly it starts getting these
> timeouts...|||
Guys, there is nothing on KB I did not read about this error and none of
the cases apply to me. As for the statement - I know exactly what stored
procedure it is timing out on and it is the same procedure that's
working just fine the rest of the time. I am really at the end of my
wits. I posted this question on several discussion groups via Google and
all. So far I did not get anything I did not think of before. I looked
at the locks, I did the profiling - I can't profile all day since it's a
production server, but I can get a 5 minutes or so when the errors are
going on and there is nothing - except that the duration of the request
goes way up and then times out after about 30000... I see that lots of
people experiencing the same error (general network error)- there are
explanations to it though like the variable passed to the stored proc
are not the correct size or SSL encription, or connection timeout set to
zero - things like that. I am thinking about switching from the sql
server to something more robust...

*** Sent via Developersdex http://www.developersdex.com ***|||NotAGuru (notaguru@.hotmail.com) writes:
> Guys, there is nothing on KB I did not read about this error and none of
> the cases apply to me. As for the statement - I know exactly what stored
> procedure it is timing out on and it is the same procedure that's
> working just fine the rest of the time. I am really at the end of my
> wits. I posted this question on several discussion groups via Google and
> all. So far I did not get anything I did not think of before. I looked
> at the locks, I did the profiling - I can't profile all day since it's a
> production server, but I can get a 5 minutes or so when the errors are
> going on and there is nothing - except that the duration of the request
> goes way up and then times out after about 30000... I see that lots of
> people experiencing the same error (general network error)- there are
> explanations to it though like the variable passed to the stored proc
> are not the correct size or SSL encription, or connection timeout set to
> zero - things like that. I am thinking about switching from the sql
> server to something more robust...

Have you checked the SQL Server error log?

"General Network Error" means that the connection with SQL Server
was severed in some unexpected way. This can be as simple as a dropped
network conection. But it also be due to a crash in the SQL Server process.
In this case, the SQL Server log will contain a stack dump.

From your description, it sounds that first you get these timeouts, and
then you get these network errors. It sounds as if SQL Server comes under
stress for some reason.

Could you post the output of "SELECT @.@.version"?

When this starts to happen, does it happen to all users that are
running this procedure? Do other processes also experience problems,
or is everything else working OK? What does this procedure do? Plain
SELECT? Updates? Access to linked servers? Would it be possible for you
to post the code?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Could you post the output of "SELECT @.@.version"?

When this starts to happen, does it happen to all users that are
running this procedure?

The account it runs under is the only account that uses it,so i don't
really know, but i think it would error for all users.

Do other processes also experience problems,
or is everything else working OK?

Everything else is working, there are no errors in the event log on sql
server.

What does this procedure do? Plain
SELECT? Updates?

Updates

Access to linked servers? nope

Would it be possible for you
to post the code? no, it's production code but it is an update on the
database.

*** Sent via Developersdex http://www.developersdex.com ***|||NotAGuru (notaguru@.hotmail.com) writes:
>> Could you post the output of "SELECT @.@.version"?

You couldn't?

>> Do other processes also experience problems,
>> or is everything else working OK?
> Everything else is working, there are no errors in the event log on sql
> server.

Note that I asked you to look for stack dumps in the SQL Server
error log. That is not the event log. The error log is in
C:\Program Files\Microsoft SQL Server\MSSQL\Log\Errror. (Replace MSSQL
if on named instance.)

>> Would it be possible for you to post the code?
> no, it's production code but it is an update on the database.

OK. Since you don't share much information, I don't think we will
be able to help you here. I would suggest that you open a case with
Microsoft. That is likely to cost you some sum of money, but I would
assume that condition also costs you money when you tracking it down.
Having a support professional to look at it, could be more cost-
effective.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks! I looked at errors log too - there are no errors.
The code would not tell you anything it is just an update statement but
here it is:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Name]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Name]
GO

CREATE PROCEDURE Name
@.var1 varchar(64),
@.var2 tinyint = NULL,
@.var3 char(2) = NULL,
@.var4 smallint = NULL,
@.var5 int = NULL,
@.var6 smallint = NULL,
@.var7 varchar(20) = NULL,
@.var8 smallint = NULL
AS

set nocount on
declare @.section_name varchar(500)
declare @.error integer
set @.error = 0
DECLARE @.return_code INTEGER
SET @.return_code = 0
declare @.proc_name varchar(100)
set @.proc_name = (select name from sysobjects (NOLOCK) where id =
@.@.procid)

set @.section_name = 'Update '

UPDATE Name
SET col2= @.var2,
col3= @.var3,
col4= @.var4,
col5= @.var5,
col6= @.var6,
col7= @.var7,
date= GETDATE()
WHERE col1= @.var1

/**** check for errors and log results
******************************/
set @.error = @.@.error
if @.error <> 0 goto failure

/************************************************** ******************/

set @.section_name = 'exit procedure with success'

goto exit_procedure

failure:
begin
declare @.error_text varchar(1500)
set @.error_text =
'Procedure: "' + @.proc_name + '" ' + char(13) +
'Parameters: ' + char(13) +
'Section Name: ' + isnull(@.section_name,'(null)')

if @.@.trancount > 0
begin
rollback transaction
set @.error_text = @.error_text + char(13) + 'Rollback occurred. '
end

if @.error >= 50000 raiserror(@.error, 16, 1)
raiserror(@.error_text, 16, 1)

set @.return_code = @.error
end

exit_procedure:
return @.return_code

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

*** Sent via Developersdex http://www.developersdex.com ***|||NotAGuru (notaguru@.hotmail.com) writes:
> Thanks! I looked at errors log too - there are no errors.
> The code would not tell you anything it is just an update statement but
> here it is:

Thanks for the code. The benefit I have from it, is that I can see
that there is nothing hiding in it. Then again, this also means that
I don't very many more suggestions. Blocking is the only thing I can
think of, but you seem to have ruled that out. And it should lead to
the connection being severed.

I assume that the .Net code uses ExecuteNonQuery to run the query?

And just to check - there are no triggers on the table, are there?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

function causes a timeout

I have a function which is causing a time out it is only being used on 1700 records and the problem seems to happen in the afternoon The client gets the error on a live system and so do I on the dev system

ALTER FUNCTION[8977].[isClosed] (@.Irefnvarchar(30),@.clidint)
RETURNS BIT
AS/* if the event is closed return true */
BEGIN
RETURN(SELECTclosed
FROMdbo.tblOCompEvents
WHERE(ClientID = @.clid)AND(IRef = @.Iref)
GROUP BYclosed
HAVING(closed = 1))
END

I am using in like this
AND([8977].isClosed(tblOCompEvents.IRef, tblOCompEvents.ClientID)IS NULL)

I have taken the IRef Argument out still times out
I have changed it from IS NULL to = 0 and to = 1, all still cause a time out

any ideas please?

When you write a function like that, it is making SQL Server go through every row to evaluate the isClosed bit, and then go through your original query to evaluate the AND. Try incorporating the function logic into the WHERE clause instead of calling it like you are.|||

See if you can join this SQL statement to your original query and get the value of the function logic directly in the SELECT stmt.

|||

This would probably run much faster:

ALTER FUNCTION[8977].[isClosed] (@.Irefnvarchar(30),@.clidint)
RETURNS BIT
AS/* if the event is closed return true */
BEGIN
RETURN(SELECT TOP 1closed
FROMdbo.tblOCompEvents
WHERE(ClientID = @.clid)AND(IRef = @.Iref) AND (closed = 1))
END

This may be even faster yet, but you need to do some performance testing.

CREATEFUNCTION [8977].[isClosed](@.Irefnvarchar(30),@.clidint)

RETURNSBIT

AS/* if the event is closed return true */

BEGIN

IFEXISTS(SELECT closedFROM dbo.tblOCompEvents

WHERE(ClientID= @.clid)AND(IRef= @.Iref)AND(closed= 1))

RETURN 1

ELSE

RETURN 0

END

Ultimately, changing your where clause to something like:

WHERE NOT EXISTS(SELECT closedFROM dbo.tblOCompEventsWHERE(ClientID= @.clid)AND(IRef= @.Iref)AND(closed= 1))

would be much much faster than using a scalar function. You can also change your query to use a join like:

SELECT ...

FROM ... a

LEFT JOIN dbo.tblOCompEvents e ON a.ClientID=e.ClientID AND a.IRef=e.IRef AND e.Closed=1

WHERE ...

AND e.ClientID IS NULL

|||

yes I suspected that was part of the problem

Thanks

|||

tried that thanks but I think the function it's self is not right

|||

thanks but I think it is more fundemental than that I don't think the function is doing what I want. it is working fine now there is less pressure on the server (after 5pm in the UK)

But I am sure I could do better
these are the rows

clientid1,row 1, false, ref1
clientid2,row 2, false, ref2
clientid2,row 2, false, ref2
clientid2,row 4, true, ref2
clientid1,row 5, false, ref1

if a series of rows have the same ref and the same client id and JUST one of the rows is trueref2 then return true if the series of rows are all false then return false
However in this case I think it will be better to return the client id and use that to include or exclude clients

|||

The function does achieve your goals, but it's very very inefficient. Any of the above 3 methods I gave will greatly reduce the resources needed to run your queries.

Is this possible?

clientid1,row 1, false, ref1
clientid2,row 2, false, ref2
clientid2,row 3, false, ref2
clientid2,row 4, true, ref2
clientid1,row 5, false, ref1
clientid2,row 6, false, ref2
clientid2,row 7, false, ref2

If so, what should it return? You said if a series of rows have the same ref and the same client id, which there are two series of rows, one series has a true, the other does not. Or did I misunderstand?

|||

your example is possible and client2 ref2 would return true and client1 ref1 false you do understand it

I have tried you option :
NOT EXISTS(SELECTclosedFROMdbo.tblOCompEventsWHERE(ClientID = dbo.tblClients.ClientID)AND(IRef = tblOCompEvents.Iref)AND(closed = 1))

thanks