Hi,
I was thinking of creating a stored procedure that would detect any records in the typical tables (T_Customer, T_Address, T_Phone, T_Eaddress) with characters symbols where they dont belong, and generally inappropriate characters like tab and return etc.
Has anyone come across a report like this already? I appreciate any advice before I get started inventing the wheel here.
Not sure if you already know about this -- Tessitura has a stored procedure, UP_FIND_INVALID_CHARACTERS, that can be run in reporting-only mode or fixing mode. It checks these tables: t_customer, t_address, tx_cust_sal and tx_alias_cust. And it checks for various characters. We have it scheduled to run nightly.
Thanks, Debbie
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Simon Basyuk Sent: Tuesday, September 12, 2017 4:08 PM To: Debbie Jacob <djacob@mos.org> Subject: RE: [Tessitura Technical Forum] illegal characters in user customer data
Hello Aaron,
Here is my script:
-----------NONPRINTING CHARACTERS IDENTIFICATION-----------
/*
Created by Simon Basyuk, DBA of Carnegie Hall
--PLEASE NOTE:
the script will catch all nonprinting characters in a specified Tess table, for instance: T_CUSTOMER , T_ADDRESS
The nonprinting characters examples: CR, LF (Line Feed), Tab, Space, .. --any one from ASCII=0 through ASCII=31
--expected result:
cust_no=xxxxxxxx, str=Simon Basyuk
Houshold, ASCII=10, @l_pos=12, l_ch= >
<
*/
declare
@l_cntr int,
@l_pos int,
@l_str varchar(256),
@l_ch char(1),
@l_cust int,
@l_ASCII_start int,@l_ASCII_end int
set @l_ASCII_start=0 --HARDCODED!
set @l_ASCII_end=31--ASCII 32 is space char --HARDCODED!
declare crs1 cursor local for SELECT
customer_no, lname --or: fname, postal_code street1,street2 --NOTE: TABLE NAME TO BE PROVIDED!
from T_CUSTOMER --or: T_ADDRESS for street1,street2
open crs1
fetch next from crs1 into @l_cust, @l_str
while (@@fetch_status=0)
BEGIN
set @l_cntr=@l_ASCII_start
while @l_cntr<=@l_ASCII_end
--checking for the first occurance of the char only!
begin
set @l_ch=char(@l_cntr)
set @l_pos=CHARINDEX(@l_ch,@l_str)
if @l_pos > 0
print 'cust_no='+ convert(varchar,@l_cust)+', str='+@l_str+
', ASCII='+ convert(varchar,@l_cntr)+', @l_pos='+ convert(varchar,@l_pos)+', l_ch= >'+@l_ch+'<'
if @l_cntr >=400--safety!
BREAK
set @l_cntr=@l_cntr+1
end --while @l_cntr<=
END --while @@fetch_status=0
close crs1
deallocate crs1
Regards,
Simon Basyuk
Manager, Database Administration
sbasyuk@carnegiehall.org
Carnegie Hall
881 Seventh Avenue New York, NY 10019
T:
212-903-9691
M:
646-296-0003
Please consider the environment before printing this e-mail.
This message may contain confidential or privileged information. If you are not the intended recipient, please advise the sender immediately by reply email and delete this message and any attachments without retaining a copy.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Aaron Glynn Sent: Tuesday, July 25, 2017 4:59 PM To: Basyuk, Simon <sbasyuk@carnegiehall.org> Subject: [Tessitura Technical Forum] illegal characters in user customer data
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!