Stored Proc - Exception Access Violation

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)

Stored Proc - Exception Access Violation

Author

Topic

boogiewoogie
Starting Member

7 Posts

Posted - 2008-07-16 : 14:31:00

Hey all,

I running into a problem with a stored proc that updates a local table based on a set of IDs from a remote table. Something like:

SELECT ID
INTO #TEMPTABLE
FROM [REMOTE-SEVER].[...].[REMOTE-TABLE]

UPDATE LOCAL-TABLE
SET VALUE=@VALUE
FROM LOCAL-TABLE LT
INNER JOIN #TEMPTABLE TT ON LT.ID=TT.ID

When I attempt to execute the stored proc from SQL Server Management Studio, I receive the following error:

SqlDumpExceptionHandler: Process 56 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

I've done some researching and there's a fix that addresses this issue via SP4. However, the SQL Server version I'm running is 8.00.2039 - SP4 (Standard). So, I doubt that's the problem.

Anyone have an idea to resolve this issue or direct me to the right path?

Thanks!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-16 : 14:33:26

Can't you use Transactional Replication instead?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-16 : 14:33:47

You need to get up to the latest build. There are post sp4 hotfixes out there.

Access violations are typically SQL Server bugs, so that's why getting to the latest build is important. If that doesn't work, then you'll need to contact Microsoft for a bug fix.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

boogiewoogie
Starting Member

7 Posts

Posted - 2008-07-16 : 16:52:43

Thanks for the suggestions.

I've been messing with the stored proc and I've isolated when the error appears. In the stored proc, I pass string parameter values which I use to filter the SELECT statement. Something like:

SELECT ID
INTO #TEMPTABLE
FROM [REMOTE-SEVER].[...].[REMOTE-TABLE] RT
WHERE RT.COL_1 = @VALUE_1

When I hard-code the value into @VALUE_1, such as:

SELECT ID
INTO #TEMPTABLE
FROM [REMOTE-SEVER].[...].[REMOTE-TABLE] RT
WHERE RT.COL_1 = 'ABC'

The stored proc completes. However, when I use the paramter name @VALUE_1, I receive the exception access violation.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-16 : 16:55:30

Access violations are typically SQL Server bugs and not bugs in your T-SQL code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

boogiewoogie
Starting Member

7 Posts

Posted - 2008-07-16 : 20:17:56

Well, I'm pretty convinced this is a SQL Server bug as Tara stated. In the meantime, I'll work-around the problem even though the solution isn't ideal. Thanks for all your input.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-17 : 00:42:37

I don't understand why you won't get to the latest build or contact MS for the bug fix. Either case is free if it's a bug.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources