CHAR(127)

[ptheriault]

Does anyone know what could add a char(127) instead of a "'s"

Here is an example of the data that I am getting in my database. Unfortionatly the datatype is Varchar(1000) instead of NVARCHAR(). So I'm getting errors when I try to replicate the data.

SELECT 'The claimant was a passenger in the insured?'+CHAR(127)+'s vehicle when the MVA occurred'

Could it be caused by a user keystroke?


- Paul
- Database performance looks fine, it must be the Network!

 

[bborissov]

CHAR(127) is no-break space (according to Character map)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ptheriault]

In my database it is showing up as a box? I'm just trying to figure out how it is convering "'" to a box or why?

- Paul
- Database performance looks fine, it must be the Network!

 

[AlexCuse]

Are you looking for char(39)?

Ignorance of certain subjects is a great part of wisdom

 

[ptheriault]

yes, it should be char(39) But for some reason it is being change to char(127).

- Paul
- Database performance looks fine, it must be the Network!

 

[AlexCuse]

Or perhaps char(127) is in there in place of char(39), as some kind of placeholder so no apostrophes in data?

If it's causing problems (of course it is ;-) ), I think you could replace teh 127 with some other character for a placeholder to fix.

Ignorance of certain subjects is a great part of wisdom

 

[ptheriault]

I've been doing that. The user claims that they have entered the data like this... "insured's". Maybe it's something the app is doing. Maybe they are trying to strip out any char(39) characters. Unfortionatly, I don't find the invalid char(127) till replication crashes. Do you know of a way to strip out the char(127) in replication?

- Paul
- Database performance looks fine, it must be the Network!

 

[AlexCuse]

Sorry I know nothing about replication. I'd think whatever stored proc is handling the input from the app, should be able to kick off another stored proc to replace the updated record's char(127) with a char(39) though. Once the data is in DB there is not so much to worry about.

Ignorance of certain subjects is a great part of wisdom

 

[philhege]

According to

http://www.alanwood.net/demos/ansi.html

char(127) is "Not used". The non-breaking space is char(160). When I print char(127) in QA, I get the box.

The question I have is: what app is using the data at your end of the replication? Is there any way to handle the transformation in the app? The reason I ask is: if you change replicated data, it's no longer replicated. You have to perform some sort of validation on the publisher side before it's replicated.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[ptheriault]

I'm going to have to take this back to the vendor. The database is storing the box character and we can't have that.
Thanks for your help everyone.


- Paul
- Database performance looks fine, it must be the Network!

 

[SQLSister]

You could put a trigger onthe table to strip it out on insert or update. That is probably your best bet. We sometimes get wierdness like this when people use the copy and paste to copy the data from another app when they do the insert of the records. They copy some non-printing charaters as well as the ones they see. We were getting some wierd stuff like this when the records were initially in Outlook for some reason.

Questions about posting. See faq183-874

 

[ptheriault]

I was thinking of using a trigger to do that, but because the table is replicated wouldn't replication grab the insert and the update? So I would get the invalid character. It's transactional.

- Paul
- Database performance looks fine, it must be the Network!

 

[philhege]

Triggers are BEFORE actions by default (there's also INSTEAD). The SQL code in a trigger is run before the transaction is committed, so your trigger actions would replicate.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[ptheriault]

Thanks Phil. That's good info!


- Paul
- Database performance looks fine, it must be the Network!

 

[AlexCuse]

I'm going to have to take this back to the vendor. The database is storing the box character and we can't have that.

Sounds like your problem is a third-party crapplication!

(I have now met my lame joke quota for this Friday. It was getting down to the wire there...)

Ignorance of certain subjects is a great part of wisdom

 

[ptheriault]

LOL, crapplication doesn't even begin to describe this monster. 12 more months and it's will be replaced! I plan to through the old server from the roof!

Thank god it's 5. I'm outta here. Thanks everyone for your help. Have a good weekend!

- Paul
- Database performance looks fine, it must be the Network!