Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 399
  • Last Modified:

SQL Server CONVERT displaying squares

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
ourguru
Asked:
ourguru
  • 6
  • 6
1 Solution
 
ralmadaCommented:
try converting to nvarchar rather than varchar alone
 
0
 
ourguruAuthor Commented:
ralmada,

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

0
 
ralmadaCommented:
check the collation in both databases, make sure you are using the same collation
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
ralmadaCommented:
also try using nvarchar(max) rather than nvarchar(8000)
0
 
ourguruAuthor Commented:
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
 
ralmadaCommented:
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
 
ourguruAuthor Commented:
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
 
ralmadaCommented:
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
 
ourguruAuthor Commented:
No help.  Could it be filling to the (8000)?
0
 
ourguruAuthor Commented:
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
 
ralmadaCommented:
ok, then use varbinary instead of binary
converT(varbinary(8000),yourcol)
0
 
ourguruAuthor Commented:
ralmada,

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

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now