Notifications
Announcements
No record found.
in search of sql code to id (within a table) the culprit of 'invalid character in ascii string' error
*This post is locked for comments
Please give us more information about what you're doing, where you get the error, what's the SQL query and so on.
there is an ax batch job producing the invalid character error; attempting to query (using SQL) certain columns in the tables involved to id the invalid characters.
If you know what characters you are searching for:
CREATE TABLE ##TempTables
(
ROWID BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY
,[TableName] VARCHAR(MAX)
,[ColName] VARCHAR(MAX)
,[Found] int
)
DECLARE @Loop INT
DECLARE @LoopMAX INT
DECLARE @SQL VARCHAR(max)
DECLARE @Value VARCHAR(max)
DECLARE @ValueNew VARCHAR(max)
DECLARE @ColName VARCHAR(max)
DECLARE @tblName VARCHAR(max)
--***************************************
--TYPE IN THE VALUE YOU LOOKING FOR BELOW
SELECT @Value = 'Raiders'
--INSERT ALL TABLE NAMES AND COLUMS HERE
INSERT INTO ##TempTables
[TableName]
,[ColName]
,[Found]
SELECT
[T].[Name] as [TableName]
,[C].[Name] as [ColName]
, 0 as [found]
FROM sys.tables [T]
INNER JOIN sys.Columns [C]
ON [C].[Object_ID] = [T].[Object_ID]
@Loop = 1
,@LoopMAX = MAX(ROWID)
FROM ##TempTables
WHILE (@Loop <= @LoopMAX)
BEGIN
@ColName = [ColName]
,@tblName = [TableName]
WHERE ROWID = @Loop
SELECT @SQL =
'IF EXISTS(SELECT ' + @ColName + ' FROM ' + @tblName + ' WHERE CAST(' + @ColName + ' as VARCHAR(max))= ''' + @Value + ''')
UPDATE ##TempTables SET FOUND = 1 Where ROWID = '+ cast(@Loop as varchar(max)) +'
END'
EXEC (@SQL)
SET @Loop = @Loop + 1
END
*
WHERE Found = 1
DROP TABLE ##TempTables
thanks for response
I don't know up front what character I'm looking for; got around issue another way. Thanks for your time
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
As AI tools become more common, we’re introducing a Responsible AI Use…
We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
Martin Dráb 4 Most Valuable Professional
Priya_K 4
MyDynamicsNAV 2
