Solved

CRAXDRT detected by database dll

Posted on 2004-09-03
42
1,999 Views
Last Modified: 2012-06-21
background:  All of the reports we have that work are not using stored procedures.  There are about 3 that are using stored procedures...this is something I was given to dive into and seeing the system for the first time (using ASP with Crystal) is new to me. I have experience with Crystal but not on the VB/ASP side of things.

So why the person used sp for these reports I don't know but all I know is that we have an ASP page for each report.  IN that page, it allows the users to select parameters via an HTML form.  Onsubmit they are then redirected to ReportEngine.asp which takes the parameters and then I guess fires up the report using the ActiveX control.  All other reports use their own asp page...and the ones that are not using stored procedures run fine.

The error I get after I click submit on the asp page for the specific report shows this error with blank results in the report after I click ok on the 1 error prompts that come up.

Error prompt 1: CRAXDRT error occured on server -2147192179 : Error detected by database dll

I have no clue where to start....this part of Crystal is foreign to me.  I tested the stored procedure and it does bring back field results.
0
Comment
Question by:dba123
  • 32
  • 7
  • 2
  • +1
42 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 11975981
Just for a start, have you looked at the user that's running the reports and if the user has permissions on the procs?

What version of the CRAXDRT dll is being used?
0
 
LVL 1

Author Comment

by:dba123
ID: 11976180
All users cannot run the report and get the error.

why does the version matter if 90% of our reports use it and only these 3 reports are getting the error.  If the CRAXDRT dll had a problem wouldn't all our reports have a problem?

however for you:  the version is 8.5.0.217
0
 
LVL 1

Author Comment

by:dba123
ID: 11976455
the users have permissions to the proc in question....it is only using one proc ....
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 400 total points
ID: 11976782
The version does make quite a bit of difference--there were a lot of bugs in the way crystal used non-table datasources....

Can you post the code you use to connect to the db (setlogoninfo) and how you're passing the parms....

0
 
LVL 1

Author Comment

by:dba123
ID: 11976835
sure, I just don't understand why you are talking about the dll version when many other reports on the same server use that dll

Here is the ASP page where it allows the user to input the parameters


<html>
<head>
      <title>Support Reports</title>
      <link rel="stylesheet" type="text/css" href="../../styles.css">
</head>
<body class="PRIMARYMAINPROPERTIES">
      <H1>Support Reports</H1>
      <form action="/Reports/scripts/ReportEngine.asp" method="POST">
            <div align="center">
                  <table style="width:120%;">
                        <tr>
                              <td colspan="4" class="GROOVE">
                                    <h4 align="center">Cases by Company</h4>
                              </td>
                        </tr>
                        <tr>
                              <td class="VGROOVE" style="width:25%;">Start Date </td>
                              <td><input class="WIDE" type="text" size="15" name="FIELDD13" value="01/01/2002"></td>
                              <td class="VGROOVE" style="width:25%;">End Date</td>
                              <td><input class="WIDE" type="text" size="15" name="FIELDD14" value="01/01/2010"></td>
                        </tr>
                        <tr>
                              <td class="VGROOVE">Company</td>
                              <td class="VGROOVE">
                                    <select size="5" name="FIELDs02" multiple>
                                          <option selected value="*">All Companies</option>    
                                          <!--#include file="../../scripts/custpart2.asp"-->
                                          <%CustomerList "reportserver","reportsdb","Name"%>
                                    </select>
                              </td>
                              <td class="VGROOVE">Agreement Type</td>
                              <td class="VGROOVE">
                                    <select size="5" name="FIELDs10" multiple>
                                          <option selected value="*">All</option>
                                          <option value="Enterprise Agreement Type">Enterprise</option>
                                          <option value="Expert Agreement">Expert</option>
                                          <option value="Evaluation Agreement">Evaluation</option>
                                          <option value="Partner Agreement">Partner</option>
                                          <option value="XMLS Support">XMLS</option>
                                    </select>
                              </td>
                        </tr>
                        <tr>
                              <td class="VGROOVE">Status</td>
                              <td class="VGROOVE">
                                    <select name="FIELDs3" multiple size="5">
                                          <option selected value="*">All Status</option>
                                          <option>Closed</option>
                                          <option>Closed - Pending Cust Conf</option>
                                          <option>Open - New</option>
                                          <option>Open - Research</option>
                                          <option>Open - Awaiting init contact</option>
                                          <option>Open - No Answer/Callback</option>
                                          <option>Open - Pending Bug Fix</option>
                                          <option>Open - Pending Extg Bug Fix</option>
                                          <option>Open - Pending New Bug Fix</option>
                                          <option>Open - Pending Cust Input</option>
                                          <option>Open - Pending Cust Conf</option>
                                          <option>Open - Pending Cust Advocate</option>
                                    </select>
                              </td>
                              <td class="VGROOVE">Priority</td>
                              <td class="VGROOVE">
                                    <select name="FIELDs1" multiple size="5">
                                          <option selected value="*">All Priorities</option>
                                          <option>1 - Enterprise Critical</option>
                                          <option>2 - Severe Impact</option>
                                          <option>3 - Degraded Operation</option>
                                          <option>4 - Minimal Impact</option>
                                    </select>
                              </td>
                        </tr>
                        <tr>
                              <td class="VGROOVE">Company In Production</td>
                              <td class="VGROOVE">
                                    <select size="3" name="FIELDs5" multiple>
                                          <option selected value="*">All</option>
                                          <option value="Yes">Yes</option>
                                          <option value="No">No</option>
                                    </select>
                              </td>
                              <td class="VGROOVE">Case Production Status</td>
                              <td class="VGROOVE">
                                    <select size="3" name="FIELDs7" multiple>
                                          <option selected value="*">All</option>
                                          <option value="N">Not in Production</option>
                                          <option value="Y">In Production</option>
                                          <option value="D">Production Down</option>
                                    </select>
                              </td>
                        </tr>
                        <tr>
                              <td class="VGROOVE" >Customer Health Status</td>
                              <td class="VGROOVE" >
                                    <select size="4" name="FIELDs6" multiple>
                                          <option selected value="*">All</option>
                                          <option value="Red">Red</option>
                                          <option value="Yellow">Yellow</option>
                                          <option value="Green">Green</option>
                                    </select>
                              </td>
                              <td class="VGROOVE">Project</td>
                              <td class="VGROOVE">
                                  <select size="4" name="FIELDs11" multiple>
                                        <option selected value="*">All Projects</option>    
                                        <!--#include file="../../scripts/projlist.asp"-->
                                        <%ProjectList "reportserver","reportsdb"%>
                                  </select>
                              </td>
                        </tr>
                        <tr>
                              <td class="VGROOVE" >Geographical Area</td>
                                 <td class="VGROOVE" >
                                    <select size="6" name="FIELDs12" multiple>
                                          <option selected value="*">All</option>
                                          <option value="Asian Pacific">Asian Pacific</option>
                                          <option value="Europe Middle East and Africa">Europe Middle East and Africa</option>
                                          <option value="North America">North America</option>
                                          <option value="South America">South America</option>
                                          <option value="Other">Other</option>                                          
                                    </select>
                              </td>
                              <td class="VGROOVE" >Suppress filter in Report Title?</td>
                              <td class="VGROOVE"  style="text-align: left;"><input type="checkbox" name="FIELDB4" value="True"></td>
                        </tr>
                        <tr>
                              <td class="VGROOVE">Suppress Case Descriptions?</td>
                              <td class="VGROOVE" style="text-align: left;"><input type="checkbox" name="FIELDB8" value="True" checked></td>
                              <td class="VGROOVE">Suppress Cases with Unknown Production Status </td>
                              <td class="VGROOVE" style="text-align: left;"><input type="checkbox" name="FIELDB9" value="True" checked></td>
                        </tr>
                  </table>
            </div>
            <p align="center"><input type="submit" name="B1" value="View Report"></p>
                                    
            <input type="hidden" name="database" value="reportsdb">
            <input type="hidden" name="server" value="reportserver">
            <input type="hidden" name="Report" value="..\support\everyone\cases by company.rpt">
      </form>
</body>
</html>
0
 
LVL 1

Author Comment

by:dba123
ID: 11976865
not sure where that setlogoninf is but here is a connection script from the page

'Create a connection object
Set cn = Server.CreateObject("ADODB.Connection")
'Open a connection; the string refers to the DSN
cnStr = "driver={SQL Server};server="& serv & ";uid=ourpassword;pwd=ourpassword;database=" & database
Set rs = Server.CreateObject("ADODB.Recordset")
0
 
LVL 1

Author Comment

by:dba123
ID: 11976870
that was from the page reportengine.asp which is the page that is called after the form above.
0
 
LVL 34

Expert Comment

by:arbert
ID: 11976953
"sure, I just don't understand why you are talking about the dll version when many other reports on the same server use that dll"

They use the DLL, but they use it for different purposes....The stored procs pass parameters and calls different functions in the DLL--it does matter....

Post the code where you set the crystal parm values for the report--not where you collect them from the web form...
0
 
LVL 1

Author Comment

by:dba123
ID: 11977397
<%@ LANGUAGE="VBSCRIPT" %>
<%response.buffer=true%>
<%
' for troubleshooting pjh
dim errCatch
errCatch = "start"

function strDecode(str)
dim i
dim x
dim tempstr
dim tempch


i=1
x=len(str)
tempstr=""
      while i<=x
            tempch=mid(str,i,1)
            if asc(tempch)=27 then
                  tempstr=tempstr+chr(asc(mid(str,i+1,1))-1)
                  i=i+2
            else
                  tempstr=tempstr+tempch
                  i=i+1
            end if
      wend
strDecode=tempstr
end function

'***********
reportname=""+Request("Report")
'eg) reportname = "Sales\territory list.rpt"
'***********

If not IsObject(session("oApp")) Then
      Set session("oApp") = Server.CreateObject("CrystalRuntime.Application")
errCatch = errCatch + " : set session oApp" + reportname
end if

Path = Request.ServerVariables("PATH_TRANSLATED")
While (Right(Path, 1) <> "\" And Len(Path) <> 0)
iLen = Len(Path) - 1
Path = Left(Path, iLen)
Wend
errCatch = errCatch + " : Path"

If IsObject(session("oRpt")) then
      Set session("oRpt") = nothing
End if
errCatch = errCatch + " : set session oRpt"

Set session("oRpt") = session("oApp").OpenReport(path & reportname, 1)

session("oRpt").MorePrintEngineErrorMessages = false
session("oRpt").EnableParameterPrompting = false

userid = "renamed_here_for_privacy_ExpertsEexchange"
password = "renamed_here_for_privacy_ExpertsEexchange"

session("oRpt").MorePrintEngineErrorMessages = False
session("oRpt").EnableParameterPrompting = False

errCatch = errCatch + " : set session oRpt Done"

'Set the location
if (Request("server")="") then
      srvr="ourserver_renamed_here_for_privacy_ExpertsEexchange"
else
      srvr=Request("Server")
end if
if (Request("database")="") then
      dbase="reportsdb"
else
      'dbase="reportsdb"
      dbase=Request("database")
end if
errCatch = errCatch + " : location " + srvr + ":" + dbase

tablecount = session("oRpt").Database.Tables.Count
i = 1
while i <= tablecount
set crtable = session("oRpt").Database.Tables.Item(Cint(i))
crtable.SetLogonInfo cstr(srvr), cstr(dbase), cstr(userid), cstr(password)
'oldlocation = crtable.location
'oldlocation = mid(oldlocation,instr(1,oldlocation,"."))
'crtable.location = userid & oldlocation
i = i+1
wend

errCatch = errCatch + " : set login"

'set crtable = session("oRpt").Database.Tables.Item(1)
'crtable.SetLogonInfo cstr(srvr), cstr(dbase), cstr(userid), cstr(password)
'crtable.SetLogonInfo "gorgon", "vanvitria", cstr(userid), cstr(password)

'pull parameters from HTML form and insert into report
if isobject(session("Param")) then set session("Param") = Nothing
Set ParamDefCollection = Session("oRpt").Parameterfields
set session("Param") = ParamDefCollection

errCatch = errCatch + " : set session param" + cStr(Err.Number)


For Each x in Request.Form
ValidField=0

if (len(x)=2) then
      strNum=left(x,1)
      strType=right(x,1)
      ValidField=1
elseif (left(x,5)="FIELD") then 'Version 2 of Field Parameter format
      strType=mid(x,6,1)
      strNum=mid(x,7,2)
      ValidField=1
end if
errCatch = errCatch + " : formFields" + strNum + ";"+strType

if ValidField=1 then
      strValues="NCBDS"
      inside=inStr(1,strValues,ucase(strType),1)
      if isnumeric(strNum) and inside>0 then
            set myParam=session("Param").Item(cInt(strNum))
            if strType=ucase(strType) then
                  'Single Value Parameter
                  temp=Request.Form(x)
                  temp=strdecode(temp)

                  select case strType
                        case "N" call myParam.SetCurrentValue(CDbl(temp), 7)
                        case "C" call myParam.SetCurrentValue(CDbl(temp), 8)
                        case "B" call myParam.SetCurrentValue(CBool(temp), 9)
                        case "D" call myParam.SetCurrentValue(CDate(temp), 10)
                        case "S" call myParam.SetCurrentValue(CStr(temp), 12)
                  end select
            else
                  'Multiple Value Parameter
                  myParam.EnableMultipleValues=1
                  start=1
                  do
                        middle=instr(start, Request.Form(x), ",")
                        if (middle>0) then
                              temp=trim(mid(Request.Form(x), start, middle-start))
                              temp=strdecode(temp)
                              'myParam.AddCurrentValue(CStr(temp))
                              select case strType
                                    case "n" call myParam.AddCurrentValue(CDbl(temp))
                                    case "c" call myParam.AddCurrentValue(CDbl(temp))
                                    case "b" call myParam.AddCurrentValue(CBool(temp))
                                    case "d" call myParam.AddCurrentValue(CDate(temp))
                                    case "s" call myParam.AddCurrentValue(CStr(temp))
                              end select
                              start=middle+1
                        else
                              if start<=len(Request.Form(x)) then
                                    temp=trim(mid(Request.Form(x), start))
                                    temp=strdecode(temp)
                                    select case strType
                                          case "n" call myParam.AddCurrentValue(CDbl(temp))
                                          case "c" call myParam.AddCurrentValue(CDbl(temp))
                                          case "b" call myParam.AddCurrentValue(CBool(temp))
                                          case "d" call myParam.AddCurrentValue(CDate(temp))
                                          case "s" call myParam.AddCurrentValue(CStr(temp))
                                    end select
                                    start=len(Request.Form(x))+1
                              end if
                        end if
                  loop while (start<len(Request.Form(x)))
            end if
      end if
end if
next
errCatch = errCatch + " : set form"+ cStr(Err.Number)

On Error Resume Next
session("oRpt").ReadRecords

errCatch = errCatch + " : set records"+ cStr(Err.Number)

  If IsObject(session("oPageEngine")) Then
        set session("oPageEngine") = nothing
  End If
set session("oPageEngine") = session("oRpt").PageEngine
'End If

Dim browserType
set browserType=Server.CreateObject("MSWC.BrowserType")

%>
0
 
LVL 1

Author Comment

by:dba123
ID: 11977402
that was the reportengine.asp page that the first asp page I posted is redirected to and which all other reports use also.
0
 
LVL 1

Author Comment

by:dba123
ID: 11977467
have fun analyzing this crap
0
 
LVL 1

Author Comment

by:dba123
ID: 11977500
I have no idea if this is a custom page or a crystal asp page that is out of the box and tweaked....
0
 
LVL 34

Expert Comment

by:arbert
ID: 11978956
Cool, the code above is what I was looking for....I'll take a look at it tomorrow.

Brett
0
 
LVL 1

Author Comment

by:dba123
ID: 11991021
Keep in mind that the ReportEngine.asp (second post of code and is called by the first asp page) is being used just fine by all other reports...so for the very few that arent working (and based on the company table), I don't see why this would be the problem.

Here is the Stored Procedure code below.  

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER    PROCEDURE rpt_supp_case_by_company
@startdate datetime,
@enddate datetime
AS

      select c.swcaseid
              ,at.swagreementtype
      into #rscbc_agr_type
      from sw_case c
            ,sw_agreement a
            ,sw_agreement_type at
      where       c.swagreementid = a.swagreementid
                and       a.swagreementtypeid = at.swagreementtypeid
         and       c.swdatecreated >= @startdate
         and       c.swdatecreated <= @enddate + 1

      select c.swcaseid, max(swdatechanged) statuschange
      into #rscbc_status
      from sw_case c
                    ,sw_audit_trail a
      where       c.swcaseid = a.swobjectid
                and       a.swobjecttype = 'CASE'
         and       c.swdatecreated >= @startdate
         and       c.swdatecreated <= @enddate + 1
      group by c.swcaseid

      select        c.swcaseid
                     ,c.swsubject
                     ,c.swpriority
                     ,c.swstatus
                     ,c.vtInProduction CaseProd
                     ,c.swdatecreated
                     ,c.swdateresolved
                     ,c.swresolutionid
                     ,c.swnote
                     ,c.vtresponseminutes
              ,co.swcustomerid
                     ,co.swname Company
                     ,co.vtInProduction CompanyProd
                     ,co.vtHealthStatus
                     ,co.vtgeographicalarea Area
            ,co.vtagreementtype CustAgreement
                     ,p.swfirstname
                     ,p.swlastname
                     ,rs.statuschange
                     ,rat.swagreementtype
                     ,c.vtprojectname
                     ,rb.swfirstname Reportedbyfirst
                     ,rb.swlastname  Reportedbylast
                     ,res.swsubject  Resolution
      from           sw_case c
                     ,sw_customer co
                     ,sw_person p
                     ,#rscbc_agr_type rat
                     ,#rscbc_status rs
                     ,sw_person rb
                     ,sw_resolution res
      where       c.swcustomerid = co.swcustomerid
         and       c.swassignedto = p.swpersonid
                and       c.swreportedby = rb.swpersonid
         and       c.swdatecreated >= @startdate
         and       c.swdatecreated <= @enddate + 1
         and       c.swcaseid *= rs.swcaseid
         and       c.swcaseid *= rat.swcaseid
                and       c.swresolutionid *= res.swresolutionid
         
      drop table #rscbc_agr_type
      drop table #rscbc_status

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



It is bringing back the following fields if I paste all that into Query Analyzer and run the selects :

swcaseid
swsubject
swpriority
swstatus
CaseProd
swdatecreated
swresolutionid
swnote
vtresponseminutes
swcustomerid
company
companyprod
vtHealthStatus
Area (which is territory)
CustAgreement
swfirstname
swlastname
statuschange
swagreementtype
vtprojectname
reportedbyfirst
reportedbylast
resolution


0
 
LVL 1

Author Comment

by:dba123
ID: 11991092
You'll notice that the ReportEngine.asp page has a section where it tries to catch any errors.  I commented out this portion out (which above it doesn't show commented out but just know that it was):


If Err.Number <> 0 Then
  Response.Write "An error occurred attempting to read records.<BR>"
  Response.Write "Error returned: "+cstr(Err.number)+": "+Err.Description
  Response.Write "<BR/>Error stack: "+ errCatch
Else

  If IsObject(session("oPageEngine")) Then
        set session("oPageEngine") = nothing
  End If
set session("oPageEngine") = session("oRpt").PageEngine
End If

but if I uncomment this error checking, the error information I am getting after the ReportEngine.asp is called and the report shows up in the browser with that dll error is this which may help determine the problem:

An error occurred attempting to read records.
Error returned: -2147192179: Error detected by database DLL.
Error stack: start : set session oApp..\support\everyone\cases by company.rpt : Path : set session oRpt : set session oRpt Done : location reportserver:reportsdb : set login : set session param0 : formFields13;D : formFields12;s : formFields14;D : formFields02;s : formFields9;B : formFields9;B : formFields10;s : formFields6;s : formFields3;s : formFields1;s : formFields11;s : formFields11;s : formFields5;s : formFields7;s : formFields8;B : formFieldsB;1 : formFieldsB;1 : set form0 : set records-2147192179
0
 
LVL 34

Expert Comment

by:arbert
ID: 11991224
Ok, that code above looks ok (actually, it looks really good as far as reusability).  I compare it with some of the proc code we've written for ASP and it's generally all the same.  So, I think I would suggest a couple of things.  1.  Have you actually opened the RPT file in Crystal Designer and ran it with the proc?  2.  Are you sure the way the parms are being assigned from the previous ASP page are in the correct order that the proc is expecting them?
0
 
LVL 1

Author Comment

by:dba123
ID: 11991347
correction:  I did find a couple of other reports that are using sp just fine....
0
 
LVL 1

Author Comment

by:dba123
ID: 11991378
1.  Have you actually opened the RPT file in Crystal Designer and ran it with the proc?

When I try to do this, then I get prompted for all the parameters by crystal.  I put in a couple of parameters and then the "Select Data Source" window pops up with 2 tabs: "find data source" and "Machine data source".  Since I was not seeing the reportdb odbc in the list, I went ahead and created my own.  That ODBC is a SQL Server ODBC that references teh reportdb.  I can run the report using my ODBC that I created from the server.

I don't understand why I get this prompt.
0
 
LVL 1

Author Comment

by:dba123
ID: 11991381
for your #2, I don's see where in the proc it is set to receive the params
0
 
LVL 1

Author Comment

by:dba123
ID: 11991406
oh, I see.  I tried running this report like you said just by opening it up in design view.  The ODBC that I created is on my local machine.  I noticed that after I run the report and then do a set location, it is now not even using the sp anymore.



0
 
LVL 1

Author Comment

by:dba123
ID: 11991407
its gotta be having problems with the sp or referencing it which is what I was thinking all along.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 1

Author Comment

by:dba123
ID: 11991424
I'm getting so damn confused.  After I ran the report the first time (and selecting my ODBC that I created locally), the report ran ok and then i did a set locaiton and it stated that it was using a straight table and no longer the proc.  Ok, but then I clicked on a record link from a subreport in this report...again set it to use my ODBC and was able to read the record in the sub.  But then when I went back to the main report and did a set location, it showed that it was using the proc again....records are listed fine.
0
 
LVL 1

Author Comment

by:dba123
ID: 11991859
I'm trying to rearrange the order of the parameter fields in field explorer when in design view in Crystal just to see if this is the problem because it looks like the request fields are coming in at a different order.  When I use the arrow to move a field up a few places in Field Explorer, then close and save the report, when I open the report, the fields don't stay in that order when I go to field exmplorer again.  

Does Crystal care?  is that why it won't save it in the order I want?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 11992840
0
 
LVL 26

Assisted Solution

by:Kurt Reinhardt
Kurt Reinhardt earned 100 total points
ID: 11992875
Here's a KB article from Business Objects regarding your issue:

http://support.businessobjects.com/library/kbase/articles/c2006871.asp

Unfortunately, it doesn't give a resolution, but it may point you in the right direction.

Regarding the parameter prompts.  Any time you run a report in Crystal, it will prompt you for the parameter values if one of two conditions are met:

1)  The parameter is used by a Stored Procedure (even if NULL).  This is because the Stored Procedure must be executed in order for a dataset to be populated for use by Crystal.

2)  The parameters are Crystal Reports Parameters and are used somewhere in the report such as:  Record Selection Criteria, Formulas, Conditional Suppression, etc...

I believe that parameter order is determined within Crystal Reports by you if they're Crystal Parameters or by the stored procedure if they are procedure parameters.  In order to change the order of the procedure parameters, you need to change their order in the procedure (and then do a verify database to reflect changes made to the procedure).

0
 
LVL 1

Author Comment

by:dba123
ID: 11993207
Ok, then here are a few other questions

1) If a sp returns more fields than there are parameters, does Crystal care?  Lets say you created 10 parameters in Crystal but your sp returns 15

2) I only see 2 parameters created in the stored procedure which are

@startdate datetime,
@enddate datetime

remember, I didn't create this sp or the report, or the asp pages.  But my question is, in Crystal I see 2 parameters with @ and the rest without.  So in other words, the sp is returning the 2 @ parameters to match the 2 parametrs in Crystal right?  

If so, then I see there are other parameters in crystal that the sp (or ASP) is passing to Crystal and they do not have teh @ sign.  With this sp above, it is returning fields....so does crystal know what to do with those fields meaning how to match them up wtih the right paramaters set in crystal?

I don't see how the sp, asp, and Crystal parameters are talking to each other; the order of passing them back and fourth, etc.  I know how you can grab what the user selected in the first asp page by grabbing it from the Request in ASP but what happens after that?
0
 
LVL 1

Author Comment

by:dba123
ID: 11993235
If I view the already created paramters in Crystal through Field Explorer I see these:

priority
company
status
hide filter
CompanyInProduction
Health Status
CaseProduction
Surpress Desc
Surpress Unknown Case Prod
Agreement
Project
region
@startdate
@enddate

so  I assume this:

1) ASP sets the call to Crystal here based on what the user selected.  So for example in my ReportEngine.asp, you see these lines:

call myParam.SetCurrentValue(CDbl(temp), 7)

looks like this adds to the parameter set that will be passed to Crystal at the end of this asp page

2)Crystal takes these parameters and delivers them to the stored procedure

3) The stored procedure brings back the records

is this the sequence of events and what is happening?
0
 
LVL 1

Author Comment

by:dba123
ID: 11993244
rhinok, it says it is a known issue but why do 90% of all our other reports work, inlcuding a couple which use other stored procedures...doesn't seem like ours is a bug here.
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 11993295
You're correct in that the parameters with an @ sign are the stored procedure parameters.  I'm not too familiar with ASP, but here's what happens from a Crystal Reports perspective:

1)  Crystal Executes the stored procedure - all stored procedure input parameters are passed to the procedure for processing and a dataset is returned.
2)  Once the dataset has been returned by the stored procedure, Crystal will use filter the results using the Crystal Reports parameters on the client.  These parameters/filters are never passed to the DB for processing.  This is perfectly ok, depending on the circumstances.  I would use these additional parameters to change sorting, grouping, conditional suppression, etc...  If the purpose of these parameters is to filter the results in the dataset (which can be quite large), then this can be terribly inefficient.

So even if the ASP collects all of the parameter values, Crystal doesn't use most of them until after the Stored Procedure  has been executed and has returned data.
0
 
LVL 1

Author Comment

by:dba123
ID: 11996798
Ok, so I don't understand a few things...and I'm talking about explaining it to me from a novice level here:

1) How does crystal match the asp parameters wtih the Crystal made parameters.

2) Then how does crystal take it's parameters (received asp values for those parameters) and shove them into the stored procedure?  If I only have 2 @ signs in front of only 2 parameters, what about the others?  How does the stored procedure take those other parameters without the @ or does it even use it at all?  The sp we have above only has 2 parameters...the 2 dates.  So what does Crystal do with the rest that was passed from ASP - (I pasted the parameters I see in Crystal in my previous post...what about those with out the @) ?

3) Once the stored procedure is ran, I can see that it will bring back thse fields after running the SQL Connectivity test and also running the sp through query analyzer:

1,  swcaseid,  NUMERIC, 15
2,  swsubject,  VARCHAR, 250
3,  swpriority,  CHAR, 30
4,  swstatus,  CHAR, 30
5,  ReasonClosed,  CHAR, 30
6,  swdatecreated,  TIMESTAMP, 23
7,  swdateresolved,  TIMESTAMP, 23
8,  swresolutionid,  NUMERIC, 15
9,  swnote,  TEXT, 2147483647
10,  vtresponseminutes,  FLOAT, 15
11,  swcustomerid,  NUMERIC, 15
12,  Company,  CHAR, 70
13,  CustType,  CHAR, 30
14,  swfirstname,  CHAR, 40
15,  swlastname,  CHAR, 40
16,  statuschange,  TIMESTAMP, 23
17,  swagreementtype,  CHAR, 40
18,  TotalCasesOpened,  INTEGER, 10
19,  TotalPendBug,  INTEGER, 10
20,  PendBugFlag,  INTEGER, 10
21,  TotalCasesClosed,  INTEGER, 10
22,  Total5,  INTEGER, 10
23,  Total6,  INTEGER, 10

so I assume it just plugs them into the right fields on the report?  OR does it plug them back into the parameters...I don't get it.  Do the Crystal parameter names have to match exactly with the field names returned by the sp (from the list above?)

The grey areas still are exactly (in novice terms right down to the gritty details) how ASP reponse values pass to Crystal and How crystal passes what values to the sp and then back...what values and how?

0
 
LVL 1

Author Comment

by:dba123
ID: 11996826
all I know is that I still have this damn error from my initial post, what a pain in the royal ass.  I tried looking at these articles with no luck...I checked every point in the articles:

http://support.businessobjects.com/communityCS/TechnicalPapers/cr_error_detected_by_db_dll.pdf.asp

http://support.businessobjects.com/communityCS/TechnicalPapers/cr_dbconn_troubleshooting.pdf.asp

http://support.businessobjects.com/library/kbase/articles/c2006871.asp

I feel this has to be something simple....either a configuration problem in the Crystal report or ASp code.  Or driver or something is bad on the server but that doesn't make sense bacase 90% of the other reports work fine using the same techniques.

0
 
LVL 1

Author Comment

by:dba123
ID: 11997355
Ok, I re-registered craxdrt.dll.  Then I tried running the report and it ran!  But the next time I try to run the report, it crashed with the same error. I again re-registered the dll, ran the report and it ran fine....backed up then tried to re-run it and same thing..crashed.

What is up with this dll?
0
 
LVL 1

Author Comment

by:dba123
ID: 11997398
now the damn report is back to the same state....no matter if I re-register the dll, it doesn't work again.  Why would it work all of a sudden after registering the dll a few times but now it is dead again.....?
0
 
LVL 1

Author Comment

by:dba123
ID: 11997499
and now I re-registered again after some time and the report ran!  ahhhhh
0
 
LVL 1

Author Comment

by:dba123
ID: 11997507
but then goes bad again...so I need some insite on this
0
 
LVL 1

Author Comment

by:dba123
ID: 11998168
Does anyone here run CR 8.5 and can tell me what version of this dll they have?
0
 
LVL 34

Accepted Solution

by:
arbert earned 400 total points
ID: 12001764
"Regarding the parameter prompts.  Any time you run a report in Crystal, it will prompt you for the parameter values if one of two conditions are met:"

You will notice the line of ASP code that keeps any prompts from happening....
"
I believe that parameter order is determined within Crystal Reports by you if they're Crystal Parameters or by the stored procedure if they are procedure parameters.  In order to change the order of the procedure parameters, you need to change their order in the procedure (and then do a verify database to reflect changes made to the procedure)."

Agree....

"Ok, I re-registered craxdrt.dll.  Then I tried running the report and it ran!  But the next time I try to run the report, it crashed with the same error. I again re-registered the dll, ran the report and it ran fine....backed up then tried to re-run it and same thing..crashed."


Once your ASP scripts use the crystal object, it (and the DLL) stay loaded in memory.  So, after you run the report, the DLL gets loaded into memory and stays there unless windows detects that it's not being used.  So, I'm guessing this DLL never gets unloaded and for some reason a parm or memory from a previous session isn't being released.  Have you installed the latest 8.5 service pack from Crystal/BO?

0
 
LVL 1

Author Comment

by:dba123
ID: 12002483
how can I tell if a service pack was installed?  I am afraid to on a production server though.
0
 
LVL 1

Author Comment

by:dba123
ID: 12003323
>>>>Once your ASP scripts use the crystal object, it (and the DLL) stay loaded in memory.  So, after you run the report, the DLL gets loaded into memory and stays there unless windows detects that it's not being used.  So, I'm guessing this DLL never gets unloaded and for some reason a parm or memory from a previous session isn't being released.  Have you installed the latest 8.5 service pack from Crystal/BO?

ok, then why can I run other reports just fine without having to reregister the dll??
0
 
LVL 34

Expert Comment

by:arbert
ID: 12009302
"ok, then why can I run other reports just fine without having to reregister the dll??"

Because the other reports aren't using  Parms and methods in the DLL the stored proc version uses...

I think you would probably be best to close this question and post in the crystal area--these aren't really SQL Server issues.

Here is a thread that's open on parm passing that you might want to follow....

http://www.experts-exchange.com/Databases/Crystal_Reports/Q_21118737.html

0
 
LVL 1

Author Comment

by:dba123
ID: 12013443
there are some reports which are using stored procedures just fine
0
 
LVL 1

Author Comment

by:dba123
ID: 12013454
I'm closing this question....thank you for all the help so far.  I am reposting the problem in a new thread....just because this is getting so long.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

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