[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • 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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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