BULK INSERT W/odd Delimiter OK In 2000, Not 2005
Jun 12, 2008
We use BULK INSERT to load client data into our program. One of our clients uses the character '²' (0xB2) as a field delimiter in their input files. This worked fine in SS2000 but is failing in SS2005. After some testing, it appears that any high-ASCII value has the same problem; if I set the delimiter to anything below 0x80, it works and with any value of 0x80 or higher it fails.
I've verified that the format file we're using is correct for all of the tested delimiter values. (|, , ², ~, and €). The database collation sequence is SQL_Latin1_General_CP1_CI_AS if that matters.
Is there a way I can force acceptance of high-ASCII values as delimiters in SS2005? Do I need to play with the system code pages or the collation sequence?
Any assistance gratefully received.
View 7 Replies
ADVERTISEMENT
Dec 7, 2006
Hi All,I think this is a thorny problem, and I'm hoping you can help. I've notfound this exact issue described anywhere yet.I have a stored procedure that calls BULK INSERT on a set of text files.These files are FTP'd from a legacy system (a mainframe running MVS).Sometimes, the process steps on iteslf, whereby the bulk insert is attemptedon a file whose FTP is still in progress; it's not fully written to disk onthe SQL box (it's a 100MB file that takes a long time to cross the networkto the share on the Windows box), and so the insert generates a fatal errorand the procedure stops.I wrote a proc that calls the sp_OA* procs to use theScripting.FileSystemObject to test for the file's readiness. It returns anerror for me if the file does not exist. It also returns an error if I tryto run the BULK INSERT against a file which is being copied via Windows tothe SQL box. This is working just fine under those conditions; thesp_OAMethod to call OpenTextFile bombs appropriately if the file write isstill in progress.That's great, but it doesn't do the same thing during an FTP in progress.It doesn't generate the same error (that is OpenTextFile has no problemopening a partially written FTP'd file). I can also open the file inNotepad, even though it's not fully written to disk ... something I did notexpect, but there we are. What is it about FTP that's different from aWindows file system copy operation that makes the file look available forreading?If BULK INSERT is capable of detecting that it cannot do its thing on a filewhose FTP is in progress, then what can I write or call to emulate thatdetection? I tried writing a COM object in VB.NET and calling that from mySQL stored proc instead of the Scripting Engine's FSO methods. My codesimlpy tries to run a FileOpen using an Exclusive read lock; however, thisdoesn't seem to work, and I'm shooting in the dark now.Can anyone tell me what kind of file i/o request BULK INSERT makes, suchthat it is capable of abending during a run against an incompletely writtenfile using FTP?Thanks!Tim
View 3 Replies
View Related
Apr 18, 2008
Hello,
I'm just learning SSIS and I've hit my first bump. I am doing a bulk import from a tab delimited text file to an empty sql table that has a Idendity column defined. How do I tell the bulk insert task to skip that column when inserting from the text file. If I remove the identity column it imports the data fine, but I want to create the indentity column in the table too.
Thanks.
View 8 Replies
View Related
Apr 14, 2008
Ok being really new at using SQL server, I have a simple question.
I am trying to use the "Bulk Insert" command to dump a zip code list into my database. Here is my problem.
I found details on the command at http://sqlserver2000.databases.aspfaq.com/how-do-i-load-text-or-csv-file-data-into-sql-server.html but when I create a procedure in the stored procedures section of my database, I cant figure out how to get it to run it.
I created the table, created the stored procedure, and tried to write some code in my web page to run it. But it is not executing.
Any thoughts? Please help I am absolutely LOST!
View 3 Replies
View Related
Sep 4, 2006
I'm trying to import data from flat file in table and have fewproblems.1.Field Delimiter is ',' (comma). If ',' occurs in quotedstring it is still treated as field delimiter. This is BUG or ?2.In table I have datetime field that can be null, but bulkinsert reports error if in flat file is null or ''. It's OK only whenreal date is specified.Table:create table AttachmentList (Code integer not null,ClassID integer null,Description varchar(200) null,ValidUntil datetime null,constraint PK_ATTACHMENTLIST primary key (Code))flat file.1,13,'Naputak, CU 261098', ''Thanks in advanceDavor
View 3 Replies
View Related
Aug 29, 2006
I have a web page that prompts a user to select a csv file. Using a Bulk Insert the data is loaded into a SQL Server 2005 table.
I have been using the Bulk Insert with SQL Server 200 with no problems, but with 2005 I am getting the error "You do not have permission to use the bulk load statement".
My web.config file has the following connection string:
[code]
<add key="connectionString" value="Server=(local);Database=BroadCastOne;trusted_connection=true" />
[/code]
I've given bulkAdmin role to the ASPNET user. It's still not working. What am I doing wrong?
Any help is greatly appreciated,
Ninel
View 3 Replies
View Related
Nov 2, 2007
Does anyone know how to do a bulk insert using just the script task? I've been searching everyehere but can't seem to find a sample.
View 6 Replies
View Related
May 14, 2007
I can't use DTS nor DTSwizard as I need to put it in a .sql and run it through a command line via .bat file (it's more for the users).
Each row ends with an EOL character, the fields are all fixed width, but I have a little problem here, some rows are empty but just with a EOL character.
How shall I go about it?
many thanks! :D
View 2 Replies
View Related
May 24, 2006
I'm trying to set up a BULK INSERT Format File for some data that I've been sent, which, according to the data documentation, comes in fixed-width format fields (no delimiters except for end-of-row 0D0A) in SQL-Server 2005 Express.
The following is the first line...
"7999163 09182003 56586 56477 3601942 1278 22139 1102 113 118 51450 1 1 63535647 10000
7999162 09182003 56586 56477 3601942 1279 22139 1102 113 118 51450 1 1 63535647 10000 "
Looking with a hex editor, all the above whitespace are 20's.
From the documentation, I've constructed the following table...
CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0),
DB_CREATE_DATE CHAR (8),
DB_UPDATED_DATE CHAR (8),
CREATE_DATE_KEY NUMERIC (10,0),
ORDER_DATE_KEY NUMERIC (10,0),
PATIENT_KEY NUMERIC (10,0),
ORDER_KEY NUMERIC (10,0),
PROVIDER_KEY NUMERIC (10,0),
LOCATION_KEY NUMERIC (10,0),
ORDER_TYPE_KEY NUMERIC (10,0),
STATUS_KEY NUMERIC (10,0),
PRIMARY_INSURANCE_KEY NUMERIC (10,0),
EXISTENCE NUMERIC (1,0),
DURATION NUMERIC (4,0),
NUMBER_OF_VISITS NUMERIC (3,0),
ACTIVITY_TRACER_ID NUMERIC (10,0),
AGE_KEY NUMERIC (10,0)
)
To Bulk Insert this, I've written the following...
BULK INSERT MQIC.DBO.ORDER_F
FROM 'E:MDataOrder_F.txt'
WITH
(
FORMATFILE = 'E:MDataOrder_F_format.txt'
)
and written the following format file...
9.0
17
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""
2 SQLCHAR 0 8 "" 2 DB_CREATE_DATE ""
3 SQLCHAR 0 8 "" 3 DB_UPDATED_DATE ""
4 SQLNUMERIC 0 10 "" 4 CREATE_DATE_KEY ""
5 SQLNUMERIC 0 10 "" 5 ORDER_DATE_KEY ""
6 SQLNUMERIC 0 10 "" 6 PATIENT_KEY ""
7 SQLNUMERIC 0 10 "" 7 ORDER_KEY ""
8 SQLNUMERIC 0 10 "" 8 PROVIDER_KEY ""
9 SQLNUMERIC 0 10 "" 9 LOCATION_KEY ""
10 SQLNUMERIC 0 10 "" 10 ORDER_TYPE_KEY ""
11 SQLNUMERIC 0 10 "" 11 STATUS_KEY ""
12 SQLNUMERIC 0 10 "" 12 PRIMARY_INSURANCE_KEY ""
13 SQLNUMERIC 0 1 "" 13 EXISTENCE ""
14 SQLNUMERIC 0 4 "" 14 DURATION ""
15 SQLNUMERIC 0 3 "" 15 NUMBER_OF_VISITS ""
16 SQLNUMERIC 0 10 "" 16 ACTIVITY_TRACER_ID ""
17 SQLNUMERIC 0 10 "
" 17 AGE_KEY ""
However... actually running this gives the following error...
Msg 4863, Level 16, State 4, Line 1
Bulk load data conversion error (truncation) for row 1, column 13 (EXISTENCE).
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)".
Since this is my first time with this, I read the BOL items on Bulk Insert, Format Files, and each of the formatting attibutes, and made up two line "toy" examples for SQLCHAR and SQLINT, including two columns - all worked as expected.
It seemed that only SQLNUMERIC/SQLDECIMAL fell apart.
Even the following trivial example doesn't work for this field of data...
"7999163 "
CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0)
)
and
9.0
1
1 SQLNUMERIC 0 10 " " 1 TRACER_ID ""
or
9.0
1
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""
which give this error...
Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".
The statement has been terminated.
or
9.0
1
1 SQLNUMERIC 0 10 "/r/n" 1 TRACER_ID ""
which gives this error...
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)".
Also - there was the DB_CREATE_DATE and DB_UPDATED_DATE CHAR (8) were supposed to be dates in the format of mmddyyy but clearly there is no Date datatype in SQL-Server. I would suppose these need to be converted, but am unsure how. What is clear is that the data was dumped from Oracle in text form,
Any thoughts on this would be greatly appreciated...
Thanks!
View 1 Replies
View Related
Mar 8, 2008
To the Forum,
Hello, i wanted to give the forum my current process flow to see if i am close or have some more work to do. The object is to import the data as fast as possible without loosing query responsiveness of the search on the web side. Any type of response will be greatly appreciated.
Current Process
I receive multiple product inventory lists form multiple vendors. These inventory lists are in many different formats like .xls format, .txt format, .csv format and .dbf format. My server converts the file format to raw .csv. The table is very large and will consist of millions of rows. Inventory comes in alphanumeric format.
Each of the inventory lists are NOT in the same format. What i mean by this is that there are different header names for the users inventory list than what matches our database table. For Example, a user may have an excel document with the header name "Amt" but our database table field name is "Amount". In order to make this import process automated I make a single mapping file for each user. This mapping file relates the users field header in their inventory file to that of the database table.
Currently I have a process converting all files to .csv format. Every 15 minutes a routine runs that converts the .csv to XML and then performs the import Bulk Insert routine. The process deletes the users entire inventory then imports to a "tempinventory" table, which then updates the inventory table. During this 15 minutes we may get 20 inventory lists to import which could be a half a million records. I have this inventory table indexed using the primary key which is the inventory number. This is the search criteria used (Inventory Number) on the web side. The import routine to SQL works quickly ONLY if Full-Text Index is turned OFF. I assume i need the Full Text Indexing on so the queries of the full inventory lists a fast response on the web side.
Assumed Issues
Right now if I were to turn off the Indexing for each import then we would have slow queries against the database on the web side.
To have one table with millions of rows that is constantly updated as well as queried at the same time is not efficient.
Assumed Corrections
Right now it seems that partitioning the table into 10 numeric partitions, one for each number would leverage the import routine as well as the web side search routine. I would then partition the alpha partition in 1 letter increments so we would have 26 partitions, one for each letter for a total of 36 partitions.
If I have the partitions seperated then it will be easier to update the seperate index as well as perform maintenance on the index, i assume this is correct.
Future Plans
I am upgrading the web side application to .NET 3.5 so we can take advantage of XLINQ and LINQ so our searches from the web side are faster and more efficient.
I am also looking into building a SilverLight application that will allow the user to install the application locally that will take their live database file and send updates to my server so they do not need to send in their inventory file at all and the inventory lists are live. This will alleviate the need to delete the users full listing in order to make a complete update. As stated above sometimes an update may just include updating the amount of only 2 of 40,000 rows.
I was also looking into db4o to see if it would be beneficial as well, http://www.db4o.com/, has anyone worked with this before in a similar manner?
Questions
I would like to make this process much more efficient from the import routine to the search routine. Is setting up the partitions as discussed a stable plan for both routines?
Is the BULK INSERT using XML to SQL the most efficient way of importing the data to SQL?
How would i handle the full text indexing to allow fast import routines without slowing down the web side searches?
After import of new data do i need to "update" the index as well?
What is a good set of "preventative maintenance" standards should i follow when dealing with this many table updates as well as the catalogs and table data?
I know there are benefits of using LINQ when querying the database from the web side but are there any other benefits that would fit into this current process?
As for the SilverLight application would it be beneficial for the user as well as me to have the application poll their database file to find changes and send only the updated values of the list to the server via XML, which is then updated by SQL?
I am unsure what is the best way to make this process as easy and automated as possible giving the user the fastest experience possible when searching from the web side.
Is this a smarter idea so i can track just the changes made by the user on their inventory list instead of importing the same redundant data they have?
Would implementing something like db4o be beneficial for this process, http://www.db4o.com/?
Please let me know if i am way off on this process or if there are some benefits that i am not using in this process. I have been doing a lot of research and this is what i have come up with so i wanted to ask the community what they thought about it as many heads are better than one. Please feel free to rip the process apart to, i take constructive criticism.
Thanks again.
RC
View 5 Replies
View Related
May 24, 2006
I'm trying to set up a BULK INSERT Format File for some data that I've been sent, which, according to the data documentation, comes in fixed-width format fields (no delimiters except for end-of-row 0D0A) in SQL-Server 2005 Express.
The following is the first line...
"7999163 09182003 56586 56477 3601942 1278 22139 1102 113 118 51450 1 1 63535647 10000
7999162 09182003 56586 56477 3601942 1279 22139 1102 113 118 51450 1 1 63535647 10000 "
Looking with a hex editor, all the above whitespace are 20's.
From the documentation, I've constructed the following table...
CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0),
DB_CREATE_DATE CHAR (8),
DB_UPDATED_DATE CHAR (8),
CREATE_DATE_KEY NUMERIC (10,0),
ORDER_DATE_KEY NUMERIC (10,0),
PATIENT_KEY NUMERIC (10,0),
ORDER_KEY NUMERIC (10,0),
PROVIDER_KEY NUMERIC (10,0),
LOCATION_KEY NUMERIC (10,0),
ORDER_TYPE_KEY NUMERIC (10,0),
STATUS_KEY NUMERIC (10,0),
PRIMARY_INSURANCE_KEY NUMERIC (10,0),
EXISTENCE NUMERIC (1,0),
DURATION NUMERIC (4,0),
NUMBER_OF_VISITS NUMERIC (3,0),
ACTIVITY_TRACER_ID NUMERIC (10,0),
AGE_KEY NUMERIC (10,0)
)
To Bulk Insert this, I've written the following...
BULK INSERT MQIC.DBO.ORDER_F
FROM 'E:MDataOrder_F.txt'
WITH
(
FORMATFILE = 'E:MDataOrder_F_format.txt'
)
and written the following format file...
9.0
17
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""
2 SQLCHAR 0 8 "" 2 DB_CREATE_DATE ""
3 SQLCHAR 0 8 "" 3 DB_UPDATED_DATE ""
4 SQLNUMERIC 0 10 "" 4 CREATE_DATE_KEY ""
5 SQLNUMERIC 0 10 "" 5 ORDER_DATE_KEY ""
6 SQLNUMERIC 0 10 "" 6 PATIENT_KEY ""
7 SQLNUMERIC 0 10 "" 7 ORDER_KEY ""
8 SQLNUMERIC 0 10 "" 8 PROVIDER_KEY ""
9 SQLNUMERIC 0 10 "" 9 LOCATION_KEY ""
10 SQLNUMERIC 0 10 "" 10 ORDER_TYPE_KEY ""
11 SQLNUMERIC 0 10 "" 11 STATUS_KEY ""
12 SQLNUMERIC 0 10 "" 12 PRIMARY_INSURANCE_KEY ""
13 SQLNUMERIC 0 1 "" 13 EXISTENCE ""
14 SQLNUMERIC 0 4 "" 14 DURATION ""
15 SQLNUMERIC 0 3 "" 15 NUMBER_OF_VISITS ""
16 SQLNUMERIC 0 10 "" 16 ACTIVITY_TRACER_ID ""
17 SQLNUMERIC 0 10 "
" 17 AGE_KEY ""
However... actually running this gives the following error...
Msg 4863, Level 16, State 4, Line 1
Bulk load data conversion error (truncation) for row 1, column 13 (EXISTENCE).
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)".
Since this is my first time with this, I read the BOL items on Bulk Insert, Format Files, and each of the formatting attibutes, and made up two line "toy" examples for SQLCHAR and SQLINT, including two columns - all worked as expected.
It seemed that only SQLNUMERIC/SQLDECIMAL fell apart.
Even the following trivial example doesn't work for this field of data...
"7999163 "
CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0)
)
and
9.0
1
1 SQLNUMERIC 0 10 " " 1 TRACER_ID ""
or
9.0
1
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""
which give this error...
Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".
The statement has been terminated.
or
9.0
1
1 SQLNUMERIC 0 10 "/r/n" 1 TRACER_ID ""
which gives this error...
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)".
Also - the DB_CREATE_DATE and DB_UPDATED_DATE CHAR (8) were supposed to be dates in the format of mmddyyy but clearly there is no Date datatype in SQL-Server. I would suppose these need to be converted, but am unsure how. What is clear is that the data was dumped from Oracle in text form,
Any thoughts on this would be greatly appreciated...
Thanks!
View 1 Replies
View Related
May 24, 2006
I'm trying to set up a BULK INSERT Format File for some data that I've been sent, which, according to the data documentation, comes in fixed-width format fields (no delimiters except for end-of-row 0D0A) in SQL-Server 2005 Express.
The following is the first line...
"7999163 09182003 56586 56477 3601942 1278 22139 1102 113 118 51450 1 1 63535647 10000
7999162 09182003 56586 56477 3601942 1279 22139 1102 113 118 51450 1 1 63535647 10000 "
Looking with a hex editor, all the above whitespace are 20's.
From the documentation, I've constructed the following table...
CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0),
DB_CREATE_DATE CHAR (8),
DB_UPDATED_DATE CHAR (8),
CREATE_DATE_KEY NUMERIC (10,0),
ORDER_DATE_KEY NUMERIC (10,0),
PATIENT_KEY NUMERIC (10,0),
ORDER_KEY NUMERIC (10,0),
PROVIDER_KEY NUMERIC (10,0),
LOCATION_KEY NUMERIC (10,0),
ORDER_TYPE_KEY NUMERIC (10,0),
STATUS_KEY NUMERIC (10,0),
PRIMARY_INSURANCE_KEY NUMERIC (10,0),
EXISTENCE NUMERIC (1,0),
DURATION NUMERIC (4,0),
NUMBER_OF_VISITS NUMERIC (3,0),
ACTIVITY_TRACER_ID NUMERIC (10,0),
AGE_KEY NUMERIC (10,0)
)
To Bulk Insert this, I've written the following...
BULK INSERT MQIC.DBO.ORDER_F
FROM 'E:MDataOrder_F.txt'
WITH
(
FORMATFILE = 'E:MDataOrder_F_format.txt'
)
and written the following format file...
9.0
17
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""
2 SQLCHAR 0 8 "" 2 DB_CREATE_DATE ""
3 SQLCHAR 0 8 "" 3 DB_UPDATED_DATE ""
4 SQLNUMERIC 0 10 "" 4 CREATE_DATE_KEY ""
5 SQLNUMERIC 0 10 "" 5 ORDER_DATE_KEY ""
6 SQLNUMERIC 0 10 "" 6 PATIENT_KEY ""
7 SQLNUMERIC 0 10 "" 7 ORDER_KEY ""
8 SQLNUMERIC 0 10 "" 8 PROVIDER_KEY ""
9 SQLNUMERIC 0 10 "" 9 LOCATION_KEY ""
10 SQLNUMERIC 0 10 "" 10 ORDER_TYPE_KEY ""
11 SQLNUMERIC 0 10 "" 11 STATUS_KEY ""
12 SQLNUMERIC 0 10 "" 12 PRIMARY_INSURANCE_KEY ""
13 SQLNUMERIC 0 1 "" 13 EXISTENCE ""
14 SQLNUMERIC 0 4 "" 14 DURATION ""
15 SQLNUMERIC 0 3 "" 15 NUMBER_OF_VISITS ""
16 SQLNUMERIC 0 10 "" 16 ACTIVITY_TRACER_ID ""
17 SQLNUMERIC 0 10 "
" 17 AGE_KEY ""
However... actually running this gives the following error...
Msg 4863, Level 16, State 4, Line 1
Bulk load data conversion error (truncation) for row 1, column 13 (EXISTENCE).
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)".
Since this is my first time with this, I read the BOL items on Bulk Insert, Format Files, and each of the formatting attibutes, and made up two line "toy" examples for SQLCHAR and SQLINT, including two columns - all worked as expected.
It seemed that only SQLNUMERIC/SQLDECIMAL fell apart.
Even the following trivial example doesn't work for this field of data...
"7999163 "
CREATE TABLE MQIC.DBO.ORDER_F
(
TRACER_ID NUMERIC (10,0)
)
and
9.0
1
1 SQLNUMERIC 0 10 " " 1 TRACER_ID ""
or
9.0
1
1 SQLNUMERIC 0 10 "" 1 TRACER_ID ""
which give this error...
Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".
The statement has been terminated.
or
9.0
1
1 SQLNUMERIC 0 10 "/r/n" 1 TRACER_ID ""
which gives this error...
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)".
Also - the DB_CREATE_DATE and DB_UPDATED_DATE CHAR (8) were supposed to be dates in the format of mmddyyy but clearly there is no Date datatype in SQL-Server. I would suppose these need to be converted, but am unsure how. What is clear is that the data was dumped from Oracle in text form,
Any thoughts on this would be greatly appreciated...
Thanks!
View 5 Replies
View Related
Jan 17, 2008
Im having some issues with bulk insert.
This is the table:
CREATE TABLE [dbo].[tmp_GA_status](
[GA_recno] [int] NOT NULL,
[GA_desc] [varchar](40) NULL
)
This is the file (unicode):
1|"test1"
2|"test2"
3|"test3"
4|"test4"
5|"test5"
6|"test6"
7|"test7"
8|"test8"
and this is the sql:
bulk insert tmp_GA_status from 'C: empTextDumpGA_status.dta'
with (CODEPAGE='RAW', FIELDTERMINATOR='|', ROWTERMINATOR='
', DATAFILETYPE='widechar')
so yeah, pretty simple. But whatever I do I get this;
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 2 (GA_desc).
So what am I doing wrong ?
View 13 Replies
View Related
Sep 27, 2007
I have to update a field within a table of 60 records or so. Each record has a different field value. it's type varchar. i was given an excel file with the field values and was thinking of a bulk update like bulk insert, but i don't recall that it's possible that way.
Is the only way to create a table, bulk insert, then merge the two tables together with UPDATE?
Just wanted to see if there was an easier way to do it, otherwise i'll take the latter route. Thanks!
View 1 Replies
View Related
Aug 2, 2005
I am trying to import data from a text file to SQL database through a DTS package. i am always getting "Column Delmimeter not found" error at record number 8000. i copied the 8000th record into the beginning of the file to test if there is any problem with data but still i got the error at 8000 th record. i know it was a problem before and was fixed with sp1. i installed SP4 and still gettting the same error.
View 4 Replies
View Related
Oct 11, 2000
I have a table containing 8 million records.
I need to replace 2 million of these records with
a scaled down query that goes something like:
SELECT 1, ShareholderID, Assets1
FROM MyTable (Yields appx. 200,000 recods)
SELECT 2, ShareholderID, Assets2
FROM MyTable (Yields appx. 200,000 recods)
.
.
.
SELECT 10, ShareholderID, Assets1 + Assest2 + Assets3 + ... + Assets9
FROM MyTable (Yields appx. 200,000 recods)
Updates and cursors just seem to be too slow.
So far I have done the following, but was wondering if anyone could think of a better way.
SELECT 6 million records that don't need to be deleted into a #TempTable
Use statements above to select into same #TempTable
DROP and recreate Original Table
SELECT 6 + 2 million records INTO original table.
This seems rather convoluted. Is there a better approach? Would it be worth while to dump data to a file and use bcp / Bulk Insert
Any comments are appreciated,
-Marc
View 3 Replies
View Related
Apr 8, 2008
I receive the following error message when I try to use the Bulk Insert Task to load BCP data into a table:
Error: 0xC002F304 at Bulk Insert Task, Bulk 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.The bulk load failed. The column is too long in the data file for row 1, column 4. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (overflow) for row 1, column 1 (rowno).".
Task failed: Bulk Insert Task
In SSMS I am able to issue the following command and the data loads into a TableName table with no error messages:
BULK INSERT TableName
FROM 'C:DataDbTableName.bcp'
WITH (DATAFILETYPE='widenative');
What configuration is required for the Bulk Insert Task in SSIS to make the data load? BTW - the TableName.bcp file is bulk copy file as bcp widenative data type. The properties of the Bulk Insert Task are the following:
DataFileType: DTSBulkInsert_DataFileType_WideNative
RowTerminator: {CR}{LF}
Any help getting the bcp file to load would be appreciated. Let me know if you require any other information, thanks for all your help.
Paul
View 1 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
Feb 1, 2007
Hi~,
Before implementing memory based bulk copy insert with IRowsetFastLoad interface of SQL Server 2005 OLE DB provider, I want to know some considerations.
- performance : compared with T-SQL's "BULK INSERT ..." and bcp utility
- SQL Server's resource usage : when running memory based bulk copy, server resource's influence
- server side action(behavior) : when server is busy, delayed-update means IRowsetFastLoad::Commit(true) method can insert right after?
- row-count : The rowcount limitation can be inserted by IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit
- any other guide lines
View 1 Replies
View Related
Nov 14, 2007
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
View 6 Replies
View Related
Oct 12, 2007
Hi,
i have a file which consists data as below,
3
123||
456||
789||
Iam reading file using bulk insert and iam inserting these phone numbers into table having one column as below.
BULK INSERT TABLE_NAME FROM 'FILE_PATH'
WITH (KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '||')
but i want to insert the data into table having two columns. if iam trying to insert the data into table having two columns its not inserting.
can anyone help me how to do this?
Thanks,
-Badri
View 5 Replies
View Related
Jan 11, 2007
Hi all,
We are experiencing major performance issues after restoring SQL Server 2000 db into newly setup hardware environment. The application is faster on another environemnt running on Windows 2000 server with SQL Server 2000.
We have replicated exact same hardware setup, but only fifference is we are running latest version windows server (2003) and latest version of SQL Server (2005). So far we have troubleshooted the bottleneck to be one of the trigger that is inserting data into a table in one of the stored procedures. The same SP takes 9 mins to execute whereas on our env it takes around one hour. If I disable the trigger it executes in 2 minutes.
After restoring, we have executed
exec sp_updatestats
DBCC UPDATEUSAGE
DBCC DBREINDEX all tables
Set Compatibility level to SQL SERVER 2005 (90).
Now we are installing latest Service Pack for both OS and SQL Server.
Any ideas?
Your help is appreciated.
View 1 Replies
View Related
Nov 25, 2006
In case of a bulk insert, the “FOR INSERT� trigger fires for each recod or only once?
Thanks,
View 1 Replies
View Related
Apr 26, 2006
Hello,
I am wondering is the Transaction Log logged differently between BULK INSERT vs INSERT? Performance speaking, which operations is generally faster given the same amout of data inserted.
Sincerely,
-Lawrence
View 3 Replies
View Related
Feb 15, 2007
Hi~, I have 3 questions about memory based bulk copy.
1. What is the limitation count of IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit(true)?
For example, how much insert row at below sample?(the max value of nCount)
for(i=0 ; i<nCount ; i++)
{
pIFastLoad->InsertRow(hAccessor, (void*)(&BulkData));
}
2. In above code sample, isn't there method of inserting prepared array at once directly(BulkData array, not for loop)
3. In OLE DB memory based bulk copy, what is the equivalent of below's T-SQL bulk copy option ?
BULK INSERT database_name.schema_name.table_name FROM 'data_file' WITH (ROWS_PER_BATCH = rows_per_batch, TABLOCK);
-------------------------------------------------------
My solution is like this. Is it correct?
// CoCreateInstance(...);
// Data source
// Create session
m_TableID.uName.pwszName = m_wszTableName;
m_TableID.eKind = DBKIND_NAME;
DBPROP rgProps[1];
DBPROPSET PropSet[1];
rgProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[0].colid = DB_NULLID;
rgProps[0].vValue.vt = VT_BSTR;
rgProps[0].dwPropertyID = SSPROP_FASTLOADOPTIONS;
rgProps[0].vValue.bstrVal = L"ROWS_PER_BATCH = 10000,TABLOCK";
PropSet[0].rgProperties = rgProps;
PropSet[0].cProperties = 1;
PropSet[0].guidPropertySet = DBPROPSET_SQLSERVERROWSET;
if(m_pIOpenRowset)
{
if(FAILED(m_pIOpenRowset->OpenRowset(NULL,&m_TableID,NULL,IID_IRowsetFastLoad,1,PropSet,(LPUNKNOWN*)&m_pIRowsetFastLoad)))
{
return FALSE;
}
}
else
{
return FALSE;
}
View 6 Replies
View Related
May 15, 2008
I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.
The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.
The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.
So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).
I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.
Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?
From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.
View 2 Replies
View Related
Oct 27, 2006
hi friends i am trying for bulk insert using SQL server 2000using this codebulk insert xyzfrom 'D:authors.txt'WITH (FIELDTERMINATOR = ',') but it gve me error saying thatCould not bulk insert because file 'D:authors.txt' could not be opened. Operating system error code 21(error not found). i check file securityit has given full control to the file can any one give me idea about Operating System error code 21(error not found) thanks
View 1 Replies
View Related
Aug 28, 2007
Hi,I've a SP that insert records in one table and then call another insert SP on a second table. The first table is like a master table and the second is like a child table. After inserting the right record in the master table, I've to insert some record in the child table. This records differ each other only by two of about ten field, so what I'd want is not to call the second SP X times, but only one time.. Is it possible??ExampleTable1: Id (identity), Desc;Table2: Id (identity), Id_table1, Id_TableX, Num, Field1, Field2, ... Field10.In Table2 only Id_TableX and Num change every time... the other are all the same (for one record in Table1). How can I do? Probably with a bulk insert and a bulk update?? But, can I make a bulk xxx without a file??
View 3 Replies
View Related
Dec 28, 2007
hi friends i am using bulk insert cmd using my table name but i am facing error.....SO
IS IT POSSIBLE TO USE BULK INSERT WITH TEMPRARY TABLE VARIABLE
PLZ HELP ME
View 2 Replies
View Related
Dec 20, 2000
How can I do a bulk insert without the transaction being logged?
View 1 Replies
View Related
Sep 15, 2000
Hi,
I want to move data from a text file to a SQL table. After DTS creates the table, does it use Bulk Insert to copy the data from the file to the table, or BCP?
Thanks,
Judith
View 2 Replies
View Related
May 19, 2000
I am trying to do a bulk insert from a data file into a linked access database. When I run the query I get the error message:
'Server: Msg 4801, Level 16, State 81, Line 1
Bulk_main: The opentable system function on BULK INSERT table failed.
Not sure what the problem is because BOL just says to check Microsoft.com for updated error message information. However, when I went to the site there was no updated information. Has anyone else seen this error? If so, have you figured out the problem? Any help would be greatly appreciated. Thanks.
Jim
View 1 Replies
View Related
