Exception_Access_Violation Error?

Hi all,
Our production server had a raid failure, which we fixed overnight.

This is where the problem started, at least i think.

Now all of a sudden(after the server was restarted) i am getting following error, when one of the stored procedure gets executed
(I have more then 200 Stored Procedure and they are all working fine)

It does not throw error everytime it gets executed, but randomly it will fail.

"SqlDumpExceptionHandler:

Process 233 generated fatal exception c0000005

EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process"

This error also creats a dump file in \MSSQL\Log and logs into SQL Error Log.

I also did DBCC CHECKDB suggested by many people on Internet and everything seems fine, in terms of data corruption.

CHECKDB found 0 allocation errors and 0 consistency errors in database 'XXXX'.

I even tried dropping and recreating the stored procedure, but same result and no luck.

My question is why all of a sudden or server re-start would trigger this errors?

By the way I am using VB.Net 2003 and SQL Server 2000 and Windows 2000 is hte OS on DB Server.

My only fear is that our SQL server does not have latest service pack, but it never had even before the raid failure and all the stored procedure were working fine.

I restored the backup of this Server(SS 2000) and restored it on SQL Server 2005. I tried executing the the same report and it works on SQL Server 2005.

I dont understand whats happening over here and information dump files doesent make any sense to me.

Any ideas or suggestion will be highly appreciated.

Mits

Hi Mits,

I had the same problem with a stored procedure I made one simple change too. All I did was change a parameter of type nvarchar(4000) to type ntext. This caused the same error as what your getting, where the SP worked before.

Two things worked for me:

1) Use the EXEC function and ecapulate your SQL query in quotes. Example:

CREATE PROCEDURE [usp_GroupPermissions_Assert]
(
@.GroupID int,
@.PermissionName nvarchar(255),
@.PermissionValue ntext
)
AS

SET NOCOUNT ON

EXEC('
UPDATE
PERMISSIONS
SET
PERMISSIONS.PERMISSIONVALUE = ''' + @.PermissionValue + '''
FROM
PERMISSIONS_PACKAGES_GROUPS PERMISSIONS
WHERE
AND PERMISSIONS.PERMISSIONNAME = ''' + @.PermissionName + '''
AND PERMISSIONS.GROUPS_ID = ''' + @.GroupID + '''
ELSE
')

SET NOCOUNT OFF

GO

2) Install Service Pack 4.

Hope this helps.

Chris Nillissen