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
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-23 : 14:27:40
what do you get without the CHAR(..) function? (Just ASCII, that is)
Go to Top of Page

Nevarda
Starting Member

5 Posts

Posted - 2014-09-25 : 02:48:22
in order I recieve:

(a blank ascii value, I know, weird)
0
49
0
48
38
58
32
32
Go to Top of Page

Nevarda
Starting Member

5 Posts

Posted - 2014-09-25 : 05:57:57
I have backed up the table and removed the entry so that the query executes.
Temporary solution that was needed in order to get results
Go to Top of Page
   

- Advertisement -