on 01-25-2011 12:43 AM
Hi,
We have a large number of crystal reports that are called from a VS 2010 vb.net (4) application.
The vb.net application has recently be upgraded from a VS 2008 vb.net (3.5) application. In upgrading the application, the crystal drivers were upgraded from version 12.0.2000.0 to version 13.0.2000.0
The reports access a SQL Server 2008 database (which hasn't changed).
With many of the reports now, when trying to view the reports through the application we now get an error:
Database Connector Error: '[Database Vendor Code: 257]'
If the same report is viewed using the older application it works fine.
Profiling the database call, it can been seen that the new version of the App has altered the order of the parameters passed into a stored proc, now making the database call invalid.
Any help resolving this issue would be greatly appreciated.
Hello,
If you still have CR 2008 running use ProcessMonitor and look at the DatabaseOption registry keys CR 2008 is using. Then add that key to the CR 2010 registry and it should then sort the fields the same way.
All valid keys in CR 2008 will work in CR for VS 2010.
Thank you
Don
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I typically try to look up the actual meaning of the Database Vendor Code as this is being passed unmodified from the database client through the CR engine.
According to MS, the description for the error is:
[Implicit conversion from data type %ls to %ls is not allowed. Use the CONVERT function to run this query|http://technet.microsoft.com/en-us/library/cc917589.aspx].
I am not sure what that actually means, but it may be a pointer as to some next step?
Ludek
I believe I found the cause...
Microsoft updated their Native 10 client and changed the name of their dll. CR wasn't aware so it was tracked and fixed in CR 2008. See this note: 1458767 - Datetime data type fails to load correctly to MS SQL Server 2008
ADAPT01471448 - It has been fix in CR for VS 2010 but it has not been released yet.
As Ludek suggested it' snot the order causing the problem, if so then use the name of the parameter and not just the index of it then you are sure to set the correct one all the time.
If the Parameter you have/using is a Date then this is likely the cause.
I'll find out when the update will be available for CR fro Vs 2010.
Thank you
Don
Hello,
I drilled into the driver issue more and confirmed we did ship the fix in the RTM release. So this likely isn't the cause.
Need more info now, can you list the parameters you are using as seen in the designer and then list the values being set at runtime so we can duplicate the issue?
And another test is for you to not set any parameters in and have CR prompt for them and confirm they are the same as what you see in the Viewer.
Thank you
Don
Hi,
One of the reports that is not working has the following Parameter Fields in designer and type
companyName String
sortField String
hideField Boolean
@fromDate DateTime
@toDate DateTime
@restrict Number
@businessID int
@coutner int
When setting the parameters in the code, The code is similar to below:
For each parameterField in ReportDocument.dataDefinition.ParameterFields
parameterField.currentValues.Clear()
SELECT CASE parameterField.Name
CASE ....
reportDocument.SetParameterValue(parameterField.name, fieldValue)
ELSE
...
END CASE
Next
The code is the same in the VS2008 application as is it in the VS2010 application.
The report file is the same
The database is the same
The stored proc the report refers to is the same
We set the report database connection similar to the following:
Dim connInfo As ConnectionInfo = New ConnectionInfo()
connInfo.serverName = serverName
connInfo.DatabaseName = databaseName
connInfo.userID = userID
connInfo.password = password
For Each table In ReportCoument.database.Tables
Dim tableLogOn = Table.logonInfo
tableLogOn.connectionInfo = connInfo
table.applyLogOnInfo(tableLogOn)
Next
The error does not occur until the report makes a call to the database (again when the call is made to the database, the parameters are in the incorrect order)
Hello,
I can't explain it then... using the Name property should simply add the values in what ever order the report needs and then generate the SQL accordingly.
What happens if you recreate this report to see if that works? Add the parameters in the order they should be.
One other test Ludek suggested is if you do not set the parameters and simply preview the report CR should prompt you for the Parameter values. What you see in the Parameter UI should be the same order the parameters are filled at runtime.
Thanks again
Don
Yes if I recreate the report it works fine -> though with over 1000 reports this is not a solution.
If I dont set the parameters then it prompts for the parameters in the correct order.
I have just discovered one other this, this report has the: verify Stored procedure on first refresh option ticked (not to sure why anyone did that). If I untick that option then it also works fine. Again this would mean going into over 1000 reports and checking that this option is unticked - so not a satisfactory solution.
Any other ideas?
Hello,
I'm getting a VS 6 and CR XI image going to test with to see if the RDC has the ability to turn that option off at runtime. Then you could write a simple app to make the change. I had a look through our various .NET engines and I don't see how to do this in .NET.
Your only options are to add the function request to Idea Place to get the property exposed, try using the replace connection method and DoNotVerify to see if that ignores the option or maybe using the RDC, still installing, to see if it has the functionality or last option is to manually alter the reports.
This may be the API to use: CrystalDecisions.ReportAppServer.DataDefModel.CrDBOptionsEnum.crDBOptionDoNotVerifyDB
Thank you
Don
Hello,
I did more testing to see if and how we read that property and we don't. It's saved in the registry but at runtime it's not being read.
The RDC did not have a property for that setting either.
So try this, use a CASE or multiple IF statements to set the value by name this way no matter what order CR gets them they should be set.
Thank you
Don
Hi,
Sorry I'm not sure I follow you. Are you meaning make sure I set the Crystal Parameters by name (rather than index)? If so, as you can see from the code I submitted above, we are setting the crystal parameters by name.
Are you saying that the Verify Stored Procedure setting is not looked at in runtime? If so how come when this option is checked, the report makes two calls to the database (verified with SQL Profiler) whereas if it's unchecked it only makes one call?
Thank you
Ah.... Sorry about that. You are using the case statement.
What I was looking for was to see if the registry key overwrote what was saved in the RPT file. The verify is saved in the report so I was testing to see if the registry key would work as a work around but unfortunately it doesn't.
I'll keep looking but I don't believe there is a work around, your only option is likely to check the reports manually. I'm talking to the SDK Program Manager to see if it's something we could add in some future release, it won't help you now though.
Thank you
Don
Hello,
Do you know if the SP parameters were altered after the report was designed? May explain whatu2019s in the report is different than the DB and why CR is using the Report Parameter order and not the SP order.
If you simply verify the report does that fix the problem or do you have to check the option off?
Thank you
Don
Hi,
I do not no for certain whether than have been changed since the report was designed. The rpt file has been around since before I joined the company, and I have access to versions of the file dating back to 2007. In that time the parameters have not changed one bit, how the data is displayed has been altered a few times, the stored proc may have been altered to return some extra fields, but the parameters are the same.
One other thing, the parameter order in the RPT does match the parameter order in the stored proc, it's just if the option "Verify stored procedure on first refresh" is checked that it passes them in the incorrect order.
I've just tried leaving the check in place for that option and then verifying the database, and it still doesn't work, in fact it's moved one of the other parameters to the before what should be the first parameter.
i.e. Stored Proc expects parameters in this order: smallDateTime, smallDateTime, int, int, int
what has been getting passed when I first started this thread is: int, smallDateTime, smallDateTime, int, int
After doing a verify database it is getting parsed: int, int, smallDateTime, smallDateTime, int
I haven't tried doing three more verifies to see if that then gets me back to what we should have, but to do that to all rpts would be more work than simply unchecking that option.
Hello,
Did you get my direct e-mail to your e-mail address you use in Forums?
It's curious that the order does change which suggests something in the SP has changed but strange that it changes again....
As a test, what happens if you create a new report off that same SP and run it? This may tell us there is either an issue in our import routine to update the report or possibly there was some order change done and CR did not get verified. Now it's showing up...
Thank you
Don
mmmmm... this suggests that something changed at some point in the SP and the report was never verified against those changes.
If you want to go one step further and duplicate the report and test, it will at least verify it's an old report issue and not a new report problem.
Likewise if you want to remove parts of the old report and test that will verify it's not something like a formula or record selection formula in the old report causing the problem.
Do you know how many of your reports have this problem?
First, if it is a Verify issue due to not being updated I doubt R&D would be able to fix it. We may be able to but then it could be it will break someone elses reports so we won't do it.
If it's not that many reports, and I assume you'll verify them anyways, then it may easier for all to just check that option off. It could be that in the early development days when the DBA and Report Designers were building the app and reports it was easier to leave the option on so someone back then did not have to manually verify the reports.
Thanks again
Don
Thanks for verifying. The designer likely sorts them accordingly where in the app it sorts them according to the info from the SP or from CR in the order they are used.... Hard to say for sure.
We don't have a tool to look at the rpt file itself, it's a proprietary format that as far as I know no one has a tool to decode it. Not that it would help much anyways.
Don
Hi Don,
You are right that somethings in the stored proc had changed over thime, just not the parameters.
The last change was done myself, getting rid of a subreport that could be better handled by returning a couple of extra fields in the main stored procedure.
The rpt was verified against the stored procedure at the time, so that I had those extra fields to work with, but I didn't go checking the rpt options, because that verify stored proc is not something that anyone currently here uses.
As to how many rpt's have this problem, no idea. I know we have over 1000rpts, and I would be guessing that the only ones with this option checked are among the older of the rpts, but that would still be in the hundreds to check.
When stepping through the application code, when it is setting the rpt parameters (by name) the actual order that it sets them is the correct order, not the order that is then passed to the DB.
That is odd, if you want to send me your report and sample database I can test it here also. Did you get my e-mail?
Simplified app that just sets the parameters and log on info would be of great help also so we use the same methods.
I can then just attach your sample MDF and MDL database files to my server and test. If it is a bug we can get R&D to fix it but be aware this will take time to get out, likely more than 6 months so you may have no option but to check that option of manually.
Thank you
Don
Moved to .NET SDK forum
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
| User | Count |
|---|---|
| 3 | |
| 3 | |
| 2 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.