|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-19 : 09:13:34
|
| Al writes "I’m trying to build a database (SQL 7.0), which will contain Windows NT Security Event log entries. The log entries have been extracted from the servers on our domain into comma delimited text files. After some initial problems, I have been able to import these records using DTS, but have not yet succeeded using BULK INSERT or BCP. I suspect the problem has to do with the fact that the Security Event log records are not all of the same length. (Maximum length is 35, but most records have fewer fields. I was able to overcome this problem when using DTS by inserting a dummy record as the first record of my text file. This record consists of enough commas to tell fix the record length for DTS. Strangely, the same fix does not solve the problem with BULK INSERT.To replicate this situation, I have provided the following:CREATE TABLE [dbo].[Load_File] ( [LogType] [varchar] (3) NULL , [Date] [varchar] (10) NULL , [Time] [varchar] (8) NULL , [LogType2] [char] (3) NULL , [EventID] [varchar] (5) NULL , [Type] [char] (7) NULL , [Category] [varchar] (25) NULL , [DomainUser] [varchar] (50) NULL , [Computer] [varchar] (50) NULL , [Description] [varchar] (200) NULL , [Col_K] [varchar] (100) NULL , [Col_L] [varchar] (100) NULL , [Col_M] [varchar] (100) NULL , [Col_N] [varchar] (100) NULL , [Col_O] [varchar] (255) NULL , [Col_P] [varchar] (255) NULL , [Col_Q] [varchar] (255) NULL , [Col_R] [varchar] (255) NULL , [Col_S] [varchar] (255) NULL , [Col_T] [varchar] (255) NULL , [Col_U] [varchar] (255) NULL , [Col_V] [varchar] (255) NULL , [Col_W] [varchar] (255) NULL , [Col_X] [varchar] (255) NULL , [Col_Y] [varchar] (255) NULL , [Col_Z] [varchar] (255) NULL , [Col_AA] [varchar] (255) NULL , [Col_AB] [varchar] (255) NULL , [Col_AC] [varchar] (255) NULL , [Col_AD] [varchar] (255) NULL , [Col_AE] [varchar] (255) NULL , [Col_AF] [varchar] (255) NULL , [Col_AG] [varchar] (255) NULL , [Col_AH] [varchar] (255) NULL , [Col_AI] [varchar] (255) NULL ) ON [PRIMARY]GOBULK INSERT Load_File FROM 'load_file.txt' WITH ( FIELDTERMINATOR = ',', FIRSTROW = 2, KEEPNULLS, ROWTERMINATOR = '\n' )(FirstRow = 2: To omit the dummy record.)I get this error message, which seems to confirm my suspicion that the problem is the variable record length:Server: Msg 4863, Level 16, State 1, Line 1Bulk insert data conversion error (truncation) for row 2, column 35 (Col_AI).Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.The statement has been terminated.Sample Load_File.txt:A,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,AISEC,06/13/2002,09:31:54,Security,538,Success,Logon/Logoff ,BANGKOKDOMF1\BoonnakRA,BANGKOKSNTSAC,User Logoff:,` User Name: BoonnakRA,` Domain: BANGKOKDOMF1,` Logon ID: (0x0 0x3F6E6A9),` Logon Type: 2,` SEC,06/13/2002,09:31:54,Security,538,Success,Logon/Logoff ,BANGKOKDOMF1\BoonnakRA,BANGKOKSNTSAC,User Logoff:,` User Name: BoonnakRA,` Domain: BANGKOKDOMF1,` Logon ID: (0x0 0x3F6E6BD),` Logon Type: 3,` SEC,06/13/2002,09:31:54,Security,528,Success,Logon/Logoff ,BANGKOKDOMF1\BoonnakRA,BANGKOKSNTSAC,Successful Logon:,` User Name: BoonnakRA,` Domain: BANGKOKDOMF1,` Logon ID: (0x0 0x3F6EF4A),` Logon Type: 2,` Logon Process: Advapi ,` Authentication Package: MICROSOFT_AUTHENTICATION_PACKAGE_V1_0,` Workstation Name: BANGKOKSNTSAC SEC,06/13/2002,09:31:54,Security,528,Success,Logon/Logoff ,BANGKOKDOMF1\BoonnakRA,BANGKOKSNTSAC,Successful Logon:,` User Name: BoonnakRA,` Domain: BANGKOKDOMF1,` Logon ID: (0x0 0x3F6EF5D),` Logon Type: 3,` Logon Process: KSecDD,` Authentication Package: MICROSOFT_AUTHENTICATION_PACKAGE_V1_0,` Workstation Name: \\BANGKOKSNTSAC |
|
Unable to load text file using BULK INSERT 