SQL Sever 2000 Stored Procedure and Crystal OCX

[willrich]

I am developing a vb 6 app that calls a Crystal 7 report using OCX. The Crystal Report contains a Stored Procedure. The Report works fine when I am in Crystal, but when I call the report using OCX I get the data to display but no data retrieved. I have tried various methods for connecting to the database and passing the stored procedure, but to no avail.

I am able to get data in following situation. I check on the save data with report option in the crystal report and then I pass the same value via ocx that it was saved with and then the data is displayed. If I pass another value it displays the report but with no data.

Here is my code:

With CrptLPNTag
.Reset
.ReportFileName = "c:\windows\desktop\test.rpt"
.DiscardSavedData = True
.Connect = "DSN=TIM;UID=reportuser;PWD=password;DSQ=RSSMain"
'Note I also tried these connection strings to no avail.
'i = .LogOnServer("Pdssql.dll", "TIM", "RSSMain", "reportuser", "rome3117&quot
.StoredProcParam(0) = gPrintLPN
.Destination = crptToWindow
.Action = 1
End With

In the report I have tried both using both of the following set location properties to no avail also "RSSMain.dbo.Proc(LPNTag)" and "Proc(LPNTag)"

This stored procedure is pretty basic and I can get around the problem by just joining the tables in crystal, but I have other reports to create that will need to use Stored procedures so any help will be much appreciated!

Thanks in advance for your help

 

[vidru]

This works for me:

    With CrystalReport1
        .Reset
        .ReportFileName = "c:\ocxTest.rpt"
        .DiscardSavedData = True
        .LogOnServer "pdssql.dll", "SERVER", "dbName", "USER", "PASSWORD"
        .StoredProcParam(0) = intParam
        .Destination = crptToWindow
        .Action = 1
    End With

In the report, the location says "dbName.dbo.Proc(ProcName)"

If I edit it to say just "ProcName", it still works from VB.

-dave

 

[willrich]

vidru - did you have to set a variable to get the following command to work
LogOnServer "pdssql.dll", "SERVER", "dbName", "USER", "PASSWORD"

I receive a error if I do not do i = logonserver ....

Thanks for your help.

 

[vidru]

No, I didn't. What you see there is exactly how I called the report. I don't use the .ocx (just fire it up occasionally when someone here has a problem), so I haven't come across too many issues with it.

Here's a link to a whitepaper from the BusObjects/Crystal site on connecting to various data sources with the .ocx:

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

Hopefully, something in there help you out.

-dave

 

[willrich]

I was putting a ( after logonserver - that was why I was getting an error. But still no data is printing.

I have read that kb article as well as about 50 other ones in their knowledge base and it appears I am doing everything correctly, but to no avail.

Couple of other tidbits:

If I display the stored procedure parameter in the report heading - it shows with the correct value, but no other data.

I am not real skilled in stored proc., but I am assuming it is set up right since I get data to display in crystal and in SQL Server. Here is the stored procedure:

CREATE PROCEDURE LPNTag @LPN nvarchar(18)
AS
SELECT tblinvdtl.*,tblitem.*
FROM tblinvdtl,tblitem
WHERE tblinvdtl.itemnbr = tblitem.itemnbr and tblinvdtl.lpn = @LPN
GO

Thanks for your help!

 

[vidru]

Ok, let's change the procedure a bit (and tune it up some while we're at it)...

ALTER PROCEDURE LPNTag
@LPN varchar(18)
AS
SELECT d.*, i.*
FROM tblinvdtl d
JOIN tblitem i ON (d.itemnbr = i.itemnbr)
WHERE d.lpn = @LPN

I've seen problems with using NVARCHAR parameters vs. Crystal before. Switching to VARCHAR will probably fix it.

-dave

 

[willrich]

Dave -

Still no luck. I evan started the report from scratch and created a simple report with two fields.

Think its a dll issue? the version of pdssql.dll I am using is ????? Interesting... I just went and did a search for the dll and it is not on my PC. But reports still work when I don't use stored procedures????

Any thoughts?

Thanks,

John

 

[DiggerDog]

I am having the same problem aswell - nay more infor would be great - Basically the report seems to ignore parameters passed to it from the VB code