Link to home
Start Free TrialLog in
Avatar of dba123
dba123

asked on

CRAXDRT detected by database dll

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.
Avatar of arbert
arbert
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?
Avatar of dba123
dba123

ASKER

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
Avatar of dba123
dba123

ASKER

the users have permissions to the proc in question....it is only using one proc ....
SOLUTION
Avatar of arbert
arbert
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dba123
dba123

ASKER

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>
Avatar of dba123
dba123

ASKER

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")
Avatar of dba123
dba123

ASKER

that was from the page reportengine.asp which is the page that is called after the form above.
Avatar of arbert
arbert
"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...
Avatar of dba123
dba123

ASKER

<%@ 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")

%>
Avatar of dba123
dba123

ASKER

that was the reportengine.asp page that the first asp page I posted is redirected to and which all other reports use also.
Avatar of dba123
dba123

ASKER

have fun analyzing this crap
Avatar of dba123
dba123

ASKER

I have no idea if this is a custom page or a crystal asp page that is out of the box and tweaked....
Avatar of arbert
arbert
Cool, the code above is what I was looking for....I'll take a look at it tomorrow.

Brett
Avatar of dba123
dba123

ASKER

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


Avatar of dba123
dba123

ASKER

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
Avatar of arbert
arbert
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?
Avatar of dba123
dba123

ASKER

correction:  I did find a couple of other reports that are using sp just fine....
Avatar of dba123
dba123

ASKER

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.
Avatar of dba123
dba123

ASKER

for your #2, I don's see where in the proc it is set to receive the params
Avatar of dba123
dba123

ASKER

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.



Avatar of dba123
dba123

ASKER

its gotta be having problems with the sp or referencing it which is what I was thinking all along.
Avatar of dba123
dba123

ASKER

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.
Avatar of dba123
dba123

ASKER

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?
SOLUTION
Avatar of Kurt Reinhardt
Kurt Reinhardt
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dba123
dba123

ASKER

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?
Avatar of dba123
dba123

ASKER

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?
Avatar of dba123
dba123

ASKER

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.
Avatar of Kurt Reinhardt
Kurt Reinhardt
Flag of United States of America image
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.
Avatar of dba123
dba123

ASKER

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?

Avatar of dba123
dba123

ASKER

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.

Avatar of dba123
dba123

ASKER

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?
Avatar of dba123
dba123

ASKER

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.....?
Avatar of dba123
dba123

ASKER

and now I re-registered again after some time and the report ran!  ahhhhh
Avatar of dba123
dba123

ASKER

but then goes bad again...so I need some insite on this
Avatar of dba123
dba123

ASKER

Does anyone here run CR 8.5 and can tell me what version of this dll they have?
ASKER CERTIFIED SOLUTION
Avatar of arbert
arbert
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dba123
dba123

ASKER

how can I tell if a service pack was installed?  I am afraid to on a production server though.
Avatar of dba123
dba123

ASKER

>>>>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??
Avatar of arbert
arbert
"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....

https://www.experts-exchange.com/questions/21118737/Simple-500-points-question-Passing-a-parameter-from-asp-page-to-SP.html

Avatar of dba123
dba123

ASKER

there are some reports which are using stored procedures just fine
Avatar of dba123
dba123

ASKER

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.