Changing stored procedure Freezes Crystal

After I change a fairly complex stored procedure and I run a report
against it, crystal hangs at "assesing database". I have verified the
database. When I run a trace on SQL is shows repeated cachemiss over
and over. I let it run for 30 minutes and nothing.
Anyone?soulkitchen (esoulkitchen@.gmail.com) writes:

Quote:

Originally Posted by

After I change a fairly complex stored procedure and I run a report
against it, crystal hangs at "assesing database". I have verified the
database. When I run a trace on SQL is shows repeated cachemiss over
and over. I let it run for 30 minutes and nothing.

The first reaction is that this is a Crystal problem. Then again, if
SQL Server keeps generating CacheMiss over and over again, and nothing
comes in from the client, then that would be a bug in SQL Server.

Actually, I have seen that precise behaviour, but it was in an early
beta version of SQL 2005 and it occurred when ANSI_PADDING was off.

It sounds as if this is very difficult to troubleshoot on a distance. But
is it possible for you to attach a trace file, with all SP and TSQL
events enabled. Adding the Error events may be a good idea as well.
It could help if you posted the procedure as well.

And of course the version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

soulkitchen (esoulkitchen@.gmail.com) writes:

Quote:

Originally Posted by

After I change a fairly complex stored procedure and I run a report
against it, crystal hangs at "assesing database". I have verified the
database. When I run a trace on SQL is shows repeated cachemiss over
and over. I let it run for 30 minutes and nothing.

>
The first reaction is that this is a Crystal problem. Then again, if
SQL Server keeps generating CacheMiss over and over again, and nothing
comes in from the client, then that would be a bug in SQL Server.
>
Actually, I have seen that precise behaviour, but it was in an early
beta version of SQL 2005 and it occurred when ANSI_PADDING was off.
>
It sounds as if this is very difficult to troubleshoot on a distance. But
is it possible for you to attach a trace file, with all SP and TSQL
events enabled. Adding the Error events may be a good idea as well.
It could help if you posted the procedure as well.
>
And of course the version of SQL Server you are using.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

It ended up being a crystal problem I think. I checked the box to
perform the query Asynchronously. Then following is what that does.

" Retrieving data from the database server can be divided into two
parts: executing the SQL statement and fetching the rowset from the
database server. Selecting this option allows Crystal Reports to
execute the SQL statement asynchronously, which means that instead of
waiting for the SQL statement to finish executing, the program checks
intermittently to see if the user wants to cancel the process. This
option is not selected by default ".

I am not sure why this would make a difference, or even if it was my
problem, but I am going to try to re-create the problem to see if this
actually fixed it.|||soulkitchen wrote:

Quote:

Originally Posted by

Erland Sommarskog wrote:

Quote:

Originally Posted by

soulkitchen (esoulkitchen@.gmail.com) writes:

Quote:

Originally Posted by

After I change a fairly complex stored procedure and I run a report
against it, crystal hangs at "assesing database". I have verified the
database. When I run a trace on SQL is shows repeated cachemiss over
and over. I let it run for 30 minutes and nothing.

The first reaction is that this is a Crystal problem. Then again, if
SQL Server keeps generating CacheMiss over and over again, and nothing
comes in from the client, then that would be a bug in SQL Server.

Actually, I have seen that precise behaviour, but it was in an early
beta version of SQL 2005 and it occurred when ANSI_PADDING was off.

It sounds as if this is very difficult to troubleshoot on a distance. But
is it possible for you to attach a trace file, with all SP and TSQL
events enabled. Adding the Error events may be a good idea as well.
It could help if you posted the procedure as well.

And of course the version of SQL Server you are using.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

>
It ended up being a crystal problem I think. I checked the box to
perform the query Asynchronously. Then following is what that does.
>
" Retrieving data from the database server can be divided into two
parts: executing the SQL statement and fetching the rowset from the
database server. Selecting this option allows Crystal Reports to
execute the SQL statement asynchronously, which means that instead of
waiting for the SQL statement to finish executing, the program checks
intermittently to see if the user wants to cancel the process. This
option is not selected by default ".
>
I am not sure why this would make a difference, or even if it was my
problem, but I am going to try to re-create the problem to see if this
actually fixed it.

That did not seem to fix it. It am pretty sure it is a Crystal problem
though. The SP runs fine in query analyzer.|||soulkitchen wrote:

Quote:

Originally Posted by

soulkitchen wrote:

Quote:

Originally Posted by

Erland Sommarskog wrote:

Quote:

Originally Posted by

soulkitchen (esoulkitchen@.gmail.com) writes:
After I change a fairly complex stored procedure and I run a report
against it, crystal hangs at "assesing database". I have verified the
database. When I run a trace on SQL is shows repeated cachemiss over
and over. I let it run for 30 minutes and nothing.
>
The first reaction is that this is a Crystal problem. Then again, if
SQL Server keeps generating CacheMiss over and over again, and nothing
comes in from the client, then that would be a bug in SQL Server.
>
Actually, I have seen that precise behaviour, but it was in an early
beta version of SQL 2005 and it occurred when ANSI_PADDING was off.
>
It sounds as if this is very difficult to troubleshoot on a distance. But
is it possible for you to attach a trace file, with all SP and TSQL
events enabled. Adding the Error events may be a good idea as well.
It could help if you posted the procedure as well.
>
And of course the version of SQL Server you are using.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

It ended up being a crystal problem I think. I checked the box to
perform the query Asynchronously. Then following is what that does.

" Retrieving data from the database server can be divided into two
parts: executing the SQL statement and fetching the rowset from the
database server. Selecting this option allows Crystal Reports to
execute the SQL statement asynchronously, which means that instead of
waiting for the SQL statement to finish executing, the program checks
intermittently to see if the user wants to cancel the process. This
option is not selected by default ".

I am not sure why this would make a difference, or even if it was my
problem, but I am going to try to re-create the problem to see if this
actually fixed it.

>
That did not seem to fix it. It am pretty sure it is a Crystal problem
though. The SP runs fine in query analyzer

I hope someone can answer this question... The problem above was
happening, and I did not do anything to fix it, but the next day the
problem went away. I can't imagine what could have happened over night
to fix my problem. I do transaction log backups on the hour, and a full
database backup once a week, but no last night.|||soulkitchen (esoulkitchen@.gmail.com) writes:

Quote:

Originally Posted by

I hope someone can answer this question... The problem above was
happening, and I did not do anything to fix it, but the next day the
problem went away. I can't imagine what could have happened over night
to fix my problem. I do transaction log backups on the hour, and a full
database backup once a week, but no last night.

To answer what happened in an office building far far away is kind of
difficult, but I will have to guess that you restarted something.

A possibility is also that there was a blocking issue. With the situation
gone all we can do is guess.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

changing legend text

I am using Crystal report XI. How to change the text in the legend if the report is generated at runtime? It shows the whole text as " Sum of SP_Get_Details.Target" whereas i only want "Target". The option is available as Edit Axis option but that is only present in preview mode whereas i am generating the report at runtime,so legend does not appear in preview mode. The chart is Bar Chart.Hi,
A simple way is to do this is add a Formula named E.g Totals (wht ever u want) then in side the formula use the Sum Function to do the required summation, if u r showing the chart group wise then put the formula
under that group, use proper summation

e.g Sum (<Summing field here>, <the group field here>)

then use the formula to the chart to display

even then the chart legend will show as

@.Total (@. sign to state that it is a formula)

any way this better than showing the Table name +field name to the user

If u find a solution to the @. sign let me know

FaFa

Changing font color dynamically in Crystal Reports

I have a windows app which uses the free version of Crystal Reports. The idea is that I should allow the users to choose their own font and colors (for example one might want to see the title green and arial). Do you happen to know how I can do that? I don't even know if it is possible given that there is practically no code behind the reports...

Thank you in advance. :)Add parameters to the report for font and font color. Right click the report field, select "format field", click the font tab and click the formula buttons next to each of the font attributes and add the code to read your parameters to make the select settings|||Can you please give me an example?|||Select {?FontColor} <--This is your parameter field
Case "Red": <-You should have a case stament for each color
crRed
Case "Black":
crBlack
Default:
DefaultAttribute

Here are the default color attributes.
crBlack
crMaroon
crGreen
crOlive
crNavy
crPurple
crTeal
crSilver
crRed
crLime
crYellow
crBlue
crFuchsia
crAqua
crWhite

Do the same for the font replacing the colors with the font names.|||But where do I write this code?|||Right click the report field, select "format field", click the font tab and click the formula buttons next to each of the font attributes and add the code to read your parameters to make the selected settings

As I originally stated above..|||try sample code as mentioned below. You can get access to any object in the report and from code, you can change the object property just like you do using visual studio. Sorry for not answering you stright. But this info will very helpful to you I recon.

Private Sub AdjustCarPicSize(ByVal iHeight As Integer, ByVal iWidth As Integer)
'For Each section As CrystalDecisions.CrystalReports.Engine.Section In reportDocument.ReportDefinition.Sections
' For Each reportObject As CrystalDecisions.CrystalReports.Engine.ReportObject In section.ReportObjects
' If reportObject.Kind = ReportObjectKind.SubreportObject Then
' Dim subReport As SubreportObject = CType(reportObject, SubreportObject)
' Dim subDocument As ReportDocument = subReport.OpenSubreport(subReport.SubreportName)
' If (subReport.SubreportName = "CarPic") Then
' For Each section1 As CrystalDecisions.CrystalReports.Engine.Section In subDocument.ReportDefinition.Sections
' For Each reportObject1 As CrystalDecisions.CrystalReports.Engine.ReportObject In section1.ReportObjects
' 'Response.Write(reportObject1.Name)
' If (reportObject1.Name = "carpicimage1") Then
' reportObject1.Height = iHeight
' reportObject1.Width = iWidth
' End If

' Next
' Next
' End If

' End If
' Next
'Next
Dim oCarPic As CrystalDecisions.CrystalReports.Engine.ReportObject = CType(reportDocument.ReportDefinition.Sections("DetailSection6").ReportObjects("Subreport1"), SubreportObject).OpenSubreport("CarPic").ReportDefinition.Sections("ReportHeaderSection2").ReportObjects("carpicimage1")

Changing Field/Column Name

Hello:
I'm working on a Crystal report for a client and, in order for it to work
without errors, I have to change the name of a field (column) in a table.
You see, the field name is two words called "[Print Count]". The space
between these two words, believe it or not, causes errors in my report.
So, I took out the space in this field so that the field name reads
"PrintCount". That fixed my report.
Well, unfortunately, doing this caused problems elsewhere as this name
change did not update (filter to) a SQL View that a user needs in order to
conduct his daily business activities. So, I had to go back and reverse what
I did and add that space back in.
I really did not think that changing the name of a field would cause issues.
But, like I said, changing the name in this table did not update the View
that is based on this table and other tables.
How do I change this field name to be without this space and have this
change update everywhere else effectively in the database including in this
View? I tried to change it subsequently in the View but could not figure out
how to do so.
Any insights would be appreciated!
Thanks!
childofthe1980s
> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
Correct. This is by design.

> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in this
> View?
There might exist some tools that keep control of all your objects and dependencies that can handle
this for you. There's nothing built into SQL Server or any of the tools that comes with SQL Server.

> I tried to change it subsequently in the View but could not figure out
> how to do so.
ALTER VIEW. Or, of you did SELECT * (horror), you could have used sp_refreshview.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:D2FF25C0-D581-4027-BA45-E2D5CC40D597@.microsoft.com...
> Hello:
> I'm working on a Crystal report for a client and, in order for it to work
> without errors, I have to change the name of a field (column) in a table.
> You see, the field name is two words called "[Print Count]". The space
> between these two words, believe it or not, causes errors in my report.
> So, I took out the space in this field so that the field name reads
> "PrintCount". That fixed my report.
> Well, unfortunately, doing this caused problems elsewhere as this name
> change did not update (filter to) a SQL View that a user needs in order to
> conduct his daily business activities. So, I had to go back and reverse what
> I did and add that space back in.
> I really did not think that changing the name of a field would cause issues.
> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in this
> View? I tried to change it subsequently in the View but could not figure out
> how to do so.
> Any insights would be appreciated!
> Thanks!
> childofthe1980s
|||Is the view referencing the column name directly? If so, you'd need to
change it there too. If you're
doing a SELECT * on the table within the view, then run sp_refreshview to
update the view metadata.
For columns that have spaces, put brackets around them. ie select [print
count] from mytable
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:D2FF25C0-D581-4027-BA45-E2D5CC40D597@.microsoft.com...
> Hello:
> I'm working on a Crystal report for a client and, in order for it to work
> without errors, I have to change the name of a field (column) in a table.
> You see, the field name is two words called "[Print Count]". The space
> between these two words, believe it or not, causes errors in my report.
> So, I took out the space in this field so that the field name reads
> "PrintCount". That fixed my report.
> Well, unfortunately, doing this caused problems elsewhere as this name
> change did not update (filter to) a SQL View that a user needs in order to
> conduct his daily business activities. So, I had to go back and reverse
what
> I did and add that space back in.
> I really did not think that changing the name of a field would cause
issues.
> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in
this
> View? I tried to change it subsequently in the View but could not figure
out
> how to do so.
> Any insights would be appreciated!
> Thanks!
> childofthe1980s

Changing Field/Column Name

Hello:
I'm working on a Crystal report for a client and, in order for it to work
without errors, I have to change the name of a field (column) in a table.
You see, the field name is two words called "[Print Count]". The space
between these two words, believe it or not, causes errors in my report.
So, I took out the space in this field so that the field name reads
"PrintCount". That fixed my report.
Well, unfortunately, doing this caused problems elsewhere as this name
change did not update (filter to) a SQL View that a user needs in order to
conduct his daily business activities. So, I had to go back and reverse what
I did and add that space back in.
I really did not think that changing the name of a field would cause issues.
But, like I said, changing the name in this table did not update the View
that is based on this table and other tables.
How do I change this field name to be without this space and have this
change update everywhere else effectively in the database including in this
View? I tried to change it subsequently in the View but could not figure out
how to do so.
Any insights would be appreciated!
Thanks!
childofthe1980s> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
Correct. This is by design.
> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in this
> View?
There might exist some tools that keep control of all your objects and dependencies that can handle
this for you. There's nothing built into SQL Server or any of the tools that comes with SQL Server.
> I tried to change it subsequently in the View but could not figure out
> how to do so.
ALTER VIEW. Or, of you did SELECT * (horror), you could have used sp_refreshview.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:D2FF25C0-D581-4027-BA45-E2D5CC40D597@.microsoft.com...
> Hello:
> I'm working on a Crystal report for a client and, in order for it to work
> without errors, I have to change the name of a field (column) in a table.
> You see, the field name is two words called "[Print Count]". The space
> between these two words, believe it or not, causes errors in my report.
> So, I took out the space in this field so that the field name reads
> "PrintCount". That fixed my report.
> Well, unfortunately, doing this caused problems elsewhere as this name
> change did not update (filter to) a SQL View that a user needs in order to
> conduct his daily business activities. So, I had to go back and reverse what
> I did and add that space back in.
> I really did not think that changing the name of a field would cause issues.
> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in this
> View? I tried to change it subsequently in the View but could not figure out
> how to do so.
> Any insights would be appreciated!
> Thanks!
> childofthe1980s|||Is the view referencing the column name directly? If so, you'd need to
change it there too. If you're
doing a SELECT * on the table within the view, then run sp_refreshview to
update the view metadata.
For columns that have spaces, put brackets around them. ie select [print
count] from mytable
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:D2FF25C0-D581-4027-BA45-E2D5CC40D597@.microsoft.com...
> Hello:
> I'm working on a Crystal report for a client and, in order for it to work
> without errors, I have to change the name of a field (column) in a table.
> You see, the field name is two words called "[Print Count]". The space
> between these two words, believe it or not, causes errors in my report.
> So, I took out the space in this field so that the field name reads
> "PrintCount". That fixed my report.
> Well, unfortunately, doing this caused problems elsewhere as this name
> change did not update (filter to) a SQL View that a user needs in order to
> conduct his daily business activities. So, I had to go back and reverse
what
> I did and add that space back in.
> I really did not think that changing the name of a field would cause
issues.
> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in
this
> View? I tried to change it subsequently in the View but could not figure
out
> how to do so.
> Any insights would be appreciated!
> Thanks!
> childofthe1980s

Changing Field/Column Name

Hello:
I'm working on a Crystal report for a client and, in order for it to work
without errors, I have to change the name of a field (column) in a table.
You see, the field name is two words called "[Print Count]". The space
between these two words, believe it or not, causes errors in my report.
So, I took out the space in this field so that the field name reads
"PrintCount". That fixed my report.
Well, unfortunately, doing this caused problems elsewhere as this name
change did not update (filter to) a SQL View that a user needs in order to
conduct his daily business activities. So, I had to go back and reverse wha
t
I did and add that space back in.
I really did not think that changing the name of a field would cause issues.
But, like I said, changing the name in this table did not update the View
that is based on this table and other tables.
How do I change this field name to be without this space and have this
change update everywhere else effectively in the database including in this
View? I tried to change it subsequently in the View but could not figure ou
t
how to do so.
Any insights would be appreciated!
Thanks!
childofthe1980s> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
Correct. This is by design.

> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in thi
s
> View?
There might exist some tools that keep control of all your objects and depen
dencies that can handle
this for you. There's nothing built into SQL Server or any of the tools that
comes with SQL Server.

> I tried to change it subsequently in the View but could not figure out
> how to do so.
ALTER VIEW. Or, of you did SELECT * (horror), you could have used sp_refresh
view.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in messa
ge
news:D2FF25C0-D581-4027-BA45-E2D5CC40D597@.microsoft.com...
> Hello:
> I'm working on a Crystal report for a client and, in order for it to work
> without errors, I have to change the name of a field (column) in a table.
> You see, the field name is two words called "[Print Count]". The spac
e
> between these two words, believe it or not, causes errors in my report.
> So, I took out the space in this field so that the field name reads
> "PrintCount". That fixed my report.
> Well, unfortunately, doing this caused problems elsewhere as this name
> change did not update (filter to) a SQL View that a user needs in order to
> conduct his daily business activities. So, I had to go back and reverse w
hat
> I did and add that space back in.
> I really did not think that changing the name of a field would cause issue
s.
> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in thi
s
> View? I tried to change it subsequently in the View but could not figure
out
> how to do so.
> Any insights would be appreciated!
> Thanks!
> childofthe1980s|||Is the view referencing the column name directly? If so, you'd need to
change it there too. If you're
doing a SELECT * on the table within the view, then run sp_refreshview to
update the view metadata.
For columns that have spaces, put brackets around them. ie select [pr
int
count] from mytable
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:D2FF25C0-D581-4027-BA45-E2D5CC40D597@.microsoft.com...
> Hello:
> I'm working on a Crystal report for a client and, in order for it to work
> without errors, I have to change the name of a field (column) in a table.
> You see, the field name is two words called "[Print Count]". The spac
e
> between these two words, believe it or not, causes errors in my report.
> So, I took out the space in this field so that the field name reads
> "PrintCount". That fixed my report.
> Well, unfortunately, doing this caused problems elsewhere as this name
> change did not update (filter to) a SQL View that a user needs in order to
> conduct his daily business activities. So, I had to go back and reverse
what
> I did and add that space back in.
> I really did not think that changing the name of a field would cause
issues.
> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in
this
> View? I tried to change it subsequently in the View but could not figure
out
> how to do so.
> Any insights would be appreciated!
> Thanks!
> childofthe1980s