Avatar of lrollins
lrollins
Flag for United States of America asked on

converted binary fields in Crystal reports

I am running sql server 2016 on a new server.  I have views that convert binary blob fields to text that I can use in Crystal Reports.  For some reason, when I bring them into my crystal report, it only show the very first character of the converted field.  If I look at it in SQL Server, it show the entire text in the converted field.  Anyone have any ideas why it's doing this and how to fix it???
Crystal ReportsMicrosoft SQL Server

Avatar of undefined
Last Comment
lrollins

8/22/2022 - Mon
Mike McCracken

LOok at the ASCII for the text.  I suspect there is a character that Crystal is interpreting as an end of text.

ONe other idea would be to format the report object so the field can grow.  It may have a Line Feed or Carriage Return
Right click the object in the report
Click FORMAT OBJECT
CLick the COMMON TAB
Click the box by CAN GROW

mlmcc
lrollins

ASKER
It is set to grow but no luck.

How do I look at the ASCII?
Mike McCracken

Probably through SQL Manager.

You might also be able to copy it from SQL Server and paste into Word or WordPad
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
lrollins

ASKER
Someone gave me the following and said it should work but I get an invalid expression code.

CASE (UNICODE(CAST(CAST(NOTE AS VARBINARY(4000)) AS NVARCHAR(4000))) - ASCII(LEFT(CAST(NOTE AS VARBINARY(4000)), 1)))
WHEN 0 THEN CAST(CAST(NOTE AS VARBINARY(8000)) AS NVARCHAR(4000))
ELSE CAST(CAST(NOTE AS VARBINARY(8000)) AS VARCHAR(8000)) END AS NOTES

NOTE is the bit field
ASKER CERTIFIED SOLUTION
Mike McCracken

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
lrollins

ASKER
I had just figured that out.