?
Solved

How do I get rid of Error:Too many arguments specified from CRViewer in Crystal XI called from VB6 to SQL 2000

Posted on 2011-09-15
8
Medium Priority
?
1,165 Views
Last Modified: 2012-05-12
I have been converting an old app from Access to SQL server (2000) back end. All reports have been converted to use SQL ODBC datasource but there are two reports that continuously give error messages:
Failed to retrieve data from the database Details 42000 [Microsoft][ODBC SQL Server Driver][Sql Server] Procedure or function usp_Q_PeriodCI has too many arguments specified. [Database Vendor Code: 8144]

I wrote a stand alone program for testing that just loads the report reads the number of arguments and passes sample values to the report and via the report to the SQL stored procedure.
I have rewritten the stored procedure and still get the same error message.
What is strange is that from the Crystal XI development the report runs fine.
All the other reports which are variants on this report involving different combinations of customer, item, salesperson, and company work fine. Only the two reports that are by customer and item or item and customer have this problem. I have even created a report from scratch that is simple and only displays rows from the stored procedure; no grouping or summarizing and it still doesn't work. OS platform is Win XPSP3, VB6, Crystal XI.

Below is the sql stored procedures: The first reads and summarizes data for a date range and is called four times to create Current period current year, Current Period Last Year, and year to date summary data for current and last years.
The function called is used globally throughout the stored procedures and just takes two dates and creates the other 3 date ranges.
Your help would be appreciated.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER  procedure usp_Q_PeriodCI (@pDate1 datetime,
                             @pDate2 datetime,

                             @pCustno1  nvarchar(6),
                             @pCustno2  nvarchar(6),
                             @pItem1  nvarchar(15),
                             @pItem2  nvarchar(15),
                             @pCoNo1  nvarchar(2),
                             @pCoNo2  nvarchar(2))
AS
declare @d1LY datetime, @d2LY datetime, @dStart datetime, @dStartLY datetime
set nocount on
SET ANSI_WARNINGS OFF

exec usp_CalcRptPastDates @pDate1, @pDate2, @d1LY output, @d2LY output, @dStart output, @dStartLY output


SELECT
  tblCP.cono,
  tblCP.custno,
  tblCP.item,
  max(tblCP.company) AS FirstOfcompany,
  max(ItemMaster.ITMDESC) AS FirstOfDESCRIP
into #Q_PeriodCI_List
FROM
  tblCP
  JOIN ItemMaster ON tblCP.cono = ItemMaster.OLDNo AND tblCP.item = ItemMaster.ITEM
 
WHERE
  tblCP.custno Between @pCustno1 And @pCustno2 AND
  tblCP.item Between @pItem1 And @pItem2 AND
  tblCP.cono Between @pCoNo1 And @pCoNo2
GROUP BY tblCP.cono, tblCP.custno, tblCP.item


create table #Q_PeriodCI1(cono  nvarchar(2) ,custno nvarchar(6), item nvarchar(15), sales float NULL)
insert #Q_PeriodCI1 exec Q_rptCPbyConoCustnoItem @pDate1, @pDate2, @pCustno1, @pCustno2, @pItem1, @pItem2, @pCoNo1, @pCoNo2

create table #Q_PeriodCI2(cono  nvarchar(2) ,custno nvarchar(6), item nvarchar(15), sales float NULL)
insert #Q_PeriodCI2 exec Q_rptCPbyConoCustnoItem @d1LY, @d2LY, @pCustno1, @pCustno2, @pItem1, @pItem2, @pCoNo1, @pCoNo2

create table #Q_PeriodCI1_YTD(cono  nvarchar(2) ,custno nvarchar(6), item nvarchar(15), sales float NULL)
insert #Q_PeriodCI1_YTD exec Q_rptCPbyConoCustnoItem @dStart, @pDate2, @pCustno1, @pCustno2, @pItem1, @pItem2, @pCoNo1, @pCoNo2

create table #Q_PeriodCI2_YTD(cono  nvarchar(2) ,custno nvarchar(6), item nvarchar(15), sales float NULL)
insert #Q_PeriodCI2_YTD exec Q_rptCPbyConoCustnoItem @dStartLY, @d2LY, @pCustno1, @pCustno2, @pItem1, @pItem2, @pCoNo1, @pCoNo2

SELECT DISTINCT
  C.CoNo,
  C_List.custno,
  C_List.item,
  C_List.FirstOfcompany,
  C1.sales AS sales1,
  C2.sales AS sales2,
  C1_YTD.sales AS MaxOfytd1,
  C2_YTD.sales AS MaxOfytd2,
  C_List.FirstOfDESCRIP,
  C.Description AS CoNoD
FROM
  tblCompany C
  INNER JOIN #Q_PeriodCI_List C_list ON C.CoNo = C_list.cono
  LEFT JOIN #Q_PeriodCI1 C1 ON C_list.cono = C1.cono AND C_list.custno = C1.custno AND C_list.item = C1.item
  LEFT JOIN #Q_PeriodCI2 C2 ON C_list.cono = C2.cono AND C_list.custno = C2.custno AND C_list.item = C2.item
  LEFT JOIN #Q_PeriodCI1_YTD C1_YTD ON C_list.cono = C1_YTD.cono AND C_list.custno = C1_YTD.custno AND C_list.item = C1_YTD.item
  LEFT JOIN #Q_PeriodCI2_YTD C2_YTD ON C_list.cono = C2_YTD.cono AND C_list.custno = C2_YTD.custno AND C_list.item = C2_YTD.item
WHERE C1.sales<>0 OR C2.sales<>0 OR C1_YTD.sales<>0 OR C2_YTD.sales<>0

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
0
Comment
Question by:jup9140
  • 5
  • 3
8 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 36546529
Does the SP run in SQL Manager?

mlmcc
0
 

Author Comment

by:jup9140
ID: 36546587
Yes, everything is fine in Query Analyzer.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 36546996
I reread the question.

If you create a new report from the SP in CR XI then it works.

You add the new report to your application then the report doesnt work?
Are you using the dlls from CR XI in the application?

Is this CR XI R1 or R2?
Check HELP --> ABOUT CRYSTAL
WHat is the version?

CR XI R1 is 11.0.xx.xxx
CR XI R2 is 11.5.xx.xxx

You should be using v11.5 or CR XI R2

mlmcc
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:jup9140
ID: 36551263
I've been using R1 and up until last Monday (the prior week I was converting all the reports to SQL server) things were fine. Now I find that even after quitting and restarting Crystal and creating a new report it still tells me there are too many arguments. This is amazing as the procedure in question is not in the new report AT ALL!!!
I got a copy of R2 from SAP and will install it and all the patches. I hope this fixes the problem without breaking my VB6 application.
I will report back.
0
 

Author Comment

by:jup9140
ID: 36570397
I removed XI R1 then installed XI R2 and after doing so whenever I opened my application with the Viewer component, it wanted to install R2 again and asked for the location of a file that was not part of the install package.
I finally uninstalled R2 and reinstalled R1 and sp4. Everything seems okay now but I don't know why I had a problem with R2 when opening my VB6 app. Also what is there in R2 that is applicable to VB6?
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 800 total points
ID: 36571164
I don't know.  I just know that Crystal recommends using R2 with VB6.  SHortly after releasing R2 they stopped supporting R1.

R2 was released for use with VS2005 and .Net 2.0

Not sure what your issue with R2 is.  I use it all the time with VB6 with no issues.

mlmcc
0
 

Accepted Solution

by:
jup9140 earned 0 total points
ID: 36593192
The solution was to uninstall and reinstall Crystal XI and all service packs (sp4). This solved the problem.

Many thanks to those who contributed to this solution.

Jeff
0
 

Author Closing Comment

by:jup9140
ID: 36813427
This solved my immediate problem. But if I have to go to XI R2 then applying it to existing vb6 projects may prove difficult as the program wants to relaunch the R2 installer when opening the viewer form from the development einvironment.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question