June 30, 2012 at 11:13 am
Lynn,
I have been reading up on this all day. I'm not the only one asking this question. It appears that for some, the WHILE loop is the only way to do it. RBAR apparently. Pass each string to the function one at a time, exactly what I did in C#. Unless I'm wrong. Unfortunately every solution example out there on the WEB has the same schema. Build a function, create a string, pass it to the function and you get your one result. Pretty funny when you think about it. Anyway, don't spend your weekend wasting time on this. I have nothing better to do and besides I'm learning. It is just a bit confusing. I thought we were working on a way to avoid loops etc, but inevitably I am back to square one. It just threw me through a loop:-) Pardon the pun.
Rob
June 30, 2012 at 11:25 am
rjbirkett (6/30/2012)
Lynn,I have been reading up on this all day. I'm not the only one asking this question. It appears that for some, the WHILE loop is the only way to do it. RBAR apparently. Pass each string to the function one at a time, exactly what I did in C#. Unless I'm wrong. Unfortunately every solution example out there on the WEB has the same schema. Build a function, create a string, pass it to the function and you get your one result. Pretty funny when you think about it. Anyway, don't spend your weekend wasting time on this. I have nothing better to do and besides I'm learning. It is just a bit confusing. I thought we were working on a way to avoid loops etc, but inevitably I am back to square one. It just threw me through a loop:-) Pardon the pun.
Rob
Actually, you still don't need to use a loop. I am finishing up my code changes now.
June 30, 2012 at 11:37 am
I can't find it, but I thought you had posted the CREATE TABLE statement for the table dbo.CDR. Can you post it again?
Nevermind, I found it.
June 30, 2012 at 11:47 am
Here is my code and it works with my small set of data.
drop table dbo.CDRStaging;
go
with SampleData as (
select
MsgText as SampleRec
from
dbo.Syslogd
where
MsgDate = dateadd(dd, -1, cast(getdate() as date)) and
MsgText like '%CALL_END%'
),
BaseData as (
select
max(case ds.ItemNumber
when 2 then ds.Item else null end) [call_status],
max(case ds.ItemNumber
when 3 then ds.Item else null end) [channel_num],
max(case ds.ItemNumber
when 4 then ds.Item else null end) [sipcall_ident],
max(case ds.ItemNumber
when 5 then ds.Item else null end) [trunk_num],
max(case ds.ItemNumber
when 6 then ds.Item else null end) [b_channel],
max(case ds.ItemNumber
when 7 then ds.Item else null end) [sipconf_id],
max(case ds.ItemNumber
when 8 then ds.Item else null end) [trunk_group_num],
max(case ds.ItemNumber
when 9 then ds.Item else null end) [endpoint_type],
max(case ds.ItemNumber
when 10 then ds.Item else null end) [call_originator],
max(case ds.ItemNumber
when 11 then ds.Item else null end) [source_IP],
max(case ds.ItemNumber
when 12 then ds.Item else null end) [destination_IP],
max(case ds.ItemNumber
when 13 then ds.Item else null end) [sourcephone_numtype],
max(case ds.ItemNumber
when 14 then ds.Item else null end) [sourcephone_numplan],
max(case ds.ItemNumber
when 15 then ds.Item else null end) [sourcephone_number],
max(case ds.ItemNumber
when 16 then ds.Item else null end) [sourcephone_nummap],
max(case ds.ItemNumber
when 17 then ds.Item else null end) [destphone_numtype],
max(case ds.ItemNumber
when 18 then ds.Item else null end) [destphone_numplan],
max(case ds.ItemNumber
when 19 then ds.Item else null end) [destphone_number],
max(case ds.ItemNumber
when 20 then ds.Item else null end) [destphone_nummap],
max(case ds.ItemNumber
when 21 then ds.Item else null end) [call_duration],
max(case ds.ItemNumber
when 22 then ds.Item else null end) [call_codec],
max(case ds.ItemNumber
when 23 then ds.Item else null end) [packet_intervall],
max(case ds.ItemNumber
when 24 then ds.Item else null end) [rtp_IP],
max(case ds.ItemNumber
when 25 then ds.Item else null end) [rtp_port],
max(case ds.ItemNumber
when 26 then ds.Item else null end) [call_release],
max(case ds.ItemNumber
when 27 then ds.Item else null end) [termination_reason],
max(case ds.ItemNumber
when 28 then ds.Item else null end) [fax_data],
max(case ds.ItemNumber
when 29 then ds.Item else null end) [packets_in],
max(case ds.ItemNumber
when 30 then ds.Item else null end) [packets_out],
max(case ds.ItemNumber
when 31 then ds.Item else null end) [packets_lost],
max(case ds.ItemNumber
when 32 then ds.Item else null end) [header_id],
max(case ds.ItemNumber
when 33 then ds.Item else null end) [rtp_id],
max(case ds.ItemNumber
when 34 then ds.Item else null end) [callsetup_datetime],
max(case ds.ItemNumber
when 35 then ds.Item else null end) [callconnect_datetime],
max(case ds.ItemNumber
when 36 then ds.Item else null end) [callrelease_datetime],
max(case ds.ItemNumber
when 37 then ds.Item else null end) [rtp_delay],
max(case ds.ItemNumber
when 38 then ds.Item else null end) [rtp_jitter],
max(case ds.ItemNumber
when 39 then ds.Item else null end) [localrtp_source],
max(case ds.ItemNumber
when 40 then ds.Item else null end) [remotertp_source],
max(case ds.ItemNumber
when 41 then ds.Item else null end) [redirect_reason],
max(case ds.ItemNumber
when 42 then ds.Item else null end) [redirect_number],
max(case ds.ItemNumber
when 43 then ds.Item else null end) [redirect_numplan]
from
SampleData sd
cross apply dbo.DelimitedSplit8K(SampleRec,'|') ds
group by
sd.SampleRec
)
select
[call_status],
[channel_num],
[sipcall_ident],
[trunk_num],
[b_channel],
[sipconf_id],
[trunk_group_num],
[endpoint_type],
[call_originator],
[source_IP],
[destination_IP],
[sourcephone_numtype],
[sourcephone_numplan],
[sourcephone_number],
[sourcephone_nummap],
[destphone_numtype],
[destphone_numplan],
[destphone_number],
[destphone_nummap],
[call_duration],
[call_codec],
[packet_intervall],
[rtp_IP],
[rtp_port],
[call_release],
[termination_reason],
[fax_data],
[packets_in],
[packets_out],
[packets_lost],
[header_id],
[rtp_id],
cast(cast(right([callsetup_datetime], 13) as date) as varchar(10)) + ' ' + cast(cast(left([callsetup_datetime], 12) as time(7)) as varchar(16)) callsetup_datetime,
cast(cast(right(coalesce(nullif([callconnect_datetime],' '),[callsetup_datetime]), 13) as date) as varchar(10)) + ' ' + cast(cast(left(coalesce(nullif([callconnect_datetime],' '),[callsetup_datetime]), 12) as time(7)) as varchar(16)) callconnect_datetime,
cast(cast(right([callrelease_datetime], 13) as date) as varchar(10)) + ' ' + cast(cast(left([callrelease_datetime], 12) as time(7)) as varchar(16)) callrelease_datetime,
[rtp_delay],
[rtp_jitter],
[localrtp_source],
[remotertp_source],
[redirect_reason],
[redirect_number],
[redirect_numplan]
into
dbo.CDRStaging
from
BaseData
;
insert into dbo.CDR (
call_status,
channel_num,
sipcall_ident,
trunk_num,
b_channel,
sipconf_id,
trunk_group_num,
endpoint_type,
call_originator,
source_IP,
destination_IP,
sourcephone_numtype,
sourcephone_numplan,
sourcephone_number,
sourcephone_nummap,
destphone_numtype,
destphone_numplan,
destphone_number,
destphone_nummap,
call_duration,
call_codec,
packet_intervall,
rtp_IP,
rtp_port,
call_release,
termination_reason,
fax_data,
packets_in,
packets_out,
packets_lost,
header_id,
rtp_id,
callsetup_time,
callsetup_date,
callconnect_time,
callconnect_date,
callrelease_time,
callrelease_date,
rtp_delay,
rtp_jitter,
localrtp_source,
remotertp_source,
redirect_reason,
redirect_number,
redirect_numplan
)
select
cast(call_status as nvarchar(255)) call_status,
cast(channel_num as int) channel_num,
cast(sipcall_ident as int) sipcall_ident,
cast(trunk_num as int) trunk_num,
cast(b_channel as int) b_channel,
cast(sipconf_id as int) sipconf_id,
cast(trunk_group_num as int) trunk_group_num,
cast(endpoint_type as nvarchar(255)) endpoint_type,
cast(call_originator as nvarchar(255)) call_originator,
cast(source_IP as nvarchar(255)) source_IP,
cast(destination_IP as nvarchar(255)) destination_IP,
cast(sourcephone_numtype as int) sourcephone_numtype,
cast(sourcephone_numplan as int) sourcephone_numplan,
cast(sourcephone_number as nvarchar(255)) sourcephone_number,
cast(sourcephone_nummap as nvarchar(255)) sourcephone_nummap,
cast(destphone_numtype as int) destphone_numtype,
cast(destphone_numplan as int) destphone_numplan,
cast(destphone_number as nvarchar(255)) destphone_number,
cast(destphone_nummap as nvarchar(255)) destphone_nummap,
cast(call_duration as int) call_duration,
cast(call_codec as nvarchar(255)) call_codec,
cast(packet_intervall as int) packet_intervall,
cast(rtp_IP as nvarchar(255)) rtp_IP,
cast(rtp_port as int) rtp_port,
cast(call_release as nvarchar(255)) call_release,
cast(termination_reason as nvarchar(255)) termination_reason,
cast(fax_data as int) fax_data,
cast(packets_in as int) packets_in,
cast(packets_out as int) packets_out,
cast(packets_lost as int) packets_lost,
cast(header_id as int) header_id,
cast(rtp_id as nvarchar(255)) rtp_id,
cast(callsetup_datetime as time(7)) callsetup_time,
cast(callsetup_datetime as date) callsetup_date,
cast(callconnect_datetime as time(7)) callconnect_time,
cast(callconnect_datetime as date) callconnect_date,
cast(callrelease_datetime as time(7)) callrelease_time,
cast(callrelease_datetime as date) callrelease_date,
cast(rtp_delay as int) rtp_delay,
cast(rtp_jitter as int) rtp_jitter,
cast(localrtp_source as int) localrtp_source,
cast(remotertp_source as int) remotertp_source,
cast(redirect_reason as int) redirect_reason,
cast(redirect_number as int) redirect_number,
cast(redirect_numplan as int) redirect_numplan
from
dbo.CDRStaging;
go
June 30, 2012 at 12:42 pm
Still fails. Try removing the data from the callconnect_datetime segment and just filling it full of the same number of spaces and run it again. I get the same error as I did before, probably bue to the empty (full of spaces) element. Is there a way to check if it is empty or does not contain "UMT" and then replace it with the preceeding element in your code? I think this is the only problem. Or we just remove the split portion and I clean it up with my queries when reporting on the data, row by agonizing row:-)
Robert.
June 30, 2012 at 12:44 pm
rjbirkett (6/30/2012)
Still fails. Try removing the data from the callconnect_datetime segment and just filling it full of the same number of spaces and run it again. I get the same error as I did before, probably bue to the empty (full of spaces) element. Is there a way to check if it is empty or does not contain "UMT" and then replace it with the preceeding element in your code? I think this is the only problem. Or we just remove the split portion and I clean it up with my queries when reporting on the data, row by agonizing row:-)Robert.
You will need to provide us with some sample data where this code fails. Obviously my attempt to create such data has failed so I now need to go to the source.
June 30, 2012 at 1:19 pm
You are correct. I did a select top 10 because I know those records are clean, and it would not process any data at all, even after I modified the code to ignore the dat time split. How would you like the test data? Cut and paste? What is the simplest way to export it from SYSLOG (consider there is a lot of other non-related data in there)?
The sample I originally posted with the text qualifiers was imported into the db0.CDR table from access after processing and went straight in, that data came from the text srting I posted. I don't understand why it won't work on mine but does on yours. Makes no sense at all.
Rob
June 30, 2012 at 1:28 pm
First, you have run my code as is, correct? I need the exact error message you are getting when it fails.
June 30, 2012 at 1:41 pm
These are the top 35 records straight out of the syslog database.
|CALL_END |0 |0 |0 |1 |255 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |9206553478 |9206553478 |0 |0 |7260 |7260 |316 |g711Ulaw64k |20 |10.207.0.14 |12568|IP |GWAPP_NORMAL_CALL_CLEAR |0 |9253 |15846 |0 |-1 |1771355804296201211218@10.207.60.2 | 01:12:18.000 UTC Jun 29 2012 | 01:12:18.000 UTC Jun 29 2012 | 01:17:35.000 UTC Jun 29 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 01:17:35]
|CALL_END |40 |0 |1 |17 |255 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |9203588268 |9203588268 |0 |0 |7280 |7280 |88 |g711Ulaw64k |20 |10.207.0.14 |12536|TEL |GWAPP_NORMAL_CALL_CLEAR |0 |3757 |4401 |1 |-1 |11429326512862012232733@10.207.60.2 | 23:27:33.000 UTC Jun 28 2012 | 23:27:33.000 UTC Jun 28 2012 | 23:29:01.000 UTC Jun 28 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 23:29:01]
|CALL_END |4 |0 |0 |5 |254 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |6302930295 |6302930295 |0 |0 |7260 |7260 |77 |g711Ulaw64k |20 |10.207.0.11 |49520|TEL |GWAPP_NORMAL_CALL_CLEAR |0 |3860 |3864 |1 |-1 |1996093180296201214714@10.207.60.2 | 01:47:14.000 UTC Jun 29 2012 | 01:47:14.000 UTC Jun 29 2012 | 01:48:32.000 UTC Jun 29 2012 |131 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 01:48:32]
|CALL_END |19 |0 |0 |20 |250 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |7156232648 |7156232648 |0 |0 |7260 |7260 |65 |g711Ulaw64k |20 |10.207.0.11 |49256|TEL |GWAPP_NORMAL_CALL_CLEAR |0 |3248 |3250 |3 |-1 |1275381966296201253628@10.207.60.2 | 05:36:28.000 UTC Jun 29 2012 | 05:36:28.000 UTC Jun 29 2012 | 05:37:33.000 UTC Jun 29 2012 |142 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 05:37:33]
|CALL_END |3 |0 |0 |4 |255 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |9203603794 |9203603794 |0 |0 |7260 |7260 |430 |g711Ulaw64k |20 |10.207.60.3 |6620 |TEL |GWAPP_NORMAL_CALL_CLEAR |0 |73 |21503 |0 |-1 |13077063929620121411@10.207.60.2 | 01:41:01.000 UTC Jun 29 2012 | 01:41:01.000 UTC Jun 29 2012 | 01:48:11.000 UTC Jun 29 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 01:48:11]
|CALL_END |7 |0 |0 |8 |255 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |9204301702 |9204301702 |0 |0 |7260 |7260 |411 |g711Ulaw64k |20 |10.207.0.14 |12580|IP |GWAPP_NORMAL_CALL_CLEAR |0 |17042 |20577 |32 |-1 |741620692296201221410@10.207.60.2 | 02:14:10.000 UTC Jun 29 2012 | 02:14:10.000 UTC Jun 29 2012 | 02:21:02.000 UTC Jun 29 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 02:21:02]
|CALL_END |1 |0 |0 |2 |255 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |9203733906 |9203733906 |0 |0 |7260 |7260 |69 |g711Ulaw64k |20 |10.207.0.11 |49152|TEL |GWAPP_NORMAL_CALL_CLEAR |0 |3461 |3464 |0 |-1 |876181549296201213055@10.207.60.2 | 01:30:55.000 UTC Jun 29 2012 | 01:30:56.000 UTC Jun 29 2012 | 01:32:05.000 UTC Jun 29 2012 |220 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 01:32:05]
|CALL_END |13 |0 |0 |14 |243 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |1013 |1013 |0 |0 |7260 |7260 |183 |g711Ulaw64k |20 |10.207.0.14 |13008|TEL |GWAPP_NORMAL_CALL_CLEAR |0 |3828 |9150 |19 |-1 |149711153529620127117@10.207.60.2 | 07:01:17.000 UTC Jun 29 2012 | 07:01:17.000 UTC Jun 29 2012 | 07:04:20.000 UTC Jun 29 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 07:04:20]
|CALL_END |14 |0 |0 |15 |254 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |7156232648 |7156232648 |0 |0 |7260 |7260 |246 |g711Ulaw64k |20 |10.207.0.13 |12420|TEL |GWAPP_NORMAL_CALL_CLEAR |0 |5783 |12335 |15 |-1 |188993611329620126057@10.207.60.2 | 06:00:57.000 UTC Jun 29 2012 | 06:00:57.000 UTC Jun 29 2012 | 06:05:03.000 UTC Jun 29 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 06:05:03]
|CALL_END |41 |0 |1 |18 |255 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |9207169927 |9207169927 |0 |0 |7240 |7240 |49 |g711Ulaw64k |20 |10.207.0.11 |49402|TEL |GWAPP_NORMAL_CALL_CLEAR |0 |2457 |2460 |4 |-1 |17666263492862012233231@10.207.60.2 | 23:32:31.000 UTC Jun 28 2012 | 23:32:31.000 UTC Jun 28 2012 | 23:33:20.000 UTC Jun 28 2012 |68 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 23:33:20]
|CALL_END |13 |0 |0 |14 |255 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |9065537131 |9065537131 |0 |0 |7265 |7265 |319 |g711Ulaw64k |20 |10.207.0.11 |49580|TEL |GWAPP_NORMAL_CALL_CLEAR |0 |15959 |15960 |1 |-1 |73193353529620123436@10.207.60.2 | 03:43:06.000 UTC Jun 29 2012 | 03:43:06.000 UTC Jun 29 2012 | 03:48:25.000 UTC Jun 29 2012 |169 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 03:48:25]
|CALL_END |6 |0 |0 |7 |255 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |9203603794 |9203603794 |0 |0 |7260 |7260 |592 |g711Ulaw64k |20 |10.207.0.14 |12576|IP |GWAPP_NORMAL_CALL_CLEAR |0 |24354 |29627 |2 |-1 |755693639296201215230@10.207.60.2 | 01:52:30.000 UTC Jun 29 2012 | 01:52:30.000 UTC Jun 29 2012 | 02:02:22.000 UTC Jun 29 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 02:02:22]
|CALL_END |21 |0 |0 |22 |238 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |9202517769 |9202517769 |0 |0 |7260 |7260 |108 |g711Ulaw64k |20 |10.207.0.11 |49340|TEL |GWAPP_NORMAL_CALL_CLEAR |0 |5382 |5406 |21 |-1 |243044999296201272813@10.207.60.2 | 07:28:13.000 UTC Jun 29 2012 | 07:28:13.000 UTC Jun 29 2012 | 07:30:01.000 UTC Jun 29 2012 |140 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 07:30:01]
|CALL_END |0 |0 |0 |1 |255 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |9202902221 |9202902221 |0 |0 |7260 |7260 |472 |g711Ulaw64k |20 |10.207.0.11 |49452|TEL |GWAPP_NORMAL_CALL_CLEAR |0 |23562 |23601 |39 |-1 |2028153153296201255224@10.207.60.2 | 05:52:24.000 UTC Jun 29 2012 | 05:52:24.000 UTC Jun 29 2012 | 06:00:17.000 UTC Jun 29 2012 |168 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 06:00:17]
|CALL_END |0 |0 |0 |1 |255 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |9209463268 |9209463268 |0 |0 |7260 |7260 |193 |g711Ulaw64k |20 |10.207.0.14 |12988|IP |GWAPP_NORMAL_CALL_CLEAR |0 |4388 |9662 |22 |-1 |890319525296201265830@10.207.60.2 | 06:58:30.000 UTC Jun 29 2012 | 06:58:30.000 UTC Jun 29 2012 | 07:01:43.000 UTC Jun 29 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 07:01:43]
|CALL_END |22 |0 |0 |23 |255 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |9062822099 |9062822099 |0 |0 |7260 |7260 |323 |g711Ulaw64k |20 |10.207.0.14 |12628|IP |GWAPP_NORMAL_CALL_CLEAR |0 |8534 |16194 |24 |-1 |379568739296201242357@10.207.60.2 | 04:23:57.000 UTC Jun 29 2012 | 04:23:57.000 UTC Jun 29 2012 | 04:29:21.000 UTC Jun 29 2012 |0 |4 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 04:29:21]
|CALL_END |5 |0 |0 |6 |247 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |9068642434 |9068642434 |0 |0 |7260 |7260 |781 |g711Ulaw64k |20 |10.207.0.14 |13628|IP |GWAPP_NORMAL_CALL_CLEAR |0 |30196 |39057 |144 |-1 |66370149529620128951@10.207.60.2 | 08:09:51.000 UTC Jun 29 2012 | 08:09:51.000 UTC Jun 29 2012 | 08:22:53.000 UTC Jun 29 2012 |0 |4 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 08:22:53]
|CALL_END |8 |0 |0 |9 |254 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |9064746092 |9064746092 |0 |0 |7265 |7265 |90 |g711Ulaw64k |20 |10.207.0.14 |12736|TEL |GWAPP_NORMAL_CALL_CLEAR |0 |1334 |4546 |4 |-1 |1920056298296201255848@10.207.60.2 | 05:58:48.000 UTC Jun 29 2012 | 05:58:48.000 UTC Jun 29 2012 | 06:00:19.000 UTC Jun 29 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 06:00:19]
|CALL_END |17 |0 |0 |18 |255 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |9063673018 |9063673018 |0 |0 |7265 |7265 |85 |g711Ulaw64k |20 |10.207.0.14 |12612|TEL |GWAPP_NORMAL_CALL_CLEAR |0 |218 |4294 |0 |-1 |204612320229620124147@10.207.60.2 | 04:01:47.000 UTC Jun 29 2012 | 04:01:47.000 UTC Jun 29 2012 | 04:03:13.000 UTC Jun 29 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 04:03:13]
|CALL_END |9 |0 |0 |10 |255 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |9207469938 |9207469938 |0 |0 |7260 |7260 |82 |g711Ulaw64k |20 |10.207.0.11 |49502|TEL |GWAPP_NORMAL_CALL_CLEAR |0 |4123 |4133 |6 |-1 |963868462296201225116@10.207.60.2 | 02:51:16.000 UTC Jun 29 2012 | 02:51:16.000 UTC Jun 29 2012 | 02:52:39.000 UTC Jun 29 2012 |173 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 02:52:39]
|CALL_END |20 |0 |0 |21 |255 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |7153606703 |7153606703 |0 |0 |7260 |7260 |361 |g711Ulaw64k |20 |10.207.0.14 |12620|IP |GWAPP_NORMAL_CALL_CLEAR |0 |11676 |18059 |0 |-1 |728975967296201241538@10.207.60.2 | 04:15:38.000 UTC Jun 29 2012 | 04:15:38.000 UTC Jun 29 2012 | 04:21:39.000 UTC Jun 29 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 04:21:39]
|CALL_END |2 |0 |0 |3 |255 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |9203733906 |9203733906 |0 |0 |7260 |7260 |244 |g711Ulaw64k |20 |10.207.0.11 |49260|TEL |GWAPP_NORMAL_CALL_CLEAR |0 |12209 |12209 |0 |-1 |19412088296201213214@10.207.60.2 | 01:32:14.000 UTC Jun 29 2012 | 01:32:14.000 UTC Jun 29 2012 | 01:36:18.000 UTC Jun 29 2012 |158 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 01:36:18]
|CALL_END |9 |0 |0 |10 |253 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |9202890266 |9202890266 |0 |0 |7260 |7260 |325 |g711Ulaw64k |20 |10.207.0.11 |49646|TEL |GWAPP_NORMAL_CALL_CLEAR |0 |16244 |16286 |43 |-1 |194562624296201255914@10.207.60.2 | 05:59:14.000 UTC Jun 29 2012 | 05:59:14.000 UTC Jun 29 2012 | 06:04:40.000 UTC Jun 29 2012 |155 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 06:04:40]
|CALL_END |14 |0 |0 |15 |241 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |9203404158 |9203404158 |0 |0 |7260 |7260 |224 |g711Ulaw64k |20 |10.207.0.13 |13156|TEL |GWAPP_NORMAL_CALL_CLEAR |0 |7420 |11239 |33 |-1 |78498743929620128538@10.207.60.2 | 08:05:38.000 UTC Jun 29 2012 | 08:05:38.000 UTC Jun 29 2012 | 08:09:23.000 UTC Jun 29 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 08:09:23]
|CALL_END |8 |0 |0 |9 |254 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |7153833023 |7153833023 |0 |0 |7260 |7260 |96 |g711Ulaw64k |20 |10.207.0.11 |49568|IP |GWAPP_NORMAL_CALL_CLEAR |0 |4810 |4809 |0 |-1 |829886634296201221416@10.207.60.2 | 02:14:16.000 UTC Jun 29 2012 | 02:14:16.000 UTC Jun 29 2012 | 02:15:52.000 UTC Jun 29 2012 |192 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 02:15:52]
|CALL_END |5 |0 |0 |6 |255 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |7153025055 |7153025055 |0 |0 |7260 |7260 |117 |g711Ulaw64k |20 |10.207.0.11 |49770|TEL |GWAPP_NORMAL_CALL_CLEAR |0 |5859 |5864 |1 |-1 |241312420296201214948@10.207.60.2 | 01:49:48.000 UTC Jun 29 2012 | 01:49:48.000 UTC Jun 29 2012 | 01:51:46.000 UTC Jun 29 2012 |183 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 01:51:46]
|CALL_END |4 |0 |0 |5 |238 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |7153451280 |7153451280 |0 |0 |7261 |7261 |459 |g711Ulaw64k |20 |10.207.0.13 |13668|IP |GWAPP_NORMAL_CALL_CLEAR |0 |11239 |22983 |64 |-1 |1305475041296201284723@10.207.60.2 | 08:47:23.000 UTC Jun 29 2012 | 08:47:23.000 UTC Jun 29 2012 | 08:55:03.000 UTC Jun 29 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 08:55:03]
|CALL_END |10 |0 |0 |11 |242 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |7158494000 |7158494000 |0 |0 |7260 |7260 |93 |g711Ulaw64k |20 |10.207.0.11 |49610|TEL |GWAPP_NORMAL_CALL_CLEAR |0 |4664 |4692 |24 |-1 |926870062296201282058@10.207.60.2 | 08:20:58.000 UTC Jun 29 2012 | 08:20:58.000 UTC Jun 29 2012 | 08:22:32.000 UTC Jun 29 2012 |121 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 08:22:32]
|CALL_END |12 |0 |0 |13 |240 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |7152254939 |7152254939 |0 |0 |7260 |7260 |576 |g711Ulaw64k |20 |10.207.0.14 |13676|IP |GWAPP_NORMAL_CALL_CLEAR |0 |15548 |28802 |80 |-1 |1545231795296201281255@10.207.60.2 | 08:12:55.000 UTC Jun 29 2012 | 08:12:55.000 UTC Jun 29 2012 | 08:22:31.000 UTC Jun 29 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 08:22:31]
|CALL_END |5 |0 |0 |6 |250 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |9207135118 |9207135118 |0 |0 |7260 |7260 |293 |g711Ulaw64k |20 |10.207.0.14 |13536|IP |GWAPP_NORMAL_CALL_CLEAR |0 |10779 |14695 |55 |-1 |159225424729620128417@10.207.60.2 | 08:04:17.000 UTC Jun 29 2012 | 08:04:17.000 UTC Jun 29 2012 | 08:09:11.000 UTC Jun 29 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 08:09:11]
|CALL_END |19 |0 |0 |20 |241 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |3097499912 |3097499912 |0 |0 |7260 |7260 |888 |g711Ulaw64k |20 |10.207.0.14 |18352|TEL |GWAPP_NORMAL_CALL_CLEAR |0 |44189 |44403 |449 |-1 |1478083829296201215249@10.207.60.2 | 15:02:49.000 UTC Jun 29 2012 | 15:02:49.000 UTC Jun 29 2012 | 15:17:37.000 UTC Jun 29 2012 |163 |4 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 15:17:37]
|CALL_END |10 |0 |0 |11 |255 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |7156742965 |7156742965 |0 |0 |7260 |7260 |141 |g711Ulaw64k |20 |10.207.0.14 |12588|IP |GWAPP_NORMAL_CALL_CLEAR |0 |3863 |7089 |0 |-1 |95393452429620123837@10.207.60.2 | 03:08:37.000 UTC Jun 29 2012 | 03:08:37.000 UTC Jun 29 2012 | 03:10:59.000 UTC Jun 29 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 03:10:59]
|CALL_END |15 |0 |0 |16 |236 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |9062313828 |9062313828 |0 |0 |7265 |7265 |11 |g711Ulaw64k |20 |10.207.0.11 |49274|TEL |GWAPP_NORMAL_CALL_CLEAR |0 |588 |590 |0 |-1 |1800079111296201283912@10.207.60.2 | 08:39:12.000 UTC Jun 29 2012 | 08:39:12.000 UTC Jun 29 2012 | 08:39:24.000 UTC Jun 29 2012 |191 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 08:39:24]
|CALL_END |43 |0 |1 |20 |255 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |1043 |1043 |0 |0 |7299 |7299 |109 |g711Ulaw64k |20 |10.207.0.14 |12644|TEL |GWAPP_NORMAL_CALL_CLEAR |0 |5118 |5488 |1 |-1 |124781601029620124532@10.207.60.2 | 04:53:02.000 UTC Jun 29 2012 | 04:53:02.000 UTC Jun 29 2012 | 04:54:52.000 UTC Jun 29 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 04:54:52]
|CALL_END |3 |0 |0 |4 |253 |2 |ISDN |TEL |10.207.60.2 |10.207.0.11 |2 |1 |7188692276 |7188692276 |0 |0 |7292 |7292 |6 |g711Ulaw64k |20 |10.207.0.11 |49540|TEL |GWAPP_NORMAL_CALL_CLEAR |0 |342 |345 |0 |-1 |34603810829620125189@10.207.60.2 | 05:18:09.000 UTC Jun 29 2012 | 05:18:09.000 UTC Jun 29 2012 | 05:18:16.000 UTC Jun 29 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 05:18:16]
None of which will process. After a few mods, I got one record in the staging database all the fields had TEL in them and the error I got was error converting TEL to numeric. Like I said at the beginning, CDR data is a pain in the *** to work with:-(
June 30, 2012 at 2:00 pm
This is the error message running your code as is:-
Msg 306, Level 16, State 2, Line 102
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Remove the group by clause and you get this:-
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
(1 row(s) affected)
As previously, it processes only one row. That one row is clean and polished, but there is only one.
June 30, 2012 at 2:02 pm
I'm not sure what is going on here. I was able to process the data you provided with no modifications. All I did was insert it into a scaled down dbo.Syslogd table (it has 2 columns: MsgDate, MsgText).
The data is processed without error when I run my code.
I am going to put together all the code and tables I have been using. I will make a small change regarding the source table so that it uses the data you provided. You should create an empty sandbox data to run the code I post. Be sure you put the dbo.DelimitedSplit8K function in this database so that it is one less item I need to provide.
June 30, 2012 at 2:05 pm
rjbirkett (6/30/2012)
This is the error message running your code as is:-Msg 306, Level 16, State 2, Line 102
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Remove the group by clause and you get this:-
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
(1 row(s) affected)
As previously, it processes only one row. That one row is clean and polished, but there is only one.
You need the group by to pivot the data. I just realized the problem. Change my code as such:
group by
cast(sd.SampleRec as varchar(max))
June 30, 2012 at 2:06 pm
Lynn, your code works insofaras the output required. It just processes one row (assuming you remove the group by clause, and I don't see whay that would make any difference). This is probably my fault somehow, but I don't see it. I will follow your instructions to the letter.
Rob.
June 30, 2012 at 2:11 pm
Now it works:-) So that begs the question, how come it worked on your setup? The difference is KILLING me! I have tried every which way to get it all sorted out:-) I am glad however you spotted the problem. I think we can officially close this thread. I just need to write it out as a stored proc (easy to do) and call it from my front end, but I'm dying to know why it worked for you.
Rob.
June 30, 2012 at 2:14 pm
rjbirkett (6/30/2012)
Now it works:-) So that begs the question, how come it worked on your setup? The difference is KILLING me! I have tried every which way to get it all sorted out:-) I am glad however you spotted the problem. I think we can officially close this thread. I just need to write it out as a stored proc (easy to do) and call it from my front end, but I'm dying to know why it worked for you.Rob.
Simple, my source table, dbo.Syslogd, did not have the MsgText column defined as text, it is defined as varchar(8000). Since your table is defined as text, I needed to cast it as varchar(max) for the group by to work.
Viewing 15 posts - 76 through 90 (of 95 total)
You must be logged in to reply to this topic. Login to reply