9.0
78
1 SQLCHAR 0 12 "," 1 cdrRecordType ""
2 SQLCHAR 0 12 "," 2 globalCallID_callManagerId ""
3 SQLCHAR 0 12 "," 3 globalCallID_callId ""
4 SQLCHAR 0 12 "," 4 origLegCallIdentifier ""
5 SQLCHAR 0 12 "," 5 dateTimeOrigination ""
6 SQLCHAR 0 12 "," 6 origNodeId ""
7 SQLCHAR 0 12 "," 7 origSpan ""
8 SQLCHAR 0 12 ",\"" 8 origIpAddr ""
9 SQLCHAR 0 50 "\",\"" 9 callingPartyNumber Latin1_General_CI_AS
10 SQLCHAR 0 50 "\"," 10 callingPartyUnicodeLoginUserID Latin1_General_CI_AS
11 SQLCHAR 0 12 "," 11 origCause_location ""
12 SQLCHAR 0 12 "," 12 origCause_value ""
13 SQLCHAR 0 12 "," 13 origPrecedenceLevel ""
14 SQLCHAR 0 12 "," 14 origMediaTransportAddress_IP ""
15 SQLCHAR 0 12 "," 15 origMediaTransportAddress_Port ""
16 SQLCHAR 0 12 "," 16 origMediaCap_payloadCapability ""
17 SQLCHAR 0 12 "," 17 origMediaCap_maxFramesPerPacket ""
18 SQLCHAR 0 12 "," 18 origMediaCap_g723BitRate ""
19 SQLCHAR 0 12 "," 19 origVideoCap_Codec ""
20 SQLCHAR 0 12 "," 20 origVideoCap_Bandwidth ""
21 SQLCHAR 0 12 "," 21 origVideoCap_Resolution ""
22 SQLCHAR 0 12 "," 22 origVideoTransportAddress_IP ""
23 SQLCHAR 0 12 ",\"" 23 origVideoTransportAddress_Port ""
24 SQLCHAR 0 64 "\",\"" 24 origRSVPAudioStat Latin1_General_CI_AS
25 SQLCHAR 0 64 "\"," 25 origRSVPVideoStat Latin1_General_CI_AS
26 SQLCHAR 0 12 "," 26 destLegIdentifier ""
27 SQLCHAR 0 12 "," 27 destNodeId ""
28 SQLCHAR 0 12 "," 28 destSpan ""
29 SQLCHAR 0 12 ",\"" 29 destIpAddr ""
30 SQLCHAR 0 50 "\",\"" 30 originalCalledPartyNumber Latin1_General_CI_AS
31 SQLCHAR 0 50 "\",\"" 31 finalCalledPartyNumber Latin1_General_CI_AS
32 SQLCHAR 0 50 "\"," 32 finalCalledPartyUnicodeLoginUserID Latin1_General_CI_AS
33 SQLCHAR 0 12 "," 33 destCause_location ""
34 SQLCHAR 0 12 "," 34 destCause_value ""
35 SQLCHAR 0 12 "," 35 destPrecedenceLevel ""
36 SQLCHAR 0 12 "," 36 destMediaTransportAddress_IP ""
37 SQLCHAR 0 12 "," 37 destMediaTransportAddress_Port ""
38 SQLCHAR 0 12 "," 38 destMediaCap_payloadCapability ""
39 SQLCHAR 0 12 "," 39 destMediaCap_maxFramesPerPacket ""
40 SQLCHAR 0 12 "," 40 destMediaCap_g723BitRate ""
41 SQLCHAR 0 12 "," 41 destVideoCap_Codec ""
42 SQLCHAR 0 12 "," 42 destVideoCap_Bandwidth ""
43 SQLCHAR 0 12 "," 43 destVideoCap_Resolution ""
44 SQLCHAR 0 12 "," 44 destVideoTransportAddress_IP ""
45 SQLCHAR 0 12 ",\"" 45 destVideoTransportAddress_Port ""
46 SQLCHAR 0 64 "\",\"" 46 destRSVPAudioStat Latin1_General_CI_AS
47 SQLCHAR 0 64 "\"," 47 destRSVPVideoStat Latin1_General_CI_AS
48 SQLCHAR 0 12 "," 48 dateTimeConnect ""
49 SQLCHAR 0 12 ",\"" 49 dateTimeDisconnect ""
50 SQLCHAR 0 50 "\",\"" 50 lastRedirectDn Latin1_General_CI_AS
51 SQLCHAR 0 50 "\",\"" 51 pkid Latin1_General_CI_AS
52 SQLCHAR 0 50 "\",\"" 52 originalCalledPartyNumberPartition Latin1_General_CI_AS
53 SQLCHAR 0 50 "\",\"" 53 callingPartyNumberPartition Latin1_General_CI_AS
54 SQLCHAR 0 50 "\",\"" 54 finalCalledPartyNumberPartition Latin1_General_CI_AS
55 SQLCHAR 0 50 "\"," 55 lastRedirectDnPartition Latin1_General_CI_AS
56 SQLCHAR 0 12 ",\"" 56 duration ""
57 SQLCHAR 0 129 "\",\"" 57 origDeviceName Latin1_General_CI_AS
58 SQLCHAR 0 129 "\"," 58 destDeviceName Latin1_General_CI_AS
59 SQLCHAR 0 12 "," 59 origCallTerminationOnBehalfOf ""
60 SQLCHAR 0 12 "," 60 destCallTerminationOnBehalfOf ""
61 SQLCHAR 0 12 "," 61 origCalledPartyRedirectOnBehalfOf ""
62 SQLCHAR 0 12 "," 62 lastRedirectRedirectOnBehalfOf ""
63 SQLCHAR 0 12 "," 63 origCalledPartyRedirectReason ""
64 SQLCHAR 0 12 "," 64 lastRedirectRedirectReason ""
65 SQLCHAR 0 12 ",\"" 65 destConversationId ""
66 SQLCHAR 0 50 "\"," 66 globalCallId_ClusterID Latin1_General_CI_AS
67 SQLCHAR 0 12 ",\"" 67 joinOnBehalfOf ""
68 SQLCHAR 0 2048 "\",\"" 68 comment Latin1_General_CI_AS
69 SQLCHAR 0 50 "\"," 69 authCodeDescription Latin1_General_CI_AS
70 SQLCHAR 0 12 ",\"" 70 authorizationLevel ""
71 SQLCHAR 0 32 "\"," 71 clientMatterCode Latin1_General_CI_AS
72 SQLCHAR 0 12 "," 72 origDTMFMethod ""
73 SQLCHAR 0 12 "," 73 destDTMFMethod ""
74 SQLCHAR 0 12 "," 74 callSecuredStatus ""
75 SQLCHAR 0 12 "," 75 origConversationId ""
76 SQLCHAR 0 12 "," 76 origMediaCap_Bandwidth ""
77 SQLCHAR 0 12 ",\"" 77 destMediaCap_Bandwidth ""
78 SQLCHAR 0 32 "\n" 78 authorizationCodeValue Latin1_General_CI_AS
CREATE TABLE [dbo].[CDR](
[cdrRecordType] [int] NOT NULL,
[globalCallID_callManagerId] [int] NOT NULL,
[globalCallID_callId] [int] NOT NULL,
[origLegCallIdentifier] [int] NOT NULL,
[dateTimeOrigination] [int] NOT NULL,
[origNodeId] [int] NOT NULL,
[origSpan] [int] NOT NULL,
[origIpAddr] [int] NOT NULL,
[callingPartyNumber] [varchar](50) NULL,
[callingPartyUnicodeLoginUserID] [varchar](50) NULL,
[origCause_location] [int] NOT NULL,
[origCause_value] [int] NOT NULL,
[origPrecedenceLevel] [int] NOT NULL,
[origMediaTransportAddress_IP] [int] NOT NULL,
[origMediaTransportAddress_Port] [int] NOT NULL,
[origMediaCap_payloadCapability] [int] NOT NULL,
[origMediaCap_maxFramesPerPacket] [int] NOT NULL,
[origMediaCap_g723BitRate] [int] NOT NULL,
[origVideoCap_Codec] [int] NOT NULL,
[origVideoCap_Bandwidth] [int] NOT NULL,
[origVideoCap_Resolution] [int] NOT NULL,
[origVideoTransportAddress_IP] [int] NOT NULL,
[origVideoTransportAddress_Port] [int] NOT NULL,
[origRSVPAudioStat] [varchar](64) NULL,
[origRSVPVideoStat] [varchar](64) NULL,
[destLegIdentifier] [int] NOT NULL,
[destNodeId] [int] NOT NULL,
[destSpan] [int] NOT NULL,
[destIpAddr] [int] NOT NULL,
[originalCalledPartyNumber] [varchar](50) NULL,
[finalCalledPartyNumber] [varchar](50) NULL,
[finalCalledPartyUnicodeLoginUserID] [varchar](50) NULL,
[destCause_location] [int] NOT NULL,
[destCause_value] [int] NOT NULL,
[destPrecedenceLevel] [int] NOT NULL,
[destMediaTransportAddress_IP] [int] NOT NULL,
[destMediaTransportAddress_Port] [int] NOT NULL,
[destMediaCap_payloadCapability] [int] NOT NULL,
[destMediaCap_maxFramesPerPacket] [int] NOT NULL,
[destMediaCap_g723BitRate] [int] NOT NULL,
[destVideoCap_Codec] [int] NOT NULL,
[destVideoCap_Bandwidth] [int] NOT NULL,
[destVideoCap_Resolution] [int] NOT NULL,
[destVideoTransportAddress_IP] [int] NOT NULL,
[destVideoTransportAddress_Port] [int] NOT NULL,
[destRSVPAudioStat] [varchar](64) NULL,
[destRSVPVideoStat] [varchar](64) NULL,
[dateTimeConnect] [int] NOT NULL,
[dateTimeDisconnect] [int] NOT NULL,
[lastRedirectDn] [varchar](50) NULL,
[pkid] [uniqueidentifier] NOT NULL,
[originalCalledPartyNumberPartition] [varchar](50) NULL,
[callingPartyNumberPartition] [varchar](50) NULL,
[finalCalledPartyNumberPartition] [varchar](50) NULL,
[lastRedirectDnPartition] [varchar](50) NULL,
[duration] [int] NOT NULL,
[origDeviceName] [varchar](129) NULL,
[destDeviceName] [varchar](129) NULL,
[origCallTerminationOnBehalfOf] [int] NOT NULL,
[destCallTerminationOnBehalfOf] [int] NOT NULL,
[origCalledPartyRedirectOnBehalfOf] [int] NOT NULL,
[lastRedirectRedirectOnBehalfOf] [int] NOT NULL,
[origCalledPartyRedirectReason] [int] NOT NULL,
[lastRedirectRedirectReason] [int] NOT NULL,
[destConversationId] [int] NOT NULL,
[globalCallId_ClusterID] [varchar](50) NULL,
[joinOnBehalfOf] [int] NOT NULL,
[comment] [varchar](2048) NULL,
[authCodeDescription] [varchar](50) NULL,
[authorizationLevel] [int] NOT NULL,
[clientMatterCode] [varchar](32) NULL,
[origDTMFMethod] [int] NOT NULL,
[destDTMFMethod] [int] NOT NULL,
[callSecuredStatus] [int] NOT NULL,
[origConversationId] [int] NOT NULL,
[origMediaCap_Bandwidth] [int] NOT NULL,
[destMediaCap_Bandwidth] [int] NOT NULL,
[authorizationCodeValue] [varchar](32) NULL,
CONSTRAINT [PK_CDR] PRIMARY KEY CLUSTERED
(
[pkid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF
, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Files](
[id] [int] IDENTITY(1,1) NOT NULL,
[Source_Id] [int] NOT NULL,
[filename] [nvarchar](50) NULL,
[dts] [timestamp] NOT NULL,
CONSTRAINT [PK_Files] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF
, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Source](
[id] [int] IDENTITY(1,1) NOT NULL,
[unc_path] [nvarchar](255) NULL,
[name] [nvarchar](50) NULL,
CONSTRAINT [PK_Source] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [CDR].[dbo].[Source]
([unc_path]
,[name])
VALUES
('\\server\share'
,'ftpserver1')
CREATE PROCEDURE [dbo].[sp_ListFiles]
@PCWrite varchar(2000),
@DBTable varchar(100)= NULL,
@PCIntra varchar(100)= NULL,
@PCExtra varchar(100)= NULL,
@DBUltra bit = 0
AS
SET NOCOUNT ON
DECLARE @Return int
DECLARE @Retain int
DECLARE @Status int
SET @Status = 0
DECLARE @Task varchar(2000)
DECLARE @Work varchar(2000)
DECLARE @Wish varchar(2000)
SET @Work = 'DIR ' + '"' + @PCWrite + '"'
CREATE TABLE #DBAZ (Name varchar(400), Work int IDENTITY(1,1))
INSERT #DBAZ EXECUTE @Return = master.dbo.xp_cmdshell @Work
SET @Retain = @@ERROR
IF @Status = 0 SET @Status = @Retain
IF @Status = 0 SET @Status = @Return
IF (SELECT COUNT(*) FROM #DBAZ) < 4
BEGIN
SELECT @Wish = Name FROM #DBAZ WHERE Work = 1
IF @Wish IS NULL
BEGIN
RAISERROR ('General error [%d]',16,1,@Status)
END
ELSE
BEGIN
RAISERROR (@Wish,16,1)
END
END
ELSE
BEGIN
SET DATEFORMAT dmy
DELETE #DBAZ WHERE ISDATE(SUBSTRING(Name,1,10)) = 0 OR SUBSTRING
(Name,40,1) = '.' OR Name LIKE '%.lnk'
IF @DBTable IS NULL
BEGIN
SELECT SUBSTRING(Name,40,100) AS Files
FROM #DBAZ
WHERE 0 = 0
AND (@DBUltra = 0 OR Name LIKE '%<DIR>%')
AND (@DBUltra != 0 OR Name NOT LIKE '%<DIR>%')
AND (@PCIntra IS NULL OR SUBSTRING(Name,40,100) LIKE @PCIntra)
AND (@PCExtra IS NULL OR SUBSTRING(Name,40,100) NOT LIKE @PCExtra)
ORDER BY 1
END
ELSE
BEGIN
SET @Task = ' INSERT ' + REPLACE(@DBTable,CHAR(32),CHAR(95))
+ ' SELECT SUBSTRING(Name,40,100) AS Files'
+ ' FROM #DBAZ'
+ ' WHERE 0 = 0'
+ CASE WHEN @DBUltra = 0 THEN ''
ELSE ' AND Name LIKE ' + CHAR(39) + '%<DIR>%' + CHAR(39)
END
+ CASE WHEN @DBUltra != 0 THEN ''
ELSE ' AND Name NOT LIKE ' + CHAR(39) + '%<DIR>%' + CHAR(39)
END
+ CASE WHEN @PCIntra IS NULL THEN ''
ELSE ' AND SUBSTRING(Name,40,100) LIKE ' + CHAR(39) + @PCIntra + CHAR(39)
END
+ CASE WHEN @PCExtra IS NULL THEN ''
ELSE ' AND SUBSTRING(Name,40,100) NOT LIKE ' + CHAR(39) + @PCExtra + CHAR(39)
END
+ ' ORDER BY 1'
IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR
IF @Status = 0 SET @Status = @Return
END
END
DROP TABLE #DBAZ
SET NOCOUNT OFF
RETURN (@Status)
CREATE PROCEDURE [dbo].[usp_importdata]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @pathId int, @path nvarchar(255)
DECLARE @bulk varchar(255)
DECLARE @file varchar(255)
DECLARE source_cursor CURSOR
FOR select id as pathId, unc_path as [path] from Source
OPEN source_cursor
--iterate through paths
FETCH NEXT FROM source_cursor into @pathId, @path
WHILE @@FETCH_STATUS = 0
BEGIN
--get list of files in path
CREATE TABLE #Files (MyFile varchar(200))
EXECUTE sp_ListFiles @path,'#Files','%',NULL,0
DECLARE file_cursor CURSOR
FOR Select MyFile as [file] from #Files
Open file_cursor
FETCH NEXT FROM file_cursor into @file
WHILE @@FETCH_STATUS = 0
BEGIN
--import csv data in @path\@file
IF ( SELECT count(*) FROM Files where [filename]=@file and Source_Id=@pathId ) = 0
BEGIN
SET @Bulk = 'BULK INSERT CDR FROM ''' + @path + '\' + @file + ''' WITH (FORMATFILE=''h:\sqlfiles\cdrimport.fmt'', FIRSTROW=3) '
print @Bulk
EXEC ( @Bulk )
if @@error = 0
BEGIN
--insert into log, so that we don't import the same file twice
INSERT INTO Files ([filename], [source_Id])
VALUES (@file,@pathId)
-- DECLARE @work varchar(255)
-- SELECT @work = 'move ' + @path + '\' + @file + ' ' + @path + '\backup\' + @file
-- EXECUTE master.dbo.xp_cmdshell @work
END
END
FETCH NEXT FROM file_cursor into @file
END
CLOSE file_cursor
DEALLOCATE file_cursor
DROP TABLE #Files
FETCH NEXT FROM source_cursor INTO @pathId, @path
END
CLOSE source_cursor
DEALLOCATE source_cursor
END
select
callingPartyNumber, --who called
finalCalledPartyNumber, -- where the call ended up
originalCalledPartyNumber, -- where they tried to call
lastRedirectDn , -- who triggered the last forward
dateadd(second, dateTimeConnect + 36000 ,'1970-01-01') dtConnect,
dateadd(second, dateTimeDisconnect + 36000,'1970-01-01') dtDisconnect,
datetimedisconnect - datetimeconnect as duration
from CDR
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (20)
Commented:
This the error:
Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 9. Verify that the field terminator and row terminator are specified correctly.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".
Running SQL 2014 Express
Commented:
Msg 50000, Level 16, State 1, Procedure sp_ListFiles, Line 45
Access is denied.
(1 row(s) affected)
Any assistance would be appreciated.
Commented:
SELECT @Wish = Name FROM #DBAZ WHERE Work = 1
I am running this on SQL Server 2014 if that helps.
Commented:
Commented:
my cdr files are without header so what are the modifications i have to do with above code,
and i am using my pc as server so how i have to give unc path and its name please guide me??
View More