Boxes symbol in query output

Question

Boxes symbol in query output

gksharmaajmer

Ten Centuries

Points: 1120
More actions
July 13, 2013 at 1:00 am

#100156

Hi,
Here is my friend's sql server version info :
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
He runs a query which is something like this :
insert into #tmp
select deptmst.deptname As 'Department',
isnull(doctormst.title,' ')+' '+isnull(rtrim(doctormst.firstname),' ')+' '+
isnull(rtrim(doctormst.middlename),' ')+'
'+isnull(rtrim(doctormst.lastname),' ') As 'Name of Doctor',
...
...
from
inner join ...
where
....
Query is running fine and giving correct result. But when he export the query output in Microsoft Office Excel 2003 (11.5612.5606) Microsoft Office Professional Edition 2003, he gets some box type symbol in the Name of Doctor column. The weird thing is sometime there is no such symbol on the same excel, sql server and OS machine, but some time again they appears something like []this []one.
Kindly help him, how to overcome this issue.
Thanks and Regards
Girish Sharma

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Can you post the table definition? I'm afraid that there are some Unicode characters in the data that Excel won't identify.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

gksharmaajmer Ten Centuries Points: 1120 More actions · Answer 2

Thank you for your reply. Here is table defination :

SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH

, IS_NULLABLE

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'doctormst'

DOCTORMST TABLE STRUCTURE

ORDINAL_POSITIONCOLUMN_NAMEDATA_TYPECHARACTER_MAXIMUM_LENGHTIS_NULLABLE

12accodechar8YES

9activechar1YES

6address1varchar40YES

7address2varchar40YES

10degreechar40YES

16DesignationCodechar5YES

1doctorcdchar5No

5doctortypechar2YES

14doctypechar1YES

2firstnamevarchar15YES

18honapplicablechar1YES

4lastnamevarchar15YES

15medicalregnovarchar15YES

3middlenamevarchar15YES

13mobilenochar15YES

8phonevarchar30YES

19tdstypecodechar3YES

11titlevarchar4YES

17WeekApplicablechar1YES

For Unicode :

When he run below queries, there were no output :

select

firstname,lastname,middlename

from

doctormst

where

firstname != cast(firstname as varchar(1000))

select

firstname,lastname,middlename

from

doctormst

where

lastname != cast(lastname as varchar(1000))

select

firstname,lastname,middlename

from

doctormst

where

middlename != cast(middlename as varchar(1000))

So, it means, can we assume that there are no unicode character in these referenced columns please ?

Regards

Girish Sharma

gksharmaajmer Ten Centuries Points: 1120 More actions · Answer 3

In addition to above, when I sees the same excel file in MS Office 2007, Windows 7 Home Premium 64 bit machine, no box character are there, but the same file when I open in MS Office 2003 Windows XP, it shows. It means there is no issue with sql server itself, problem is somewhere else either in Excel and/or OS.

Regards

Girish Sharma

sqldriver SSChampion Points: 10440 More actions · Answer 4

I had a similar situation here:

http://www.sqlservercentral.com/Forums/Topic1441088-149-1.aspx

It turned out that Unicode characters don't store properly in VARCHAR columns, and I had to change them to NVARCHAR.

Beyond that, I can tell you that you're going to have display issues in SQL 2005 and earlier. When I query with a newer version of SSMS, they display properly. I ran into this again more recently with Korean characters and it was the same thing. Querying with 2k5 showed the rectangles, querying with 2k12 showed the correct characters.

gksharmaajmer Ten Centuries Points: 1120 More actions · Answer 5

Problem solved, but with a very uncommon / unknown way. I told him to change the query text something like this :

insert into #tmp

select deptmst.deptname As 'Department',

isnull(doctormst.title,' ')+' '+isnull(rtrim(doctormst.firstname),' ')+' '+isnull(rtrim(doctormst.middlename),' ')+''+isnull(rtrim(doctormst.lastname),' ') As 'Name of Doctor',

...

from

inner join ...

where

....

i.e. I just wrote 'Name of Doctor' column in a single line and those box characters (actually they are neither unicode nor anything else, but just Carriage Return and Line Feed characters which were coming by itself query text) gone. Even though I was not sure, that it is the solution/answer of the problem, I just tried and bingo it worked as what I wanted.

I am just sharing the solution for next readers of the question.

Regards

Girish Sharma