Problems having moved from SQL 7 to SQL 2000

We ported our database 2 weeks ago. We have come across a problem with
one
stored procedure.
1) Running some queries / Procedures in Query Analyser return
"[Microsoft][ODBC SQL Server Driver][Shared
Memory]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken"
The SQL Logs have the following errors whenever the procedure runs
* Exception Address = 00000000
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
I have tried to run the procedure from SQL Analyser on my client
machine and locally on the server. Both return the same error.
The stored procedure completes and the records appear in the relvent
tables. At best we get the errors described above and entries in teh
SQL log. The worst scenario we have had is each time the procedure
fired the AQL server services terminated unexpectedly disconencting
everyone from the SQL server and all databases (More of a problem I am
sure you will agree).
The stored procedure is outlined below. Running in SQL Analyser does
not display the printed debug messages although they are viewable in
the Stack dump.
SQL VERSION : Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec
17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
CREATE PROCEDURE spSaveNewCall
/*----*/
/* PROCEDURE : spSaveNewCall */
/* Description : saves a new call in database */
/*----*/
@.i_str_CustID varchar(50),
@.i_str_CustRef varchar(10),
@.i_str_ContactID int,
@.i_str_ProductCode varchar(6),
@.i_str_ModuleCode varchar(6),
@.i_str_Version varchar(10),
@.i_str_PatchLevel varchar(10),
@.i_str_EmergencyPatch varchar(10),
@.i_str_Environment varchar(4),
@.i_str_CallType varchar(100),
@.i_str_CallDescription text,
@.i_str_Priority varchar(1),
@.i_str_SLAclass varchar(10),
@.i_str_CustType varchar(15),
@.i_str_ModuleName varchar(40),
@.i_str_ProductName varchar(40),
@.i_str_ServerPlatform varchar(40)
AS
DECLARE @.l_str_CallID varchar(8)
DECLARE @.l_boo_calllog int
DECLARE @.l_boo_asgnmnt int
DECLARE @.l_boo_detail int
DECLARE @.l_boo_subset int
BEGIN TRANSACTION
print 'DEBUG : --update sequence number for anyone else trying to log
a call'
UPDATE HeatSeq
SET SeqValue = SeqValue + 1
WHERE SeqKey = 'CallID'
--get the next call id
SET @.l_str_CallID = (SELECT SeqValue AS NextCall FROM HEATSeq WHERE
SeqKey = 'CallID')
SET @.l_str_CallID = RIGHT('00000000' + @.l_str_CallID,8)
print 'DEBUG : --start logging call'
SET @.l_boo_calllog = 0
SET @.l_boo_asgnmnt = 0
SET @.l_boo_detail = 0
SET @.l_boo_subset = 0
INSERT INTO CallLog
( CallID,
CompanyRef,
CustID,
CallType,
CallStatus,
Tracker,
Priority,
CallDesc,
RecvdBy,
RecvdDate,
RecvdTime,
ModBy,
ModDate,
ModTime,
CallSource,
SLAClass,
Environment,
ProductCode,
ModuleCode,
OnHold,
ProductVersion,
PatchLevel,
EmergencyPatch,
CustType,
ProductModule,
Product,
DBPlatform,
SLA_ClockStatus,
SLA_CalcCBWarn,
SLA_CalcCloseWarn,
SLA_CalcClose,
SLA_CalcCB,
SLA_Status,
Weighting,
PriorityDesc,
ServerPlatform,
DTLastMod,
Escalated,
EscalationOrder
)
( SELECT @.l_str_CallID,
@.i_str_CustRef,
@.i_str_CustID,
@.i_str_CallType,
'Open',
'HSS',
@.i_str_Priority,
@.i_str_CallDescription,
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'Website',
@.i_str_SLAclass,
@.i_str_Environment,
@.i_str_ProductCode,
@.i_str_ModuleCode,
'F',
@.i_str_Version,
@.i_str_PatchLevel,
@.i_str_EmergencyPatch,
@.i_str_CustType,
@.i_str_ModuleName,
@.i_str_ProductName,
'Progress',
'SLA is Stopped',
SLA_WarnResponse,
SLA_WarnComplete,
SLA_TgtComplete,
SLA_TgtResponse,
'OK',
Weighting,
SLA_PriorityDesc,
@.i_str_ServerPlatform,
datediff(ss,'01-01-1970',getdate()),
'F',
0
FROM SLAMatrix,
Priority
WHERE SLA_Priority = Priority.Priority
AND SLA_Priority = @.i_str_Priority
AND SLA_Class = @.i_str_SLAclass
)
print 'DEBUG : --check calllog has been updated'
SET @.l_boo_calllog = @.@.ERROR
print 'DEBUG : --save the subset'
INSERT INTO Subset
( CustID,
CallID,
CustType,
EmailID,
Phone,
CompanyName,
Contact,
Ext,
CustRefReq,
OrchMgr,
Phone2,
Ext2,
PhoneDesc1,
PhoneDesc2,
ContactMethod,
Fax1,
Alert,
AccMgr,
ProjMgr,
Supported,
Mobile,
KeyCustomer,
ContactSeqNum
)
( SELECT @.i_str_CustID,
@.l_str_CallID,
CustType,
Email1,
Telephone1,
CustomerName,
ContactName,
Extension1,
CustRef,
FPOC,
Telephone2,
Extension2,
Tel1Description,
Tel2Description,
ContactMethod,
Fax1,
ALERT,
AccountManager,
ProjectManager,
Supported,
Mobile1,
KeyCustomer,
ContactSeqNum
FROM Contacts, Profile
WHERE ContactSeqNum = @.i_str_ContactID
AND Contacts.CustID = Profile.CustID
)
print 'DEBUG : --check subset has been updated'
SET @.l_boo_subset = @.@.ERROR
print 'DEBUG : --save the details'
INSERT INTO Detail
(
CallID,
Details
)
VALUES
(
@.l_str_CallID,
''
)
print 'DEBUG : --check detail has been updated'
SET @.l_boo_detail = @.@.ERROR
print 'DEBUG : --save the assignment'
INSERT INTO Asgnmnt
(
AssignedBy,
DateAssign,
TimeAssign,
GroupName,
CallID,
HEATSeq,
GroupEMail,
DTLastMod,
Assignee,
GroupDesc,
ResolveOrder,
WhoResolv
)
VALUES
(
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'WebUpdate',
@.l_str_CallID,
datediff(ss,'01-01-1970',getdate()),
'CustomerServices@.orchard-systems.co.uk',
datediff(ss,'01-01-1970',getdate()),
'HSS',
'New call logged on-line',
0,
''
)
print 'DEBUG : --check asgnmnt has been updated'
SET @.l_boo_asgnmnt = @.@.ERROR
IF @.l_boo_calllog = 0 AND @.l_boo_asgnmnt = 0 AND @.l_boo_detail = 0
AND @.l_boo_subset = 0
BEGIN
print 'DEBUG : --Transaction Commit'
COMMIT TRANSACTION
--return the call id
SELECT @.l_str_CallID AS NextCall
END
ELSE
BEGIN
print 'DEBUG : --Transaction Rollback'
ROLLBACK TRANSACTION
SELECT 'No Call Raised' AS NextCall
END
/*----*/
GOAt the very least, you need to correct your logic to accurately figure out
what is happening. You have a fundamental mistake in your error handling.
Try the following:
if object_id ('tempdb..#test') is not null
drop table #test
create table #test (test_id int not null)
insert #test (test_id) values (null)
print 'debug'
select @.@.error
insert #test (test_id) values (null)
select @.@.error
print 'debug'
go
Notice how the print statement changes the value of @.@.error. Erland has an
excellent discussion of error handling
(http://www.sommarskog.se/error-handling-II.html). Once you understand the
above, you should then correct the 2nd logic error. There would appear to
be no need to execute any insert statements that follow the first failed
insert statement. Yet your procedure just keeps blindly inserting until the
end. This only wastes server resources. You should also review any trigger
logic that will execute - this is a frequent problem area.
Inspite of the logic issues, the error log indicates that the fault is
within sql server. This requires assistance from MS to diagnose and
correct. The profiler might offer some insight - perhaps there is a problem
with parallelism. If so, you might be able to at least avoid the problem.
Perhaps the problem can be avoided entirely by correcting the logic flaws
alone.sql