Bulk Insert, Skip Rows With Duplicate Key Error?
Does sql server have a way to handle errors in a sproc which would allow
one to insert rows, ignoring rows which would create a duplicate key
violation? I know if one loops one can handle the error on a row by row
basis. But is there a way to skip the loop and do it as a bulk insert?
It's easy to do in Access, but I'm curious to know if SQL Server proper
can handle like this. I am guessing that a looping operation would be
slower to execute?
View Complete Forum Thread with Replies
Related Forum Messages:
Skip The First Line Of The Data File - Bulk Insert
Hi, I have a data file and the contents of it are as follows 2 -- This is the header indicating the no of records in my files 1001|s1 1006|s2 The content of format file is as follows. This is to skip first column of the all the rows and get only Subs (i.e s1 and s2 ) 9.0 2 1 SQLCHAR 0 100 "|" 0 ID "" 2 SQLCHAR 0 100 " " 1 Subs "" Here is my query to get all the Subs from my data file SELECT * FROM OPENROWSET( BULK 'datafile.txt', FORMATFILE = 'FormatFile.fmt', FIRSTROW = 2 ) AS a But this query retuns only s2 where i was expeting s1 and s2. The reason being is that the firts row i.e header doesn't follow the format Can any one please let me know how to skip the first line in the data file and get the result as required ~Mohan
View Replies !
Skip Field Terminator While Inserting Data To A Table-Bulk Insert
Hi, I have a data file which consists of data as below, 4 PPU_FFA7485E0D|| T_GLR_DET_11|| While iam inserting into table using bulk insert, this pipe(||) is also getting inserted into the table, here is my query iam using to insert the data using bulk insert. BULK INSERT TABLE_NAME FROM FILE_PATH WITH (FIELDTERMINATOR = ''||'''+',KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '''') Can any one help on this. Thanks, -Badri
View Replies !
Error: 0xC002F304 At Bulk Insert Task, Bulk Insert Task: An Error Occurred With The Following Error Message: &&"Cannot Fetch A Row
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 Replies !
Don't Insert Duplicate Rows
Hi, I need to insert rows into table1 from table2 and table3 but I don't want to insert repeated combinations of col2, col3. So, table1 has the primary key col2, col3. This the table1: create table table1( col1 int not null, col2 int not null, col3 int not null, constraint PK_table1 primary key (col2, col3) ) This is my "insert" code: INSERT INTO table1 SELECT table2.col1,table2.col2, table3.col3 FROM table2, table3 WHERE table2.col1 = table3.col1 Wich conditions shoud i add to this code? Thanks. fmilano.
View Replies !
Preventing Duplicate Rows On Insert
I have a table using an identity column as its Primary Key and twocolumns (table reduced for simplicity) EmployeeNumber and ArrivalTime.CREATE TABLE [tblRecords] ([ID] [bigint] IDENTITY (1, 1) NOT NULL ,[EmployeeNumber] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL ,[ArrivalTime] [datetime] NOT NULL ,CONSTRAINT [PK_tblRecords] PRIMARY KEY CLUSTERED([ID]) ON [PRIMARY]) ON [PRIMARY]GOI have an insert procedure that checks for duplicates before insertinga new record:IF (SELECT TOP 1 [ID] FROM tblRecords WHERE EmployeeNumber =@SocialSecurity) IS NULLBEGININSERT INTO tblRecords(EmployeeNumber,ArrivalTime)VALUES (@EmployeeNumber, @ArrivalTime)SELECT SCOPE_IDENTITY()ENDELSESELECT 0 AS DuplicateRecordIn 99.9% of the cases, this works well. However, in the event that theinsert attempts are literally "ticks" apart, the "SELECT TOP 1..."command completes on both attempts before the first attempt completes.So I end up with duplicate entries if the procedure is called multipletimes vey quickly. The system needs to prevent duplicateEmployeeNumbers within the past 45 days so setting the EmployeeNumberto UNIQUE would not work. I can check for older entries (45 days ornewer) very easily, but I do not know how to handle the times when theprocedure is called multiple times within milliseconds. Would aTRANSACTION with a duplicate check after the INSERT with a ROLLBACKwork in this case? Any help is greatly appreciated!-E
View Replies !
Bulk Insert - Bulk Load Data Conversion Error
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 Replies !
Bulk Insert Skips Rows
Hi Guys, My little bulk insert is only bringing every second row of a CSV file. this is not good as i need every row. My SQLcommand is thus. InsertCommand="BULK INSERT TBL_Unitel_services FROM 'C:/webroot/servicedesk/csvs_Services/csv.csv' WITH (FIRSTROW = 1, FIELDTERMINATOR = ',', ROWTERMINATOR = '', MAXERRORS = 0) "
View Replies !
BULK INSERT - MISSING ROWS
Hello Iam using: Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2) I am using BULK INSERT to import some pipe-delimited flat files into a database. I am firstly converting the file using VB.NET, to ensure each line of the file has a carriage return (by using streamwriter.writeline), and I am also ensuring there is no blank line at the end of the file (by using streamwriter.write). Once I have done this, my BULK INSERT command appears to work OK. This is how I am using the statement: BULK INSERT tempHISTORY FROM 'C:TEMPHISTORY.TXT' WITH ( FIRSTROW = 2, FIELDTERMINATOR = '|', ROWTERMINATOR = ' ' ) NB: The first row in the file is a header row. This appears to work OK, however, I have found that certain files seem to miss the final line of the file! I have analysed these files incase they have an inconsistant number of columns but they don't. I have also found that if I knock off the last column of the tempHISTORY table, the correct number of rows are imported. However, of course, I can't just discard one of the columns from the file, I need to import the entire file. I cannot understand why BULK INSERT is choosing to miss the final line in the file, when the schema of the destination table matches the structure of the file.
View Replies !
BULK INSERT Inserts 2 Rows
Greetings Got bulk insert thing going real nice thanks to your feedback! But now I notice that the BULK INSERT creates 2 identical rows while the flat file has the column headers and then corresponding row, just one row. Why would it do that. Interestingly if the data file and format file are residing on a shared folder on SQL 2005, and I ran the BULK INSERT from studio it only inserts one row. But when I do BULK INSERT via user interface it creates 2 rows. What is going here.
View Replies !
Count Rows Imported By Bulk Insert
I have a stored procedure which will run automatically. I've got try...catch code in the procedure, but I found a bug with the code where if there are any import errors, it doesn't recognize that that there was an error and it runs through the try code as through there was no problems. (I reported the bug). I added some code using @@rowcount to check if there were rows imported, and if not, it moves the data file to a error folder so I know there was a problem with the import. But this only checks if at least one row was imported, not if all the rows in the datafile have been imported. (i.e. if the first row imported correctly, and the second did not, it still sees it as successful). The problem is some of the data files have only one row to import and some have multiple rows. Is there a way to count the number of rows in the datafile, then count the number of rows imported, to verify they are the same number imported? Thanks, Laura
View Replies !
Count Rows Imported By Bulk Insert
I have a stored procedure which will run automatically. I've got try...catch code in the procedure, but I found a bug with the code where if there are any import errors, it doesn't recognize that that there was an error and it runs through the try code as through there was no problems. (I reported the bug). I added some code using @@rowcount to check if there were rows imported, and if not, it moves the data file to a error folder so I know there was a problem with the import. But this only checks if at least one row was imported, not if all the rows in the datafile have been imported. (i.e. if the first row imported correctly, and the second did not, it still sees it as successful). The problem is some of the data files have only one row to import and some have multiple rows. Is there a way to count the number of rows in the datafile, then count the number of rows imported, to verify they are the same number imported? Thanks, Laura
View Replies !
BCP Utility - Skip Rows..
Hello All,Does the BCP utility enable you to selectively import rows from a flatfile to a table ?For example:The first column in my flat file contains a record type - 1, 2..7I only need to import types 1, 2, & 3Can this be specified in the .fmt file ?Thanks in advancehharry
View Replies !
Skip Rows In Excel
Hi, How to skip my 12 header rows from XLS input source? (before the Excel driver reads (by default, 8 rows) in the specified source to guess at the data type of each column.) thx, f.sor
View Replies !
Footer Rows To Skip
OK. We know there is Header rows to skip options and it works great. I've got the file that has a "footer". Here is an example: . PSC filename=table1 records=0000000000525 ldbname=db1 timestamp=2006/02/07-16:25:00 numformat=44,46 dateformat=mdy-1910 map=NO-MAP cpstream=ISO8859-1 . 0000260611 It's ALWAYS last 12 rows. Is there a way to split at this point and put the 12 rows in a different location? The task is twofold - I don't need these control rows in my data and I need value of "records" to verify loaded number of rows. UPDATED: After some testing I found out that the Flat File source does not see that footer at all. This is good and bad - I do want to load this metedat into some other tables. Dima.
View Replies !
Data Rows To Skip Does Nothing
I have put an expression on the data rows to skip in a flat file connection, however it is having no affect. I have tried setting delay validation but still nothing. Any tips
View Replies !
Skip Rows Where The First Column With Letter 'S' - BCP
Hi All,I have this data file with fix length(see below). I am able to insertit into the database using bcp, but now I want to skip (do not insert)the row which start with letter 'S' into the database. Is there away todo it? By the way I am using -F2 option to skip the first record.Here is my data:Record 1 04XXX2 13106900240120042003040045061 Testing N POLYDOROS TRUSTEEE2 12621241640280041004040045633 What are they MARTIN &XXXXXS C1000003200400409850000059611000000500001000000001 9613000000576497500S X1000003200000209850000059613000000000000000000001 9613000000573497000Thanks for your help.Ted Lee
View Replies !
Skip Rows Affect The Results
Dear all, In Flat File Source properties windows there's Preview node, when we check that node there's an option to skip the data in how many rows. Is it affect the result ? Best regards, Hery
View Replies !
WANT TO SKIP ROWS AAND ALSO UNPIVOT
hI, i have this particular problem with the unpivot.The below is my flat file source.The dates can go upto 130 columns.this count can also vary.SM,SR,SB are again values repeating for diff instrument.They are the values of the instrument on the particular dates.This is a snap shot of one feed.Other feeds may have the dates differing.How do i read this file. Problem 1:If i skip the first row and unpivot the 2nd row,then with the new feed,with new dates my SSIS package will bomb as it will not find the col names. Problem 2:IF i uncheck the "Use first row as column headers" then the problem 1 is solved but the o/p will be 20080101 20061102 20061103 1.2 1.3 1.2. 1.5 .....and so on.. IS there any other way to fix this.These are feeds with the spread values of instruments on particular dates.Please help. RUN 2.01E+11 132238 0 45 INSTRID DATATYPES 20081101 20061102 20061103 Z03369 SM 1.1 1.2 1.3 Z03369 SB 1.3 1.3 1.7 Z03369 SR 2 3 4 Z81910 SM 1.1 1.2 1.3 Z81910 SB 1.3 1.3 1.7
View Replies !
DTS Bulk Insert ERROR
Hi I'm trying to export data from a database on one Sql Server 2000 to another Sql Server 2000 on a different host. I'm using DTS to copy all the objects and data between servers. All the tables get created fine but when it tries to insert data i get a "Bulk Insert Error". It seems to work if i limit the rows to 100 using a query but i've got over 100,000 rows of data to copy. I've looked everywhere on the net for help but noone seems to have the same problem. Any ideas?
View Replies !
Error When Doing A Bulk Insert
I'm doing a bulk insert from a text file to sql server 7 I'm getting an error: Server: Msg 4867, Level 16, State 1, Line 1 Bulk insert data conversion error (overflow) for row 1, column 169 (LOT_WIDTH). Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error. The statement has been terminated. Now my lot-width field coming in is defined as a numeric 9(5). My table is defined as an INT. Any suggestion? I'm new to SQL7 Thanks Jason
View Replies !
Bulk Insert Error
Simple test project. Created Flat File connection, database connection (both local), and Bulk Insert Task. When running the package I get the following error: [Bulk Insert Task] Error: 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.Bulk load: An unexpected end of file was encountered in the data file.". I've tried different settings for the Flat File config, and the database connection, but still get the error. Any suggestions would be helpful. Tks.
View Replies !
Error Msg 141 On Bulk Insert
I've got the following SP to automatically insert all files in a directory into the database: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE Imp_Header_PO_sp @FilePath varchar(1000) = 'D:EBTOutbound', @WIPPath varchar(1000) = 'D:EBTOutboundWIP', @ArchivePath varchar(1000) = 'D:EBTOutboundArchive', @FileNameMask varchar(1000) = '*Header.txt' AS BEGIN SET NOCOUNT ON; declare @Filename varchar(1000), @File varchar(1000) declare @cmd varchar(2000) create table #Dir (s varchar(8000)) -- Move Header files to WIP select @cmd = 'move ' + @FilePath + @FileNameMask + ' ' + @WIPPath select @cmd = 'dir /B ' + @WIPPath + @FileNameMask delete #Dir insert #Dir exec master..xp_cmdshell @cmd delete #Dir where s is null or s like '%not found%' -- Import file while exists (select * from #Dir) begin select @FileName = min(s) from #Dir select @File = @WIPPath + @FileName select @cmd = 'bulk insert' select @cmd = @cmd + ' POWebOutHeader' select @cmd = @cmd + ' from' select @cmd = @cmd + ' ''' + replace(@File,'"','') + '''' select @cmd = @cmd + ' with (Fieldterminator = ',')' -- Import the data exec (@cmd) -- remove filename just imported delete #Dir where s = @FileName -- Archive the file select @cmd = 'move ' + @WIPPath + @FileName + ' ' + @ArchivePath + @FileName exec master..xp_cmdshell @cmd end drop table #Dir END GO When I try to execute the code, I get the following error, on this line: select @cmd = @cmd + ' with (Fieldterminator = ',')' Msg 141, Level 15, State 1, Procedure Imp_Header_PO_sp, Line 46 A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations. I've tried to find a fix for this error, but it seams to only relate to a select statement and not a Bulk Insert. Can someone please help me figure out how to fix this error? Thanks, Laura
View Replies !
Bulk Insert Error
Hi, I am using bulk insert to insert a lot of information from file to memory. In many cases it do the work but in one place it gives me the exception. My code: BULK INSERT tblCompVSNet1 FROM 'E:EasySeriesWindowsApplication1inDebug blCompVSNet1.tbl' WITH ( FIELDTERMINATOR = '|',ROWTERMINATOR = '|',LASTROW = 0,ROWS_PER_BATCH = 10000,CODEPAGE = 'RAW',TABLOCK) The error: Invalid object name 'tblCompVSNet1' The table exists and the query works fine in query analyzer but in code through OLEDB it doesn't work sometimes. How can I solve my problem? Thank's Alexei
View Replies !
Bulk Insert Eof Error
I am attempting to bulk insert a comma delimited text file with double quotes as the text qualifier but I keep getting an error message(EOF) on the bulk insert. I think the problem lies in my format file (see below) Please take a look and let me know what I am missing? Thanks, Matt Error message: 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)". Format File: 8.0 19 1 SQLCHAR 0 0 """ 0 first_quote SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 0 "","" 1 nt_id SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 0 "","" 2 first_name SQL_Latin1_General_CP1_CI_AS 4 SQLCHAR 0 0 "","" 3 last_name SQL_Latin1_General_CP1_CI_AS 5 SQLCHAR 0 0 "","" 4 department SQL_Latin1_General_CP1_CI_AS 6 SQLCHAR 0 0 "","" 5 phone SQL_Latin1_General_CP1_CI_AS 7 SQLCHAR 0 0 "","" 6 mgmt_level SQL_Latin1_General_CP1_CI_AS 8 SQLCHAR 0 0 "","" 7 emp_id SQL_Latin1_General_CP1_CI_AS 9 SQLCHAR 0 0 "","" 8 rc SQL_Latin1_General_CP1_CI_AS 10 SQLCHAR 0 0 "","" 9 subrc SQL_Latin1_General_CP1_CI_AS 11 SQLCHAR 0 0 "","" 10 location SQL_Latin1_General_CP1_CI_AS 12 SQLCHAR 0 0 "","" 11 floor SQL_Latin1_General_CP1_CI_AS 13 SQLCHAR 0 0 "","" 12 supervisor_id SQL_Latin1_General_CP1_CI_AS 14 SQLCHAR 0 0 "","" 13 status SQL_Latin1_General_CP1_CI_AS 15 SQLCHAR 0 0 "","" 14 hiredate SQL_Latin1_General_CP1_CI_AS 16 SQLCHAR 0 0 "","" 15 jobtitle SQL_Latin1_General_CP1_CI_AS 17 SQLCHAR 0 0 "","" 16 paygrade SQL_Latin1_General_CP1_CI_AS 18 SQLCHAR 0 0 "","" 17 id SQL_Latin1_General_CP1_CI_AS 19 SQLCHAR 0 0 "" " 18 email SQL_Latin1_General_CP1_CI_AS
View Replies !
BULK INSERT Error
Hello SQLTEAM I have a flat fix length file... H315620060417 H315620060417 I have a format file 8.0 2 1 SQLCHAR 0 5 "" 4 MCO_Number SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 8 " " 5 Run_Date SQL_Latin1_General_CP1_CI_AS I get the following BULK INSERT "BULK INSERT data conversion error (truncation) for row 1, column 1 (MCO_Number). Columns in destination table are nvarchar (5) and nvarchar (8). I have tried using " " and "" as row terminators. Any help appreciated.
View Replies !
Bulk Insert Error
My server updated from SQL2000 to SQL2005, SP2. I found error in bulk insert: - "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 1. Verify that the field terminator and row terminator are specified correctly.". I do read a few article saying that after apply SP2 and hotfixes, this error should be fix, but unfortunately, it is not in my case, what should i do to fix it? This is my script: - BULK INSERT wng01_work..nw_business_person FROM 'g:SQLFTPCDIS_Extractew_worker.dat' WITH ( MAXERRORS = 1, FORMATFILE ='g:sqlftpcdis_extractew_work.fmt' ) Please advice, Thank you
View Replies !
Bulk Insert Eof Error
I am attempting to bulk insert a comma delimited text file with double quotes as the text qualifier but I keep getting an error message(EOF) on the bulk insert. I think the problem lies in my format file (see below) Please take a look and let me know what I am missing? Thanks, Matt Error message: 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)". Format File: 8.0 19 1 SQLCHAR 0 0 """ 0 first_quote SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 0 "","" 1 nt_id SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 0 "","" 2 first_name SQL_Latin1_General_CP1_CI_AS 4 SQLCHAR 0 0 "","" 3 last_name SQL_Latin1_General_CP1_CI_AS 5 SQLCHAR 0 0 "","" 4 department SQL_Latin1_General_CP1_CI_AS 6 SQLCHAR 0 0 "","" 5 phone SQL_Latin1_General_CP1_CI_AS 7 SQLCHAR 0 0 "","" 6 mgmt_level SQL_Latin1_General_CP1_CI_AS 8 SQLCHAR 0 0 "","" 7 emp_id SQL_Latin1_General_CP1_CI_AS 9 SQLCHAR 0 0 "","" 8 rc SQL_Latin1_General_CP1_CI_AS 10 SQLCHAR 0 0 "","" 9 subrc SQL_Latin1_General_CP1_CI_AS 11 SQLCHAR 0 0 "","" 10 location SQL_Latin1_General_CP1_CI_AS 12 SQLCHAR 0 0 "","" 11 floor SQL_Latin1_General_CP1_CI_AS 13 SQLCHAR 0 0 "","" 12 supervisor_id SQL_Latin1_General_CP1_CI_AS 14 SQLCHAR 0 0 "","" 13 status SQL_Latin1_General_CP1_CI_AS 15 SQLCHAR 0 0 "","" 14 hiredate SQL_Latin1_General_CP1_CI_AS 16 SQLCHAR 0 0 "","" 15 jobtitle SQL_Latin1_General_CP1_CI_AS 17 SQLCHAR 0 0 "","" 16 paygrade SQL_Latin1_General_CP1_CI_AS 18 SQLCHAR 0 0 "","" 17 id SQL_Latin1_General_CP1_CI_AS 19 SQLCHAR 0 0 "" " 18 email SQL_Latin1_General_CP1_CI_AS
View Replies !
XLS Import: How To Skip Empty Or NULL Rows
Hi, I am working with my first spreadsheet as a data souce within SSIS. It has multiple worksheets with a table of values on each Rows are marked with dates and the columns are marked as intervals (1m, 3m, 2y, etc.). I have no control over the fact that the monthly format of the XLS file will be such that: 1. There are some blank rows between the start of the worksheet and the column headers. (the number may never change) 2. Most cells have decimals to represent interest rates, but one or two rows are mostly blank with one cell populated and some 'helpful text' in the same row describing the data. 3. The main worksheet has two tables side by side giving a 5-year moving average of data - one for one company, and another for a second company. 4. The monthly version of the file will have data added to the end (no historical changes to previous data). I think my main problem is configuring the package to gracefully 'skip' to the first populated row in each worksheet. How do I do that? The 'column headers in first row' option is checked, and that helps, but doesn't allow to skip N number of data rows like a flat file connection manager.
View Replies !
Error Message On Attempted Duplicate Insert
Hi All, I have a web form that inserts information into two tables in sql 2005 database on a submit button click. I have a unique key on the tables preventing duplicate information which works fine. The problem I have is that at the minute if a users tries to insert a duplicate row they just get the built in sql error message. Is there a way I can validate the information before if goes into the database and display perhaps a label telling the user of their error or is there a way I can customize the build in sql error message? I've had a search on various forums and sites and can't find any info that would help me. Thanks in advance Dave
View Replies !
'Cannot Insert Duplicate Key' Error With Identity Column As PK
I guess there is first for everything...I had never seen error like this before Violation of PRIMARY KEY constraint 'PK_table1'. Cannot insert duplicate key in object 'table1'. The statement has been terminated. In this case Primary Key is Identity column. I do not include Idenity column in Insert statement. Incidently SQL server machines had cluster failure couple of days before. I am not sure whether it has anything to do with it. I see this error randomly. How should I debug it
View Replies !
Error Handling And Bulk Insert
I do not understand the error handling of SQL Server here. Any error inbulk insert seems to halt the current T-SQL statement entirely, renderingit impossible to log an error. The first statement below executes asexpected, and were I to replace "print" with something meaningful I coulddo some useful error handling. The second statement just seems to totallybail out after the error, preventing me from doing any useful errorhandling. This is a problem b/c I would like to schedule bulk inserts andneed to be notified if there is a problem.The following can be run in QA to demonstrate:print 'BEFORE TYPICAL ERROR'raiserror('Some Error', 16, 10)if (@@ERROR <> 0) print 'I can catch and log this error - good!' elseprint 'I can not catch and log this error - bad!'print 'AFTER TYPICAL ERROR'goprint 'BEFORE BULK INSERT'Bulk insert Northwind.dbo.ordersfrom 'ThisFileDoesNotExist'if (@@ERROR <> 0) print 'I can catch and log this error - good!' elseprint 'I can not catch and log this error - bad!'print 'AFTER BULK INSERT'goTIA,Dave
View Replies !
Capturing Bulk Insert Error
Hi, Can someone help me out with capturing the bulk insert error.I have a job which calls a procedure in which I used the bulk insert command .If the bulk insert is failing due to some reason as wrong delimitor,wrong path etc then the job fails.I need to track that error and see that the job doesnt stop and goes onto the next cursor record. Thanks, Nodbek
View Replies !
Capturing Bulk Insert Error
Hi, Can someone help me out with capturing the bulk insert error.I have a job which calls a procedure in which I used the bulk insert command .If the bulk insert is failing due to some reason as wrong delimitor,wrong path etc then the job fails.I need to track that error and see that the job doesnt stop and goes onto the next cursor record. Thanks, Nodbek
View Replies !
Bulk Insert Returning Error
Help! I am importing a large comma delimited text file into an existing table useing the BULK INSERT command. The table is 4 colums (char16, char16, varchar50, char1). The first 100 or so lines go in without an error. then, I recieve an error stateing that an entry is too long for the field in the database, and kicks me out. The entry is 50 characters, which is allowed. Any ideas why this would happen?
View Replies !
Stream Error In Bulk Insert
I am getting the following error when I try to do a bulk insert Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error. The statement has been terminated. I have used the same input file through DTS and it loads with no problems, so I'm fairly sure the file is OK. I have only used the default DTS options eg mapping the input file straight to the table, so there is only a small amount of actual transforming being done. My only thought now is that there is a problem using bulk insert into a datetime field, and by using DTS this gats resolved. I can't find any help in the help file, anyone got any ideas?? Ta Mike
View Replies !
Bulk Insert Error...Urgent.
I am using the following bulk insert statement: bulk insert DB_Kash.dbo.tb_category from 'C:cpdataSUPPLIER_5305OUTPUTTb_Category.txt' with (formatfile = 'C:b_category.txt') This works on one sql server and the same code does not work on another server.I have taken care to see that the path is appropriate. Are there any server settings involved? The table structure is the same in both cases and the select into nulk copy option has been selected. The table has full text indexing set on it but I don't think that this would make any difference. The only error it gives is "ILE DB Stream reported an error.The stream does not provide any expalanation regarding this error." Something like this. Infact the same format file and datafile work fine when I am doing BCP. I have tried with select into bulk copy option on and off too. Any info on this is greatly appreciated. thanks Sush.
View Replies !
SSIS Error For Bulk Insert
im trying to do a bulk insert & am getting the following error . 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.Bulk load: An unexpected end of file was encountered in the data file.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 3 (calling_natr_addr_ind).". i have set the connectn timeout to 0, but the error persists. please help. thanks, zainab
View Replies !
Bulk Insert Error 7301
I have a query for bulk insert. It works fine. But when I use it today and run into following error message. error 7301:Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)". SET @Sql = 'BULK INSERT #FVF_Tmp FROM ''' + @FilePath +'''' + ' WITH (BATCHSIZE = 100000,FIRSTROW = 2,TABLOCK, DATAFILETYPE = '''+ 'widechar' + ''')' EXECUTE (@Sql) The @FilePath points to a .csv file then use data in temp table inser into a permanent table. The input excel file is .csv file with 5 columns. Thanks in advance.
View Replies !
SP Causes The Error Violation Of UNIQUE KEY Constraint Cannot Insert Duplicate Key
The following SP causes the error "Violation of UNIQUE KEY constraint 'AlumniID'. Cannot insert duplicate key in object [table name]. The statement has been terminated." AlumniID is the table's PK and is set to autoincrement. I'd appreciate any help or suggestions. 1 ALTER PROCEDURE dbo.sp_CreateUser 2 3 @UserID uniqueidentifier, 4 @UserName nvarchar(128), 5 @Email nvarchar(50), 6 @FirstName nvarchar(25), 7 @LastName nvarchar(50), 8 @Teacher nvarchar(25), 9 @GradYr int 10 11 AS 12 SET NOCOUNT ON; 13 --DECLARE @UserID uniqueidentifier 14 --SELECT @UserID = NULL 15 --SELECT @UserID = UserID FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName-- AND @ApplicationId = ApplicationId 16 INSERT INTO [table] 17 (UserID,UserName,Email,FirstName,LastName,Teacher,GradYr) 18 VALUES (@UserID,@UserName,@Email,@FirstName,@LastName,@Teacher,@GradYr
View Replies !
Type Mismatch Error On Bulk Insert
I am getting a type mismatch error when I do a bulk insert.---Begin Error Msg---Server: Msg 4864, Level 16, State 1, Line 1Bulk insert data conversion error (type mismatch) for row 1, column 14(STDCOST).---End Error Msg---The STDCOST is set to decimal (28,14) and is a formatted in Access as anumber, single with 14 decimal. I don't know why I would be getting a TypeMismatch error.Any idea?Mike
View Replies !
BULK INSERT Failing With Truncation Error...
Howdy y'all, what the hey am I doing wrong here? I am trying to suck in a HUGE flat file that is tab-delimited and each row ends with a hex :0D:0A. The first few lines of the file are: 00000000h: 31 30 30 30 32 09 32 30 30 33 2D 30 31 2D 32 39 ; 10002.2003-01-29 00000010h: 20 30 30 3A 30 30 3A 30 30 2E 30 30 30 09 32 30 ; 00:00:00.000.20 00000020h: 2E 33 39 30 30 09 31 39 2E 38 30 30 30 09 32 30 ; .3900.19.8000.20 00000030h: 2E 33 34 30 30 09 34 32 31 33 37 09 31 2E 30 30 ; .3400.42137.1.00 00000040h: 30 30 0D 0A 31 30 30 30 32 09 32 30 30 33 2D 30 ; 00..10002.2003-0 00000050h: 31 2D 33 30 20 30 30 3A 30 30 3A 30 30 2E 30 30 ; 1-30 00:00:00.00 00000060h: 30 09 32 30 2E 33 35 30 30 09 31 39 2E 38 30 30 ; 0.20.3500.19.800 00000070h: 30 09 31 39 2E 38 37 30 30 09 33 33 39 33 33 09 ; 0.19.8700.33933. here is my table script:CREATE TABLE [dbo].[HSF_Staging_TEST] ( [OSID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Date] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Time] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [High] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Low] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Price] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Volume] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Splits] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GOHere is my BULK INSERT statement:osql -S(local) -Uusername -Ppassword -Q "BULK INSERT Trades.dbo.HSF_Staging_Test FROM '\devserverinputfilesDataDataHSF.txt' WITH (FIELDTERMINATOR = ' ', ROWTERMINATOR = '', TABLOCK)" -o".HSF_Staging_Test_LOG.txt" -e".HSF_Staging_Test_ERR.txt"yeah, dang near perfect code, eh? ;) well...here is the error I get...Msg 4863, Level 16, State 1, Server TRADES, Line 1 Bulk insert data conversion error (truncation) for row 1, column 8 (Splits). Msg 4863, Level 16, State 1, Server TRADES, Line 1 Bulk insert data conversion error (truncation) for row 2, column 8 (Splits). Msg 4863, Level 16, State 1, Server TRADES, Line 1 Bulk insert data conversion error (truncation) for row 3, column 8 (Splits). Msg 4863, Level 16, State 1, Server TRADES, Line 1 Bulk insert data conversion error (truncation) for row 4, column 8 (Splits). Msg 4863, Level 16, State 1, Server TRADES, Line 1 Bulk insert data conversion error (truncation) for row 5, column 8 (Splits). Msg 4863, Level 16, State 1, Server TRADES, Line 1 Bulk insert data conversion error (truncation) for row 6, column 8 (Splits). Msg 4863, Level 16, State 1, Server TRADES, Line 1 Bulk insert data conversion error (truncation) for row 7, column 8 (Splits). Msg 4863, Level 16, State 1, Server TRADES, Line 1 Bulk insert data conversion error (truncation) for row 8, column 8 (Splits). Msg 4863, Level 16, State 1, Server TRADES, Line 1 Bulk insert data conversion error (truncation) for row 9, column 8 (Splits). Msg 4863, Level 16, State 1, Server TRADES, Line 1 Bulk insert data conversion error (truncation) for row 10, column 8 (Splits). Msg 4863, Level 16, State 1, Server TRADES, Line 1 Bulk insert data conversion error (truncation) for row 11, column 8 (Splits). Msg 4865, Level 16, State 1, Server TRADES, Line 1 Could not bulk insert because the maximum number of errors (10) was exceeded. Msg 7399, Level 16, State 1, Server TRADES, Line 1 OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error. OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.]. The statement has been terminated.Is the problem something about the :OD:0A at the end of each row, rather than just the :0A??? If so, how the heck do I specify that? I think based on my testing so far (also tried ' ' as the rowtermination param, but then it gives me a truncation error for the first row only, leading me to think it cannot find the end of the row that way. I am still looking through archives and on the web, but have not seen anything specific to my issue yet...and cannot believe that I am the first to BULK INSERT this kind of data. Help is appreciated! ~Paul
View Replies !
MS DTS Bulk Insert Fails Showing Error
Hi, I am new to MS DTS and i am using MS SQL 2000 as my database. I am trying to do a Bulk insert using MS DTS package. The package is trying to load data from Text file to a SQL 2000 table. When runninh the package i am getting an error saying that 1 task failed during execution and the task is shown in red colour indicating that the task has failed. Now when i get the details of the error it shows the follows: Could not bulk insert because the file D:DtsFile.txt could not be opened. Operation system error code: 21 (The device is not ready). Please help me in solving this problem, if any one has got this error and resolved or have any idea of the error please help. :) Regards, Rajeev Prabhu
View Replies !
Bulk Insert Error File Does Not Exist
Hello I am trying to bulk insert a text file into SQL 2005 table. When I execute the bulk insert I get the error "Msg 4860, Level 16, State 1, Line 1. Cannot bulk load. The file "\ENDUSER-SQLEnduserTextB1020063.txt" does not exist." The text file that it is saying does not exist I recently created thru my code. I can open the file but only when I rename the file will the Bulk Insert work. After creating the text file I am moving it to the server that SQL server is running on. Also if I run sp_FileExists it also says the file does not exist unless again I rename the file then this stored procedure recognizes the file. I dont' know if I have a permission issue or what is the problem. Any help would be appreiated. Thanks Chris
View Replies !
Error When I Try To Bulk Insert With A Format File
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 Replies !
Bulk Insert Task - Error File
Using BCP or BULK INSERT you can specify an Error File (-e and ERRORFILE). However this does not seem to be exposed in SSIS via the Bulk Insert Task. Does anyone know if I'm missing something and the Property is called something else or if can be accessed via script? Cheers, -Ryan
View Replies !
Header Rows To Skip On Flat File Import/Export
Just attempting to import a simple tab delimited text file into my SQL Server 2005 database using the SQL Server Import and Export wizard. Column names are specified within the first line of the file. The Header Rows to Skip field value is listed as 0, but the wizard indicates that "The field, Header rows to skip, does not contain a valid numeric value". Why isn't zero (0) a valid numeric value? I don't want to skip any rows. PLUS, I get the same error when trying to export to a text file although the header rows to skip field does not exist. I can increase the number to 1 or more, but the wizard will skip part of my data .. unacceptable. What am I missing here? I installed SP1 of SQL server 2005, but that did not help. Thanks in advance.
View Replies !
Skip First Row In A Datatable At Insert Into SQL...
Hi, I have the code below. I need to skip the first row in the datatable as it has the headers. This works now, but my gridview gets the header row inserted as a record.Private Shared Sub InsertData(ByVal sourceTable As System.Data.DataTable, ByVal destConnection As SqlConnection) ' old method: Lots of INSERT statements ' first, create the insert command that we will call over and over: destConnection.Open() Using ins As New SqlCommand("INSERT INTO [tblAppointmentDisposition] ([contactdate], [dnbnumber], [prospectname], [businessofficer], [phonemeeting], [followupcalldate2], [phonemeetingappt], [followupcalldate3], [appointmentdate], [appointmentlocation], [appointmentkept], [applicationgenerated], [applicationgenerated2], [applicationgenerated3], [comments], [newaccount], [futureopportunity]) VALUES (@contactdate, @dnbnumber, @prospectname, @businessofficer, @phonemeeting, @followupcalldate2, @phonemeetingappt, @followupcalldate3, @appointmentdate, @appointmentlocation, @appointmentkept, @applicationgenerated, @applicationgenerated2, @applicationgenerated3, @comments, @newaccount, @futureopportunity)", destConnection) ins.CommandType = CommandType.Text ins.Parameters.Add("@contactdate", SqlDbType.Text) ins.Parameters.Add("@dnbnumber", SqlDbType.Text) ins.Parameters.Add("@prospectname", SqlDbType.Text) ins.Parameters.Add("@businessofficer", SqlDbType.NVarChar) ins.Parameters.Add("@phonemeeting", SqlDbType.Text) ins.Parameters.Add("@followupcalldate2", SqlDbType.Text) ins.Parameters.Add("@phonemeetingappt", SqlDbType.Text) ins.Parameters.Add("@followupcalldate3", SqlDbType.Text) ins.Parameters.Add("@appointmentdate", SqlDbType.Text) ins.Parameters.Add("@appointmentlocation", SqlDbType.Text) ins.Parameters.Add("@appointmentkept", SqlDbType.Text) ins.Parameters.Add("@applicationgenerated", SqlDbType.Text) ins.Parameters.Add("@applicationgenerated2", SqlDbType.Text) ins.Parameters.Add("@applicationgenerated3", SqlDbType.Text) ins.Parameters.Add("@comments", SqlDbType.Text) ins.Parameters.Add("@newaccount", SqlDbType.Text) ins.Parameters.Add("@futureopportunity", SqlDbType.Text) ' and now, do the work: For Each r As DataRow In sourceTable.Rows For i As Integer = 0 To 16 ins.Parameters(i).Value = r(i) Next ins.ExecuteNonQuery() 'If System.Threading.Interlocked.Increment(rowscopied) Mod 10000 = 0 Then 'Console.WriteLine("-- copied {0} rows.", rowscopied) 'End If Next End Using destConnection.Close() End Sub
View Replies !
|
TRACKER
