×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Passing SQL Server parameters from a report

Passing SQL Server parameters from a report

Passing SQL Server parameters from a report

(OP)
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

RE: Passing SQL Server parameters from a report

I was playing with SQL 7 this weekend, specifically Stored Procedures.

I created reports in CR V7 and V8 (but not info) using a sample SP in the Northwind database.  It is very similar to yours (Sales by year).  The where clause didn't cause a problem and they both returned data.

The CR used with INFO is slightly different from the standalone CR, until you get to Enterprise, so this might also be a factor.

Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB
http://www.kenhamady.com/

RE: Passing SQL Server parameters from a report

(OP)
Thanks for the reply Ken!

I found the problem - the driver p2ssql.dll, the Crystal driver for MS SQL Server native connections, has a bug that does not allow the passing of the parameters to the stored procedures.

I updated the driver on each client machine to version 8 which I downloaded form the Crystal Decisions support website and the problem was fixed.
Thanks again,

Cody ford
Data Mining, Cleansing and Reporting Specialist
codyjford@hotmail.com
 
SQL, VB6, Seagate Info/Crystal Reports

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close