I/O Error Reading BCP Format File
Jul 20, 2000
I added a field to an exisiting table (CHAR 30), and I added to field to the BCP Format File.
The BCP worked fine before the new field, and still works fine when I exclude the new field
from the format file, but with the new field I receive the following error:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP format file
(2 row(s) affected)
Here is a copy of my format file:
7.0
11
1 SQLCHAR 0 6 "" 1 Empl_ser_no
2 SQLCHAR 0 2 "" 2 Empl_stat_cd
3 SQLCHAR 0 1 "" 3 Guest_type_fl
4 SQLCHAR 0 30 "" 4 Job_tl
5 SQLCHAR 0 14 "" 5 Pager_no
6 SQLCHAR 0 1 "" 6 Security_Clear_fl
7 SQLCHAR 0 9 "" 7 SSN
8 SQLCHAR 0 30 "" 8 Emer_cont_nm
9 SQLCHAR 0 14 "" 9 Emer_cont_no
10 SQLCHAR 0 1 "" 10 Citizen_fl
11 SQLCHAR 0 30 "
" 11 Job_Res
The input file is just a fixed positonal text file, and I have checkid it 100 times- it is fine.
Any sugestions from any BCP gurus out there? THANKS
View 1 Replies
ADVERTISEMENT
Feb 29, 2000
Hi All,
I had a problem today where BCP would not read the format file I created giving the following error:
I/O error while reading BCP format file.
I searched the archives here at swynk.com and found a thread titled exactly the same as the one I am now creating. There were numerous responses to this thread but none of them seemed to solve the problem for me (short of the one that suggested using BCP to create the format file, which I didn't want to do).
I used BCP to create a format file for me (which looked identical to the format file that I created) and it worked fine. With my suspicions aroused, I used a binary file viewer to look at the contents of each file and highlight the differences. Apart from the usual variations in whitespace I noticed that the my file did not have any carriage returnline feed at the end of the last row definition.
My format file ended at the end of the last row definition line. It did not have any carriage returns and line feeds. I matched the one generated by BCP and added the following "
" to the end of the file, tested my format file and it worked perfectly.
The moral of the story is, make sure when you create a format file, you have an empty line at the end of the file (ie: after your last "Server Column Name", make sure you have "
" sans quotes).
Hope this saves someone a few hours :-)
Cheers,
Dan.
View 1 Replies
View Related
Feb 4, 2005
I have a report that is based on a query that is read from a .sql file and is executed in the stored procedure. I found this code on the internet. When I run the SP in Query Analyser it tells me that "2 rows have been affected". I don't know why I don't get the data. Any help??
Stored procedure:
CREATE PROCEDURE TimeTracker_Report_ClientNumbers2
AS
DECLARE @SQLQuery as NVARCHAR(4000)
BEGIN
CREATE TABLE #tmpQuery (Query NVARCHAR(4000))
BULK INSERT #tmpQuery FROM '\servernameQueriesReport_ClientNumbers.sql'
SELECT @SQLQuery = Query FROM #TMPQUERY
EXECUTE sp_executesql @SQLQuery
END
GO
and Report_ClientNumbers.sql contains the following query:
SELECT Name, ClientNumber FROM Companies WHERE Not ClientNumber IS NULL ORDER BY ClientNumber
View 1 Replies
View Related
Oct 1, 2007
Msg 4862, Level 16, State 1, Server PATH-SQLDEV, Line 2
Cannot bulk load because the file "c:DATABATCHBCPFormat.fmt" could not be read. Operating system error code (null).
Above is the error I get. The problem is I do not know what is causing this error. It occurs when I attempt to use SQLCMD with bulk insert.
I am using SQLserver 2005 and I have a similar set up in a test database that works, why this format file does not is beyond me, but my experience is when the format file has an error in it, such as a mispelled datatype or a incorrect column number, the error will zero in on that, rather then declare the entire file unreadable. Furthermore if I go into the file, change something so where it is incorrect (like a column number) it will zero in on that error. So I know that the format file can be read. If I knew what this error was all about I would at least know where to begin in fixing it. I have also tried using a very small sample file for the data being inserted. Same error.
Please help
View 2 Replies
View Related
Nov 9, 2014
I am trying to load a fixed width text file using `Bulk Insert` and a XML format file. I have used the same process and XML file on another fixed width, except with less columns.
Error
Msg 4857, Level 16, State 1, Line 16
Line 4 in format file "PATHCaddr.xml": Attribute "type" could not be specified for this type.
SQL Server Table
[code lang="sql"]
create table [dbo].[raw_addr](
address_numbervarchar(max),
addr_linelvarchar(max),
addr_line2varchar(max),
street_novarchar(max),
[code]....
View 0 Replies
View Related
Jun 29, 2015
I'm trying to use Bulk insert for the first time and getting the following error. I think it might have something to do with my Format File and from the error msg there's a conversion error for the first column. In my database the Field is nvarchar(6) so my best guess is to use SQLNChar for the first column. I've checked the end of each line is CR LF therefore the is correct for line 7 right?
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (ASXCode).
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)".
BULK
INSERTtbl_ASX_Data_temp
FROM
'M:DataASXImportTest.txt'
WITH
(FORMATFILE='M:DataASXSQLFormatImport.Fmt')
[code]...
View 5 Replies
View Related
Oct 1, 2010
When creating xml fileformat its throwing me error "invalid ordinal".
When created non-xml file format, no error, and was also able to load data file into sql table. Not sure why bcp (Version: 10.50.1600.1) is not able to create xml file format.
C:>BCP "MyGDB.dbo.Items_Import" format nul -f"C:AnkitTempBCPItemsMaster.xml" -x -w -T -S"(Local)"
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid ordinal for field 2 in xml format file.
Column_nameTypeComputedLengthPrecScaleNullableTrimTrailingBlanksFixedLenNullInSourceCollation
Item Numbervarcharno18 noyesnoSQL_Latin1_General_CP1_CI_AS
Description1nvarcharno80 yes(n/a)(n/a)SQL_Latin1_General_CP1_CI_AS
Description2nvarcharno80 yes(n/a)(n/a)SQL_Latin1_General_CP1_CI_AS
UMvarcharno3 yesyesyesSQL_Latin1_General_CP1_CI_AS
View 1 Replies
View Related
Apr 11, 2007
My colleague is working on bulk insert task from SSIS and since the data file does not contain any valid delimeter one of the suggestion he got is to use a file format to address the issue. Thus a bcp command is used to generate the format file, as per below.
bcp <database name>.dbo.<table name> format nul -T -S <server name> -n -f out.fmt
The file file format was generated, from the data flow we added the BULK INSERT task and set the properties accordingly including the File Format and location of the file. Upon running the task itself we encountered the error as per below.
[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot bulk load. The file "C:HFISTAT.fmt" does not exist.".
Progress: The Bulk Insert task is completed. - 100 percent complete
Task Bulk Insert Task failed
Have checked the file and it is in C: drive and it is not protected or read-only. Validated the output file and it is as per expected. Any help would be appreciated very much.
View 7 Replies
View Related
Mar 27, 2007
Hi all,
ISSUE:
====================
In SQL 2005 (sp2) I get the following error when preforming a bulk
insert with an associated xml format file:
"Could not bulk insert. Unknown version of format file"
Question:
====================
I am unsure what they mean by "unknown version". Specifically the
format file in question was created using bcp. Also the entire table
scenario was created from a msdn example.
Any ideas? have you seen this before?
NOTE: i can reproduce this issue outside the example but will refer to
msdn considering it is simple and easily reproducible.
Scenario
====================
I can reproduce this error with the BULK INSERT example discussed on
msdn (example A)
http://msdn2.microsoft.com/en-us/library/ms191234.aspx
TO REPRODUCE:
* In short the table structure is:
Person (Age int, FirstName varchar(20), LastName varchar(30))
* Data File Template:
Age<tab>Firstname<tab>Lastname<return>
* xml file format from bcp (and described on msdn)
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR=" "
MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=" "
MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="
"
MAX_LENGTH="30"
COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="age" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="firstname" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="lastname" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
* Here is some the actual sql statement that pulls this all together
BULK INSERT mytestnames
FROM 'C:datatestexampledata-c.Dat'
WITH (FORMATFILE = 'C:datatestexamplefmt.Fmt');
Thanks in advanced for any feedback.
Cheers!
View 1 Replies
View Related
Mar 27, 2007
Below is an overview of my problem:
ISSUE:
====================
In SQL 2005 (sp2) I get the following error when preforming a bulk
insert with an associated xml format file:
"Could not bulk insert. Unknown version of format file"
Question:
====================
I am unsure what they mean by "unknown version". Specifically the
format file in question was created using bcp. Also the entire table
scenario was created from a msdn example.
Any ideas? have you seen this before?
NOTE: i can reproduce this issue outside the example but will refer to
msdn considering it is simple and easily reproducible.
Scenario
====================
I can reproduce this error with the BULK INSERT example discussed on
msdn (example A)
http://msdn2.microsoft.com/en-us/library/ms191234.aspx
TO REPRODUCE:
* In short the table structure is:
Person (Age int, FirstName varchar(20), LastName varchar(30))
* Data File Template:
Age<tab>Firstname<tab>Lastname<return>
* xml file format from bcp (and described on msdn)
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR=" "
MAX_LENGTH="12"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=" "
MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="
"
MAX_LENGTH="30"
COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="age" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="firstname" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="lastname" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>
* Here is some the actual sql statement that pulls this all together
BULK INSERT mytestnames
FROM 'C:datatestexampledata-c.Dat'
WITH (FORMATFILE = 'C:datatestexamplefmt.Fmt');
Thanks in advanced for any feedback.
View 1 Replies
View Related
Dec 4, 2014
I am trying to create and later read a data file from a package deployed in SSISDB, but it is not reading it while it is successfully creating the file. The same package when run from the file system package, runs successfully. Generating ispac and deploying in SSISDB is running for infinite time. Is it a permission issue?
View 7 Replies
View Related
Jun 5, 2015
I try to import data with bulk insert. Here is my table:
CREATE TABLE [data].[example](
col1 [varchar](10) NOT NULL,
col2 [datetime] NOT NULL,
col3 [date] NOT NULL,
col4 [varchar](6) NOT NULL,
col5 [varchar](3) NOT NULL,
[Code] ....
My format file:
10.0
7
1 SQLCHAR 0 10 "@|@" 2 Col2 ""
1 SQLCHAR 0 10 "@|@" 3 Col3 ""
2 SQLCHAR 0 6 "@|@" 4 Col4 Latin1_General_CI_AS
[Code] .....
The first column should store double (in col2 and col3) in my table
My file:
Col1,Col2,Col3,Col4,Col5,Col6,Col7
2015-04-30@|@MDDS@|@ADP@|@EUR@|@185.630624@|@2015-04-30@|@MDDS
2015-04-30@|@MDDS@|@AED@|@EUR@|@4.107276@|@2015-04-30@|@MDDS
My command:
bulk insert data.example
from 'R:epoolexample.csv'
WITH(FORMATFILE = 'R:cfgexample.fmt' , FIRSTROW = 2)
Get error:
Msg 4823, Level 16, State 1, Line 2
Cannot bulk load. Invalid column number in the format file "R:cfgexample.fmt".
I changed some things as:
used ";" and "," as column delimiter
changed file type from UNIX to DOS and adjusted the format file with "
" for row delimiter
Removed this line from format file
1 SQLCHAR 0 10 "@|@" 2 Col2 ""
Nothing works ....
View 7 Replies
View Related
Nov 16, 2006
Hi All,
I am stuck at one place, where I have to convert CSV format file data into SAP IDOC format file. In SSIS we don't have any such SAP adapter (though we have .NET Data Provider for mySAP suite [SSIS SAP Adapter] but this is still not fully supported by Microsoft, plus it doesn't have feature to convert data into IDOC format) that can do this. Can someone here please provide me some pointers on any third party adapters available in market to do this job or if anyone has already developed some custom approach to achieve this task?
Your quick response on this is highly appreciated.
Regards,
Kuldeep Chauhan
View 2 Replies
View Related
Feb 9, 2000
I have some records that have been deleted. I need to find out who did it and to do that I need to read the logs. Are there any utilities that will allow me to read login 7.0? How about 6.5?
Chris
View 2 Replies
View Related
Jun 10, 2002
Hi
How can I read an ini file entry and pass this parameter to a stored procedure which will be run in a DTS Package?
Thanks for the input
mipo
View 2 Replies
View Related
Jun 22, 2008
How do I view the SQL 2005 transaction log file(.ldf)?
Is there a built-in utility?
Thank you
View 3 Replies
View Related
Jun 24, 2008
I am storing one text file on the server.This text file contains some mobile numbers.The file look like
009198XXXXXXXX
009198XXXXXXXX
009198XXXXXXXX
009198XXXXXXXX
etc
Here I need to read this text file row by row mobile number and then insert these mobile numbers into a table by using sql procedure or sql trigger or any other method or coding. Is it is possible or not?
If so then anybody can help me!
regards
Shaji
View 4 Replies
View Related
Jul 13, 2006
Hello friends...
We have sql server 2000 and i need to read one perticular xml and from that i want ID field stored in one table...is it possible by query analyzer to read xml and stored ID in table...I have only path of that xml file...
like
select ID from ("D:XMLFolder*.xml)
that i want
View 2 Replies
View Related
Oct 17, 2006
Hi
I have a problem with SSIS in that I need to read an XML file in from a URL,which brings me back the below XML. What I need to be able to do is essentially create a single record, including the nested XML <image> into the same record. This information then needs to be entered into a database table.
What I found is that if I use a data flow component, and have an XML source pointing to this URL, it essentially creates a different output for every level of nesting. This causes me serious issues because I would then have to somehow get to the information in the <image> tag, and then get everything below that, etc, to be able to get the images related to each <stock> record. This is a pain.
I also looked at using an XML task, where I could use an XSLT to transform to the required output, however, I can't point this XML task to a URL to retrieve the XML.
If anyone has any ideas I would greatly appreciate it
Thanks
Darrell
XML Format
------------------
<feed date="2006-10-17 17:08:46">
<usedstock>
<stock id="SFU22991">
<make>Peugeot</make>
<model>206</model>
<description>Peugeot 206 1.4 X-Line, 5 door Hatchback Tiptronic Auto 4Spd</description>
<hotline>08451553610</hotline>
<spec>Air Conditioning, Power Steering, Central Locking, Electric Windows, Alloy Wheels, CD Player</spec>
<deepurl>http://www.regvardy.com/used_car/dseller/SFU22991</deepurl>
<search>http://www.regvardy.com/used_cars/dseller/Peugeot/206</search>
<regdate>2005-01-27</regdate>
<colour>Blue</colour>
<price>7495</price>
<stocktype>USED</stocktype>
<category>Small</category>
<transmission>Tiptronic Auto 4Spd</transmission>
<bodystyle>Hatchback</bodystyle>
<fueltype>Petrol</fueltype>
<type>NCV</type>
<doors>5</doors>
<engine>PE0BC</engine>
<capcode>PE2614SED5HPIA</capcode>
<capid>26440</capid>
<image>
<urls>
<url>http://www.regvardy.com/imagedata/vehicles/g/pj54lyg_1.jpg</url>
<url>http://www.regvardy.com/imagedata/vehicles/g/pj54lyg_2.jpg</url>
<url>http://www.regvardy.com/imagedata/vehicles/g/pj54lyg_3.jpg</url>
</urls>
<libraryimage/>
</image>
</stock>
</usedstock>
</feed>
View 4 Replies
View Related
Jan 22, 2008
I am trying to read from a datasource that is a CSV file using C#, but for some reason I keep getting an error that says:
System.Exception: System.Data.OleDb.OleDbException: Syntax error in FROM clause.
My code looks like this:
String sql = " SELECT IDnumber FROM report.csv WHERE username = @Username ";
ArrayList parameters = new ArrayList();
parameters.Add(new OleDbParameter("@Username", Username));
DataTable dt = OleDbUtility.getDataTable(sql, parameters, OleDbUtility.GetCNetIDConnectionString());
It worked with an Excel file, so any ideas on this one?
It seems like the connection string is fine...
View 6 Replies
View Related
Mar 13, 2007
Brand new to SSIS so bear with me, if something is obvious.
I want to be able to read a file from a certain directory. But the file name changes every day. So today its File20061203 tomorrow File20061304 or the next day it could be FileNB4434. The format in the file will always be the same though. I just want for a user to be able to drop a file in a directory and the package pick it up once a day.
Would I have to to create a script task or could I use a variable. I have been trying to use the variable but have not been able to get them to work. This calls for only looking for 1 text file in a folder but any additional links that show some good variable examples would be appreciated. One where only part of the variable changes File(Variable)Division.txt
Thanks in advance
View 1 Replies
View Related
Jul 21, 2007
Hello,
I'm trying to read data from xml file (i want to use this file as enviornment config file). i tried 2 methods and failed in both of them:
1.a. I loaded the XML file to the report server
1.b. I wrote a custom code for reading the XML. i got #Error
2.a. I loaded the XML file to the report server
2.b. I used XML dataset for reading the XML file.
thank you all
View 3 Replies
View Related
Sep 25, 2007
I need to know how to import a text file into a stored procedure as one big varchar. I don’t want to import the data straight into my tables. I need to be able to work with it in the stored proc.
View 2 Replies
View Related
Jan 18, 2005
hi
I need to read a hex file, the file path is in the database, and I need to read the file from sql.
Any help would be appresiated
View 2 Replies
View Related
Feb 7, 2002
I need to select a list of rows from excel file.
I formed my query in this way :
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="MOC02c:empest/xls";Extended properties=Excel 97-2000')...xactions
This gives an error
Error is
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Could not find installable ISAM.]
If anybody knows how to read from an excel file to a query analyser , pelase reply.
Regards
Suresh
View 1 Replies
View Related
May 25, 1999
Is there any way to read a text file with one value in it and load that value into a variable?
View 1 Replies
View Related
Feb 20, 2004
It's probably a really easy thing to do, but I can't find out how to do it, so hopefully somebody can spare the time to help.
I want to read in T-SQL contained in a file held on disk and execute it in a stored procedure.
Anybody know how to get the contents of the text file into a variable so I can exec it ?
Thanks for your time.
View 4 Replies
View Related
Feb 20, 2004
It's probably a really easy thing to do, but I can't find out how to do it, so hopefully somebody can spare the time to help.
I want to read in T-SQL contained in a file held on disk and execute it in a stored procedure.
Anybody know how to get the contents of the text file into a variable so I can exec it ?
Thanks for your time.
View 1 Replies
View Related
Feb 27, 2004
hi to all
I have been trying to read XML file in SQL Server with Stored Procedure. but i coud't get any solution.
can any one Help me.
I have XML file with List Of Countryes each Country has Id.
I need to read all Country Id,CountryName and need to be insert into DataBase or Return as String (output parameter).
How do I achive this? please Help me.
Cheers
:confused:
Buddu
View 1 Replies
View Related
May 2, 2007
Guys, need help! I know this is not area for VBScript question, but possible I will find someone to help. Here is my question.
How can I read a text file of product IDs (ProductID contain only the first three character at the bigining of each line -- for example 220)and retrieve just those lines that meet a specified pattern?
Thk
View 4 Replies
View Related
May 27, 2008
When importing data from a source server it is useful to know which rows have changed since the last import. Is there a good way to get this information from the SQL Server log files? Does "dbcc log" provide this function, for example? Is this something that is done a lot or would it be considered a kludge?
Thanks,
Ron Rice
View 6 Replies
View Related
Oct 7, 2007
Hi,I have a requirement to load data from a flat file.The flat file has a naming convention and thus a timestamp is appended to it. eg : RMRS_07102007_001.delThis makes the file name different each day, Is there any method where by I can provide a dynamic name in the file connection manager.P.S. I tried a variable in For each loop with file enumatror and stored that in a variable , but I was not able to use that variable in File connection manager. pls guide if i was missing anything.Thanks in Advance, Ashish Prasad
View 1 Replies
View Related
Oct 16, 2006
does anyone here have a sample to how mssql can read a comma delimeted textfile...
View 1 Replies
View Related
