SQL Server 2000 Stored Procedure w Text Parameters 1

[FredHelm]

I have designed a SQL Server 2000 stored procedure where all the parameters are of varchar type with exception to one parameter which is a text type. This stored procedure works for report files created in Version 7, 8 and 8.5. When I design the report and set the location of the datasource in crystal reports version 9, the designer prompts me to fill in the parameters which I do. I click ok, then a dialog box appears with only an ok button and no message. I believe this is an error message box but I'm not sure. I click ok and it seems that Crystal Reports wants me to re-enter the value for the text parameter.

I'm connecting to the database through ODBC if that helps.

Has anyone else encountered this problem? Does the service pack 2 solve this issue? The service pack has no mention of this problem.

Thank you in advance for all suggestions to fix this problem.

 

[synapsevampire]

You never mentioned how you were connecting to the database from CR 9, perhaps you can try a different connectivity to get around the error.

Why would you have a data type of Text?

Change it to a varchar as well, parameters are meant as something to easily type/select and pass to limit rows.

You can then cast it as text immediately in the SP if you have some reason why that makes sense.

-k

 

[FredHelm]

Thank you for your reply.

I am connecting to the database in CR9 with ODBC as in previous versions.

I use the data type of text to receive a comma separated list of values from a multi-select control. The number of items in the multi-select control has no upper limit. Using a varchar(8000) means that only so many items can be picked otherwise a right string truncation can occur causing the stored procedure to raise errors.

Where I fail to comprehend is my program logic works for previous versions of Crystal Reports but not the latest version of Crystal. I can't believe that Crystal (or Business Objects or whatever they are calling themselves these days) would drop the functionality of text stored procedure parameters.

Fred

 

[pandpp]

Fred,

I don't have an answer unfortunately, but I have a question for you please.

I understand what you are trying to do with your multi-select list. I have attempted to get the very same thing to work with CR, with no success to date.

Could you give me a few quick pointers of how you managed it, with the previous CR versions.

Peter.

 

[FredHelm]

Peter,

I'm not sure if you are using Crystal Parameters or Stored Procedure parameters. Regardless the strategy should be the same.

My parameter type is string type. The value of the parameter is a comma separated list. For arguments sake let's use 4,5,6,8 where these numbers are a list of numbers for an identity column. To return the 4 records without parsing the string the where clause looks like the following:

Select * From NorthWind.dbo.Products 
Where ','+'4,5,6,8'+',' like '%,'+Convert(varchar(3),ProductId)+',%'

Points of concern: [ul][li]conversion of the number data type to a string[/li]
[li] appending a leading and trailing commas[/li]
[/ul]

The above select statement is done in the stored procedure but you should be able to do the same thing in the record selection formula.

Hope this helps.

Fred

 

[pandpp]

Fred,

Thanks for responding.

Once I get the values into the SP, I know how to get the result I need.

What I am having trouble with is, how to go from the Multi-Select param in CR, which is effectively an array, to the Text param you are passing to the SP.

I can append the values in the array together, to provide a string, but, I haven't figured out how to pass this down to the SP, all before the SP is called by CR.

I hope all that makes sense.

Thanks,

Peter.

 

[FredHelm]

If I understand you question correctly in that you are building a string inside CR and then you want to execute the stored procedure from within crystal then the only way I can see this working is to call the stored procedure from the subreport.

As soon as you click the lightning bolt in the designer you are prompted to fill in the parameters. At this point no formulas have been executed within Crystal therefore your comma separated string has not been created. If executing the stored procedure as a subreport does not work for you then you will have to build your comma separated list outside of the report file.

I don't think I fully understand how you have setup the crystal rpt file. If you want could you e-mail me the rpt to my e-mail account fhelm@sri.bc.ca. Could you also let me know what version the rpt file is in?

Fred