June 5, 2009 at 6:37 am
Hello,
I am trying to load a text files (tab delimited) into a table in SQL Server 2005 table using bulk insert.
here is the SQL i am using:
bulk
insert audit_test.dbo.audit_IIS_log
from 'C:\exp090411_test1.log'
with
(fieldterminator='\r',
rowterminator='', errorfile='C:\exp090411_test1.error'
)
GO
and got the "type mismatch" error below
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (datetime).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (datetime).
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
here is my table structure
CREATE TABLE [dbo].[Audit_IIS_Log](
[datetime] [smalldatetime] NULL,
[s-ip] [varchar](50) NULL,
[cs-method] [varchar](50) NULL,
[cs-uri-stem] [varchar](50) NULL,
[cs-uri-query] [nchar](10) NULL,
[s-port] [varchar](50) NULL,
[cs-username] [varchar](90) NULL,
[c-ip] [varchar](50) NULL,
[cs(user-Agent)] [varchar](500) NULL,
[sc-status] [nchar](10) NULL,
[sc-substatus] [nchar](30) NULL,
[sc-win32-status] [nchar](10) NULL
) ON [PRIMARY]
GO
here is what the text file look like
exp090411_test1.log
#Software: Microsoft Internet Information Services 6.0
#Version: 1.0
#Date: 2009-04-11 04:00:08
#Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) sc-status sc-substatus sc-win32-status
2009-04-11 04:00:08 172.23.18.251 POLL /exchange/mgriswold/Inbox - 443 - 72.17.136.226 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727;+.NET+CLR+3.0.04506.30;+.NET+CLR+3.0.04506.648;+InfoPath.1;+.NET+CLR+3.0.4506.2152;+.NET+CLR+3.5.30729) 401 2 2148074254
2009-04-11 04:00:11 172.23.18.251 POLL /exchange/briskerac/Inbox - 443 - 74.126.66.85 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+GTB5) 401 2 2148074254
My questions: is the use of the data type "smalldatetype" approriate here? THANKS in advance!
June 5, 2009 at 8:38 am
to test you may want to convert your datetime to a character field, have a look at it, and begin trying to cast() or convert() the values.
June 5, 2009 at 8:41 am
SMALLDATETIME is fine for your data.
SELECT CAST('2009-04-11 04:00:11' AS SMALLDATETIME)
I would check your field and row terminator options. Is the data file as is in your post?
June 5, 2009 at 11:44 am
Is the data file as is in your post?
Yes excpet that I only posted the first three rows there
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply