Hi All,
Facing an issue while exporting a report(invoice) to PDF in Crystal 8.5 after exporting 1000's of files(invoices) to PDF.
Error msg "2147190908 Failed to export the report". after generating this error, OKB PDF file is generated and we are nt getting any invoices from then.
We have a Licensed Copy of Crystal Reports 8.5 Developer Version
We are facing a Problem while exporting our Invoices in PDF Format
After Printing Some Invoices It Starts giving the following error
Error message" -2147190908 Failed to export the report".
The Number at which the process starts giving this error is not Consistent .
Some time the problem starts after printing 60, 000 Invoice and some times
it starts coming after 40 to 50 thousand.
But once the problem comes , then it keeps coming whatever we do.
We did the following to rectify the problem but of no use
1. Restarted the machine
2. Un installed the Application and re-installed it .
3. Replace the Crystal DLLs , some of them I am listing here (Crxddrt.dll ,
crxdrt.dll, crxDesignerctrl.DLL, CRXDUI.DLL)
4. Applied the HOT fix patch CR85devwin_en.zip on our existing Development
env
It is a very Critical Problem as Implementation is starting from 31st
January , and Client is just waiting for this patch
Any help will be highly appreciated , In case somebody assure me to
solve this problem we can also go for the annual Support Contract.
Please help !!!!
Regards,
RajWe are experiencing the exact same issue. Reports have been exporting fine for over a year and then suddenly we are getting the same export error.
We have also tried reinstalling Crystal and before we re-image the machine, has anyone found a solution to this issue or at least what we can look into.
You said that there is not a set number of reports where you start to see this error. How were you able to get it to start temporarily working again??
Thanks for any help!|||Incase anyone comes looking, our solution was to empty out the temp folder where Crystal was creating the files before converting to PDF. We had over 75000 temp files sitting in the folder. Why Crystal doesn't clean up after itself I don't know, but we ended up creating a service that empties this folder everyday.
Hope this solves your issue as well.
walther|||Bumping this thread for resolution.
Our system just recently experienced the same issue.
Win2k3 Server
Citrix Metaframe XP
Crystal Reports 8.5
Adobe Acrobat 5
I've already uninstall acrobat, crystals, java, and a plethora of other software in hopes of fixing this bug. I've regsvr32 practically all the dlls and deleted the temp files/folders as well.
I've done practically everything short of reinstalling the entire server.
Any suggestions would be greatly appreciated.|||Hello,
I know this is an ancient subject, but I am running into the same issue. I see that Walther, you mentioned that you solved the problem by deleting the files out of the temp folder. Can you tell me where Crystal Reports keeps it's temp files. I want to view that folder on our box to see what it looks like.
Thanks, Neil
Showing posts with label msg. Show all posts
Showing posts with label msg. Show all posts
Thursday, March 8, 2012
Tuesday, March 6, 2012
2005 vs 2008 NOEXPAND hint behavior
With SQL 2005, using the NOEXPAND hint can result in an error:
Msg 8171, Level 16, State 2, Line 1
Hint 'noexpand' on object 'dbo.ervStats' is invalid.
I haven't tried it with 2008 yet but I expect the same behavior. That behavior can cause problems and I would like to understand the logic behind it. I would expect a Warning to be raised, not an error.
Scenario:
OLTP application using one or more indexed view to speed up some functionalities. This can happen as some db are OLTP but also used for real time reporting that can't be done on a mirror. At some point you may find out 1 indexed view is the cause of excessive locking for 1 specific site, so you remove the index....but must now recompile and redeploy the application to hundreds of desktops to remove that 8171 error message.
If a warning was issued instead of an error, the problem would not occur. Nothing is changed at the logical level and the query would run as expected is the hint was ignored.
There is possibly a very good reason to raise an error instead of a warning, but I can't see it right now. Before I create a feature request on Connect can somebody shed some light on that behavior? (I might change my mind once I understand the logic:-) )
PS: I KNOW indexed views are not meat for OLTP scenarios, but there are edge cases where they do help when designed properly. Sometimes business scenarios change and they must be changed or dropped.
thanks
L
Hi Lakusha - I'm not sure of the direct answer to your question, but as another option vs. recompiling and redeploying your application, have you looked at the possibility of using a plan guide for addressing the issue?
|||
Hi Chad,
No, I haven't looked at plan guides for this. Perhaps I am wrong, but I feel plan guides can't help much in our context. We have hundreds of servers at hundreds of locations (ISV context). I can manage plan guides easily but customers support teams won't. Even something as trivial as dropping an index on a view can prove too much because their schema becomes non standard and they must remember to drop it again after each major upgrades (and sometimes they won't and this will become a support call). Support will check immediately if they upgraded recently and forgot to run the drop index script but plan guides are another matter.
A plan guide will also lock you up. In a complex query I might want to use a NOEXPAND on a materialized view (especially when the Enterprise query optimizer sometimes can't be trusted to choose the view but also because some customers use the Enterprise versions while others use Standard...so the lowest common denominator wins) but I certainly don't want to force the plan on the entire query because it will certainly be a good plan for some customers but not for others.
I also think that discarding a hint is certainly worth a warning but not an error since it does not change anything from a logical stand point. The exact behavior could even be controlled at the db level, or even better, at the hint level.
Most of all, I would like to understand why they choose to raise an error and not a warning. In the end it is best to understand why things work a certain way.
PS: I know MS Research has done a lot of work to support outer joins and stacked indexed views but I haven't seen anything new advertized for indexed views in SQL2008...
thanks
L
|||
Hi Lakusha. Let me try to address points mentioned in this thread one by one.
The behavior of noexpand hint is same as any other hint and, as far as I know, except consistency with other hints there are no other fundamental reasons why it is an error instead of a warning. You definitely can propose this via Connect.
Plan guides will not really solve this problem (with one exception, see my next bullet) since noexpand hint is not a query hint.
You can make a plan guide with USE PLAN providing plan which has indexed view in it and this will force using indexed view same way as noexpand hint. But ... you will be pinning whole plan not allowing for potentially better alternatives as circumstances change, and in SQL Server 2005 it still will generate an error if index is not present on the view. In CTP5 we are planning to have plan guides functionality which will not error out in such case but rather generate plan as if no plan guide was present.
The only workaround I know about is to wrap your query in an SP with following logic:
if <Index is present on the view>
then <run query with noexpand hint in it>
else <run same query without noexpand hint>
There is an added inconvenience of having to wrap it into SP, but it will let you avoid redeployments you mention.
Thanks Alex.
I might make a suggestion about this on Connect then. As for plan guides, I am aware of the drawbacks. Using a SP is indeed a good suggestion. I did not consider it because, for internal reasons, we have chosen to never use them. It is the old adhoc vs SP debate again and in our case ad hoc queries won (we use a DAL that works quite a bit like LINQ).
Thanks
|||Thanks for the post. It's very helpful.
Do you mind providing some more insight regarding the 3rd bullet? I have an ad-hoc query submitted from Business Objects. When I copy the query from Profiler and add a "noexpand", the query runs lightening fast. Without the "noexpand", the query is tremendously inefficient.
So, I then ran the query (with the noexpand) and captured the showplan XML and stuffed the showplan results into the use plan option. Next, when I run the query (with the noexpand and the use plan), it runs great.
Then, when I remove the "noexpand" from the query (but not the use plan), the following error is issued:
Msg 8698, Level 16, State 0, Line 2
Query processor could not produce query plan because USE PLAN hint contains plan that could not be verified to be legal for query. Remove or replace USE PLAN hint. For best likelihood of successful plan forcing, verify that the plan provided in the USE PLAN hint is one generated automatically by SQL Server for the same query.
The reason I removed "noexpand" from the query is because Business Objects does not submit the query with noexpand. I suspect that the reason the error was issued is because the query expanded the view and the plan did not.
In all cases, the query referenced the indexed view. We are running the Enterprise version.
So, how can noexpand be implemented when my company is not the author of the code?
- Is there a machine or database level configuration that could be set?
- How can a noexpand appear in the plan but not in the query without getting the error message above?
- Other?
Any insight you provide will be greatly appreciated!
|||(DaveIII@.discussions.microsoft.com) writes: > Then, when I remove the "noexpand" from the query (but not the use > plan), the following error is issued: > > > > Msg 8698, Level 16, State 0, Line 2 > > Query processor could not produce query plan because USE PLAN hint > contains plan that could not be verified to be legal for query. Remove > or replace USE PLAN hint. For best likelihood of successful plan > forcing, verify that the plan provided in the USE PLAN hint is one > generated automatically by SQL Server for the same query. I guess this is because that in order to validate that your plan is valid, the optimizer keeps generating plans, until it has generated yours. But when the query has NOEXPAND, the optimizer never sees the indexed view; it sees the expanded query, and then will have to try view substitution to come back to the view. And since view substitution is an expensive operation, it does not try everything. Clearly, this is not a very pleasant situation. If nothing else, it's not a very intuitive behaviour. I have not checked Books Online, to see if this restriction is documented. I think you should submit this issue on Connect, but check Books Online first, as I think you should have a different angle depending on whether it's documented or not. If it's not documented, just submit it as a bug. If it's documented, you are better off making this as a suggestion, I think. Of course, in either case, the devs would appreciate if you can supply a repro. -- Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
2005 vs 2008 NOEXPAND hint behavior
With SQL 2005, using the NOEXPAND hint can result in an error:
Msg 8171, Level 16, State 2, Line 1
Hint 'noexpand' on object 'dbo.ervStats' is invalid.
I haven't tried it with 2008 yet but I expect the same behavior. That behavior can cause problems and I would like to understand the logic behind it. I would expect a Warning to be raised, not an error.
Scenario:
OLTP application using one or more indexed view to speed up some functionalities. This can happen as some db are OLTP but also used for real time reporting that can't be done on a mirror. At some point you may find out 1 indexed view is the cause of excessive locking for 1 specific site, so you remove the index....but must now recompile and redeploy the application to hundreds of desktops to remove that 8171 error message.
If a warning was issued instead of an error, the problem would not occur. Nothing is changed at the logical level and the query would run as expected is the hint was ignored.
There is possibly a very good reason to raise an error instead of a warning, but I can't see it right now. Before I create a feature request on Connect can somebody shed some light on that behavior? (I might change my mind once I understand the logic:-) )
PS: I KNOW indexed views are not meat for OLTP scenarios, but there are edge cases where they do help when designed properly. Sometimes business scenarios change and they must be changed or dropped.
thanks
L
Hi Lakusha - I'm not sure of the direct answer to your question, but as another option vs. recompiling and redeploying your application, have you looked at the possibility of using a plan guide for addressing the issue?
|||Hi Chad,
No, I haven't looked at plan guides for this. Perhaps I am wrong, but I feel plan guides can't help much in our context. We have hundreds of servers at hundreds of locations (ISV context). I can manage plan guides easily but customers support teams won't. Even something as trivial as dropping an index on a view can prove too much because their schema becomes non standard and they must remember to drop it again after each major upgrades (and sometimes they won't and this will become a support call). Support will check immediately if they upgraded recently and forgot to run the drop index script but plan guides are another matter.
A plan guide will also lock you up. In a complex query I might want to use a NOEXPAND on a materialized view (especially when the Enterprise query optimizer sometimes can't be trusted to choose the view but also because some customers use the Enterprise versions while others use Standard...so the lowest common denominator wins) but I certainly don't want to force the plan on the entire query because it will certainly be a good plan for some customers but not for others.
I also think that discarding a hint is certainly worth a warning but not an error since it does not change anything from a logical stand point. The exact behavior could even be controlled at the db level, or even better, at the hint level.
Most of all, I would like to understand why they choose to raise an error and not a warning. In the end it is best to understand why things work a certain way.
PS: I know MS Research has done a lot of work to support outer joins and stacked indexed views but I haven't seen anything new advertized for indexed views in SQL2008...
thanks
L
|||Hi Lakusha. Let me try to address points mentioned in this thread one by one.
The behavior of noexpand hint is same as any other hint and, as far as I know, except consistency with other hints there are no other fundamental reasons why it is an error instead of a warning. You definitely can propose this via Connect.
Plan guides will not really solve this problem (with one exception, see my next bullet) since noexpand hint is not a query hint.
You can make a plan guide with USE PLAN providing plan which has indexed view in it and this will force using indexed view same way as noexpand hint. But ... you will be pinning whole plan not allowing for potentially better alternatives as circumstances change, and in SQL Server 2005 it still will generate an error if index is not present on the view. In CTP5 we are planning to have plan guides functionality which will not error out in such case but rather generate plan as if no plan guide was present.
The only workaround I know about is to wrap your query in an SP with following logic:
if <Index is present on the view>
then <run query with noexpand hint in it>
else <run same query without noexpand hint>
There is an added inconvenience of having to wrap it into SP, but it will let you avoid redeployments you mention.
Thanks Alex.
I might make a suggestion about this on Connect then. As for plan guides, I am aware of the drawbacks. Using a SP is indeed a good suggestion. I did not consider it because, for internal reasons, we have chosen to never use them. It is the old adhoc vs SP debate again and in our case ad hoc queries won (we use a DAL that works quite a bit like LINQ).
Thanks
|||Thanks for the post. It's very helpful.
Do you mind providing some more insight regarding the 3rd bullet? I have an ad-hoc query submitted from Business Objects. When I copy the query from Profiler and add a "noexpand", the query runs lightening fast. Without the "noexpand", the query is tremendously inefficient.
So, I then ran the query (with the noexpand) and captured the showplan XML and stuffed the showplan results into the use plan option. Next, when I run the query (with the noexpand and the use plan), it runs great.
Then, when I remove the "noexpand" from the query (but not the use plan), the following error is issued:
Msg 8698, Level 16, State 0, Line 2
Query processor could not produce query plan because USE PLAN hint contains plan that could not be verified to be legal for query. Remove or replace USE PLAN hint. For best likelihood of successful plan forcing, verify that the plan provided in the USE PLAN hint is one generated automatically by SQL Server for the same query.
The reason I removed "noexpand" from the query is because Business Objects does not submit the query with noexpand. I suspect that the reason the error was issued is because the query expanded the view and the plan did not.
In all cases, the query referenced the indexed view. We are running the Enterprise version.
So, how can noexpand be implemented when my company is not the author of the code?
- Is there a machine or database level configuration that could be set?
- How can a noexpand appear in the plan but not in the query without getting the error message above?
- Other?
Any insight you provide will be greatly appreciated!
|||(DaveIII@.discussions.microsoft.com) writes: > Then, when I remove the "noexpand" from the query (but not the use > plan), the following error is issued: > > > > Msg 8698, Level 16, State 0, Line 2 > > Query processor could not produce query plan because USE PLAN hint > contains plan that could not be verified to be legal for query. Remove > or replace USE PLAN hint. For best likelihood of successful plan > forcing, verify that the plan provided in the USE PLAN hint is one > generated automatically by SQL Server for the same query. I guess this is because that in order to validate that your plan is valid, the optimizer keeps generating plans, until it has generated yours. But when the query has NOEXPAND, the optimizer never sees the indexed view; it sees the expanded query, and then will have to try view substitution to come back to the view. And since view substitution is an expensive operation, it does not try everything. Clearly, this is not a very pleasant situation. If nothing else, it's not a very intuitive behaviour. I have not checked Books Online, to see if this restriction is documented. I think you should submit this issue on Connect, but check Books Online first, as I think you should have a different angle depending on whether it's documented or not. If it's not documented, just submit it as a bug. If it's documented, you are better off making this as a suggestion, I think. Of course, in either case, the devs would appreciate if you can supply a repro. -- Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
2005 vs 2008 NOEXPAND hint behavior
With SQL 2005, using the NOEXPAND hint can result in an error:
Msg 8171, Level 16, State 2, Line 1
Hint 'noexpand' on object 'dbo.ervStats' is invalid.
I haven't tried it with 2008 yet but I expect the same behavior. That behavior can cause problems and I would like to understand the logic behind it. I would expect a Warning to be raised, not an error.
Scenario:
OLTP application using one or more indexed view to speed up some functionalities. This can happen as some db are OLTP but also used for real time reporting that can't be done on a mirror. At some point you may find out 1 indexed view is the cause of excessive locking for 1 specific site, so you remove the index....but must now recompile and redeploy the application to hundreds of desktops to remove that 8171 error message.
If a warning was issued instead of an error, the problem would not occur. Nothing is changed at the logical level and the query would run as expected is the hint was ignored.
There is possibly a very good reason to raise an error instead of a warning, but I can't see it right now. Before I create a feature request on Connect can somebody shed some light on that behavior? (I might change my mind once I understand the logic:-) )
PS: I KNOW indexed views are not meat for OLTP scenarios, but there are edge cases where they do help when designed properly. Sometimes business scenarios change and they must be changed or dropped.
thanks
L
Hi Lakusha - I'm not sure of the direct answer to your question, but as another option vs. recompiling and redeploying your application, have you looked at the possibility of using a plan guide for addressing the issue?
|||Hi Chad,
No, I haven't looked at plan guides for this. Perhaps I am wrong, but I feel plan guides can't help much in our context. We have hundreds of servers at hundreds of locations (ISV context). I can manage plan guides easily but customers support teams won't. Even something as trivial as dropping an index on a view can prove too much because their schema becomes non standard and they must remember to drop it again after each major upgrades (and sometimes they won't and this will become a support call). Support will check immediately if they upgraded recently and forgot to run the drop index script but plan guides are another matter.
A plan guide will also lock you up. In a complex query I might want to use a NOEXPAND on a materialized view (especially when the Enterprise query optimizer sometimes can't be trusted to choose the view but also because some customers use the Enterprise versions while others use Standard...so the lowest common denominator wins) but I certainly don't want to force the plan on the entire query because it will certainly be a good plan for some customers but not for others.
I also think that discarding a hint is certainly worth a warning but not an error since it does not change anything from a logical stand point. The exact behavior could even be controlled at the db level, or even better, at the hint level.
Most of all, I would like to understand why they choose to raise an error and not a warning. In the end it is best to understand why things work a certain way.
PS: I know MS Research has done a lot of work to support outer joins and stacked indexed views but I haven't seen anything new advertized for indexed views in SQL2008...
thanks
L
|||Hi Lakusha. Let me try to address points mentioned in this thread one by one.
The behavior of noexpand hint is same as any other hint and, as far as I know, except consistency with other hints there are no other fundamental reasons why it is an error instead of a warning. You definitely can propose this via Connect.
Plan guides will not really solve this problem (with one exception, see my next bullet) since noexpand hint is not a query hint.
You can make a plan guide with USE PLAN providing plan which has indexed view in it and this will force using indexed view same way as noexpand hint. But ... you will be pinning whole plan not allowing for potentially better alternatives as circumstances change, and in SQL Server 2005 it still will generate an error if index is not present on the view. In CTP5 we are planning to have plan guides functionality which will not error out in such case but rather generate plan as if no plan guide was present.
The only workaround I know about is to wrap your query in an SP with following logic:
if <Index is present on the view>
then <run query with noexpand hint in it>
else <run same query without noexpand hint>
There is an added inconvenience of having to wrap it into SP, but it will let you avoid redeployments you mention.
Thanks Alex.
I might make a suggestion about this on Connect then. As for plan guides, I am aware of the drawbacks. Using a SP is indeed a good suggestion. I did not consider it because, for internal reasons, we have chosen to never use them. It is the old adhoc vs SP debate again and in our case ad hoc queries won (we use a DAL that works quite a bit like LINQ).
Thanks
|||Thanks for the post. It's very helpful.
Do you mind providing some more insight regarding the 3rd bullet? I have an ad-hoc query submitted from Business Objects. When I copy the query from Profiler and add a "noexpand", the query runs lightening fast. Without the "noexpand", the query is tremendously inefficient.
So, I then ran the query (with the noexpand) and captured the showplan XML and stuffed the showplan results into the use plan option. Next, when I run the query (with the noexpand and the use plan), it runs great.
Then, when I remove the "noexpand" from the query (but not the use plan), the following error is issued:
Msg 8698, Level 16, State 0, Line 2
Query processor could not produce query plan because USE PLAN hint contains plan that could not be verified to be legal for query. Remove or replace USE PLAN hint. For best likelihood of successful plan forcing, verify that the plan provided in the USE PLAN hint is one generated automatically by SQL Server for the same query.
The reason I removed "noexpand" from the query is because Business Objects does not submit the query with noexpand. I suspect that the reason the error was issued is because the query expanded the view and the plan did not.
In all cases, the query referenced the indexed view. We are running the Enterprise version.
So, how can noexpand be implemented when my company is not the author of the code?
- Is there a machine or database level configuration that could be set?
- How can a noexpand appear in the plan but not in the query without getting the error message above?
- Other?
Any insight you provide will be greatly appreciated!
|||(DaveIII@.discussions.microsoft.com) writes: > Then, when I remove the "noexpand" from the query (but not the use > plan), the following error is issued: > > > > Msg 8698, Level 16, State 0, Line 2 > > Query processor could not produce query plan because USE PLAN hint > contains plan that could not be verified to be legal for query. Remove > or replace USE PLAN hint. For best likelihood of successful plan > forcing, verify that the plan provided in the USE PLAN hint is one > generated automatically by SQL Server for the same query. I guess this is because that in order to validate that your plan is valid, the optimizer keeps generating plans, until it has generated yours. But when the query has NOEXPAND, the optimizer never sees the indexed view; it sees the expanded query, and then will have to try view substitution to come back to the view. And since view substitution is an expensive operation, it does not try everything. Clearly, this is not a very pleasant situation. If nothing else, it's not a very intuitive behaviour. I have not checked Books Online, to see if this restriction is documented. I think you should submit this issue on Connect, but check Books Online first, as I think you should have a different angle depending on whether it's documented or not. If it's not documented, just submit it as a bug. If it's documented, you are better off making this as a suggestion, I think. Of course, in either case, the devs would appreciate if you can supply a repro. -- Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Subscribe to:
Posts (Atom)