Wrong order of parameters when CR 2008 runs stored procedure

[Former Member]

Hello,

Can you please help to fix the issue with the reports which using stored precedures.

My report runs the SP wtih signature:

procedure [dbo].[crystal_AuditReport]

@AuditID nvarchar(100) = null,

@DocumentRoot nvarchar(100) = null,

@Stuff1 bigint = null,

@Stuff2 bigint = null,

@Stuff3 bigint = null as

But MS SQL profiler shows the following query:

exec "DBName"."dbo"."crystal_AuditReport";1 -5, N'{182c0093-fbb9-48e8-8ade-6e7ac45dfb39}', -5, N'C:\UPLOAD\ATTACHMENTS\', -5

As can you see the order is wrong. It leads to the error:

Source: CrystalDecisions.ReportAppServer.DataSetConversion

Exception Type: CrystalDecisions.CrystalReports.Engine.DataSourceException

Exception Message: Database Connector Error: 'ADO Error Code: 0x80040e07

Source: Microsoft OLE DB Provider for SQL Server

Description: Error converting data type nvarchar to bigint.

SQL State: 22018

Native Error: 8114 [Database Vendor Code: 8114 ]'

Error in File AuditTrailReport {2571BD2C-28AF-4F48-A7F6-C967674D1099}.rpt:

Database Connector Error: 'ADO Error Code: 0x80040e07

Source: Microsoft OLE DB Provider for SQL Server

Description: Error converting data type nvarchar to bigint.

SQL State: 22018

Native Error: 8114 [Database Vendor Code: 8114 ]'

InnerException: System.Runtime.InteropServices.COMException (0x800002C3): Database Connector Error: 'ADO Error Code: 0x80040e07

Source: Microsoft OLE DB Provider for SQL Server

Description: Error converting data type nvarchar to bigint.

SQL State: 22018

Native Error: 8114 [Database Vendor Code: 8114 ]'

Error in File AuditTrailReport {2571BD2C-28AF-4F48-A7F6-C967674D1099}.rpt:

Database Connector Error: 'ADO Error Code: 0x80040e07

Source: Microsoft OLE DB Provider for SQL Server

Description: Error converting data type nvarchar to bigint.

SQL State: 22018

Native Error: 8114 [Database Vendor Code: 8114 ]'

at CrystalDecisions.ReportAppServer.Controllers.ReportSourceClass.Export(ExportOptions pExportOptions, RequestContext pRequestContext)

at CrystalDecisions.ReportSource.EromReportSourceBase.ExportToStream(ExportRequestContext reqContext)

StackTrace: at CrystalDecisions.ReportAppServer.ConvertDotNetToErom.ThrowDotNetException(Exception e)

at CrystalDecisions.ReportSource.EromReportSourceBase.ExportToStream(ExportRequestContext reqContext)

at CrystalDecisions.CrystalReports.Engine.FormatEngine.ExportToStream(ExportRequestContext reqContext)

at CrystalDecisions.CrystalReports.Engine.FormatEngine.Export(ExportRequestContext reqContext)

at CrystalDecisions.CrystalReports.Engine.FormatEngine.Export()

at CrystalDecisions.CrystalReports.Engine.ReportDocument.Export()

Please note the report was developed using CR 9, now I'm using CR 2008 sp 3 runtime.

Thank you

[Former Member]

Thank you for your replies

"Verify database" was first thing which I did. But it did not help. I have beed working with CR for several years, but I did not face similar issue before.

Initially I installed CR 2008 SP 3 (version 12.3.2003.812) on our web server. The reports worked on one web-site, but do not work on other web-site. It was very strange because both web-sites are setup on the same server. Today I removed CR 2008 SP 3 and installed the version lower than was (12.0.2000.840) and the report are working on both sites.

[paul_aziz]

I have similar problem. In my case the report previews well in both US and UK cultures but will only display in the application in only US culture.  The problem has to do with date parameter in my sp.

As hard as I have tried, I have not been able to get the report display in UK culture.

So concentrate your energy on the Date parameter in your sp. if you do have one.

[Former Member]

As the last resort, what works for me in CR2008 is this:

SSMS

- remove all parameters in stored procedure (comment them out, create variables with the same names to leave your SQL code intact)

- parse, execute

CR

- Database > Verify Database

- CR removes all parameters (if you're using them in formulas, you need to comment these out)

SSMS

- un-comment all parameters in SP (remove variables with the same names). Move them around as you wish

- parse, execute

CR

- Database > Verify Database

- CR adds all parameters in the same order as the stored procedure

[former_member11696]

If you are using CR 2008 runtime then you should have CR 2008 Designer to Verify the report then it should work.

Thank you

Don