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 Date

Hello all, I have a question. I am fairly new to all of this, so bear with me if it is something simple (as I kinda hope it is).

In SSRS, I have a report that runs against a SQL '05 DB. The DB tables are created with a SSIS package gathering information from an AS/400 DB2 database.

The package and report run fine. One of the columns in the table is for a date (date of birth). The SSIS package gathers the column information and inserts it into SQL Server as a Decimal datatype (Decimal 6,0). The dates (decimals?) are now in the format 40207, as they were on the 400, where that specific date would be April 2, 2007. 120707 would be December 7, 2007 and so on. I would like to format the date to be mm/dd/yy or even m/dd/yy for the "single digit" months in the report for easy readability.

I have tried setting the format options in reporting services for formatting the field as a date, and even using the cdate conversion or FormatDateTime in an expression. I cannot seem to get the date to change. I either get an "# error" for the values of the fields or I get the same date that already was there (40207)?

It seems as if none of the date formatting options are working. Is this something I need to do on the package (set the mappings on the create table to create the columns as a Date, not Numeric or something similar?), or should it be easier than that by converting the output of the report to display mm/dd/yy with the "/"?

Any help is greatly appreciated.

Thanks

Expression-> Common Functions -> Date & Time -> Month || Day || Year|||

Although I am confused as to why you are storing a date value in a decimal field, this can be shown correctly via the report. Try this from your select query:

left(convert(varchar, <datefield>), len(convert(varchar, <datefield>)) - 4) + '/'
+ left(right(convert(varchar, <datefield>), 4), 2) + '/'
+ right(convert(varchar, <datefield>), 2)

Or...

reverse(stuff(stuff(reverse(<datefield>), 3, 0, '/'), 6, 0, '/'))

However, I would recommend that you update the SSIS package to handle the dates and put them in a datetime field.

Hope this helps.

Jarret

|||

Thanks for the replies.

Kenny - I don't have that option, although they are there individually. Are you meaning that I type that in along with something like (=Fields!DOB(etc))

If so, I may be doing it incorrectly, but I cannot get any of the "common" functions to work with my date/time stuff. I did however have good luck integrating the "globals" on my page (execution time, etc).

Jarrett, thanks for the reply, I haven't got to trying that part yet.

Thanks again everyone.

|||

The Month, Day, & Year functions would work if your field was already a date, but it's not, it's a decimal.

The functions that Kenny mentioned require that you pass in a date type. If you try to use them by passing a decimal type, you will get the following error:

Conversion from type 'Decimal' to type 'Date' is not valid.

Jarret

|||

Thanks. How should I put it in the SSIS package to convert? I tried using a data transformation, but got an error. The data is stored in the DB2 database as a decimal, I was just bringing it over with a simple SSIS package.

Thanks for your help.

|||

Within SSIS, in your source connection's SQL command, add a column with the statement:

reverse(stuff(stuff(reverse(convert(varchar(6), <datefield>)), 3, 0, '/'), 6, 0, '/')) as DecimalDate

Then, in your SQL database destination, map DecimalDate (instead of <datefield>) to your date type field in your SQL table.

Hope this helps.

Jarret

|||Thanks.|||

Did this fix your issue?

Jarret

|||

Yes, I can use the reverse method you described above and produce the results I want. However, I have not been successful in making the dates come over in the SSIS package. I must say though, I have been busy and haven't been completly dedicated to that recently.

Thanks for your help.

Changing Date

Hello all, I have a question. I am fairly new to all of this, so bear with me if it is something simple (as I kinda hope it is).

In SSRS, I have a report that runs against a SQL '05 DB. The DB tables are created with a SSIS package gathering information from an AS/400 DB2 database.

The package and report run fine. One of the columns in the table is for a date (date of birth). The SSIS package gathers the column information and inserts it into SQL Server as a Decimal datatype (Decimal 6,0). The dates (decimals?) are now in the format 40207, as they were on the 400, where that specific date would be April 2, 2007. 120707 would be December 7, 2007 and so on. I would like to format the date to be mm/dd/yy or even m/dd/yy for the "single digit" months in the report for easy readability.

I have tried setting the format options in reporting services for formatting the field as a date, and even using the cdate conversion or FormatDateTime in an expression. I cannot seem to get the date to change. I either get an "# error" for the values of the fields or I get the same date that already was there (40207)?

It seems as if none of the date formatting options are working. Is this something I need to do on the package (set the mappings on the create table to create the columns as a Date, not Numeric or something similar?), or should it be easier than that by converting the output of the report to display mm/dd/yy with the "/"?

Any help is greatly appreciated.

Thanks

Expression-> Common Functions -> Date & Time -> Month || Day || Year|||

Although I am confused as to why you are storing a date value in a decimal field, this can be shown correctly via the report. Try this from your select query:

left(convert(varchar, <datefield>), len(convert(varchar, <datefield>)) - 4) + '/'
+ left(right(convert(varchar, <datefield>), 4), 2) + '/'
+ right(convert(varchar, <datefield>), 2)

Or...

reverse(stuff(stuff(reverse(<datefield>), 3, 0, '/'), 6, 0, '/'))

However, I would recommend that you update the SSIS package to handle the dates and put them in a datetime field.

Hope this helps.

Jarret

|||

Thanks for the replies.

Kenny - I don't have that option, although they are there individually. Are you meaning that I type that in along with something like (=Fields!DOB(etc))

If so, I may be doing it incorrectly, but I cannot get any of the "common" functions to work with my date/time stuff. I did however have good luck integrating the "globals" on my page (execution time, etc).

Jarrett, thanks for the reply, I haven't got to trying that part yet.

Thanks again everyone.

|||

The Month, Day, & Year functions would work if your field was already a date, but it's not, it's a decimal.

The functions that Kenny mentioned require that you pass in a date type. If you try to use them by passing a decimal type, you will get the following error:

Conversion from type 'Decimal' to type 'Date' is not valid.

Jarret

|||

Thanks. How should I put it in the SSIS package to convert? I tried using a data transformation, but got an error. The data is stored in the DB2 database as a decimal, I was just bringing it over with a simple SSIS package.

Thanks for your help.

|||

Within SSIS, in your source connection's SQL command, add a column with the statement:

reverse(stuff(stuff(reverse(convert(varchar(6), <datefield>)), 3, 0, '/'), 6, 0, '/')) as DecimalDate

Then, in your SQL database destination, map DecimalDate (instead of <datefield>) to your date type field in your SQL table.

Hope this helps.

Jarret

|||Thanks.|||

Did this fix your issue?

Jarret

|||

Yes, I can use the reverse method you described above and produce the results I want. However, I have not been successful in making the dates come over in the SSIS package. I must say though, I have been busy and haven't been completly dedicated to that recently.

Thanks for your help.