cancel
Showing results for 
Search instead for 
Did you mean: 

ODBC Stored Procedure Error

Former Member
0 Kudos

Hi all... i have a problem i can't solve....

i have a very very simple stored in my sql server db

CREATE PROCEDURE [dbo].[try]

@Par as int

AS

BEGIN

SET NOCOUNT ON;

SELECT Field1 FROM Table1 WHERE Option=@Par

END

then, i create a simple report. i choose odbc connection and the dsn name. then i added the SP and crystal ask me for the parameter value. then i added the field to the report, print it and save it... and this works fine.

very simple...

now... i need to print "Field2"... so a alter the procedure

SELECT Field1, Field2 FROM Table1 WHERE Option=@Par

open the report and press "verify database"... crystal ask me for parameters and then i have the error

"the table doesn't exists in the database. do you want to remove it?"

i presso no and i have the message "database is ok" and the report works, but without field2..

i also tried to not modify the SP with field2, so just create the report and press "verify database", with no modifications at all... same error!

?????????

obviously this is an example i made to verify if my real report (much more complicated) as a problem, but is not.

with old crystal report 8 no problems! but if i use the report made with cr8 with the 2008 runtime (vb .net 2008 application) the report doesn't work

what shall i do? i can't have a report that i can't modify in the future!!

thanks

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

This the Pubs sample SP that does work:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[byroyalty] @percentage int

AS

select * from titleauthor

where titleauthor.royaltyper = @percentage

See if this basic SP, using your tables, works for you.

Thank you

Don

Former Member
0 Kudos

what do you mean? this SP is identical to the one i wrote in my post...

0 Kudos

Sorry, so it is.....

What happens if you create a new report off the new SP?

Thanks

Don

Former Member
0 Kudos

it happens what i wrote...

0 Kudos

That is strange.... I've never heard of this happening...

Try using the OLE DB provider to see if that works, could be an ODBC issue.

Try creating a report in the .NET IDE and see if that works.

Don

Former Member
0 Kudos

with ole db it works, but i have 2 problems with oledb

1) i can't use ole db in my real scenario

2) with the sample SP, it works. but in my real SP, i have a output parameters (used from the code, not for the report). when i choose the SP in Crystal, with oled it asks me only the input parameters and then i have the error "the procedure xxx expect parameter @output

i'll try to create the report from the .net 2008 ide (never do that)

thanks for your attention

Former Member
0 Kudos

i tryed just now from vs .net 2008. create a new crystal report project, choose new report with wizard and create the report. launch the program and i have the report previewed. then i right-clicked on database fields (on the left), click verify database and i have the same problem "the table doesn't exists"

Former Member
0 Kudos

ah... the report continues to works, there is no problem with it... but i haven't the new fields in "database fields" list

0 Kudos

Are you altering the SP at runtime?

CR assumes the datasource structure never changes, if it does it requires a refresh and verify DB to be called.

This is very strange and doesn't make sense. I think there is something going on that you are not telling us?

How are you logging on and setting the report to the log on info?

Once logged on are you changing the SP? CR will also cache the database structure.

Are you using Report.Refresh once all the changes are done?

Have you installed [CR for VS 2008 SP 1|https://smpdl.sap-ag.de/~sapidp/012002523100009351512008E/crbasic2008sp1.exe]?

Don

Former Member
0 Kudos

no, the problem is only in cr 2008, i have the problem without using visual studio...

due to your post saying "could be a odbc related problem", i tryed to change the odbc. normally, i use "SQL SERVER" as driver and the versione is 2000.85.1132.00. so i change the odbc and i choose SQL SERVER Native Client 10.0.

now the report works O_O, i can verify database and if i change the sp, i have the new fields in the list... the problem is that with this drive, if i change the database in the ODBC, the report doesn't change source. with the other driver if i changed the database in the odbc the report show the data of the other db...

0 Kudos

CR 2008 doesn't support MS SQL Server 2000.

When you configure your DSN use the SQL Native 10 driver

As for doing it in code search on "ReplaceConnection_Click"

Edited by: Don Williams on Mar 2, 2011 10:07 AM

Former Member
0 Kudos

i don't know why i have that version of sql server odbc driver... i tryed in a windows 7 pc and it works.. i have windows xp... i0ll make more tests, but know i know the problem is the driver.

sorry for the stolen time and thanks for all

Edited by: Rollo82 on Mar 2, 2011 8:30 PM

0 Kudos

Thanks, please mark the post as answered....

Answers (0)