Bulk Insert Linked Server Error
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers bulk insert into linked server or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x sql server bulk insert error handling Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only sql server bulk insert from table takes a minute: Sign up sql server - bulk insert error up vote 7 down vote favorite 1 I am using bulk insert and getting below error: Note: The data in the load file is not beyong the configured column sql server bulk insert c# length Running Command: bulk insert load_data from 'C:\temp\dataload\load_file.txt' with (firstrow = 1, fieldterminator = '0x09', rowterminator = '\n',MAXERRORS = 0, ERRORFILE = 'C:\temp\dataload\load_file') Contents of load file: user_name file_path asset_owner city import_date admin C:\ admin toronto 04/12/2012 Error: Msg 4863, Level 16, State 1, Line 1 Bulk load data conversion error (truncation) for row 1, column 6 (validated). Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider
Sql Server Bulk Insert Example
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)". sql sql-server share|improve this question edited Dec 4 '12 at 23:01 Clockwork-Muse 8,41531733 asked Dec 4 '12 at 22:47 Bruce 77592143 Can you provide load_data schema? –Hamlet Hakobyan Dec 4 '12 at 22:53 Is column 6 by chance the last field in the row? It sounds like the field or rowterminator may be incorrect. I've seen this in the past. Since it doesn't find the terminator, it thinks the field/row extends on to the next field or row. Just a guess. –brian Dec 4 '12 at 22:55 You don't appear to have 6 columns... Also, formatting is a little different than you might otherwise expect here on SO, so I tweaked your data so that it lines up... which unfortunately resulted in the delimiter tabs going missing (although personally I prefer non-whitespace characters for this anyways). –Clockwork-Muse Dec 4 '12 at 23:08 add a comment| 2 Answers 2 active oldest votes up vote 8 down vote accepted The number of columns was incorrect. I had recently changed the table schema but forgotten to do a refresh on the table. share|improve this answer answered Dec 11 '12 at 20:05 Bruce 77592143 same error for me in the same situation: 8 table columns but
SQL Server experts to answer whatever question you can come up with. Our new SQL
Sql Server Bulk Insert Permission
Server Forums are live! Come on over! We've restricted the ability to sql server bulk insert excel create new threads on these forums. SQL Server Forums Profile | ActiveTopics | Members | Search | sql server bulk insert performance ForumFAQ Register Now and get your question answered! Username: Password: Save Password Forgot your Password? All Forums SQL Server 2008 Forums Transact-SQL (2008) BULK INSERT - Last column all http://stackoverflow.com/questions/13713351/sql-server-bulk-insert-error NULL Reply to Topic Printer Friendly Author Topic harlingtonthewizard Constraint Violating Yak Guru Australia 352 Posts Posted-08/17/2014: 23:25:02 I have the following table. If one or more of the rows for the last column contain a value then this code works. If all values in the csv for the last column have no value http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=195750 then it fails? What's interesting is if I make say Program field all with no value it also works.Create Table #t ([EntryDate] varchar(20), [Name] varchar(100),[ProgramDate] varchar(10), [Program] varchar(100), [Event] varchar(100), [Open_Over_35_Age_Group] varchar(100) NULL) Set @sql='BULK INSERT #t FROM "'+@path_name+'" WITH (DATAFILETYPE=''char'', FIELDTERMINATOR='','', ROWTERMINATOR = ''\n'', FIRSTROW = 1, KEEPNULLS);' Print @sql Exec(@sql)c:\SARWCStartList\SARWC_120814.csvBULK INSERT #t FROM "c:\SARWCStartList\SARWC_120814.csv" WITH (DATAFILETYPE='char', FIELDTERMINATOR=',', ROWTERMINATOR = '\n', FIRSTROW = 1, KEEPNULLS);Msg 4832, Level 16, State 1, Line 1Bulk load: An unexpected end of file was encountered in the data file.Msg 7399, Level 16, State 1, Line 1The 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 1Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".Examples;Works;EntryDate Name Bib No. ProgramDate Program Event Open_Over_35_Age_Group06/08/2014 12:14 Alix 1 Harlington 848 2014-08-09 Timed Walks 1 Hour OpenWorks;EntryDate Name Bib No. ProgramDate Program Event Open_Over_35_Age_Group06/08/2014 12:14 Alix 1 Harlington 848 2014-08-09 Timed Walks NULL OpenFails where input data (csv)
Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server 2005 » Business Intelligence » Bulk Insert Error: Linked Server "(null)" Bulk Insert Error: Linked Server "(null)" Rate http://www.sqlservercentral.com/Forums/Topic1006483-147-1.aspx Topic Display Mode Topic Options Author Message Brandie TarvinBrandie Tarvin Posted Monday, October 18, 2010 12:24 PM SSCertifiable Group: General Forum Members Last Login: Today @ 12:07 PM Points: 7,346, Visits: 8,497 This one is driving me batty. Please read ALL the details before responding. There's a kicker at post-end.We converted a DTS pkg to SSIS 4 years ago. We ended up with several Bulk Insert Tasks which worked beautifully. Now I need to set up a Bulk Insert bulk insert Task using the same table (different name) and same file on another database. So we scripted out the original table, SANS foreign keys, ran that code on the new database and created a new package to take that file to the new table.Bulk Insert Task is exactly set up the same in this package as it is in the other package. Connection: MyDBDestinationTable: MyDB.dbo.NewTableNameFormat: SpecifyRowDelimiter: {CR}{LF}Column Delimeter: ~File: MyFileConnMgrCodePage: OEMDataFileType: charBatchSize: 0LastRow: 0FirstRow: 0Options: (nothing checked)SortedData: MaxErrors: 0When it kept sql server bulk erroring out, I even copied the other package, deleted unnecessary schtuff, pointed the Bulk Insert Task to the new DB and table. Still getting the damn error.SSIS ErrorBulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Cannot bulk load because the maximum number of errors (10) was exceeded.Bulk load data conversion error (truncation) for row 11, column 38 (Column38)...