Click here to monitor SSC
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 Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 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
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8468 Visits: 18080
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 Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 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 Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 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
Mr or Mrs. 500
Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)

Group: General Forum Members
Points: 562 Visits: 2492
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 Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 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