May 14, 2007 at 11:21 am
I am maintaining a system based around VB6, calling SQL2000 stored procedures via ODBC using MDAC 2.7.
I have a screen that is passes a set of values to a SP, which then stores the data by EXECing a 2nd SP, once for each value. The VB then re-reads the data and redisplays. The problem is that the SP aborts part way through and never actually stores the data. There are no apparent errors or exceptions and SQL-Profiler simply reports an "Attention" event with no details.
The MSDN implies that the Attention event is caused by a timeout from the client BUT I am certain that this is not the case here. Adding WaitFor statements to slow down the SP does not alter the point at which the event occurs. Also this is nowhere near being my slowest SP.
I have managed to reproduce the error using the following minimal SP (where System & Country are existing tables with 1 and 24 rows respectively);
Create Procedure TEST AS
Select * from System
Select * from System
Select * from Country
GO
This error occurs on several servers and does not appear to be related to server load, memory or disk space. Also DBCC reports no errors for the tables or the entire database catalog.
The error only occurs when run from VB6 (via SQLExecDirect) ... it is fine if run from VB6 via ADO and also if run from Query Analyzer.
Any ideas about what is causing this would be very welcome so that I can determine how likely it is to occur in the rest of the system.
Thanks for any help.
May 15, 2007 at 3:44 am
May 15, 2007 at 5:01 am
Sorry I forgot to answer your second question.
No, I haven't checked the code outside of a stored proc from VB. The error only seems to occur when run via ODBC and our database layer is coded under the assumption that all access is via stored procedures.
However I have run the EXEC'd procedure directly from VB with no problems and I have also run the top-level procedure from VB using ADO with no problems.
I have also run the top-level procedure from Query Analyser with no problems and have copied the code from the procedures into the query window so that it runs as an ad-hoc script - still with no problems.
I have reproduced this error here on several PCs, databases and servers however I have just confirmed that the program is working correctly on-site, which may imply a SQL Server service-pack or setup problem.
May 15, 2007 at 5:11 am
I'm sure I posted this earlier but it seems to have been lost in the ether - my apologies if this posts twice.
These are the stored procedures that are causing the problem;
CREATE Procedure UpdateConfigurationData
@SystemDescription varchar(255),
@CurrentDirectory varchar(255),
@TempWorkFilePath varchar(255),
@ScanFileLibPath varchar(255),
@ScanFileCurPath varchar(255),
@FSA_Zeta_Dir varchar(255),
@FSA_Zeta_SubDir varchar(255),
@vcActiveLHS varchar(20),
@vcActiveRHS varchar(20),
@vcInActiveLHS varchar(20),
@vcInActiveRHS varchar(20)
AS
Declare @SystemID varchar(3)
-- Get System Identifier --
Select @SystemID = SystemID from System where ActiveSystem = 'Y'
Update System
Set
SystemDescription = @SystemDescription,
CurrentDirectory = @CurrentDirectory,
TempWorkFilePath = @TempWorkFilePath,
Where ActiveSystem = 'Y'
-- Update SysParm table --
Exec UpdateSystemParameter 'SFCURRLIB', @SystemID, Null, @ScanFileLibPath
Exec UpdateSystemParameter 'SFCURRPATH', @SystemID, Null, @ScanFileCurPath
Exec UpdateSystemParameter 'FSALIQ', @SystemID, Null, @FSA_Zeta_Dir
Exec UpdateSystemParameter 'FSALIQ3', @SystemID, Null, @FSA_Zeta_SubDir
Exec UpdateSystemParameter 'INACTVCAPR', @SystemID, Null, @vcInActiveRHS
Exec UpdateSystemParameter 'ACTIVECAPL', @SystemID, Null, @vcActiveLHS
Exec UpdateSystemParameter 'ACTIVECAPR', @SystemID, Null, @vcActiveRHS
Exec UpdateSystemParameter 'INACTVCAPL', @SystemID, Null, @vcInActiveLHS
GO
CREATE Procedure UpdateSystemParameter
-- Primary Key fields --
@vcCode varchar(255),
@vcSystemID varchar(3) = '???',
-- Values to be updated --
@vcSystemArea varchar(20) = '???',
@vcDataItem varchar(255) = '???',
@vcDataType varchar(20) = '???',
@vcDescription varchar(100) = '???',
@vcHelpDetails varchar(2000) = '???',
@vcUserConfigurable varchar(1) = '?',
@vcParameterGroupCode varchar(255) = '???',
-- use these to change the primary key ! --
@vcNewCode varchar(255) = '???',
@vcNewSystemID varchar(3) = '???'
AS
/*
Inserts/Updates the specified record in the CSSystemParameters table.
Notes:
This routine allows updates on any subset of the columns. Any variables
not supplied are defaulted to '???', are coerced to Null via the NullIf function
and are defaulted to the existing value via the IsNull function !
This approach provides a "non-destructive" update where the calling code need not
supply all values. The downside is that it is not possible to set a column to Null,
which should not be a problem in this case (except for the group code - see update code).
@vcUserConfigurable is written to a column of type cstYesNoFlag
which can ONLY accept values of 'Y'/'N'/Null.
*/
Declare @iCount int
If @vcSystemID = '???'
Select @vcSystemID = [SystemID]
From System
Where [ActiveSystem] = 'Y'
Select @iCount = count(Code)
from dbo.[CSSystemParameters]
Where [Code] = @vcCode and [SystemID] = @vcSystemID
If isnull(@iCount,0) >=1
BEGIN
-- perform the update --
UPDATE dbo.[CSSystemParameters]
SET
[SystemID] = IsNull(NullIf(@vcNewSystemID, '???'), [SystemID]),
[Code] = IsNull(NullIf(@vcNewCode, '???'), [Code]),
[SystemArea] = IsNull(NullIf(@vcSystemArea, '???'), [SystemArea]),
[DataItem] = IsNull(NullIf(@vcDataItem, '???'), [DataItem]),
[DataType] = IsNull(NullIf(@vcDataType, '???'), [DataType]),
[Description] = IsNull(NullIf(@vcDescription, '???'), [Description]),
[HelpDetails] = IsNull(NullIf(@vcHelpDetails, '???'), [HelpDetails]),
[UserConfigurable] = IsNull(NullIf(@vcUserConfigurable, '?'), [UserConfigurable]),
[ParameterGroupCode] = case @vcParameterGroupCode
when '' then NULL
else IsNull(NullIf(@vcParameterGroupCode, '???'), [ParameterGroupCode])
End
WHERE
[Code] = @vcCode and [SystemID] = @vcSystemID
END
else
BEGIN
-- perform an insertion --
Insert dbo.[CSSystemParameters]
(
[SystemID] ,
[Code] ,
[SystemArea] ,
[DataItem] ,
[DataType] ,
[Description] ,
[HelpDetails] ,
[UserConfigurable],
[ParameterGroupCode]
)
Values
(
@vcSystemID,
@vcCode,
@vcSystemArea,
@vcDataItem,
IsNull(@vcDataType, 'unspecified'),
@vcDescription,
@vcHelpDetails,
@vcUserConfigurable,
@vcParameterGroupCode
)
END
Select @@RowCount
GO
According to SQL Profiler the "Attention" event is being generated during the fifth EXEC, which is aborted, and the remianing three EXEC statements are never reached.
I have swapped the order of the EXECs around and it is always the last three that get missed, this eliminates the possibilty of bad data. I have added a 5-second WaitFor statement after the first EXEC without affecting the point at which the Attention event is generated, this would seem to rule out client-side timeouts but may imply an elapsed-CPU-time link. I have also tried adding code to log progress to another table (e.g. Insert LogTable (LogText) Select 'After EXEC #1') ... this moves the event generation point so that more than three EXECs get missed out, again this may imply a link with elapsed-CPU-time.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply