Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Boxes symbol in query output Expand / Collapse
Author
Message
Posted Saturday, July 13, 2013 1:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 01, 2014 2:28 AM
Points: 47, Visits: 110
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
Post #1473284
Posted Saturday, July 13, 2013 12:03 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 2,763, Visits: 5,915
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1473305
Posted Sunday, July 14, 2013 12:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 01, 2014 2:28 AM
Points: 47, Visits: 110
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_POSITION COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGHT IS_NULLABLE
12 accode char 8 YES
9 active char 1 YES
6 address1 varchar 40 YES
7 address2 varchar 40 YES
10 degree char 40 YES
16 DesignationCode char 5 YES
1 doctorcd char 5 No
5 doctortype char 2 YES
14 doctype char 1 YES
2 firstname varchar 15 YES
18 honapplicable char 1 YES
4 lastname varchar 15 YES
15 medicalregno varchar 15 YES
3 middlename varchar 15 YES
13 mobileno char 15 YES
8 phone varchar 30 YES
19 tdstypecode char 3 YES
11 title varchar 4 YES
17 WeekApplicable char 1 YES

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

Post #1473349
Posted Sunday, July 14, 2013 1:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 01, 2014 2:28 AM
Points: 47, Visits: 110
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
Post #1473351
Posted Monday, July 15, 2013 9:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 9:32 AM
Points: 299, Visits: 1,195
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.
Post #1473727
Posted Monday, July 15, 2013 9:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 01, 2014 2:28 AM
Points: 47, Visits: 110
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
Post #1473734
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse