Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old November 20th, 2003, 11:07 PM
Authorized User
Join Date: Jun 2003
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default Stored Procedure/Command Object results differ


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:

DECLARE @strSurvivingSN AS VARCHAR (14)
DECLARE @strOriginatingSN AS VARCHAR (14)
DECLARE @strLicenseSNHistory AS VARCHAR(200)

DECLARE @intSeparatorPosition AS INT

DECLARE @intCurrentRecordID AS INT

SELECT T1.Serial_No, T1.RecordID
FROM A_AccountLicenses AS T1

OPEN crsrSNBasis

FETCH crsrSNBasis INTO @strSN, @intCurrentRecordID

WHILE @@fetch_status = 0
      EXEC LicenseSNHistory @strSN, @strSurvivingSN OUTPUT, @strOriginatingSN OUTPUT, @strLicenseSNHistory OUTPUT

      UPDATE A_AccountLicenses
      SET SNHistory = @strLicenseSNHistory
      WHERE CURRENT OF crsrSNBasis 

      WHILE @strLicenseSNHistory <> ''
            SET @intSeparatorPosition =  CHARINDEX(',', @strLicenseSNHistory)
            IF @intSeparatorPosition > 0 
                  SET @strCurrentSN = LEFT(@strLicenseSNHistory,@intSeparatorPosition - 1)
                  SET @strLicenseSNHistory = RIGHT(@strLicenseSNHistory, LEN(@strLicenseSNHistory) - @intSeparatorPosition)
                   SET @strCurrentSN = @strLicenseSNHistory
                   SET @strLicenseSNHistory = ''

            INSERT INTO A_AccountLicensesHistory (RequestID, AccountLicenseID, ActiveSN, Serial_No, LastDate)
            VALUES (13, @intCurrentRecordID, @strSN, @strCurrentSN, GetDate())

         FETCH crsrSNBasis INTO @strSN, @intCurrentRecordID

CLOSE 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.


Similar Threads
Thread Thread Starter Forum Replies Last Post
Command text was not set for the command object Sheraz Khan Classic ASP Databases 2 May 29th, 2007 12:57 AM
Returning Stored Procedure Results from ASP page JennaAckerson Classic ASP Basics 0 December 23rd, 2005 11:44 AM
Yukon Results executes Stored Procedure bmains SQL Server 2000 0 May 23rd, 2005 12:35 PM
Command text was not set for the command object. deepa12 BOOK: Beginning ASP 3.0 5 November 2nd, 2004 05:37 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.