October 10, 2011 at 1:00 pm
I have a database with some barcode values stored. Some of the barcode values contain what appear to be control characters. The issue is that on one server (win2008 server, sql 2005 9.00.5057) a query of the table does not show the control chars. When a backup of that database is restored on another server (win2k3, sql 2005 9.00.3042, or WinXP with same sql 2005 9.00.3042) the control chars do appear when the same query is run.
I don't know if this is an issue with SQL or Windows. Also, if the data is exported from SQL on the Win2008 server and the resulting text file is loaded into Notepad, no control char. But when opened with Edit.com, the control chars appear. This makes me suspect windows. Anyone seen anything similar?
October 10, 2011 at 1:20 pm
What are you viewing the query results with?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 10, 2011 at 1:40 pm
Sorry, viewing in Management Studio
October 10, 2011 at 1:42 pm
OK so what do you mean by control characters? What are you seeing? Are you viewing in table view, text view?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 10, 2011 at 2:04 pm
Text view. Here are some screen shots which are pretty legible in paint or full screen in windows picture viewer. Results are from the same database. You'll see the funky rectangular object within the SER_NO contents when viewed on Win2k3 server but not in the Win2k8 server.
I know that they result from serial number barcodes that contain parenthesis and this is how a particular model of scanner is storing and forwarding the data to SQL. I'd like to have the Win2k8 server show the garbage character if possible.
October 10, 2011 at 2:11 pm
That is table view. Is the database version the same on both? The "garbage" character is still there just not visible in SSMS. You could add a replace to your query if all you are looking for is some way to view those characters in SSMS (assuming you know what character(s) they are).
select replace(ser_no, char(?), '|') from ItemMaster
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 10, 2011 at 2:20 pm
Correct, the garbage char is is not visible on SSMS. I was wondering why that was the case on the Win2k8 server but not when viewed on the Win2k3 server. Same database. Backed up on Win2k8 and restored to Win2k3.
I'm in the process of scrubbing the data but was thinking there was perhaps a configuration setting or collation setting or ??? that was responsible for the difference.
October 10, 2011 at 2:28 pm
Are the versions of sql the same? Or is it the same db server with two versions of sql client? The version of windows shouldn't come into play at all here. It is all about how SSMS (the GUI) is presenting data. I don't know of any setting to hide/show characters from the extended character set. I don't think collation would have anything to do with either but that is easy enough to check. The answer there will somewhat depend on your answer to what we are looking at.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 10, 2011 at 2:46 pm
The sql versions are slightly different; Win2k8 is running Microsoft SQL Server 2005 - 9.00.5057.00 (Intel X86) and the Win2k3 Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86). I'm running SSMS on each server directly.
Speaking of how the GUI is presenting the data, the database is part of a retail POS package with the GUI written in Delphi. Application clients pointing to the Win2k8 machine do not see the garbage but that would make sense as it is sql supplying the data and on the Win2k8 server, sql is choosing to drop the garbage or doesn't know what to do with it. Not sure.
October 10, 2011 at 2:56 pm
Well it does seem a little strange that the two different version of sql are handling these characters differently. So your old server is on SP2 and the newer one is on SP4 if I am correct on version numbers? Given that your two servers are not running the same version it is not 100% surprising that some stuff like this is a little different.
Is this one table the only place you are seeing this? Do you know what character(s) are causing this? Can you simple use a replace to show/hide the characters you want?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 10, 2011 at 3:08 pm
I can deal with the data that is in the database, now that I know it is there. Finding it was the hard part because sql isn't showing it. The next step is to deal with the barcode scanners and prevent more bad data from getting in. I was hoping I could make a change on this Win2k8 machine (sql or windows) so that if more of this garbage gets in, I'll at least be able to see it more easily.
I will try installing sp4 on this Win2k3 server and see if it then behaves as the Win2k8 machine.
October 10, 2011 at 3:11 pm
Maybe an insert trigger to kill the garbage before it gets in? Those non-viewable characters can be a real pain in the ... if you don't know they are there. Let me know if installing the latest SP fixes your issue. There were probably be others at some point with the same issue and if you can update the thread they will benefit from knowing what corrected the issue for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 10, 2011 at 3:16 pm
I can deal with the data that is in the database, now that I know it is there. Finding it was the hard part because sql isn't showing it. The next step is to deal with the barcode scanners and prevent more bad data from getting in. I was hoping I could make a change on this Win2k8 machine (sql or windows) so that if more of this garbage gets in, I'll at least be able to see it more easily.
I will try installing sp4 on this Win2k3 server and see if it then behaves as the Win2k8 machine.
October 10, 2011 at 3:17 pm
Doh! Double post. Sorry. I'll let you know how it goes. Thanks for your help.
October 10, 2011 at 3:19 pm
You are welcome although I am not sure how much help I actually provided. Maybe just acted as a sounding board for you. At any rate, glad you at least have some direction to go.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply