SMS Text msg data Unreadable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SMS Text msg data Unreadable

Author

Topic

Nevarda
Starting Member

5 Posts

Posted - 2014-09-23 : 08:28:42

Hi,
Our company sends out sms text mesgs from our MSSQL Database and stores the responses in a table when a reply is received. My problem is now that some mesgs are showing as blank(even when I copy the raw data field out of the query return window) yet when I check the length of the field, it contains data. I set the field to a Temp Variable and converted the contents to ascii and definitely found that it shows data.
we use this reply as a rating system and I have queries and functions that extract the info I need, the problem is that I need to convert this field into a readable field first before I can extract the info.
sql example:

Select T2.ReceivedData , Datalength(T2.ReceivedData) Length
FROM DMZWEB01.[SMS_SERVICE].[dbo].[Sent] T1
LEFT JOIN DMZWEB01.[SMS_SERVICE].[dbo].[Reply] T2 ON T1.ID = T2.ID
INNER JOIN Clm_ClaimsRegister T3 ON T1.PolicyNumber = T3.Claim_No
Where T1.id = 1158008

This returns a blank field and a datalength of 6.
to prove that the field contains data,
a query that can run is:

DECLARE @Temp VarChar(8) =
(Select T2.ReceivedData
FROM DMZWEB01.[SMS_SERVICE].[dbo].[Sent] T1
LEFT JOIN DMZWEB01.[SMS_SERVICE].[dbo].[Reply] T2 ON T1.ID = T2.ID
INNER JOIN Clm_ClaimsRegister T3 ON T1.PolicyNumber = T3.Claim_No
Where T1.id = 1158008
)

SET TEXTSIZE 0
SET NOCOUNT ON
DECLARE @position int, @string char(8)
SET @position = 1
SET @string = @Temp
WHILE @position <= DATALENGTH(@string)
BEGIN
SELECT -- ASCII(SUBSTRING(@string, @position, 1)),
CHAR(ASCII(SUBSTRING(@string, @position, 1)))
SET @position = @position + 1
END
SET NOCOUNT OFF

this returns < Line numbers >
1>
2>1
3>
4>0
5>&
6>:
7>

the actual field is pasted between these two comment '' signs below
''
see? nothing

does anyone have any idea ?

Nevarda
Starting Member

5 Posts

Posted - 2014-09-23 : 09:16:03

I believe that this kind of reply is from some type of new smart phone.
this is however a theory right now.
The problem is that my report is now returning an error of

Msg 245, Level 16, State 1, Line 6
Conversion failed when converting the varchar value '.1.0' to data type int.

my Functions that I use already cater for replacing the full stops between numbers.

any assistance is appreciated