?
Solved

Crystal issues

Posted on 2007-10-01
18
Medium Priority
?
726 Views
Last Modified: 2012-05-05
I am using Crystal 8.5.  Crystal reports crashes when trying to view / connect to stored procedures.  Everytime that I try to do this, Crystal crashes.  Are there limits to how many stored procedures can be used?
0
Comment
Question by:jstreur
  • 8
  • 5
  • 4
  • +1
18 Comments
 
LVL 15

Expert Comment

by:dbbishop
ID: 19992589
I've never had any problems. I've ALWAYS used stored procedures as my data source is CR 8.5 and XI. Are there sub-reports? How many? What do you mean by "Are there limits to how many stored procedures can be used"?
0
 

Author Comment

by:jstreur
ID: 19992655
We have created 2 identical reports, with the exception of the search criteria.  The report that we have set up to search for job number using stored procedures works, however, when we changed the search criteria to a date range, it crashes.  Please help.
0
 
LVL 15

Expert Comment

by:dbbishop
ID: 19992706
Can we see the T-SQL code in the procedure? How are you passing the date range? Are they being passed as strings or as Date objects?
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
LVL 101

Expert Comment

by:mlmcc
ID: 19992877
There are limits on the stored procedure.  CR8.5 can only have a single data source for a report.

WHat is the stored procedure you are using?

Are the stored procedures the same except for the search criteria?

mlmcc
0
 

Author Comment

by:jstreur
ID: 19993264
What is the limit on stored procedures?  We have 21,376 stored procedures in our database
0
 
LVL 15

Expert Comment

by:dbbishop
ID: 19993530
What mlmcc meant is you can only have one stored procedure as the data source for a report. There is no limit to the number of procedures in SQL Server. I think there is some confusion there between terminology.
0
 

Author Comment

by:jstreur
ID: 19993678
Attached to the bottpm of this comment is the T-SQL to the stored procedure of the report.    Right now the procedure is not accepting any parameters.  You can see it is hard coded to 9-25-2008.  The stored procedure runs without error in Query Analyzer.   The report runs correctly in Crystal Reports with and without parameters, but does not run in Any View IDS.  
I can reproduce the error this report generates in Any View by creating a new report pointed to a test environment and re-pointing the data source in Crystal to the production environment.   The report will run in Crystal Reports properly against the new data source location.  However, when published to Any View, Any View produces the same error code.  
This behavior led me to believe that the reports original data source  was not our production environment.  I removed all databases from the report.  When I attempted to re-add the stored procedure Crystal Reports crashed.  Any time I go to, Database,  Add Database to Report& , Browse through ODBC, then Dynamics, Crystal Reports 8.5 will crash.  The dynamics ODBC connection is required for Any View IDS to access data.  If I turn off Stored Procedures in the Options, Crystal Reports will not crash.  
There are 21,376 stored procedures in that database catalog.   I can reproduce the crash on a blank Crystal Report.  This effectively makes it so we cant attach any stored procedures to new reports using the dynamics connection.    I can attach stored procedures to reports on other database catalogs on the same server (such as the master database) using the same dynamics ODBC.  
Crystal Reports 10 can see all the stored procedures and we were able to attach to stored procedures using the same connection.  This test was performed on the same computer with Crystal Report 8.5 which crashes.  However, Crystal Reports 10 doesnt appear to work with the installed version of Any View.

To recap:

We have two issues at-hand.  First is that Crystal 8.5 wont allow us to attach stored procedures to reports using the dynamics ODBC going to GSTEL anymore.  Second is that Any View IDS wont run a Crystal Report that has had its data source changed during the course of development.   We can get around the second issue if we can point directly to GSTEL on production and attach a stored procedure.

It is important to note that we were able to attach stored procedure successfully a few weeks ago.  And Crystal Report 8.5 on Uranus and Mars also crash.

T-SQL:

CREATE   PROCEDURE [dbo].[CP_STM_SHORTAGE_BY_DATES]
      -- Add the parameters for the stored procedure here
        -- @FROMSCHEDULED char(10),
        -- @TOSCHEDULED char(10)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

   
declare @table table (PPN_I char(32), CPN_I char(32), TLEVEL INT, POSITION_NUMBER INT, QUANTITY_I decimal(15,5), CALC_POS varchar(100),
  BACKFLUSHITEM_I bit, WCID_I VARCHAR(12) NULL, OPCODE_I VARCHAR(8) NULL, LABORTIME_I decimal(15,5) null, RTSEQNUM_I varchar(12), ITEMDESC varchar(150), REVISIONLEVEL_I varchar(52),
  GZ_DDLLength varchar(50), GZ_DDLWidth varchar(50), GZ_LengthStr varchar(31), GZ_WidthStr varchar(31), SETUPTIME_I decimal(15,5) null, LISTPRCE decimal(15,5) null,
  LABOR_COST_I decimal(15,5) null, LABOR_VARIABLE_OVERHEAD_AMOUNT decimal(15,5) null, orgItemNumber varchar(200), jobnumber_i varchar(200) )

declare @TempItemNmbr varchar(200)
declare @ItemPos int
declare @jobnumber_i varchar(200)
select @ItemPos = 1

declare @FROMSCHEDULED char(10)
declare @TOSCHEDULED char(10)

select @FROMSCHEDULED = '9-25-2008'
select @TOSCHEDULED = '9-25-2008'

declare myc cursor for
        select distinct PARENTNUMBER_I, PK010033.PPN_I from ICJC1002 left join WR010130 on ICJC1002.CHILDNUMBER_I = WR010130.MANUFACTUREORDER_I LEFT JOIN PK010033 ON PK010033.MANUFACTUREORDER_I = WR010130.MANUFACTUREORDER_I WHERE WR010130.SCHEDULESTARTDATE_I >= @FROMSCHEDULED AND WR010130.SCHEDULESTARTDATE_I <= @TOSCHEDULED and PPN_I is not null order by PK010033.PPN_I
open myc

fetch next from myc into @jobnumber_i, @TempItemNmbr
 
while @@fetch_status = 0
begin

      
--insert main item
insert into @table
SELECT @TempItemNmbr, @TempItemNmbr, 0, 1, @ItemPos, cast(@ItemPos as varchar(200)) + '.0', 1, isnull(RTLINE.WCID_I, ''),
   isnull(OPCODE_I, ''), isnull(LABORTIME_I, 0), isnull(RTLINE.RTSEQNUM_I, ''), I.ITEMDESC, isnull(RTMAIN.REVISIONLEVEL_I, ''), '', '', '', '', isnull(SETUPTIME_I, 0), isnull(IP.LISTPRCE, 0),
   isnull(LC.COST_I, 0), isnull(LC.VARIABLE_OVERHEAD_AMOUNT, 0), @TempItemNmbr, @jobnumber_i
from RT010001 RTMAIN
 left join RT010130 RTLINE ON RTLINE.ROUTINGNAME_I = RTMAIN.ROUTINGNAME_I AND RTLINE.ITEMNMBR = RTMAIN.ITEMNMBR
 left join IV00101 I ON I.ITEMNMBR = @jobnumber_i
 left join IV00105 IP on IP.ITEMNMBR = @jobnumber_i
 left join LC010014 LC on LC.LABORCODE_I = RTLINE.LABORCODE2_I
where RTMAIN.RTPRIMARY_I = 1 AND RTMAIN.ITEMNMBR = @TempItemNmbr
order by RTLINE.RTSEQNUM_I

insert into @table
select A.PPN_I, A.CPN_I, 1, POSITION_NUMBER, A.QUANTITY_I,  cast(@ItemPos as varchar(200)) + '.' + cast(POSITION_NUMBER as varchar(500)), BACKFLUSHITEM_I, isnull(RTLINE.WCID_I, ''),
   isnull(OPCODE_I, ''), isnull(LABORTIME_I, 0), isnull(RTLINE.RTSEQNUM_I, ''), I.ITEMDESC, isnull(RTMAIN.REVISIONLEVEL_I, ''),
    isnull(UM.GZ_DDLLength, ''), isnull(UM.GZ_DDLWidth, ''), isnull(UM.GZ_LengthStr, '') , isnull(UM.GZ_WidthStr, ''), isnull(SETUPTIME_I, 0), isnull(IP.LISTPRCE, 0),
       isnull(LC.COST_I, 0), isnull(LC.VARIABLE_OVERHEAD_AMOUNT, 0), @TempItemNmbr, @jobnumber_i
   from BM010115 A
     left join RT010001 RTMAIN ON RTMAIN.ITEMNMBR = A.CPN_I AND RTMAIN.RTPRIMARY_I = 1
     left join RT010130 RTLINE ON RTLINE.ROUTINGNAME_I = RTMAIN.ROUTINGNAME_I AND RTLINE.ITEMNMBR = RTMAIN.ITEMNMBR
     left join IV00101 I ON I.ITEMNMBR = A.CPN_I
     left join GZ00004 UM on A.PPN_I = UM.ITEMNMBR AND A.CPN_I = UM.CMPTITNM
     left join IV00105 IP on IP.ITEMNMBR = I.ITEMNMBR
     left join LC010014 LC on LC.LABORCODE_I = RTLINE.LABORCODE2_I
where A.PPN_I = @TempItemNmbr and A.BOMCAT_I = 1
order by cast(POSITION_NUMBER as numeric(15,5)), cast(RTLINE.RTSEQNUM_I as numeric(15,5))

declare @tlevel int
set @tlevel = 1
while (select count(A.CPN_I) from BM010115 A where A.PPN_I in (select CPN_I from @table WHERE TLEVEL = @tlevel and orgItemNumber = @TempItemNmbr)) > 0
  begin
   insert into @table
   select A.PPN_I, A.CPN_I, @tlevel + 1, POSITION_NUMBER, A.QUANTITY_I, (select top 1 calc_pos from @table where CPN_I = A.PPN_I and TLEVEL = @tlevel and orgItemNumber = @TempItemNmbr) + '.' + cast(A.POSITION_NUMBER as varchar(500)),
      BACKFLUSHITEM_I, isnull(RTLINE.WCID_I, ''), isnull(OPCODE_I, ''), isnull(LABORTIME_I, 0), isnull(RTLINE.RTSEQNUM_I, ''), I.ITEMDESC, isnull(RTMAIN.REVISIONLEVEL_I, ''),
      isnull(UM.GZ_DDLLength, ''), isnull(UM.GZ_DDLWidth, ''), isnull(UM.GZ_LengthStr, '') , isnull(UM.GZ_WidthStr, ''), isnull(SETUPTIME_I, 0), isnull(IP.LISTPRCE, 0),
      isnull(LC.COST_I, 0), isnull(LC.VARIABLE_OVERHEAD_AMOUNT, 0), @TempItemNmbr, @jobnumber_i
    from BM010115 A
      left join RT010001 RTMAIN ON RTMAIN.ITEMNMBR = A.CPN_I AND RTMAIN.RTPRIMARY_I = 1
      left join RT010130 RTLINE ON RTLINE.ROUTINGNAME_I = RTMAIN.ROUTINGNAME_I AND RTLINE.ITEMNMBR = RTMAIN.ITEMNMBR
      left join IV00101 I ON I.ITEMNMBR = A.CPN_I
      left join GZ00004 UM on A.PPN_I = UM.ITEMNMBR AND A.CPN_I = UM.CMPTITNM
      left join IV00105 IP on IP.ITEMNMBR = I.ITEMNMBR
      left join LC010014 LC on LC.LABORCODE_I = RTLINE.LABORCODE2_I
   where A.PPN_I in (select CPN_I from @table WHERE TLEVEL = @tlevel and orgItemNumber = @TempItemNmbr) and A.BOMCAT_I = 1
   order by cast(A.POSITION_NUMBER as numeric(15,5)), cast(RTLINE.RTSEQNUM_I as numeric(15,5))

   set @tlevel = @tlevel + 1
  end

      
        select @ItemPos = @ItemPos + 1
        fetch next from myc into @jobnumber_i, @TempItemNmbr
end

close myc
deallocate myc


select distinct t.PPN_I, t.CPN_I , t.TLEVEL, t.POSITION_NUMBER, t.QUANTITY_I , t.CALC_POS,
  t.BACKFLUSHITEM_I , t.WCID_I , t.OPCODE_I, t.LABORTIME_I, t.RTSEQNUM_I , t.ITEMDESC , t.REVISIONLEVEL_I ,
  t.GZ_DDLLength, t.GZ_DDLWidth , t.GZ_LengthStr , t.GZ_WidthStr , t.SETUPTIME_I, t.LISTPRCE, t.LABOR_COST_I, t.LABOR_VARIABLE_OVERHEAD_AMOUNT,
  replace(t.calc_pos, '.', 'z'), '1.'+ t.CALC_POS as TempCalcPos, i1.ITMCLSCD,
      case i2.REPLENISHMENTMETHOD when 1 then 'Make' else 'Buy' end as MakeBuyCode,
      pk.SUGGESTEDQTY_I, isnull(pk.manufactureorder_i, '') as manufactureorder_i, isnull(pk.manufactureorderst_i, '') as manufactureorderst_i,
 icjc.jobnumber_i, icjc.jobdescription_i as jobdescription_i, icjc.custname as custname
  from @table t
      left join iv00101 i1 on i1.itemnmbr = t.cpn_i
      left join iv00102 i2 on i2.itemnmbr = i1.itemnmbr
      left join pk010033 pk on pk.ppn_i = t.ppn_i and pk.itemnmbr = t.cpn_i
        left join icjc0001 icjc on icjc.jobnumber_i =  t.jobnumber_i
--      left join wr010130 wr on wr.manufactureorder_i = pk.manufactureorder_i
--where tlevel <= 1
--where pk.manufactureorderst_i in (2,3)
where pk.manufactureorder_i in (select distinct childnumber_i from icjc1002 where parentnumber_i = icjc.jobnumber_i)
order by replace(t.calc_pos, '.', 'z'), t.RTSEQNUM_I
END



GO

0
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 19993720
Has the database changed in structure?

Did you change database drivers?

Did you add a bunch of new stored procedures?

mlmcc
0
 

Author Comment

by:jstreur
ID: 19993819
During testing I had added up to 6 total.  There are only 4 new ones that are needed right now.   I deleted the test queries and that didnt help.  
0
 

Author Comment

by:jstreur
ID: 19993828
The database has not changed in structure, and the drivers have not changed.
0
 

Author Comment

by:jstreur
ID: 19997922
Does anyone have any ideas?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 19999188
Did this work in the past?

mlmcc
0
 
LVL 42

Expert Comment

by:frodoman
ID: 19999286
Did you make a change to the operating system - perhaps installing SP2 on XP?

frodoman
0
 

Author Comment

by:jstreur
ID: 20001276
mlmcc - Like I said in the earlier post, We have created 2 identical reports, with the exception of the search criteria.  The report that we have set up to search for job number using stored procedures works, however, when we changed the search criteria to a date range, it crashes.
frodoman - We have not made any changes to the operating system.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 20001418
How are you changing the search criteria?

mlmcc
0
 
LVL 15

Expert Comment

by:dbbishop
ID: 20002215
I noticed in the procedure you have defined the dates as VARCHAR(). Try changing to DATETIME or SMALLDATETIME. What is the source of these dates on the client side (i.e. parameter in pararmeters collection, bound to controls on a form, etc.?)
0
 
LVL 15

Expert Comment

by:dbbishop
ID: 20045355
How about a hint? You accepted mlmcc's comments asking if the database, structure or data sources changed, but you said the database, structures and data sources had not changed. Kind of confusing. How did you fix the problem.
0
 

Author Comment

by:jstreur
ID: 20045448
I am sorry, someone did go in and make a change to the database.  Thank you for your help!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

600 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