Fmt Could Not Be Read. Operating System Error Code Null
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 bulk load: an unexpected end of file was encountered in the data file. Stack Overflow the company Business Learn more about hiring developers or posting ads with bulk insert format file us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is
Bulk Insert In Sql Server
a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Bulk Insert w/ .fmt file: Operating system error code (null) up vote 7 down vote favorite 1 I'm trying to import a text file defined by a .fmt format. Instead of being comma-delimited, this text file is what I would call column-delimited (i.e. the first 8 characters is the first field, the next 3 characters is the second, etc). When I run the query below, I get a "Operating system error code (null)" message, which is odd since I'm using SQL Server 2008r2 with Vista. Please explain to me what this error message means and how do I get around it? I've googled it and found similar questions on other forums, but they're never really answered. Here's my query: BULK INSERT LoadTable FROM '\\Dev2\Queries\Test.txt' WITH (FIRSTROW = 2, FORMATFILE = '\\Dev2\fmt\Test.fmt', KEEPNULLS) Here's the error I get: Cannot bulk load because the file "\\Dev2\fmt\test.fmt" could not be read. Operating system error code (null). Here's what Test.fmt contains: 9.0 7 1 SQLCHAR 0 8 "" 1 Record_Control_Data SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 3 "" 2 Filler "" 3 SQLCHAR 0 1 "" 3 Member_Code SQL_Latin1_General_CP1_CI_AS 4 SQLCHAR 0 2 "" 4 Member_Sequence_Number SQL_Latin1_General_CP1_CI_AS 5 SQLCHAR 0 9 "" 5 Participant_SSN SQL_Latin1_General_CP1_CI_AS 6 SQLCHAR 0 20 "" 6 LastName SQL_Latin1_General_CP1_CI_AS 7 SQLCHAR 0 250 "\r\n" 7 Filler10 SQL_Latin1_General_CP1_CI_AS (This test.fmt and test.txt is a simplified version of a dataset with 120 columns. But the error message is the same. Once I can get this test version running, I'll apply the fix to the real data) Edit In response to the other half-answers around the web, I should have complete read/write permission to these folders & files. sql-server sql-server-2008 tsql sql-server-2008-r2 share|improve this question edited Nov 22 '11 at 16:27 asked Nov 22 '11 at 16:16 PowerU
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 or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. http://stackoverflow.com/questions/8229989/bulk-insert-w-fmt-file-operating-system-error-code-null Join them; it only takes a minute: Sign up C:\Temp\PetSales.fmt could not be read. Operating system error code (null) up vote 0 down vote favorite I am trying to import some data from a text file into a table in my database. However i get the following error: "C:\Temp\PetSales.fmt could not be read. Operating system error code (null)" What http://stackoverflow.com/questions/9593595/c-temp-petsales-fmt-could-not-be-read-operating-system-error-code-null does this mean? Im wondering is it because i have put 0 for the destination columns which i dont want to import into my table (Column 3, 5, 6)? Sales = My table PetDatabase = The name of my database holding queries, tables, etc. P1 = Is my text file with 14 columns PetSales.FMT = Is my notepad file which i have changed the extension to FMT. My following SQL statement: BULK INSERT Sales FROM 'C:\Temp\P1.txt' WITH (FORMATFILE = 'C:\Temp\PetSales.FMT') Format file: 10.0 14 1 SQLDATETIME 0 0 "," 3 Date SQL_Latin1_General_CP1_CI_AS 2 SQLDATETIME 0 0 "," 4 Time SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 0 "," 0 Pet SQL_Latin1_General_CP1_CI_AS 4 SQLCHAR 0 0 "," 1 order SQL_Latin1_General_CP1_CI_AS 5 SQLCHAR 0 0 "," 0 Pet SQL_Latin1_General_CP1_CI_AS 6 SQLCHAR 0 0 "," 0 Pet SQL_Latin1_General_CP1_CI_AS 7 SQLCHAR 0 0 "," 5 Desc SQL_Latin1_General_CP1_CI_AS 8 SQLCHAR 0 0 "," 6 Tele SQL_Latin1_General_CP1_CI_AS 9 SQLCHAR 0 0 "," 7 Mob SQL_Latin1_General_CP1_CI_AS 10 SQLCHAR 0 0 "," 8 Customer SQL_Latin1_General_CP1_CI_AS 11 SQLCHAR 0 0 "," 9 Address 1 SQL_Latin1_General_CP1_CI_AS 12 SQLCH
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: how to create a fmt with nulls?? SQL Server > Transact-SQL Question 0 https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bccbf690-5519-4552-b1f0-748b1c97d091/how-to-create-a-fmt-with-nulls?forum=transactsql Sign in to vote Hi if i am not in the correct post please dont erase it move it please i REALLY need help with this.... I am trying to create a fmt to use it with the bulk insert and load this kind of registries (this is an example): this are SOME OF THE regystries in .Del format i create.... really there bulk insert are like 1'000.000 10@"ABASTECEDORA NAVAL Y INDUSTRIAL, S.A."@-0- @"USA"@-0- @-0- @-0- @-0- @-0- @-0- @-0- @-0- 15@"ABDELNUR, Nury de Jesus"@"individual"@"COLOMBIA"@-0- @-0- @-0- @-0- @-0- @-0- @-0- @-0- 22@"ACECHILLY"@"vessel"@"MEXICO"@-0- @-0- @"Unknown vessel type"@-0- @-0- @-0- @"Acechilly Navigation Co., Malta"@-0- 24@"ACEFROSTY"@"vessel"@"CUBA"@-0- @-0- @"Unknown vessel type"@-0- @-0- @-0- @"Acefrosty Shipping Co., Malta"@-0- 25@"ACEFROSTY SHIPPING CO., LTD."@-0- @"ESPAÑA"@-0- @-0- @-0- @-0- @-0- @-0- @-0- @-0- 36@"AEROCARIBBEAN AIRLINES"@-0- @"USA"@-0- fmt could not @-0- @-0- @-0- @-0- @-0- @-0- @-0- 39@"AEROTAXI EJECUTIVO, S.A."@-0- @"USA"@-0- @-0- @-0- @-0- @-0- @-0- @-0- @-0- 41@"AGENCIA DE VIAJES GUAMA"@-0- @"USA"@-0- @-0- @-0- @-0- @-0- @-0- @-0- @-0- 66@"AGUILLAR, Rodolfo"@"individual"@"CUBA"@"Director, Banco Nacional de Cuba"@-0- @-0- @-0- @-0- @-0- @-0- @"; Director, Banco Nacional de Cuba." 137@"ALEGRIA DE PIO"@"vessel"@"ESPAÑA"@-0- @-0- @"Unknown vessel type"@-0- @-0- @-0- @"Naviera Maritima de Arosa, Spain"@-0- the table seem like this: USE DataBase create table TableN ( ent_num numeric(18), SDN_Name text, SDN_Type text, Program text, Title text, Call_Sign text, Vess_Type text, Tonnage text, GRT text, Vess_flag text, Vess_owner text, remarks text ) as show the -0- i need it to be inserted as null in the database and the closest I be for the fmt file was this: this is the most aproach of my .fmt but the registries are stored not as I need it to.... 9.0 34 1 SQLCHAR 0 18 "@" 1 ent_num "" 2 SQLCHAR 0 0 "\"" 0 quotat1 "" 3 SQLCHAR 0 350 "\"" 2 SDN_Name SQL_Latin1_General_CP1_CI