Access Voilation and General Network Error

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

SQL Server 2000 Forums

SQL Server Development (2000)

Access Voilation and General Network Error

Author

Topic

mallikarjuna_
Starting Member

20 Posts

Posted - 2003-11-18 : 13:03:28

Hi ,
Can someone help me on the problem I am facing with SQL Server 7.0.
I have a query , its actually part of a stored procedure, selecting a lot of columns from a few tables with a group by clause. The stored procedure used to execute successfuly earlier. All of a sudden it started throwing the error message "SqlDumpExceptionHandler: Process 14 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process".Strangely if I run the same query without some columns in the query it retrieved data, but when I use all the columns it again throws the error message. There was an unnecessary IN clause in the query. I removed the IN clause and used "=" by which the query was returning the data. Before I could be happy that the problem got resolved , as I succesfully executed the stored procedure to load data for a month, it starting throwing the error message "[Microsoft][ODBS SQL Server Driver][TCP/IP Sockets]ConnectionCheckForData (CheckforData()). General network error. Check your network documentation. Connection Broken". Again if I remove some of the columns , the query returns the data. Now if I run the same query on the server it returns the Access Voilation error message and if I run this query from the client it returns the General network error. Can some one please help me on this.

Thnks

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-18 : 13:52:15

Can you post the code?

Do you have a lot of text columns?

quote:

All of a sudden it started throwing the error message

To me that says something changed btw...

Brett

8-)

mallikarjuna_
Starting Member

20 Posts

Posted - 2003-11-18 : 14:11:32

Hi Brett,
Thanks for replying. There are 75 columns in the select statement out of which 25 are text columns. There is a case statement too in the query.
The query goes like this.

select Table1.col1,..Table1.col10, Table2.col1,..Table2.col10,Table3.col1,Table3.col2,sum(Table1.col11),..sum(Table1.col100),
(case when Table1.colX = 'Y' then sum(Table1.col25) else count(Table1.col1) end), Table1.col35,..Table1.col45
from Table1,Table2,Table3
where Table1.col1 = Table2.col1
and Table1.col2=Table2.col2
and Table3.col1=Table1.col3
group by Table1.col1,..Table1.col10, Table2.col1,..Table2.col10,Table3.col1,Table3.col2,Table1.col35,..Table1.col45

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-18 : 14:17:29

A lot of access violations are correct with the a newer service pack. Which service pack are you using?

Tara

mallikarjuna_
Starting Member

20 Posts

Posted - 2003-11-18 : 14:19:38

Service Pack4
The version is 7.00.1063

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-18 : 14:28:17

MS probably has a workaround for this access violation. Check out MS' knowledge base at http://support.microsoft.com and search on c0000005 EXCEPTION_ACCESS_VIOLATION.

You'll need to weed through the articles to determine which one fits your query.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-18 : 14:48:30

quote:
Originally posted by mallikarjuna_

There are 75 columns in the select statement out of which 25 are text columns.

That's a lot of data ofr each row...

What do you do with it when you get it?

And Text columns in a simple select will get truncated, no?

What's in the text columns anyway? varchar(8000) isn't big enough?

Brett

8-)

mallikarjuna_
Starting Member

20 Posts

Posted - 2003-11-18 : 14:53:31

The data retrieved from the query is loaded to a table on a monthly basis. The text columns has descriptions and the max width is 120. If I remove the case statement it always retrieves the data.

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-18 : 15:03:51

I didn't think you could do that....What meaning does it have btw


USE Northwind
GO

SELECT    OrderId
	, CASE WHEN EmployeeId = 5 THEN SUM(ShipVia) ELSE COUNT(ShipVia) END AS Huh 
  FROM Orders
GROUP BY OrderId, EmployeeId

Brett

8-)

mallikarjuna_
Starting Member

20 Posts

Posted - 2003-11-18 : 15:20:45

Am working on a Hospital DW project. The logic is if the patient category is Out Patient then calculate consult visits as sum of Consult Types else count of case numbers. There are a few hospitals that are covered in this project and two hospitals have only Out Patient category. The stored procedure is almost same for all the hospitals, done so for standardization , so the case statement is used and, this case statement is causing the Access Violation problem for only one of the hospital which has only Out Patient data. I realize that if I doesnt use the case statement this AV problem is not there, but ironically the same stored procedure is in use from last couple of years and there was no problem. My problem seems to be solved as of now, but still puzzled why suddenly this behaviour, and even if I keep the case statement but remove a few columns, the select statement does retrieve data.
Btw thanks for the replies. Would appreciate if you would let me know if there is any other solution.

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-18 : 15:33:17

I thought NULL might be a problem...but it's not


CREATE TABLE myTable99 (Col1 int, col2 int)
GO

   INSERT INTO myTable99 (Col1, Col2)
	SELECT 1,1    UNION ALL
	SELECT 1,NULL UNION ALL
	SELECT 2,1    UNION ALL
	SELECT 2,1    UNION ALL
	SELECT NULL,1
GO

  SELECT Col1, CASE WHEN Col1 = 1 THEN SUM(Col2) ELSE COUNT(Col2) END
    FROM myTable99
GROUP BY Col1


DROP TABLE myTable99
GO

You say if you take out the case it works..

And that this hospital is different because it's ONLY outpatient...

Definete clues there...

What's it 3AM in singapore?

Brett

8-)

mallikarjuna_
Starting Member

20 Posts

Posted - 2003-11-18 : 15:41:51

Yes it works if I doesnt use Case and for another hospital which has only Out Patient data, the same query works fine.
Its almost 5am now.

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-18 : 15:47:23

And you're doing a group by on 45 columns?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources