Passing SQL Server parameters from a report

[HoustonGuy]

Although I've worked with all of these products for years I've never encountered this before, so I'm sending my flare up now before I take on much more water...

I'm using SQL Server 7 stored procedures as the datasource for my reports along with Seagate Info. When I schedule a report and pass date parameters to the procedure the report works ONLY when I omit the WHERE clause in my stored procedure.

Example:

This stored procedure does NOT return data in my report:

CREATE PROCEDURE rptCheckRegisterTEST
@dtStart datetime,
@dtEnd datetime
AS

SELECT ShippedDate
FROM Orders
WHERE ShippedDate >= @dtstart
AND ShippedDate <= @dtend

This stored procedure DOES return data:

CREATE PROCEDURE rptCheckRegisterTEST
@dtStart datetime,
@dtEnd datetime
AS

SELECT ShippedDate
FROM Orders

Omitting the where clause allows the report to generate data properly. I'm upgrading the Seagate Info 7 installation to MR1. I'm also going to check the version of p2ssql.dll since there are known date parameter issues with certain verions of this driver.

I'm using Seagate Info 7 that installs Crystal Reports 7 against an MS SQL server 7.0 Back end.

I've also tried passing a string to the stored procedure and converting it for use in the procedure and using a temp table as my final selection. Everything runs fine in Query Analyzer before I test the report. Crystal as well as Seagate Info should be able to handle this simple procedure.

I'm still trying various things. Any insight or help is appreciated.

Thanks!
Cody ford
Data Mining, Cleansing and Reporting Specialist
codyjford@hotmail.com

SQL, VB6, Seagate Info/Crystal Reports