Hello,
We currently have an SQL database which serves as our Identity source for the Work_Phone attribute. We use an Identity collector with an SQL driver to collect the "Phone" column from our database, and populate our Identity record with the phone number.
For some reason when we collect, certain phone numbers are appended with [NEW LINE] at the end. Example in screenshot. Has anyone else run into this issue and/or know how to resolve?
Thank you
Hello,
I have used the below query in our SQL environment and retrieved a hexadecimal result with no additional characters
select cast(phone as VARBINARY(128)) from
Is it possible the collector is adding this information in during collection?
Thank you
It's possible, however I think it's unlikely. As you mentioned there are only certain phone numbers which are being appended with 'NEW LINE'
Can you provide an exact SQL (the one which you mentioned above) output from one of the phone numbers which are being appended with 'NEW LINE'?
Another approach:
Under Admin->System->Diagnostics , uncheck the Remove Internal Data Files After Upload option
Run the identity collector for phone numbers.
On the server, locate AveksaDataDir directory.
In that directory you will find several files. Look for UserData.data with the latest timestamp (it will have a numeric prefix - it's not the run ID)
This is an example of what you might find:
~ª~ª~ª~ª~ª~ª~ª~ª~ª~ªSalesªAtest@test.com
Note: ª is being used a delimiter.
See if you are able to spot any additional chars next to the phone number.
Once you completed that test, check the previously unchecked checkbox (Remove Internal Data Files After Upload).
Hello Boris,
The sql query used on the Ms SQL server is as follows:
select * from dbo.directory where pager2='1002670';
Here query result shows the phone number which has no NEW LINE as reported.
-Riddhish
You should try the following and then post the output here
select cast(phone as VARBINARY(128)) from dbo.directory where pager2='1002670';
Try the second approach which I suggested in my previous reply
Hello Boris,
Customer is able to see the [NEWLINE] in the UserData.data file.
Please see below result from VARBINARY query. When converted, that is the correct phone number for the associated employee without any extra characters.
0x323438333132343833340D0A
-Riddhish
0D and 0A are not numbers
A is a new line
D is carriage return
Either make sure the values in the table are without extra chars or adjust your query to trim the "special" chars
Maybe there are additional non visible chars in the column.
Here is an example:
When executing the query with dump function, you can see that there are additional chars
Note: 10 in ascii is a new line.
You can try using the following SQL query:
Note: the SQL queries are from Oracle SQL