converted binary fields in Crystal reports

lrollins
lrollins used Ask the Experts™
on
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???
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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
lrollinsIT Manager

Author

Commented:
It is set to grow but no luck.

How do I look at the ASCII?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Probably through SQL Manager.

You might also be able to copy it from SQL Server and paste into Word or WordPad
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

lrollinsIT Manager

Author

Commented:
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
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
You have an extra ) before the WHEN

mlmcc
lrollinsIT Manager

Author

Commented:
I had just figured that out.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial