We help IT Professionals succeed at work.

converted binary fields in Crystal reports

149 Views
Last Modified: 2018-09-17
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

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
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
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
lrollinsIT Manager

Author

Commented:
I had just figured that out.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.