Reports Pointing to Stored Procedure

[khan007]

I AM ON CRYSTAL 8.5, WIN 2K, SQL 2000, ALSO HAVE ACTIVE X VIEWER.

I am facing some problems with Stored Procedures.

Initially I have almost 300 reports based on views, each having 100+ formulas. Working fine.

Now I need to Create one stored procedure for each report, each stored procedure is based on same old respective view and would also pass parameters to that report.
So my problem is, I need to replace old view with new Stored Procedure or you can say my report is pointing to that SP.
I cannot use Set location/Set alias as these options are not working with Stored Proceudre.
It means would I have to recreate each report again?


Last month when we changed the views name (because of our new naming convention scheme) i resolved this problem by doing
"set location and set alias". But that logic is not working with SP.
Please advice me what I suppose to do, may be there is some solution rather than going each and every report and change manually each and every formula.

Is there any tool in market to have this functionality.

Thanks In advance.

 

[vidru]

When you tried the Set Location method, what did the initial location look like, and what did you type in as the new location?

I ask because the syntax for tables/views is different than for stored procedures. The Location for a table/view may look like this

dbName.dbo.TableName

, but a stored procedure's location should look like one of the following:

Jenmar.dbo.Proc(spMyProcedure;1)

or

Proc(spMyProcedure;1)

If you haven't tried this yet, give it a shot and let us know if it worked or not.

-dave

 

[khan007]

The initial location was dbo.view1_VW.

The new location is dbo.Proc(RPT_proc1;1)

once i say done.
it pops up an error window says

Error opening file
There are no fields in the file dbo.Proc(RPT_proc1;1)

From my undersdanding, you were asking me to do this.
Anyway i already tried this.

Any opinion?
Thanks

 

[vidru]

Try getting rid of the 'dbo.' before 'Proc'. If that doesn't work, can you show me the text of the RPT_proc1 procedure?

-dave

 

[bdreed35]

It sounds like your issue is in the contents of the Stored Procedure. The SP must return a dataset at the end in order for Crystal to be able to use it. It should have the basic shell like this:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE procedure RPT_proc1
AS

SELECT field1,
field2
FROM table1

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Replace the SELECT statement with the SELECT statement from your view.

~Brian

 

[khan007]

The text in Sp is given below.

CREATE PROCEDURE dbo.RPT_Proc1
(
/** Reports dependent parameters ***/
@START_DATE varchar(50)='',
@END_DATE varchar(50)='',
@DISCIPLINE varchar(50)='',

/**Compulsory parameters for all report in new UI */
@REPORTID varchar(50)='',
@LOGINUSER varchar(50)='',
@IPADDRESS varchar(50)=''
)
AS

Declare @query varchar(2000)
Declare @strDynamic varchar(2000)
set @strDynamic = ''

SET NOCOUNT ON

set @END_DATE = replace(@END_DATE,',','/')
set @START_DATE = replace(@START_DATE,',','/')

/*Pass Report dependent parameters except DATES parameters*/

EXEC DBO.S_RPT_MASTER @DISCIPLINE=@DISCIPLINE,
@REPORTID=@REPORTID,
@query=@strDynamic output

set @query ='select * from dbo.VW_'+@REPORTID +
' where '+ @strDynamic
+' AppointmentDate between CAST(Convert (varchar,ltrim(rtrim('''+@START_DATE+''')),101) as datetime) '
+' and CAST(Convert (varchar,ltrim(rtrim('''+@END_DATE+''')),101) as datetime)'


print(@query)
exec (@query)
GO

 

[vidru]

Crystal will not handle dynamically executed sql in a stored procedure very well, if at all. The last statement of the procedure has to be a SELECT statement for Crystal to be able to understand what fields and datatypes to expect.

I could be wrong though...

-dave

 

[synapsevampire]

Dave: Crystal is much more forgiving of SQL Server than Oracle, so it should work, although the print(@query) statement might cause a problem, and seems pointless for a report.

-k

 

[bdreed35]

khan-

I think Dave is right regading the Dynamic SQL. Since you need to reference your views based upon a parameter (@REPORTID).

I would try creating a Temp table, then change your Dyanmic SQL to Insert the selected records into the temp table. At the end of the proc you can SELECT * FROM #temp_table. Also, remove the print(@query) line.

Let me know how that works out.

~Brian

 

[vidru]

You're right k, I just wrote a test proc that's using dynamic sql and it worked.

I also added a PRINT statement right before my EXEC statement, and the procedure still worked.

I think the problem lies in the dynamically created sql. If you execute the procedure from query analyzer does it return anything?

-dave

 

[bdreed35]

I just tested this as well and k is right. Dave, I couldn't get the proc to work with Crystal with the print statement, but it worked with Crystal once I removed the print statement and recomplied it. Your probably right that the error lies in the dyanmic sql if there still is an error when the print statement is removed.

~Brian

 

[khan007]

I removed the print statement from SP but i am still getting the same error.

Does it means that error lies in the dyanmic sql and i need to change my SP script.

Thanks guys.

 

[bdreed35]

Are you getting the error in Crystal or in Query Analyzer?

~Brian

 

[khan007]

I analyzer i am getting records when i run thta sp, but

In crystal designer,
Once i try to set location with new SP instead of View
it pops up an error window says

Error opening file
There are no fields in the file dbo.Proc(RPT_proc1;1)

Any opinion?
Thanks

 

[bdreed35]

Try creating a new report and pointing it to the stored proc. I have seen instances where an existing report won't except a SP for some reason but a new report will. This will tell us if it is the report or the proc.

~Brian

 

[khan007]

yes new report is working fine and i also test this one with an old report and it seems ok to me.

But i am unable to set an alias . it is not allowing me by giving an pop up error window.

"The alias you have entered is not allowed.
The alias must have more than one character and not contain any special characters."

please let me know what i suppose to do else report will shoe old view name and fields every where.

Thanks

 

[bdreed35]

So if you do Set Location with the existing report, it gives you an error and closes up, correct? I am not sure what else to tell you. You can try doing a verify database against the old view and then try a Set Location to the new SP. Don't worry about the alias because you can set that at any point.

One time when I had a similar problem, I did a Database--> Remove Database so that I dropped the datasource that I had. I then added the SP and added the fields again. This isn't always a good option especially if there are a lot of fields on the report. If you are going to do this, make sure you have a backup of the report just in case.

~Brian

 

[khan007]

Thanks a lot, you guys spent a lot of time with me.

What i am doing at this point is, adding a SP first in report, removing every instances of old parameters from report and replacing it with new SP parameters, remove everything from "Edit selection formula", then replacing previous fields with new in each group and formula , then placing all other fields in report, once everything replaced then delete the old view from report, but problem is if i have 200 formulas, the i need to do a lot of labor work.

So i thought set location and set alias will replace everything from view to SP as fields are same in both.

anyway i will try to find some logic to resolve this issue.

Thanks

 

[bdreed35]

If your new SP returns the same fields to the report as the original view, just do a Set Location and point to the new SP. It will take care of the updating everything for you.

~Brian

 

[khan007]

Thanks a lot my dear friends.

I have resolved all the issues.

1)Removed the Print statement from SP.
2)Set location, and pointed to new SP
3)Cleaned edit_selection_formula as query was already in SP
4)Replaced old parameters with new parametrs (SP based) every where in report and delete old one.
5)Set Alias, and in order to get rid of error set it as
RPT_proc1 instead of Proc(RPT_proc1;1).

It worked perfect for me.
Thanks a lot.