SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Boxes symbol in query output


Boxes symbol in query output

Author
Message
gksharmaajmer
gksharmaajmer
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 112
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
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40964 Visits: 19815
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
gksharmaajmer
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 112
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
gksharmaajmer
gksharmaajmer
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 112
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
sqldriver
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2128 Visits: 2536
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
gksharmaajmer
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 112
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search