December 5, 2005 at 2:19 pm
Hi folks, I've run into any issue where the results I'm returning in Query Analyzer are different depending on whether they are returned as text or grid. This is on SQL 2000 SP4.
I have some junk values in my fields and I think they are the issue(this is the value ' '). In grid form the results return correctly(sometimes, more below). In text, all the values after ' ' are not returned. So, if I have a table with 30 fields and I'm doing a select * from table and field 12 has ' ' then I won't see fields 13 and on.
In grid form sometimes that value causes all the values to "shift" back one field. For exampe, if field 20 has ' ' then the grid display will show the value for field 22 as field 21 instead.
Very odd. I didn't know the data within the table could affect how query Analyzer displays the results. We are worried about this causing issues for applications...though we haven't heard anything yet. I should mention that outputting the results to a file works just fine...the data is not displayed incorrectly. I apologize if this issue was addressed before. I did not see it on the forum.
Thanks for your time.
December 5, 2005 at 2:22 pm
What does this value - mean? Why do u even have it in there?
What is the data type of the column that stores the value?
December 5, 2005 at 2:25 pm
Good question. Its junk from a main frame. It was described as a hex character to me. We've had the issue in several columns -all either char or varchar.
December 5, 2005 at 2:33 pm
-What is the data in Mainframe for that character? Is that datatype compatible with SQL Server CHAR or VARCHAR datatypes?
-What is the length of the value inside the field which has the garbage value? Do a SELECT LEN(column) from Table Where Column LIKE '%garbage%'
-Try changing the datatype to text or try using Nvarchar (unicode data type) instead of Non-Unicode datatypes.
December 5, 2005 at 2:55 pm
-What is the data in Mainframe for that character? Is that datatype compatible with SQL Server CHAR or VARCHAR datatypes?
Don't know -have asked several times. New job!
-What is the length of the value inside the field which has the garbage value? Do a SELECT LEN(column) from Table Where Column LIKE '%garbage%'
Its shows up accurately...I guess. If there is one junk value its '1'. Two, '2'
-Try changing the datatype to text or try using Nvarchar (unicode data type) instead of Non-Unicode datatypes.
No luck with that, same result.
So, have you seen instances where the data stored affects the display in Query Analyzer?
December 5, 2005 at 3:04 pm
We have that problem Query Analyzer quits displaying after that value. Get the results to MSWord or EXcel from the database directly you will be able to see that.
Character is very well taken to SQL Server. That is why you are able to see that as 'value value'
Since that character is meaningless we removed it from the data. You may want to do the same unless it is meant something.
I guess if you make results to file and open that file in notepad the problem will be there still (Don't remember this exactly). If you open it in word pad you will see the data.
Regards,
gova
December 5, 2005 at 3:23 pm
Good to know its been seen before -we haven't had any issues with applications so Its a bit of a non-issue. When we output the results to text it looks fine.
Thank you.
December 6, 2005 at 10:42 am
I would not write this off as a non-issue. Just because it does not impact/affect your application at present, what is to say that it will not in the future. You do have a problem with your data coming from the mainframe <period> If there are any non-printable characters, as you now know, you will see the square box. At present you are relying on the 'If I ignore it it will go away philosophy'. If this were me I'd have the mainframe folks save a file that is to be downloaded to you and then you should save the same file on the Windows side prior to loading it into SQL Server. Then examine both in a standard text editor, then a hex editor (there are lots of free and demo versions available). Depending on the mainframe version there may be access to spf/edit (it does text and hex !).
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
December 6, 2005 at 12:57 pm
I think you make some good points. There is the context of the situation though. I can not make changes to the data on the mainFrame(no access). Apparently the application has been handling the non-printable characters for quite some time(5 years). My original post was to see if the values within the SQL table would affect the view from query analyzer. Looks like it does.
Thanks for your time.
December 6, 2005 at 6:33 pm
You may not be able to control the mainframe, but you control the target side. You can write an instead-of trigger instead of insert (trigger action happens before the insert) for your table(s) in question and filter out unacceptable/unknown chars, eg allow ascii 48 - 57, 64 - 126, etc to pass.
Win
December 6, 2005 at 6:59 pm
Didn't think of that...good idea. The issue I run into now is performance. There are about 50 columns on the table I believe and any one of them can get the "junk" characters. I'll have to give it a go tomorrow. Thanks.
December 6, 2005 at 7:20 pm
Applications quite often treat multi-row inserts as individual transaction. I split my triggers into 2 sections for @@rowcount=1 and @@rowcount >1. In the latter I use a cursor which is more time consuming, but may not be accessed frequently.
Win
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply