Solved

SQL Server CONVERT displaying squares

Posted on 2010-09-14
12
384 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

730 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