No results using info desktop with parameter stored procedure

[bluecjh]

I have created a Crystal report which links to a stored procedure on sql server 6.5. This refreshes nicely
returning good results. the 2 parameters are dates.

I created a new report on Info Desktop, set all parameters
then sheduled the report providing promts of
Date(2003,09,01) and Date(2003,09,30). The report
runs successfully but returns nil data, the report appears but no results, even the parameters which are included on the report are empty.
can anyone help?
thanks,

 

[Bond7]

Hi,

I would suggest you check your stored proc. Is it returning the results set with same parameters in sql analyser? If so then try DateTime(2003,09,01) and DateTime(2003,09,30).

 

[bluecjh]

Yes the stored procedure delivers the results nicely both
in the QA and to the crystal report. My C Rpt is working
fine. My problem is that now I have completed my
work on the Stored proc and C Rpt I want to submit the
C Rpt onto 'Info Desktop' for others on our intranet to
view. Alas on the Info Desktop the C Rpt schedules
and runs 'successfully' but no results are returned
the report has no data in it, it is as if it never made
a communication to the Stored Proc. ??

 

[Bond7]

Ok, tell me did you try with DateTime instead of Date.

I guess you have the date parameter value type as date. Change two date parameters to datetime, verify the report and save it. Test run it from designer. Go to info desktop and refresh the report object and schedule it. Enter parameter values as DateTime(2003,09,01) and DateTime(2003,09,30).

Good luck.

 

[bluecjh]

Hi Bond7

Tried your suggestion but fistly the stored procedure
dictates the data type as DateTime in Crystal anyway.

I entered the DateTime(... parameters you suggested. all I got was the correct
appearance of the parameters on the report but no
stats were retrieved from the stored procedure. Any other ideas?

thanks

 

[Bond7]

Hi,

I am sorry to know that my ideas are working. I don't really know what datetime format you are using in your stored proc. Is it possible for you copy the stored proc here?

Try this if it is different from my code for the date variables:

Declare two more date variables @a datetime
@b Datetime

Select @a = Convert(datetime, (convert(varchar(11), @date1)
+ '00:00:00'), 103)
Select @b = Convert(datetime, (convert(varchar(11), @date2)
+ '23:59:59'), 103)
and replace your date varibles with @a and @b in where clause.

Note: @date1 and @date2 are your parameters, replace with correct varible. This is only the examples.

 

[danrab]

Try to enter a 'default' date value within your crystal report. If you refresh your report within info desktop, the entered value will be retrieved as well within your parameter values. This will provide you with the correct syntax.

 

[bluecjh]

My SP

The convert solution failed to make a difference, Danrabs
suggestion was useful but to no avail I'm now going to try and connect to an SP without parameters at all and see if that works, but if you can help with this... thanks


IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=object_id('tempdb..#t_dtto')) --uses system function object_id()
DROP TABLE #t_dtto

GO


IF EXISTS(SELECT NAME FROM sysobjects WHERE NAME='dtto_month2')
DROP PROCEDURE dtto_month2
GO

CREATE PROCEDURE dtto_month2
@date1 DATEtime, @date2 DATEtime



AS

BEGIN

-- @stringdatefrom VARCHAR(20), @stringdateto VARCHAR(20)
--Script supply data to the DTTO Monitoring Crystal report (to supply NPD month end returns)


--SELECT @datefrom = CONVERT(DATETIME,@stringdatefrom)
--SELECT @dateto = CONVERT(DATETIME,@stringdateto)

declare @datefrom datetime
declare @dateto datetime

SELECT @datefrom = convert(datetime, (convert(varchar(11), @date1) + '00:00:00'), 103)
SELECT @dateto = convert(datetime, (convert(varchar(11), @date2) + '23:59:59'), 103)

CREATE TABLE #t_dtto

(dtto VARCHAR(40),
total INT,
termreason VARCHAR(2) null)


INSERT INTO #t_dtto

SELECT

dtto = "Total commencements for month", total = COUNT(crn), termreason = NULL


FROM

t_dtto1


WHERE

datename(month,commencement)=datename(month,@datefrom)
AND datename(year,commencement)=datename(year,@datefrom)

UNION

SELECT
dtto ="Total caseloads for current month", total =COUNT(crn), termreason = NULL

FROM
t_dtto1

WHERE
commencement < @dateto
AND (termdate > @dateto OR termdate IS null)

UNION

SELECT
dtto =&quot;Terminations for month&quot;,total =COUNT(crn), termreason

FROM
t_dtto1

WHERE
status = &quot;term&quot;

AND datename(month,termdate)=datename(month,@datefrom)
AND datename(year,termdate)=datename(year,@datefrom)

GROUP BY
termreason

UNION

SELECT
dtto =&quot;No. referred to DTTO&quot;, total =COUNT(crn), termreason = NULL

FROM
t_dtto2
WHERE
datename(month,datetyped)=datename(month,@datefrom)
AND datename(year,datetyped)=datename(year,@datefrom)

UNION


SELECT
dtto =&quot;No. assessed for DTTO&quot;, total =COUNT(crn), termreason = NULL

FROM
t_dtto2

WHERE
suitable = &quot;y&quot;
AND datename(month,datetyped)=datename(month,@datefrom)
AND datename(year,datetyped)=datename(year,@datefrom)

UNION


SELECT
dtto =&quot;No. of proposals&quot;, total =COUNT(crn), termreason = NULL

FROM

t_dtto3
WHERE
datename(month,datetyped)=datename(month,@datefrom)
AND datename(year,datetyped)=datename(year,@datefrom)

UNION


SELECT
dtto =&quot;No. of breaches&quot;, total =COUNT(crn), termreason = NULL

FROM
t_dtto4
WHERE
datename(month,contactdate)=datename(month,@datefrom)
AND datename(year,contactdate)=datename(year,@datefrom)

UNION


SELECT
dtto =&quot;Commencements within 2 working days&quot;, total =COUNT(DISTINCT(crn)), termreason = NULL

FROM
t_dtto6
WHERE
datename(month,contactdate)=datename(month,@datefrom)

AND datename(year,contactdate)=datename(year,@datefrom)
AND wk_day<=2
AND action LIKE '%treatment%'

SELECT * FROM #t_dtto

END

 

[bluecjh]

ps. The test without parameters worked. so the problem
does seem to lies with the issue of parameters?

 

[bluecjh]

...
note: I examined the stored procedure to see what values
were being received by saving them to a table and examining
the table

the receiving parameter @date1 held NULL.

accordingly

Select @a = Convert(datetime, (convert(varchar(11), @date1)
+ '00:00:00'), 103)

evaluated to 1 Jan 1900 0:00

presumably the problem lies with what is being
passed from crystal to the SP i.e NULL.
and yet parameters appear correctly at the top of the report?
any ideas... thanks

 

[Bond7]

Hi,

It seems the report got corrupted. I suggest you create another new report from scratch. The date parameters should be automatically created. Then you can copy the fileds and formatting from the existing report and test it.

 

[synapsevampire]

I don't think that it's corrupted.

What connectivity are you using? If ODBC, use the CR supplied ODBC driver.

What version of Crystal?

How did you determine that what is being passed is blank?

Depending upon your version of Crystal, try a Set Location (CR 9) or a Verify Database (CR 8.5).

Please post Crystal version and type of connectivity used if the above doesn't resolve.

-k

 

[bluecjh]

we are using CR version 7!

How did I determine that what is being passed is blank?

I saved the parameters values within the stored proc to a
table (see above). they were Null.

I am using the standard ODBC connection offered on
creating a new report, the connection works in all
other respects i.e. CR's are being created all the time. What is seemingly unique about this scenario is the use of
INFO desktop with CR and a parameterised SP.

I reiterate: My CR's work fine with parameterised SP's.
It is when I introduce Info Desktop into the equation
that I get no results.

 

[AISIDT]

bluecjh -

I was glad to find a thread that exactly describes the problem I'm having today. Unfortunately, it doesn't appear that your problem got resolved within this thread.

As with your problem, in CR the results are fine and the parameter is passed to the server. After posting to the desktop, the server receives a null value for the date parameter when the report is run.

Did you find a way to resolve this problem? If so, I would greatly appreciate any suggestions.

Thanks!

 

[bluecjh]

Unfortunately not aisidt,

However a colleague of mine thought of a devious way round
the problem, essentially i think the idea was to code
in the SProc that if it gets a null value to then look
up a particular table which contains the dates you were to pass.

I think you can create a functiuon in a crystal report which
will write to this table (i.e the dates you are trying to pass)

the sproc then has what is required!!

I have not tried this myself so i can't comment in more detail.

good luck

 

[Fahim]

Hi
I have a similar problem. I was just wondering if you found a solution to this.
I have a crystal report that has a stored procedure as a data source. There are two date parameters I am passing as string. The report runs fine in CR 7.0. When I add the report to Seagate Info 7.0 the report does not return any data. I ran a trace on SQL Server and found that Seagate Info passes the parameters as NULL.
Any help will be appreciated.
Thanks
Fahim

 

[Fahim]

Hi
Just adding to my previous thread.
I have solved the problem. I used an ODBC driver instead of the native SQL Server driver supplied by Seagate Info.
Hope this helps you too.
Good Luck