Solved

SQL Server CONVERT displaying squares

Posted on 2010-09-14
12
359 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
 
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now