• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

Error when a new database parameter is added to the report.

Hi,

  I have an existing report, which uses a stored procedure and calls it like this.
 
  CALL SP_DROPTEST({?ARG1})
 
  Where Arg1 is an integer parameter, coming from the stroed procedure.
 
  Now, i have added another parameter, a string to the stored procedure.
  On the crystal side, i have added this parameter as Arg2, like below.
 
  CALL SP_DROPTEST({?ARG1},{?ARG2})
 
  But when i hit "OK", it gives me a data connector error and fails to retrieve data.
  Attaching the error screenshot.
 
  How can i fix this problem? Please help.
 
 



 Error Screenshot
0
pvsbandi
Asked:
pvsbandi
  • 4
  • 3
  • 2
  • +2
1 Solution
 
vastoCommented:
Verify database
0
 
pvsbandiAuthor Commented:
Verify Database gave me another error: error
0
 
mlmccCommented:
Try logging out of the database and logging back in through Crystal.
Then Verify database

If that doesn't work
Log Crystal out of the database
Close Crystal
Open Crystal
Open the report
Verify database

mlmcc
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
pvsbandiAuthor Commented:
Did all that..still doesn't work.
 If i pass a literal in the place of Arg2, it works fine. But if i create a parameter and  add to the command, it doesn't prompt for it and then error out.
0
 
tliottaCommented:
If i pass a literal in the place of Arg2, it works fine.

That would make me suspect that the procedure definition of the parameter does not match the parameter definition that you are attempting to use to pass the value. The success of the literal shows that the proc exists and can be accessed from the client.

Tom
0
 
pvsbandiAuthor Commented:
That's correct. The stored procedure by itself works fine.
  but the second variable is a varchar on the Db side and is declared as a string on the crystal side.
 
0
 
mlmccCommented:
STring and varchar should match.  Crystal changes the varchar to string.

How did you add the parameters on the Crystal side?

mlmcc
0
 
James0628Commented:
If you're doing this in a CR Command, you have to create the parameter from within the CR Command editor, not through the regular parameter editor.

 And if this is a string parameter, it should probably be in single-quotes (or whatever your db uses for a string literal):

CALL SP_DROPTEST({?ARG1},'{?ARG2}')


 CR will replace the parameter with its value, so if you enter 123 for ?ARG1 and ABC for ?ARG2, you end up with

CALL SP_DROPTEST(123,'ABC')


 Without the quotes, you end up with

CALL SP_DROPTEST(123,ABC)

 which the db probably won't understand.

 James
0
 
James0628Commented:
Also, with some db's, CR can execute stored procedures directly.  If you have that option, that would probably be more efficient than executing a Command that calls a SP.

 James
0
 
pvsbandiAuthor Commented:
Thanks! ultimately, this is what i did.. but i still issues adding a new parameter, when i call the procedure through command..
0
 
James0628Commented:
I've never tried to execute a SP from a CR Command.  I just connect directly to the SP.  When you do that, CR automatically adds the SP parameters to the report.  There may be some trick to handling SP parameters in a Command.  It seems straightforward enough, but maybe not.  IAC, I'm glad the alternative worked for you.  It's probably more efficient anyway.

 James
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now