Hex value change from o to �

Hi,

I have load Cobol flat file into sql table by bulk insert.
There a packed comp-3 filed. I am trying to convert to Hex decimal.

The flat file was like 'o'  o = 9c in hex.
after bulk insert into sql table the value became '�'  the last char � = 
3a in hex.
any ideas why the last char changed after bulk insert?

Thnaks, 


0
Mecn
12/24/2009 3:43:21 PM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
850 Views

Similar Articles

[PageSpeed] 1

Mecn (mecn@yahoo.com) writes:
> I have load Cobol flat file into sql table by bulk insert.
> There a packed comp-3 filed. I am trying to convert to Hex decimal.
> 
> The flat file was like 'o'  o = 9c in hex.
> after bulk insert into sql table the value became '�'  the last char � = 
> 3a in hex.
> any ideas why the last char changed after bulk insert?
 
You loaded the value into a character column?

The reason is that SQL Server thinks that you have a file in the OEM
charset, that is CP437 or CP850 of old DOS.

Use CHARSET=RAW to get the file inserted without conversions.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
12/24/2009 4:06:14 PM
It works now,
thnaks a lot!!!!



"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9CEBAE05C1376Yazorman@127.0.0.1...
> Mecn (mecn@yahoo.com) writes:
>> I have load Cobol flat file into sql table by bulk insert.
>> There a packed comp-3 filed. I am trying to convert to Hex decimal.
>>
>> The flat file was like 'o'  o = 9c in hex.
>> after bulk insert into sql table the value became '�'  the last char � 
>> =
>> 3a in hex.
>> any ideas why the last char changed after bulk insert?
>
> You loaded the value into a character column?
>
> The reason is that SQL Server thinks that you have a file in the OEM
> charset, that is CP437 or CP850 of old DOS.
>
> Use CHARSET=RAW to get the file inserted without conversions.
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 


0
Mecn
12/24/2009 6:32:08 PM
Reply:

Similar Artilces:

Duplicate value
I have a form with a subform on it. The subform has 5 fields, set up as datasheet view: ID (autonumber) Start End Qty (sum of end-start) Line Type (combo box) I've been asked to see if the 'End' can automatically be entered at the 'Start' on the next line; however, there is upon occassion the need to change the start number. For example: ID Start End Qty Line 1 0 215 215 (whatever) 1 215 300 85 (whatever) 1 325 ...

Lookup multiple values
On one worksheet, I have a "table" with a header column & header row, along with data. ON another worksheet, I would like all of the data in the first worksheet to be in a list; the applicable column header would be placed into one column, the row header into another one. Example: Table on first worksheet: A X P 1 2100 2101 2102 2 2103 2104 2105 3 2106 2107 2108 Desired results on 2nd worksheet: A B C 2100 1 A 2101 1 X 2102 1 P 2103 2 A 2104 2 X 2105 3 P ...

result of Function to cut last 2 values out
i have used =--LEFT(L3, LEN(L3)-2). This cut off last 2 digits o value. i.e. 12345678912 became 123456789. But 00123456789 becam 1234567. How do I make it come out 001234567??? thanks -- Message posted from http://www.ExcelForum.com Hi =INT(L3) or =INT(L3*1) when in L3 is numeric string -- Arvi Laanemets (When sending e-mail, use address arvil<At>tarkon.ee) "ifew552 >" <<ifew552.179bt5@excelforum-nospam.com> wrote in message news:ifew552.179bt5@excelforum-nospam.com... > i have used =--LEFT(L3, LEN(L3)-2). This cut off last 2 digits of > valu...

Limit Values
Hello, I have the following issue which I am not able to solve. I want to limit the value possibilities of a cell to #,0 and #,5 For example: 8,5 or 7,0 but not 6,2 and so on. I tried to do it with validation but it doesn't work. Hope anyone can solve this out for me. Many thanks. Noepie assumed the cell A1 need to limit the number to ( 0,5,7,8) Go to data | validaton | allow:custom | formula : =3DOR (A1=3D0,A1=3D5,A1=3D7,A1=3D8) | ok On Nov 30, 2:33=A0pm, Noepie <Noe...@discussions.microsoft.com> wrote: > Hello, > > I have the following issue which I am not able t...

A record with these values already exists.
Every time I try to do something with a quote, opportunity, or invoice, I get this error: A record with these values already exists. A duplicate record cannot be created. Select one or more unique values and try again. I have no snap-ins. Any thoughts? Same problems here, found a solution? "Bebandit" wrote: > Every time I try to do something with a quote, opportunity, or invoice, I get > this error: > A record with these values already exists. A duplicate record cannot be > created. Select one or more unique values and try again. > > I have no snap-ins. ...

removing duplicate values from a column
It sounds so simple that I must be overlooking the obvious answer, but I can't seem to find a way to remove duplicate values from a column. The case is simple: I have 600+ emailaddresses in column A, sorted alphabetically and there's a bunch of duplicate addresses in there (some of 'em occur up to 7 times!!). Instead of manually running through the whole list, removing the duplicates, there MUST be some soft of filter/function in excel to do this for me... Could anyone PLEASE help me with this!?!? Cheers! -- NOTE: remove the spamtrap from the emailaddress Jops, Select the em...

3 mm Long Arm Metric Allen Wrench- (023-58056) Category: Hex Keys
Price:$69.00 Image: http://thediscountdatabase.info/image.php?id=B001HWDNJ0 Best deal: http://thediscountdatabase.info/index.php?id=B001HWDNJ0 Item #: 023-58056. All hex keys are precision formed to exact tolerances for precise fitResists rotational distortion Measuring System = MetricTip Size = 3 mmTip Type = HexMaterial = Alloy SteelFinish = Black OxideHandle Type = L-Handle Meets Fed. Std. GGG-K-275 AMED 1|Meets ASME/ANSI B18.3 Customers also search for: Discount 3 mm Long Arm Metric Allen Wrench-, Buy 3 mm Long Arm Metric Allen Wrench-, Wholesale 3 mm Long Arm Metric Allen Wrench-, ...

Need a query to return a value if out of range
I have the table below. I need to create a query that will return a score based on a passed Age and a passed LimitValue. For example: If Male= True, Age = 33 and LimitValue = 70 I need to return a score of 4. Because Age <= AgeHighLimit and Male = True when the LimitValue >= HighLlimit (60) in the table the highest score should be returned. Likewise, if Male= True, Age = 33 but LimitValue = 24 I need to return a score of 1. Because for that age and gender when the LimitValue <= HighLlimit (25) in the table the lowest score should be returned. I can write a query that ...

use Vlookup for sum all found value?
Hi, I am new here and have a problem hope I can get help in here! I have two worksheet "W1" and "W2" In W1 worksheet, it has a column "emp_pin" which is unique. This value is used in the Vlookup function to find a numeric value "Paid" in W2 worksheet. If "W1" and "W2" are one to one relationship, it will work fine. However I have encountered the one to many relationship, it means one row in "W1" has many rows in "W2". I just wonder if I can sum up all the found value "paid" in "W2". should...

Creating an EXCEL COUNTIF formula for a range of values
I need to create a COUNTIF formula that counts the cells where the dollar value in the cells is greater than $8 but less then $10? Is this a multiple criteria situation? Pat one way: =SUMPRODUCT(--(A2:A43>8)*--(A2:A43<10)) Regards Trevor "Pat Walsh" <Pat Walsh@discussions.microsoft.com> wrote in message news:47369EFC-E57A-441A-86D1-7C70DBF9FF2F@microsoft.com... >I need to create a COUNTIF formula that counts the cells where the dollar > value in the cells is greater than $8 but less then $10? Is this a > multiple > criteria situation? What doe...

Look up values and place in spreadsheet
I have an excel workbook with two sheets. On Sheet2, in column A is employee name. In column B is comments about that employee. The employee's name could be repeated several times throughout the workbook. Sheet2 has 1000 rows with multiple employees on it. On Sheet1 I want to put all the comments that match Employee1 (which is in cell A1 on Sheet1). There will probably be 20 matches, so I want the comments to be in rows 2-21. Is this possible? Thanks in advance. Try this *array* formula in A2 of Sheet1: =INDEX(Sheet2!$B$1:$B$30,SMALL(IF(Sheet2!$A$1:$A$30=$A$1,ROW($A$1:$A$30))...

change web proxy method attribute at run time
Here is what I have and what I would like to do: I am building a web client application. I add a web reference to some web service server to my project so that now I can call the web service. A proxy class is generated for me. This is nice. It works! Now, the method that is generated in the proxy class has an attribute preceding it that, among other things, contains the web address of the web service and the particular method at that service to be called like this: System.Web.Services.Protocols.SoapRpcMethodAttribute("http://some.machine/service/procedure", , RequestNamespace="u...

How to compare column values and have Excel highlight the least value
I created a spreadsheet of approximately 3,000 + rows of data (column A - pricing information by product). The columns represent my different customers price for that respective product (columns B - E). If this is the following information: A2 = Product A B2 = $1.00 C2 = $1.50 D2 = $1.25 E2 = $2.00 What formula do I use to compare the cells (B2 - E2) so that Excel can then return the lowest cost item in that row and highlight it or change it's color, etc...? Thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usen...

highest and lowest values
Hi All, How do I remove the highest and lowest values from a series of fields? In what way do you want to remove them? If you want to ignore their values in a sum, for example, then you could have something like: =SUM(A1:A20)-MAX(A1:A20)-MIN(A1:A20) and =COUNT(A1:A20)-2 to get a count of how many values - the first formula divided by the second will give you the average, for example. Hope this helps. Pete On Apr 20, 4:33 pm, "jim3975" <3975...@planet.nl> wrote: > Hi All, > How do I remove the highest and lowest values from a series of fields? You mean delet...

Versions created by property-only changes
In WSS 3.0, if you have a document library with versioning turned on, and someone edits only the properties associated with the document and does NOT change the document itself, I can see a new version in the version history. My question is, does that new version create a new instance of the document itself in the database? Put another way, if I upload a 10MB file, and then without every change that 10MB file I make multiple changes to the properties that result in 10 versions in teh version history, is that entry taking up 100MB+ in the database because the same file is replic...

Event on changed comment for a cell
Hi, I'm searching for the event that detects a chnage in a comment of cell performed by a another macro (here Cognos PowerPlay for Excel). The Worksheet_Change and the Worksheet_SelectionChange events won't work. Can anybody help? I don't believe any event will detect changing a comment. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "MattiasP" <mattias.plaecking@daimlerchrysler.com> wrote in message news:1121263758.570599.41530@o13g2000cwo.googlegroups.com... > Hi, > I'm searching for the eve...

Calculating Values in a Query
I have 4 fields in a query, Drawing Dim., Tolerance, Upper Limit, and Lower Limit. I am trying to calculate the Upper and Lower Limit fields by taking the Drawing Dim. +/- the Tolerance (this works fine on my form for entering the data). The problem is that the Tolerance is a combo box (dropdown), and the user chooses from values in the table. I am pretty sure this is why I am getting a Data Type Mismatch Error in my query, but I cant figure out how to get it to work. Can someone please help me? Thanks!! >>The problem is that the Tolerance is a combo box (dropdown), and th...

Change Framework 3.5 to 2.0 change
Hi, I have build my application in framework 3.5 and i used update panel in my website. but i want to change its framework 2.0 so i have changed the property of web project and build but it gives me error that update panel is not defined. I have added ajaxcontroltoolkit.dll to bin folder but not solved my problem Please suggest me to solve this problem. Thanks in advance. Hemant The UpdatePanel is part of the base AJAX library which is included in 3.5 and an additional download for 2.0 (perhaps http://www.microsoft.com/downloads/details.aspx?FamilyID=ca9d90fa-e8c9-42e3-a...

change a color of a cell based on it's contents
Hello, I read many messages about the conditionnal formatting but only three levels are not enough. I explain what I want to do : I've an excell sheet containing some datas taken from the volume occupied by a database. During the day, the database is growing up, and in the morning, a cleaning is performed. We pick up at regular interval during the day. I would like to color the cell based on the % it content from a dark green to a dark red. Something like that : 1-5 : very dark green 6-10 : dark green 11-15 : grenn 16-20 : light green 21-25 : very light green 26-30 : a little bit yellow ...

Zero Values
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel When using Vlookup i get #NA returned in cells that have a number with a 0 in front. ie: 04011 returns #NA. I then try and type the number 04011 in the cell and find the 0 vanishes but the correct Vlookup result is returned. <br> I need the 0 to remain in the lookup value cell as it should be 04011, how do i get the 0 to remain when typed? <br> Cell formatting is GENERAL and when I change to NUMBER the correct result is returned however the 0 vanishes from the lookup value. <br> SHOW ZERO VALUES...

Runtime changing values in Registry #2
I've used the following code to enter into the registry.... I've written this code in the Process function of StartPOS.... Dim regKey As RegistryKey Dim ver As Decimal regKey = Registry.LocalMachine.OpenSubKey("Software\Microsoft\Retail Management System\Store Operations\Hooks\001", True) regKey.SetValue("Caption", "InitializeTransaction") regKey.SetValue("Description", "Test this") regKey.SetValue("HookType", "6") regKey.SetValue("ObjectName", "ClassL...

Validation rules changed upon condition
Hi, all, I have a XL worksheet for user to enter data. In cell C11, I have a validation rule to allow user to pick value from a list. I want to set up a validation rule in cell D11 (wherever) and the available list of value to choose depends on the value the user chose in cell C11. That means if user choose 1 in cell C11, the validation rule in cell D11 will become a list of "AA,BB,CC". And if user choose 2 in cell C11, the available list of value will become "DD,EE,FF". Can anybody tell me how to do it? Do I need to write a VBA script and how to? Thanks in advance. Huyeo...

Incrementing values
I am creating a spreadsheet to import values into my accounting software. The first row is the product name (actually a reference to another worksheet where the product name was developed by referencing various cells). The next 4 rows are descriptive info and then the sequence starts again. I want to increment just the row with the product names while leaving the the next 4 rows unchanged. Is there a way to do this? I tried filtering the worksheet so just the rows with the product names show but the incrementing is taking place as if all rows were visible, so rather than incr...

Global file reference change
I have two spreadsheets, one tracking expenses, the other tracking income. The income sheet(12 pages, 1 for each month) also has a column for each day of the month to show the expenses for that day/month. It references the expenses file. My problem is this: I have a template for each, blank, that each year I rename for the year. This means I have to go in and edit each cell for 12 months x 30 days...360 cells, and change the file reference to show the new expenses file. Is there any way to do a global find and replace for the entire income sheet to make this/these changes? Sorry if I hav...

How to find the minimum value in a database with multiple values .
I am trying to find the earliest transaction date for a program participant with multiple records relating to each participant. For example in a list of transactions participants are identified by their SSN, there are many transactions by each participant with the date of each transaction recorded. I want to find the earliest transaction date by each participant without sorting the database. One way =MIN(IF(B3:B300="a",A3:A300)) dates in A3:A300 (need to be real excel dates thus numeric), participants in B3:B300 and the above formula will find the earliest date for partici...