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
1,072 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 100

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 100

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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 100

Assisted Solution

by:mlmcc
mlmcc earned 200 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

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.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now