No data returned from Stored Procedure

[snoopyyu]

I have a report with the data source from a SQL Server 2000 stored procedure. When I execute this stored produre from Query Analyzer, I got more than 10000 records returned, but got no records back for the report from Crystal Report 8 and error message "Failed to open rowset" from Crystal Report.NET. Any help or ideas will be greatly appreciated. Thanks in advance.

Below is my stored procedure:

CREATE PROCEDURE spTest
@dtDateWanted as DateTime,
@strCurr as varchar(3)
AS

SET NOCOUNT ON

/* ger current date for later comparison */
declare @strCurrDate as DateTime
select @strCurrDate = GetDate()

select x0.linvoice, x0.lmatter as matter, x2.mcurrency as currency, x0.ltradat as billdate, x0.lindex as lindex, x0.lamount
from ledger x0, ledcode x1, matter x2, periodt x3
where x0.llcode = x1.lccode
and x0.lmatter = x2.mmatter
and x0.lperiod = x3.pe
and x1.lcdebcr = 'D'
and x0.laptoin is null
and x0.ltradat <= @dtDateWanted
and (year(x3.pebedt) < year(@dtDateWanted) or (year(x3.pebedt) = year(@dtDateWanted) and month(x3.pebedt) <= month(@dtDateWanted)))
and x0.lzero = 'N'
and x0.lamount <> 0
and x1.lcfco <> 'T'
and x2.mcurrency like (select case when @strCurr = '*' then '%' else @strCurr + '%' end)

GO

 

[synapsevampire]

How are you connecting to the database? This is usually the culprit, try using the CR odbc or a native connection.

BTW, paste your SQL into the View creator in SQL Server and it will convert it into the preferred format.

-k

 

[snoopyyu]

Thanks for your ideas, synapsevampire. I am just the native connection already.

It looks like the problem is with @dtDateWanted Parameter which is a &quot;DateTime&quot; type. After I convert the @dtDateWanted Parameter to a varchar(10), the report is working. Wondering if there is any restriction on using datetime type as parameter for SQL Server stored procedure using as a datasource. Any ideas or info will be greatly appreciated. Thanks!

 

[vidru]

Try defining your parameter as SMALLDATETIME instead of DATETTIME.

-dave

 

[synapsevampire]

Dave: Why would that matter?

I use SP's with datetime often, there's nothing different in the format, I think you misunderstand what that is, it's simply the breadth that a date can cover, not it's format.

-k

 

[vidru]

K,

It was merely a suggestion... I've used DATETIME's as parameters as well, but I've also seen DATETIME parameters and NVARCHAR's do strange things with Crystal.

-dave

 

[synapsevampire]

Dave: Have an example of a problem with a datetime parameter?

An nvarchar I can understand something being odd, but a datetime?

Snoopy: They work fine in SQL Server, change the connectivity to CR ODBC, it'll pass just fine if you format the entry correctly.

-k

 

[vidru]

K,
Don't recall the specifics, but it was a Time Clock report, using 8.0, pdsodbc.dll, called from a Delphi app. Absolutely can't remember what the error was, but once we changed the parameter from DATETIME to SMALLDATETIME, our problem went away.

snoopyyu,
Just do what he says, and nobody gets hurt...

-dave

 

[synapsevampire]

Yeah, old versions had some bugs, typically the hotfixes corrected them.

Switching to a char field certainly works, as would a numeric, but this is the sort of poorly researched issue which propagates tales that Crystal won't do things that it will.

I'm always amazed that experts don't ask what type of connectivity is being used, this being the cause for a lot of failures for SP related problems. And it's one of the first things a Crystal support person will ask for this very reason.

...nobody gets hurt...clever lad...

-k

 

[lyanch]

I have problems related to datetime parameters and native sybase (11.9) connections. There were several outcomes relating to sending null values especially (null is a valid request). It would often cause crystal to hard crash, lock up and sometimes show no records when calling the procedure from another place using the same parameters would show records. The solution ended up being going back several versions of the native dll. This problem was apparantly fixed in Crystal 9.

Lisa