Solved

SQL Server CONVERT displaying squares

Posted on 2010-09-14
12
371 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
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

831 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