Collation problem

  • Hi,

    All setting are same in sql server 2005 and is align with sql server 2000 database where report is running fine

  • panwar.jt (8/24/2009)


    My complete analysis is

    1-- This report is running in sql server 2000

    2-- I restore a copy of databse to Sql Sever 2005

    3-- Report stops due to different collation between my databse and server

    4-- Change the collation of my database to alien with server collation

    5-- Then report start malfunctioning(just displaying one charecter of char and varchar type columns numeric value is displaying correct)

    6-- Then I put few blank space as mentioned above or comment that select stament report start working fine.

    In this case I never touch report, All things I did in database so I think that there is something in databse.

    Is there any setting in database where I can set default size of blank as 10 byte???????

    Humm...

    after step 4, did you RECOMPILE/refresh all you SP, UDF, views, etc ???

    after step 4 did you execute "VERIFY DATABASE" into Crystal report ?

    On step 6 you put few blank and now the report work fine. Now, try to eliminate those few blalnk to verify if your report is stil working ?

    Calico

  • calico (8/26/2009)


    panwar.jt (8/24/2009)


    My complete analysis is

    1-- This report is running in sql server 2000

    2-- I restore a copy of databse to Sql Sever 2005

    3-- Report stops due to different collation between my databse and server

    4-- Change the collation of my database to alien with server collation

    5-- Then report start malfunctioning(just displaying one charecter of char and varchar type columns numeric value is displaying correct)

    6-- Then I put few blank space as mentioned above or comment that select stament report start working fine.

    In this case I never touch report, All things I did in database so I think that there is something in databse.

    Is there any setting in database where I can set default size of blank as 10 byte???????

    Humm...

    after step 4, did you RECOMPILE/refresh all you SP, UDF, views, etc ???

    after step 4 did you execute "VERIFY DATABASE" into Crystal report ?

    On step 6 you put few blank and now the report work fine. Now, try to eliminate those few blalnk to verify if your report is stil working ? http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gif

    Calico

    I'm curious how you know he's running Crystal Reports as the reporting engine... the poster, so far as I can see, hasn't confirmed this as of yet.

    Random Technical Stuff[/url]

  • Hi,

    After Step 4 I have alter my proc many time and also try all options in report " VArify Database, Set location, etc"

    Just now I drop proc and create again but no result output is same.

    Can u confirm me one things look below query

    Select '' as ID, '' as Name

    Is there any collation for Column ID and Name in above select statement, If yes how I can check that.

    In above select statement size of ID and name is varchar(1)

    Can we set sefault size for such clolumns(ID,Name) as varchar(10)

    Thanks

    KD

  • panwar.jt (8/26/2009)


    Hi,

    After Step 4 I have alter my proc many time and also try all options in report " VArify Database, Set location, etc"

    Just now I drop proc and create again but no result output is same.

    Can u confirm me one things look below query

    Select '' as ID, '' as Name

    Is there any collation for Column ID and Name in above select statement, If yes how I can check that.

    In above select statement size of ID and name is varchar(1)

    Can we set sefault size for such clolumns(ID,Name) as varchar(10)

    Thanks

    KD

    I believe that is should use the default database collation.

    Random Technical Stuff[/url]

  • Yes It will be default I checked.

    But can we set default size of columns

  • Question: Is there any setting in database where I can set

    default size of blank as 10 byte???????

    Answer : check the behavior of ANSI_PADDING ON with

    varchar(10)

    Question : Select '' as ID, '' as Name : Is there any collation

    for Column ID and Name in above select

    statement, If yes how I can check that.

    Answer : It depend where the execution of the select take

    place. We must see the complete script. It can be

    collation of tempdb or collation of the database

    May be something corrupted with your report. May be the 'image' of the SP is damaged in the .rpt file. ?

    Did you ever 'refresh' the image of the SP contained in a .rpt file ? This is a delicate task but I ever did that when the SP of a .rpt was renamed.

    Callico

  • panwar.jt (8/21/2009)


    This is My Proc It execute correctly and return some data but report just pick one charcter of each char and varchar datatype

    if Proc It execute correctly and return some data but report just pick one charcter of each char, why don't you try to ENLARGE (horizontally and verticaly) those problematics fields in you report just to see if what happen !

    Calico

  • panwar.jt (8/19/2009)


    Dear Frnds

    I change collation in my database.

    My application is developed in .net framework 1.1 and using .net crystal report

    Now few report is not running

    problem is that in only one charector is printing in report.

    like is procedure return 'Hello' but report print only 'H'

    Please help

    Thanks

    KD

    Can you confirm to me that your application has been developed in .net framework 1.1 AND with SQL 2000

    Can you confirm to me that your report work fine with SQL 2000 and doesn't work with SQL 2005.

    If you want to use SQL 2005 with your application/report, don't you have to convert your application to .net framework 2.0 before ???

    Calico

  • Hi Calico,

    May be something corrupted with your report. May be the 'image' of the SP is damaged in the .rpt file.

    I did not find anything like that, I like to tell u that report run fine if I just put some Blank space in that select statement

    And Size of report field is OK no issue of size.

    I have two unconventional solution but its a tedious task to apply this in all proc

    1-Just amazing that report run fine if put some blank space in a select statement and that select statement never execute because that statement is in a IF condition that never meets true.

    2- If I make comment this complete select statement because there is no use of this statement until my database is not empty completly.

    Ques-How much blank space is required in this select statement

    Ans- As per resultset columns size

    If you want to use SQL 2005 with your application/report, don't you have to convert your application to .net framework 2.0 before

    Yes I have done it Now both are In 2005, But report problem is still persisting.

  • For me, I think the problem is with you application/report.

    Good luck with your problem.

    Calico

  • Did you try this TEST ?

    Create a new report (with the SP without spaces added) and ONLY display the problematic fields in the new report. Don't do anything else than drag the problematics fields onto your report and execute the report.

    Please DON'T copy/paste anything from the original report.

    The new report MUST be totally new.

    Does only first letter appear on the new report again ?

    Calico

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply