Error Opening ADORecordset to Retrieve Flowfield from SQL

mxlim

I have a report which connects to multiple DB's on SQL Server using Microsoft ActiveX Data Objects 2.8 Library. It calls a stored procedure from the SQL Server which returns a table of records from several databases.

The report works fine but it throws up an error when I try to retrieve a flowfield. On the SQL side I've tested the stored procedure in the SQL Query Analyzer and it returns the table with flowfield as expected. When i run the report in Navision it throws the following error: "The call to member EOF failed. ADODB.Recordset returned the following message: Operation is not allowed when the object is closed".

When I remove the SELECT FLOWFIELD from the stored procedure, the report runs. Otherwise it doesn't. I've been tearing my hair up trying to solve the problem. Does anyone have any ideas?

mxlim

Here's the code for the Stored Procedure:

CREATE PROCEDURE [dbo].[USP_GetVendorAll]
  @CompanyList NVARCHAR(2000)
AS

BEGIN
  SET NOCOUNT ON;
  DECLARE @CompanyName VARCHAR(30)
  DECLARE @OrigCompanyName VARCHAR(30) 
  DECLARE @SQLString NVARCHAR(4000)
  DECLARE @Counter INT
  CREATE TABLE #tmp
  (
    CName VARCHAR(100),
    No VARCHAR(100),
    Name VARCHAR(100),
    Addr VARCHAR(100),
    City VARCHAR(100),
    PhoneNo VARCHAR(100),
	Amt	DECIMAL(20,2)
  )
  
  DECLARE curCompany CURSOR FOR SELECT [Name] as CompanyName from dbo.Company
  OPEN curCompany
  
  SET @Counter = 0
  SET @SQLString = ''
  
  FETCH NEXT FROM curCompany INTO @CompanyName
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @OrigCompanyName = @CompanyName
    SET @CompanyName = REPLACE(@CompanyName,'.','_');
    SET @CompanyName = REPLACE(@CompanyName,'"','_');
    SET @CompanyName = REPLACE(@CompanyName,'\','_');
    SET @CompanyName = REPLACE(@CompanyName,'/','_');
    SET @CompanyName = REPLACE(@CompanyName,'''','_');
	
    SET @SQLString = @SQLString +
    '
    SELECT ''' + @OrigCompanyName + ''' AS CName,[' + @CompanyName + '$Vendor].[No_] AS No,[' + @CompanyName + '$Vendor].[Name],[' + @CompanyName + '$Vendor].[Address] AS Addr,[' + @CompanyName + '$Vendor].[City],[' + @CompanyName + '$Vendor].[Phone No_] AS PhoneNo,SUM([' + @CompanyName + '$Detailed Vendor Ledg_ Entry].[Debit Amount]) AS Amt
    FROM [' + @CompanyName + '$Vendor] JOIN iter_charlist_to_table(''' + @CompanyList + ''',DEFAULT) AS s ON ''' + @OrigCompanyName + ''' = s.nstr LEFT JOIN [' + @CompanyName + '$Detailed Vendor Ledg_ Entry]
	ON [' + @CompanyName + '$Detailed Vendor Ledg_ Entry].[Vendor No_] = [' + @CompanyName + '$Vendor].[No_] AND [' + @CompanyName + '$Detailed Vendor Ledg_ Entry].[Entry Type] <> 2
	GROUP BY [' + @CompanyName + '$Vendor].[No_],[' + @CompanyName + '$Vendor].[Name],[' + @CompanyName + '$Vendor].[Address],[' + @CompanyName + '$Vendor].[City],[' + @CompanyName + '$Vendor].[Phone No_]
    '
    FETCH NEXT FROM curCompany INTO @CompanyName
    INSERT INTO #tmp EXEC sp_executesql @SQLString
    SET @SQLString = ''
  END;
  
SET @SQLString = ''
SET @SQLString = @SQLString + 'SELECT * FROM #tmp'
EXEC sp_executesql @SQLString

CLOSE curCompany
DEALLOCATE curCompany

END
GO

Here's the code in the Navision report [DataItem = Integer]:

//-- Create connection
IF ISCLEAR(ADOConnection) THEN CREATE(ADOConnection);
ConnectionString := STRSUBSTNO(Parameter000,ServerName,DBName,Login,Password);
ADOConnection.ConnectionString(ConnectionString);
ADOConnection.Open;

//-- Execute command
IF ISCLEAR(ADOCommand) THEN CREATE(ADOCommand);
ActiveConnection := ADOConnection;
ADOCommand.ActiveConnection := ActiveConnection;
ADOCommand.CommandText := SPName;
ADOCommand.CommandType := 4;
ADOCommand.CommandTimeout := 0;
ADOParameter := ADOCommand.CreateParameter('@CompanyList',200,1,2000,SPParameters);
ADOCommand.Parameters.Append(ADOParameter);
ADOCommand.Execute;
IF ISCLEAR(ADORecordset) THEN CREATE(ADORecordset);
ADORecordset.Open(ADOCommand);

//-- Get record
WHILE NOT ADORecordset.EOF DO BEGIN
  //Get records here
  IF RptTempTable.INSERT THEN
  COMMIT;
  ADORecordset.MoveNext;
  PrevCompany := CurrCompany;
END;

//-- Close connection
ADORecordset.Close;
ADOConnection.Close;
CLEAR(ADOCommand);
CLEAR(ADOParameter);
CLEAR(ADOConnection);

garak

why do you have a commit in your while loop ?

mxlim

That's for grouping the virtual table.

Anyway I have solved the problem. The error occurred because for certain records, the flowfield returns NULL instead of 0.00 (when there is no Ledger Entry). So what I did was to SET ANSI_WARNINGS OFF and SET ANSI_NULLS OFF in the stored procedure. Then update records which have NULL to 0.00 and everything runs okay.

kriki

[Topic moved from Navision forum to SQL General forum]