Hi:
I have a process that works just fine from my machine, but fails when run on a different machine.
This is an Access Data Project connected to SQL Server 7.0 using ADO 2.7. Locally I run Windows 2000 and Access 2000. The SQL Server is located in another state, but I am inside the firewall and on the network there.
The process is pretty standard, execute stored procedures using a command object to rebuild specific tables in the database. Again, from my machine, it works just fine.
If I remotely log into another machine (Windows 2000, Access 2002) that is within a couple of feet of the SQL Server and run the same application on that machine, one stored procedure fails. Aside from the Access 2000 and Access 2002 difference, there is nothing else that should make a difference. I've tried using the Access 2002 app in both Access 2000 format and Access 2002 format. No difference.
The stored procedure creates a cursor and processes a number of records sequentially. With each record it processes it updates two tables with the results.
For what it's worth, here it is:
Code:
DECLARE @strSN AS VARCHAR(14)
DECLARE @strSurvivingSN AS VARCHAR (14)
DECLARE @strOriginatingSN AS VARCHAR (14)
DECLARE @strLicenseSNHistory AS VARCHAR(200)
DECLARE @intSeparatorPosition AS INT
DECLARE @strCurrentSN AS VARCHAR(12)
DECLARE @intCurrentRecordID AS INT
DECLARE crsrSNBasis CURSOR
FOR
SELECT T1.Serial_No, T1.RecordID
FROM A_AccountLicenses AS T1
WHERE T1.SNHistory IS NULL
OPEN crsrSNBasis
FETCH crsrSNBasis INTO @strSN, @intCurrentRecordID
WHILE @@fetch_status = 0
BEGIN
EXEC LicenseSNHistory @strSN, @strSurvivingSN OUTPUT, @strOriginatingSN OUTPUT, @strLicenseSNHistory OUTPUT
UPDATE A_AccountLicenses
SET SNHistory = @strLicenseSNHistory
WHERE CURRENT OF crsrSNBasis
WHILE @strLicenseSNHistory <> ''
BEGIN
SET @intSeparatorPosition = CHARINDEX(',', @strLicenseSNHistory)
IF @intSeparatorPosition > 0
BEGIN
SET @strCurrentSN = LEFT(@strLicenseSNHistory,@intSeparatorPosition - 1)
SET @strLicenseSNHistory = RIGHT(@strLicenseSNHistory, LEN(@strLicenseSNHistory) - @intSeparatorPosition)
END
ELSE
BEGIN
SET @strCurrentSN = @strLicenseSNHistory
SET @strLicenseSNHistory = ''
END
INSERT INTO A_AccountLicensesHistory (RequestID, AccountLicenseID, ActiveSN, Serial_No, LastDate)
VALUES (13, @intCurrentRecordID, @strSN, @strCurrentSN, GetDate())
END
FETCH crsrSNBasis INTO @strSN, @intCurrentRecordID
END
CLOSE crsrSNBasis
DEALLOCATE crsrSNBasis
The failure mode is that it doesn't complete processing all of the records that should be processed. As you can see from the code, it should process ALL records 'WHERE T1.SNHistory IS NULL', but in fact it processes a handful successfully and then moves on. I can't figure out why. I've tried to use the ADO.Error object to get error information, but none is returned.
Anyone know of a bug that may be responsible for this behavior?
Thanks. Any and all help is appreciated.
JK
Stored Procedure/Command Object results differ
