Solved

SQL Server CONVERT displaying squares

Posted on 2010-09-14
12
392 Views
Last Modified: 2013-11-25
I am doing a convert on a BLOB, on one machine it displays the text fine, on another, it is filling the CONVERTed field with squares. SQL-CONVERT-COMMAND.doc
0
Comment
Question by:ourguru
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 33673003
try converting to nvarchar rather than varchar alone
 
0
 

Author Comment

by:ourguru
ID: 33673069
ralmada,

Made it worse, now field begins with squares and only has a letter here and there with squares all throughout.

0
 
LVL 41

Expert Comment

by:ralmada
ID: 33673083
check the collation in both databases, make sure you are using the same collation
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 41

Expert Comment

by:ralmada
ID: 33673129
also try using nvarchar(max) rather than nvarchar(8000)
0
 

Author Comment

by:ourguru
ID: 33673210
No go on the nvarchar(max), same output.

I am accessing the same database, just from different machines.  I am using SQL BIDS, not sure if that helps.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33673344
Ok, that sounds like the fonts installed on the machines. A square/rectangle is the default glyph displayed when the font doesn't
have access to the right characters.
0
 

Author Comment

by:ourguru
ID: 33673357
I believe it is either filling with CR or LF or both.
when i export to excel, the field is full of extra CRs
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33673507
well, you can get rid of the CR LF by doing
replace(replace(convert(varchar(8000), convert(binary(8000), yourcol)), char(13), ''), char(10), '')
0
 

Author Comment

by:ourguru
ID: 33673545
No help.  Could it be filling to the (8000)?
0
 

Author Comment

by:ourguru
ID: 33673570
Ok,  i just changed the convert(binary(8000) to (10) just to see and that works.  Is there a way to get the length first so that it doesn't fill to 8000?
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 33673587
ok, then use varbinary instead of binary
converT(varbinary(8000),yourcol)
0
 

Author Comment

by:ourguru
ID: 33674344
ralmada,

That did it!!!  Thank you very much!!!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question