Bulk Insert From Zero Byte File
Ok, hope someone has an idea out there. I have eight fixed width text files that I am importing into SQL 2000 tables in stored procedures called from a vb.net program. Problem is one of the files stands a good chance of being 0 k. As of I now i call one stored proc that then calls one proc for each file. If i try to run it anyhting after the zero byte file fails to import. If i remark it out everything else imports. I check for file.exists on the vb.net and the file passes the test. Any way to handle the 0 byte on the proc side
View Complete Forum Thread with Replies
Related Forum Messages:
How To Insert Data From A File Into Table Having Two Columns-BULK INSERT
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 Replies !
XML Format File For Bulk Insert Of Text File With Fixed Length Columns
Hey All, Similar to a previous post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=244646&SiteID=1), I am trying to import data into a SQL Table. I am trying to program a small application that will import product data obtained through suppliers via CD-ROM. One supplier in particular uses Fixed width colums, and data looks like this: Example of Data 0124015Apple Crate 32.12 0124016Bananna Box 12.56 0124017Mango Carton 15.98 0124018Seedless Watermelon 42.98 My Table would then have: ProductID as int Name as text Cost as money How would I go about extracting the data with an XML Format file? I am stumbling over how to tell it where to start picking up data for a specific column. Is there any way that I could trim the Name column (i.e.: "Mango Carton " --> "Mango Carton")? I don't know if it makes any difference, but I've been calling SQL from my code by doing this: Code in C# Form SqlConnection SqlConnection = new SqlConnection(global::SQLClients.Properties.Settings.Default.ClientPhonebookConnectionString); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "INSERT INTO PhonebookTable(Name, PhoneNumber) VALUES('" + txtName.Text.ToString() + "', '" + txtPhoneNumber.Text.ToString() + "')"; cmd.Connection = SqlConnection; SqlConnection.Open(); cmd.ExecuteNonQuery(); SqlConnection.Close(); RefreshData(); I am running Visual Studio C# Express 2005 and SQL Server Express 2005. Thanks for your time, Hayden.
View Replies !
BULK INSERT FROM UTF-8 FILE
Hi there,I have some text files saved using a UTF-8 encoding. The "BULK INSERT"statment in Sql Server 2000 is failing with a column length error. Savingthe file as ASCII removes the problem. However, I would like to import filesin UTF-8 format. I understand that the BCP tool, when used from the commandline, can take an "-F UTF8" argument, which allows it to work. Can this bedone from the SQL> prompt with the BULK INSERT statement?Cheers,Tobin
View Replies !
Bulk Insert From A .csv File
Hi folks, I have a small problem - I am unable to load data from a .csv file into a table in SQL Server. Here is the command I am running: BULK INSERT CCSProgramParticipation FROM 'c: est.csv' WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR = ',', ROWTERMINATOR = '' ) Data in test.csv is the following format: (date fields can be blank) NY580232,0,6/30/2006,3567,396,7/1/2005,9/9/2005 NY580232,0,6/30/2006,14850,462,12/12/2005, .... .... What I see is the data does get loaded; however, data from the following row is getting inserted in the last field of a particular row (previous row) - it seems like the rowterminator is being ignored. Has anyone encountered this issue? Please let me know your thoughts on this. Thanks so much! -Parul
View Replies !
How To Bulk Insert From A Txt File?
Well hi here is my text file "Kelly","Reynold","kelly@reynold.com" "John","Smith","bill@smith.com" "Sara","Parker","sara@parker.com" and a table with Id , name, surname, email the ID is a autoincrement thats why it gives me error any way to to skip this ID so let the sql create it automaticaly for every row?
View Replies !
Bulk Insert Command With Dbf File
hi friends i am using bulk insert command for txt files but now i want to use bulk insert command for dbf files so plz any one can tell me how to use this command for dbf files thanx in advance.........
View Replies !
Bulk Insert With Dynamic File Name
Hi everyone,I am trying to bulk insert some data from a csv file to a table. I can do it as part of a button on click event, but don't know how to do it using a stored procedure. This is what I have,ALTER PROCEDURE dbo.TestImportData ( @filename varchar(50) ) AS BULK INSERT dbo.[TestTable] FROM @filename WITH ( FIELDTERMINATOR = ',' ) /* SET NOCOUNT ON */ RETURNI get the error message "Incorrect syntax near '@filename', Incorrect syntax near 'with'). What am I doing wrong? What should I do? Please help!
View Replies !
Dynamic File Name For Bulk Insert
SQL Server 2K OK, I'm probably being a bone-head here and am clearly in over my head but how do you (or can you?) set up a Bulk Insert to take a dynamic path/file name? What I want to do is pass in the path and file name from an external process to a stored procedure that bulk inserts the content of the file and then does some other routines on it. I haven't had any luck getting Bulk Insert to run if the path/file name is not hard-coded in the sproc as a string. The point is to have a master routine that can exercise the process for several different customers and use meta data in a table to inform what file to bulk insert. Any suggestions? Thanks!
View Replies !
How To Bulk Insert A File With No Row Terminator?
Hi All, I have a file that has fixed row size of 148 and fixed column size, but the file has no end of line character. I know it is wierd but a client has made the file and refuses to change the format. So I am stuck with reading it the way it is. In Enterprise Manager, I used the Import/Export wizard and I specified fixed length and it let me specify 148 as the lenght of each line. Then it recognized the file and I was able to read it in. I saved the DTS package and I can run it over and over again using dtsrun. However I want to do the same thing using Bulk Insert. How do you specify fixed row length for Bulk insert and how do you give it individual column lengths? Thanks, Shab
View Replies !
Bulk Insert Using Format File.......
I want to use the bulk insert statement to insert data from a text file that contains more columns than the target sql table does. I am using SQL 7.0. I am using a format file, but I can't work out how to achieve the above. SQL books online (and the msdn website) do not describe how to do this, but it is intimated that it can be done. Any suggestions ? Regards, Stuart.
View Replies !
Bulk Insert Of Text File . Please Help .
Hi , I have imported an event log file from an NT server in a test database . The table has been created automatically with some 10 columns as Col001...Col002 ...and so on till Col0010 . Now i want to copy the data of the event logs to specific columns . So i created a table with name such as Evnt , date , time , server and evntdescription so that whenever i can execute a simple query like Select * from tablename where type = 'app' , Server = 'test1 ' . so that i get all the results for that server 'test1 with type 'application' . The problem is how do i insert that event log file into the table which i have created with different columns names . So that the data with 'App' should go to column 'type , data with server name should go to the column name server and so on .. I tried all the was but could not succeed . Can i get some help in this regard please through some stored procedures or through DTS , if its possible . The server is SQL Server 2000 with SP 1. Many thanks Anita .
View Replies !
Using A Format File With BULK INSERT
Hi, im attempting the following bulk insert statement: BULK INSERT gtaRatesTemp FROM 'D: estData.CSV' WITH (FIRSTROW = 3, FORMATFILE = 'D: estFormat.Fmt' testData.CSV example: "Country","Country Code","City","City Code","Currency","Item","Item Code","Address 1","Address 2","Address 3","Address 4","Telephone","Rating","Location 1","Location 2","Location 3","No. of","Dates","Dates","Days","Days","Min","Min","Basis","Twin","Single","Triple","Quad","Twin for sole use","Child","Child Age","Child Age","Meals included","Distance to City Centre (Kms)","Child Details","Special Conditions" "","","","","","","","","","","","","","","","","Rooms","From","To","From","To","Pax","Nights","","Price","Price","Price","Price","Price","Price","From","To","","","" "Australia","AA","Palm Cove, QLD","PALC","AUD(Australian Dollars)","ALASSIO ON THE BEACH(1BD POOL)","ALA","139 WILLIAMS ESPLANADE","PALM COVE","QUEENSLAND 4879","AUSTRALIA","61-7-40591550","4","Waterfront/Beach","","","24","14 Jun 2007","30 Jun 2007","Mon","Sun","1","2","Room","181.00","181.00","","","181.00","","","","Breakfast not included","1","","Cancellations: (14 Jun 2007-31 Mar 2008) 0-5 days prior 100% of total; 6-10 days prior 100% for 1 Night; 11 days or more No charge; " "Australia","AA","Palm Cove, QLD","PALC","AUD(Australian Dollars)","ALASSIO ON THE BEACH(1BD POOL)","ALA","139 WILLIAMS ESPLANADE","PALM COVE","QUEENSLAND 4879","AUSTRALIA","61-7-40591550","4","Waterfront/Beach","","","24","01 Jul 2007","31 Oct 2007","Mon","Sun","1","2","Room","231.00","231.00","","","231.00","","","","Breakfast not included","1","","Cancellations: (14 Jun 2007-31 Mar 2008) 0-5 days prior 100% of total; 6-10 days prior 100% for 1 Night; 11 days or more No charge; " "Australia","AA","Palm Cove, QLD","PALC","AUD(Australian Dollars)","ALASSIO ON THE BEACH(1BD POOL)","ALA","139 WILLIAMS ESPLANADE","PALM COVE","QUEENSLAND 4879","AUSTRALIA","61-7-40591550","4","Waterfront/Beach","","","24","01 Nov 2007","31 Mar 2008","Mon","Sun","1","2","Room","181.00","181.00","","","181.00","","","","Breakfast not included","1","","Cancellations: (14 Jun 2007-31 Mar 2008) 0-5 days prior 100% of total; 6-10 days prior 100% for 1 Night; 11 days or more No charge; " "Australia","AA","Palm Cove, QLD","PALC","AUD(Australian Dollars)","ANGSANA RESORT (1 BDRM BEACH)","ANG2","1 VEIVERS ROAD","PALM COVE","QUEENSLAND 4879","AUSTRALIA","61-7-40553000","5","Waterfront/Beach","","","67","14 Jun 2007","30 Jun 2007","Mon","Sun","1","1","Room","412.50","412.50","","","412.50","0","2","12","Breakfast not included","0","At this hotel an additional bed has not been provided in the room, child will need to share the existing bedding.","Cancellations: (14 Jun 2007-31 Mar 2008) 0-17 days prior 100% of total; 18-33 days prior 50% of total; 34 days or more No charge; " "Australia","AA","Palm Cove, QLD","PALC","AUD(Australian Dollars)","ANGSANA RESORT (1 BDRM BEACH)","ANG2","1 VEIVERS ROAD","PALM COVE","QUEENSLAND 4879","AUSTRALIA","61-7-40553000","5","Waterfront/Beach","","","67","01 Jul 2007","30 Nov 2007","Mon","Sun","1","1","Room","463.00","463.00","","","463.00","0","2","12","Breakfast not included","0","At this hotel an additional bed has not been provided in the room, child will need to share the existing bedding.","Cancellations: (14 Jun 2007-31 Mar 2008) 0-17 days prior 100% of total; 18-33 days prior 50% of total; 34 days or more No charge; " testFormat.Fmt: 8.0 36 1 SQLCHAR 0 50 """ 0 country SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 50 "","" 1 countryCode SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 200 "","" 2 city SQL_Latin1_General_CP1_CI_AS 4 SQLCHAR 0 50 "","" 3 cityCode SQL_Latin1_General_CP1_CI_AS 5 SQLCHAR 0 50 "","" 4 currency SQL_Latin1_General_CP1_CI_AS 6 SQLCHAR 0 500 "","" 5 item SQL_Latin1_General_CP1_CI_AS 7 SQLCHAR 0 50 "","" 6 itemCode SQL_Latin1_General_CP1_CI_AS 8 SQLCHAR 0 400 "","" 7 address1 SQL_Latin1_General_CP1_CI_AS 9 SQLCHAR 0 400 "","" 8 address2 SQL_Latin1_General_CP1_CI_AS 10 SQLCHAR 0 400 "","" 9 address3 SQL_Latin1_General_CP1_CI_AS 11 SQLCHAR 0 400 "","" 10 address4 SQL_Latin1_General_CP1_CI_AS 12 SQLCHAR 0 100 "","" 11 telephone SQL_Latin1_General_CP1_CI_AS 13 SQLCHAR 0 50 "","" 12 rating SQL_Latin1_General_CP1_CI_AS 14 SQLCHAR 0 100 "","" 13 location1 SQL_Latin1_General_CP1_CI_AS 15 SQLCHAR 0 100 "","" 14 location2 SQL_Latin1_General_CP1_CI_AS 16 SQLCHAR 0 100 "","" 15 location3 SQL_Latin1_General_CP1_CI_AS 17 SQLCHAR 0 100 "","" 16 noOfRooms SQL_Latin1_General_CP1_CI_AS 18 SQLCHAR 0 100 "","" 17 datesFrom SQL_Latin1_General_CP1_CI_AS 19 SQLCHAR 0 100 "","" 18 datesto SQL_Latin1_General_CP1_CI_AS 20 SQLCHAR 0 50 "","" 19 daysFrom SQL_Latin1_General_CP1_CI_AS 21 SQLCHAR 0 50 "","" 20 daysTo SQL_Latin1_General_CP1_CI_AS 22 SQLCHAR 0 100 "","" 21 minPAX SQL_Latin1_General_CP1_CI_AS 23 SQLCHAR 0 100 "","" 22 minNights SQL_Latin1_General_CP1_CI_AS 24 SQLCHAR 0 50 "","" 23 basis SQL_Latin1_General_CP1_CI_AS 25 SQLCHAR 0 100 "","" 24 twinPrice SQL_Latin1_General_CP1_CI_AS 26 SQLCHAR 0 100 "","" 25 singlePrice SQL_Latin1_General_CP1_CI_AS 27 SQLCHAR 0 100 "","" 26 triplePrice SQL_Latin1_General_CP1_CI_AS 28 SQLCHAR 0 100 "","" 27 quadPrice SQL_Latin1_General_CP1_CI_AS 29 SQLCHAR 0 100 "","" 28 twinForSoleUsePrice SQL_Latin1_General_CP1_CI_AS 30 SQLCHAR 0 100 "","" 29 childPrice SQL_Latin1_General_CP1_CI_AS 31 SQLCHAR 0 100 "","" 30 childAgeFrom SQL_Latin1_General_CP1_CI_AS 32 SQLCHAR 0 100 "","" 31 childAgeTo SQL_Latin1_General_CP1_CI_AS 33 SQLCHAR 0 50 "","" 32 mealsIncluded SQL_Latin1_General_CP1_CI_AS 34 SQLCHAR 0 50 "","" 33 distanceToCityCentre SQL_Latin1_General_CP1_CI_AS 35 SQLCHAR 0 500 "","" 34 childDetails SQL_Latin1_General_CP1_CI_AS 36 SQLCHAR 0 1500 "" " 35 specialConditions SQL_Latin1_General_CP1_CI_AS My problem is that once inserted, column 35(childDetails) and 36(specialConditions) are being merged into one. I think it may be an error with one of the values i have for the terminator in the format file but im unsure what exactly. Does anyone have any ideas?
View Replies !
Bulk Insert From Csv Using XML Format File
Hello, I am doing a bulk insert using a XML Format file from a csv file. Most everything works just fine, but my delimiter is a , the problem is one of the column sets sometimes contains a , within "" like this: value1,"value,2",value3 So when I do my insert it is distorting the column values because unlike excel it is not ignoring the comma within the quotes. Is there any way to set an attribute within the format file to prevent this from happening?
View Replies !
Bulk Insert, Bcp For Import Log File In Sql
i have a log file, i am trying to import data from it to SQL in order to analyze the data (able to query on the data), however that task seems impossible. In fact the log file contains a varying number of column fields (error logged, various types of data logged demand varying number of columns). More than that the fields themself are hard to extract. An example of data in my log is:xxxxxxxx is some alphanumeric chars2008-01-09 20:16:05,4784E36F.req,10.1.1.26,xxxxxxxxx,OK -- SMPP - xxxxxxx:xxxxx,Sender=xxxx;SMSCMsgId=2028eecc;Binary=1;DCS=8;Data=xxxxxxxxxxxxxx...2008-01-09 20:16:05,4784E338.req,10.1.1.26,xxxxxxxx,Retry Pending - ERROR: Timeout waiting for response from server or lost connection -- SMPP - xxxxxxxxxxx:xxxxx,Sender=xxxxx........ I may use regular expressions to extract the data, and maybe use a regular INSERT to put in the right table. Thus it seems like making a manual Bulk Insert(yeah and it may take much more time), it seems strange, can i use somehow some additional tool (in SQL package or external), to assist somehow. Thanks and sorry if this is double posted !
View Replies !
Bulk Insert From Text File
I import a group of sentences INSERT from a text file .... test Insert Into XXXXX Values('UUUUUU','3') Insert Into XXXXX Values('UUUUUU','3') Insert Into XXXXX Values('UUUUUU','3') Insert Into XXXXX Values('UUUUUU','3') Insert Into XXXXX Values('UUUUUU','3') Insert Into XXXXX Values('UUUUUU','3') Insert Into XXXXX Values('UUUUUU','3') Insert Into XXXXX Values('UUUUUU','3') The file contains 1000 insert (Aprox); I read lines for lines the file I make the insert In VS.NET 2003 it works correctly and the process consumes little memory but In VS.NET 2005 the pocket is without space. How I can specify the factor of growth of the database SQL Mobile? How another thing can be happening? I sorry for my inlges ... i speak spanish Thanks.
View Replies !
BUlk Insert Using Format File
I Have a fixed width file where the format look like below: f1 - 16 f2 - 64 f3 - 64.. ....etc and the format file that i created looks like: 8.0 20 1 SQLCHAR 0 16 "" 0 ExtraField 2 SQLCHAR 0 64 "" 0 ExtraField 3 SQLCHAR 0 64 "" 0 ExtraField 4 SQLCHAR 0 16 "" 1 DatabaseName 5 SQLCHAR 0 128 "" 0 ExtraField 6 SQLCHAR 0 24 "" 2 DelivaryDay 7 SQLCHAR 0 4 "" 0 ExtraField 8 SQLCHAR 0 3 "" 0 ExtraField 9 SQLCHAR 0 24 "" 0 ExtraField 10 SQLCHAR 0 3 "" 0 ExtraField 11 SQLCHAR 0 24 "" 0 ExtraField 12 SQLCHAR 0 64 "" 0 ExtraField 13 SQLCHAR 0 24 "" 0 ExtraField 14 SQLCHAR 0 24 "" 0 ExtraField 15 SQLCHAR 0 24 "" 3 CompleteDate 16 SQLCHAR 0 24 "" 0 ExtraField 17 SQLCHAR 0 24 "" 0 ExtraField 18 SQLCHAR 0 24 "" 0 ExtraField 19 SQLCHAR 0 24 "" 0 ExtraField 20 SQLCHAR 0 256 "" 0 ExtraField I need to take only three coulmns from the file and the text file won't contain any delimeters between the fields. I tried to execute this using Bulk..Insert and i am getting the error: Cannot bulk load. Invalid column number in the format file "C:sampleXXXX.fmt" Any one can help me what is the problem here? I am using SQL Server 2000.
View Replies !
Could Not Bulk Insert. File ' @PathFileName ' Does Not Exist.
Someone help me out .How to solve the problem.I built a stored procedure in MS SQL 2005 to bulk insert into a table by reading the .txt file. But my stored procedure throws an error. "Could not bulk insert. File ' @PathFileName ' does not exist." My stored given below :- CREATE PROCEDURE [dbo].[ps_CSV_Import] AS DECLARE @PathFileName varchar(2000) ----Step 1: Build Valid BULK INSERT Statement DECLARE @SQL varchar(2000) SELECT @PathFileName="D:Areazone.txt" BEGIN SET @SQL = "BULK INSERT Temp FROM '"+" @PathFileName "+"' WITH (FIELDTERMINATOR = '"",""') " END --Step 2: Execute BULK INSERT statement EXEC (@SQL) --Step 3: INSERT data into final table INSERT mstArea(Description,Refid) SELECT SUBSTRING(Description,2,DATALENGTH(Description)-1), SUBSTRING(RefId,1,DATALENGTH(RefId)-0) FROM Temp --Step 4: Empty temporary table TRUNCATE TABLE Temp Please help me ,if someone have any solution
View Replies !
BULK INSERT - Does It Lock The File While Reading?
Howdy, all. We have a (log-type) file that's constantly being writtento. We need to grab the latest rows out of this file, insert them intoa table, and then process them. We've found out the hard way that BCPlocks the file while it's inserting, so rows can't be written to thefile while the BCP is running.Our current workaround is to make a copy of the file (using ftp), butwe're running into other problems.I'm trying to find a better way to do this. We've narrowed down acouple.1) use TAIL or something similar to grab the records since we last ran.2) use BULK INSERT, provided it doesn't lock the file.3) get suggestions off of USENET. :)Any suggestions or comments?Thanks,Michael
View Replies !
Bulk Insert - Fixed Length File
I'm trying to do an insert using Bulk Insert with a fixed length file.I'm using a format file.I'm getting the following error message:Cannot perform bulk insert. Invalid collation name for source column 16in format file '\wbhq.comdfsdviDataIntGOPFilesGOPFormatFile. txt'.Any suggestions are appreciated.Thanks!JenniferFormat File Contents:8.0161SQLCHAR02""0Space""2SQLCHAR04""1YearID""3SQLCHAR02""0Space""4SQLCHAR02""2PeriodID""5SQLCHAR02""3CompanyID""6SQLCHAR01""0Dash""7SQLCHAR04""0Space""8SQLCHAR01""0Dash""9SQLCHAR04""4UnitID""10SQLCHAR01""0Dash""11SQLCHAR04""5AccountCode""12SQLCHAR05""0Space""13SQLCHAR01""6AccountType""14SQLCHAR029""0Space""15SQLCHAR016""7GLAmount""16SQLCHAR0105" "0Space""Bulk Insert Statement:BULK INSERT FlatFile_GOPFROM '\wbhq.comdfsdviDataIntGOPFilesGLPAM.GOP'WITH(FORMATFILE ='\wbhq.comdfsdviDataIntGOPFilesGOPFormatFile. txt')Table Definition:CREATE TABLE [dbo].[FlatFile_GOP] ([YearID] [smallint] NOT NULL ,[PeriodID] [smallint] NOT NULL ,[CompanyID] [smallint] NOT NULL ,[UnitID] [smallint] NOT NULL ,[AccountCode] [int] NOT NULL ,[AccountType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[GLBalance] [money] NOT NULL) ON [PRIMARY]GOFile Contents:2007 210- -0002-3000 G196395.102007 210- -0002-3700 B1484.002007 210- -0002-3700 G1571.132007 210- -0002-3800 B157457.002007 210- -0002-3800 G161577.73
View Replies !
Bulk Insert: Unexpected End-of-file (EOF) Encountered...
Hi to all,I have a problem about a importation of a file *.csv with SQL Server,through a bulk insert, called in a store procedure that a c# sw calls.This is the description of the error:-----System.Data.SqlClient.SqlException è stata individuataMessage="Bulk Insert: Unexpected end-of-file (EOF) encountered indata file. OLE DB provider 'STREAM' reported an error. The providerdid 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.]. Thestatement has been terminated."Source=".Net SqlClient Data Provider"ErrorCode=-2146232060Class=16LineNumber=1Number=4832Procedure=""Server="ets3971"State=1StackTrace:at System.Data.SqlClient.SqlConnection.OnError(SqlExc eptionexception, Boolean breakConnection)atSystem.Data.SqlClient.SqlInternalConnection.OnErro r(SqlExceptionexception, Boolean breakConnection)atSystem.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObjectstateObj)at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,SqlCommand cmdHandler, SqlDataReader dataStream,BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObjectstateObj)atSystem.Data.SqlClient.SqlCommand.FinishExecuteRead er(SqlDataReader ds,RunBehavior runBehavior, String resetOptionsString)atSystem.Data.SqlClient.SqlCommand.RunExecuteReaderT ds(CommandBehaviorcmdBehavior, RunBehavior runBehavior, Boolean returnStream, Booleanasync)atSystem.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehaviorcmdBehavior, RunBehavior runBehavior, Boolean returnStream, Stringmethod, DbAsyncResult result)atSystem.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResultresult, String methodName, Boolean sendToPipe)at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at sarbox.Default.LoadFlux_Click(Object sender, EventArgs e) inc:Inetpubwwwrootarbox2.2SoxAdminDefault.aspx .cs:line 1509---Th@nks to allAB@
View Replies !
BULK INSERT Flat File With Only One Column
Hi, I have a text file with a single column that i need to bulk insert into a table with 2 colums - an ID (with identity turned on) and col2 my text file looks like: row1 row2 row3 ... row10 so my bulk insert i have like this: BULK INSERT test FROM 'd: estBig.txt' WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR = ',', ROWTERMINATOR = ' ' ) but i get the error: Server: Msg 4866, Level 17, State 66, Line 1 Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly. However, as you can see from the text file, there is only one column, so i dont have any field terminators. Any ideas how to make this work? Thanks.
View Replies !
File Could Not Be Opened In Bulk Insert Task
hi with my ssis-package i have to read several flatfiles. this files are stored in different folders on a unix machine. in my loop-task i have first a script, that checks whether the file exists. it does, so i set the path to a variable. in the connection-manager for the flat-file i have set source for the file to that variable. the bulk-insert-task starts, read the file and everithing is cool. but, sometimes, the package fails with this message: [Bulk Insert Task] Error: An error occurred with the following error message: "Cannot bulk load because the file "\Owpu0kas 0 0 5 eceivedw000005.090" could not be opened. Operating system error code 53(error not found)." i stop the degugging-mode, restart debuging and what happens? it runs ... maybe for the next one, two or three files and the error comes again, but never on the same file. i'm going crazy, what can i do? thanks for your help.
View Replies !
Importing DB2 Flat File With Bulk Insert
HI, I am having trouble importing a flat file that was extracted from an AS400 server, into a SQL 2005 DB table using Bulk Insert. This file contains a column (field) that is of a Packed Decimal data type. Data in all other fields is displayed normally when viewing this file in a text editor such as Note Pad or Text Pad, but this one field comes up with unknown encoding: squares, thick vertical lines, basically strange characters and no numeric data. Does anyone have any experience dealing with file of this sort? Any insight would be appreciated. Thanks.
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 !
Could Not Bulk Insert. File ' @PathFileName ' Does Not Exist.
Someone help me out .How to solve the problem.I built a stored procedure in MS SQL 2005 to bulk insert into a table by reading the .txt file. But my stored procedure throws an error."Could not bulk insert. File ' @PathFileName ' does not exist."My stored given below :-CREATE PROCEDURE [dbo].[ps_CSV_Import]AS DECLARE @PathFileName varchar(2000) ----Step 1: Build Valid BULK INSERT Statement DECLARE @SQL varchar(2000) SELECT @PathFileName="D:Areazone.txt" BEGIN SET @SQL = "BULK INSERT Temp FROM '"+" @PathFileName "+"' WITH (FIELDTERMINATOR = '"",""') " END--Step 2: Execute BULK INSERT statementEXEC (@SQL)--Step 3: INSERT data into final tableINSERT mstArea(Description,Refid)SELECT SUBSTRING(Description,2,DATALENGTH(Description)-1), SUBSTRING(RefId,1,DATALENGTH(RefId)-0) FROM Temp--Step 4: Empty temporary tableTRUNCATE TABLE TempPlease help me ,if someone have any solution
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 !
Bulk Insert From Txt File Less Data Than Columns
Hi, I´m trying to bulk insert files that looks like this: aaaa,bbb,dddd, ccc,dfd,tghj, each file can have up to 10 data fileds per line, and each file will have same number of data fileds in particular file, let´s say 3 like above. Second file could have let´s say 10 and that is maximum. I read the file and insert data with fieldterminator in temp table from witch I insert data to other tables regarding some parameters inside. Now problem is: 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)". That is because I´m trying to insert 3 fields of data in temporary table which is made of 10 columns (It have to be 10 because next file could have 10 fileds of data). If the temp table has same number of columns like text file has data fields than it works. What is solution for this problem? Can I bulk insert NULL in columns for which I don't have data? I can also import each line of text file to one column (with delimiter inside) but than I don´t know how to insert that data to correct tables or even to one table but to seperate data fields to columns with fieldterminator which is , in this case. I'm new to SQL and I would apriciate any help. Thank you
View Replies !
How To Bulk Insert A File With Fixed Row Length And No Row Terminator?
Hi All, I have a file that has fixed row size of 148 and fixed column size, but the file has no end of line character. I know it is wierd but a client has made the file and refuses to change the format. So I am stuck with reading it the way it is. In Enterprise Manager, I used the Import/Export wizard and I specified fixed length and it let me specify 148 as the lenght of each line. Then it recognized the file and I was able to read it in. I saved the DTS package and I can run it over and over again using dtsrun. However I want to do the same thing using Bulk Insert. How do you specify fixed row length for Bulk insert and how do you give it individual column lengths? Thanks, Shab
View Replies !
Bulk Insert Into Table With More Columns Than Data Within File
Hey all I have a bulk insert situation that would be nice to be able to pull off. I have a flat file with 46 columns that are to go into a table. The table, I want to have a 47th column to be updated later on by means of a stored proc saying if the import into the system was sucessful or not. I have the rowterminator set as '"' thinking that would tell SQL to begin on the next row, leaving the importstatus column null but i still receive an error. First of all, is this idea possible within this insert statement. Secondly, if so, what would be the syntax to tell the insert statement to skip that particular column. It is the last column listed in the table so it just needs to start on the next row after it inserts the last bit of data in the flatfile. If this is not possible, is it possible to bulk insert into a temp table? Thanks
View Replies !
BULK Insert From A Text File Name Stored Into A Variable
Hello I need to write a proc to load data from txt files I receive into a table. It works fine when I specify bulk insert.... from 'myfilename.txt' BUT my filename will always change and I store it into a variable @filename When I try to run the bulk insert instruction ... from @filename it doesn't work.. do you know why? Thank you in advance
View Replies !
ForEach File Enumeration With Bulk Insert Problem
OK, a new package, with a Foreach container enumerating CSV files in a directory. I create the container pointing it at the directory and retrieving the fully qualified name, and create a variable (called 'CSVFiles') with a package scope, but no value. Inside the container is a bulk insert task. The destination db/table is set, and the input flat file connection manager for the CSV files is defined with the connection string set to the variable created above. As it iterates through the files, the variable is correctly set to the next file in the directory (I put a message box in the stream to display the file name/variable). It resembles 'C: empLocation1.csv'. But when it gets to the bulk insert, I get this error message: [Bulk Insert Task] Error: The specified connection "CSVFiles" is either not valid, or points to an invalid object. To continue, specify a valid connection. What's going on here? Can I not use a bulk insert task in the container? Or some other parameter needs to be set? SQL Server 9.00.3159
View Replies !
Inconsistent Errors Using Bulk Insert With A Format File
As part of a c# program, utilizing .Net 2.0, I am calling a sproc via a SqlCommand to bulk load data from flat files to a various tables in a SQL Server 2005 database. We are using format files to do this, as all of the incoming flat files are fixed length. The sproc simply calls a T-SQL BULK INSERT statement, accepting the file name, format file name and the database table as input paramaters. As expected, this works most of the time, but periodically (to often for a production environment), the insert fails. The particular file to fail is essentially random and when I rerun the process, the insert completes successfully. A sample of the error messages returned is as follows (@sql is the string executed): Cannot bulk load. Invalid destination table column number for source column 1 in the format file "\RASDMNTTRAS_ROOTBCP_Format_FilesEMODT3.fmt". Starting spRAS_BulkInsertData. @sql = BULK INSERT Raser.dbo.EMODT3_Work FROM '\RASDMNTTRAS_ROOTAmeriHealthworkpdclmsemodt3.20060511.0915.txt.DATA' WITH (FORMATFILE = '\RASDMNTTRAS_ROOTBCP_Format_FilesEMODT3.fmt'); The format file for this particular example is as follows (I apologize for the length): 8.0 62 1 SQLCHAR 0 1 "" 1 Record_Type SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 15 "" 2 Vendor_Number SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 20 "" 3 Extract_Subscriber_Number SQL_Latin1_General_CP1_CI_AS 4 SQLCHAR 0 20 "" 4 Extract_Member_Number SQL_Latin1_General_CP1_CI_AS 5 SQLCHAR 0 2 "" 5 Claim_Nbr_Branch_Code SQL_Latin1_General_CP1_CI_AS 6 SQLCHAR 0 8 "" 6 Claim_Nbr_Batch_Date_CCYYMMDD SQL_Latin1_General_CP1_CI_AS 7 SQLCHAR 0 3 "" 7 Claim_Nbr_Batch_Sequence_Nbr SQL_Latin1_General_CP1_CI_AS 8 SQLCHAR 0 3 "" 8 Claim_Nbr_Sequence_Number SQL_Latin1_General_CP1_CI_AS 9 SQLCHAR 0 3 "" 9 LINE_NUMBER SQL_Latin1_General_CP1_CI_AS 10 SQLCHAR 0 1 "" 10 Patient_Sex_Code SQL_Latin1_General_CP1_CI_AS 11 SQLCHAR 0 3 "" 11 Patient_Age SQL_Latin1_General_CP1_CI_AS 12 SQLCHAR 0 4 "" 12 G_L_Posting_Tables_Code SQL_Latin1_General_CP1_CI_AS 13 SQLCHAR 0 50 "" 13 G_L_Posting_Tbls_Code_Desc SQL_Latin1_General_CP1_CI_AS 14 SQLCHAR 0 2 "" 14 Fund_TYPE SQL_Latin1_General_CP1_CI_AS 15 SQLCHAR 0 1 "" 15 Stop_Loss_Or_Step_Down_Code SQL_Latin1_General_CP1_CI_AS 16 SQLCHAR 0 2 "" 16 Stop_Loss_Fund SQL_Latin1_General_CP1_CI_AS 17 SQLCHAR 0 50 "" 17 Stop_Loss_Fund_Desc SQL_Latin1_General_CP1_CI_AS 18 SQLCHAR 0 8 "" 18 Post_Date SQL_Latin1_General_CP1_CI_AS 19 SQLCHAR 0 1 "" 19 Rebundling_Status_Indicator SQL_Latin1_General_CP1_CI_AS 20 SQLCHAR 0 8 "" 20 Co_Payment_Grouper SQL_Latin1_General_CP1_CI_AS 21 SQLCHAR 0 50 "" 21 Co_Payment_Grouper_Desc SQL_Latin1_General_CP1_CI_AS 22 SQLCHAR 0 8 "" 22 Co_Payment_Accumulator SQL_Latin1_General_CP1_CI_AS 23 SQLCHAR 0 50 "" 23 Co_Payment_Accumulator_Desc SQL_Latin1_General_CP1_CI_AS 24 SQLCHAR 0 8 "" 24 Co_Insurance_Grouper SQL_Latin1_General_CP1_CI_AS 25 SQLCHAR 0 50 "" 25 Co_Insurance_Grouper_Desc SQL_Latin1_General_CP1_CI_AS 26 SQLCHAR 0 8 "" 26 Co_Insurance_Accumulator SQL_Latin1_General_CP1_CI_AS 27 SQLCHAR 0 50 "" 27 CI_Accumulator_Desc SQL_Latin1_General_CP1_CI_AS 28 SQLCHAR 0 8 "" 28 Coverage_Grouper SQL_Latin1_General_CP1_CI_AS 29 SQLCHAR 0 50 "" 29 Coverage_Grouper_Desc SQL_Latin1_General_CP1_CI_AS 30 SQLCHAR 0 8 "" 30 Coverage_Accumulator SQL_Latin1_General_CP1_CI_AS 31 SQLCHAR 0 50 "" 31 Coverage_Accumulator_Desc SQL_Latin1_General_CP1_CI_AS 32 SQLCHAR 0 8 "" 32 Deductible_Grouper SQL_Latin1_General_CP1_CI_AS 33 SQLCHAR 0 50 "" 33 Deductible_Grouper_Desc SQL_Latin1_General_CP1_CI_AS 34 SQLCHAR 0 8 "" 34 Deductible_Accumulator SQL_Latin1_General_CP1_CI_AS 35 SQLCHAR 0 50 "" 35 Deductible_Accumulator_Desc SQL_Latin1_General_CP1_CI_AS 36 SQLCHAR 0 8 "" 36 Unit_Grouper SQL_Latin1_General_CP1_CI_AS 37 SQLCHAR 0 50 "" 37 Unit_Grouper_Desc SQL_Latin1_General_CP1_CI_AS 38 SQLCHAR 0 8 "" 38 Unit_Accumulator SQL_Latin1_General_CP1_CI_AS 39 SQLCHAR 0 50 "" 39 Unit_Accumulator_Desc SQL_Latin1_General_CP1_CI_AS 40 SQLCHAR 0 8 "" 40 Out_Of_Pocket_Grouper SQL_Latin1_General_CP1_CI_AS 41 SQLCHAR 0 50 "" 41 Out_Of_Pocket_Grouper_Desc SQL_Latin1_General_CP1_CI_AS 42 SQLCHAR 0 8 "" 42 Out_Of_Pocket_Accumulator SQL_Latin1_General_CP1_CI_AS 43 SQLCHAR 0 50 "" 43 Out_Of_Pocket_Acc_Desc SQL_Latin1_General_CP1_CI_AS 44 SQLCHAR 0 3 "" 44 Service_Edit_Code SQL_Latin1_General_CP1_CI_AS 45 SQLCHAR 0 50 "" 45 Service_Edit_Code_Desc SQL_Latin1_General_CP1_CI_AS 46 SQLCHAR 0 8 "" 46 System_Date_MEDMAS_CCYYMMDD SQL_Latin1_General_CP1_CI_AS 47 SQLCHAR 0 8 "" 47 Last_Change_MEDMAS_CCYYMMDD SQL_Latin1_General_CP1_CI_AS 48 SQLCHAR 0 10 "" 48 Medicare_Termination_Reason_Code SQL_Latin1_General_CP1_CI_AS 49 SQLCHAR 0 10 "" 49 User_ID_MEDMAS SQL_Latin1_General_CP1_CI_AS 50 SQLCHAR 0 10 "" 50 User_ID_Last_Modified SQL_Latin1_General_CP1_CI_AS 51 SQLCHAR 0 8 "" 51 Adjudication_Date_CCYYMMDD SQL_Latin1_General_CP1_CI_AS 52 SQLCHAR 0 9 "" 52 Adjudication_Time SQL_Latin1_General_CP1_CI_AS 53 SQLCHAR 0 10 "" 53 Adjudication_User_ID SQL_Latin1_General_CP1_CI_AS 54 SQLCHAR 0 9 "" 54 A_P_Batch_Number SQL_Latin1_General_CP1_CI_AS 55 SQLCHAR 0 7 "" 55 A_P_Sequence SQL_Latin1_General_CP1_CI_AS 56 SQLCHAR 0 3 "" 56 CPA_Batch_Number SQL_Latin1_General_CP1_CI_AS 57 SQLCHAR 0 8 "" 57 CPA_Date_CCYYMMDD SQL_Latin1_General_CP1_CI_AS 58 SQLCHAR 0 1 "" 58 Manual_Authorization_Flag SQL_Latin1_General_CP1_CI_AS 59 SQLCHAR 0 50 "" 59 Fund_Description SQL_Latin1_General_CP1_CI_AS 60 SQLCHAR 0 1 "" 60 DRG_Inclusion_Indicator SQL_Latin1_General_CP1_CI_AS 61 SQLCHAR 0 1 "" 61 Future_Expansion SQL_Latin1_General_CP1_CI_AS 62 SQLCHAR 0 2 " " 62 Company_Number SQL_Latin1_General_CP1_CI_AS Has anyboy run across this before, or have any ideas as to what might be happening? Thanks in advance.
View Replies !
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 !
Bulk Insert - Inserting Only One Column From Data File
Hi, I have a data file in the folloing format SubjectId1|class1 SubjectId2|class2 SubjectId3|class3 I just wanted to insert only SubjectIds into my table 'Subjects' which has the follwing schama ignoring the classes The row delimeter is " " and the column delimeter is ' | ' Table Subjects { ID (Autoincrement) SubjectId varchar(20) } Can any one provide the format file for doing this or suggest anyway to do this? Please do note that the file may contain millions of records Thank u ~mohan
View Replies !
Bulk Insert From Native Format Data File.
With "bcp MyDatabase.dbo.MyTable out C:MyFile.Dat -n -T" command line, I could get an exported data file. And I can also import this file into MyTable using 'BULK INSERT MyDatabase.dbo.MyTable FROM 'C:MyFile.dat' WITH (DATAFILETYPE='native');' query statement. Now, I want to make my own data file just like made by bcp above. Although I could make file of 'char' type, 'native' type file is needed for performance and other reasons. And the format file should not be used. Any one help?
View Replies !
Error Using Bulk Insert On A Comma Delimited File
I am having a problem using the Bulk Insert task. I am getting the msg: SSIS package "Package.dtsx" starting. Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "You do not have permission to use the bulk load statement.". Task failed: Bulk Insert Task SSIS package "Package.dtsx" finished: Success. I have been granted ownership of the database. I also tried in one of my old databases that I just finished developing and I got the same msg. The file I am importing is comma delimited. I am importing it into a table that has 50 bytes allocation for each field (the max input field size is 40 bytes). The connection is solid; Format = “Specify� RowDelimiter = {CR}{LF} columnDelimiter = Comma {,} No other options are set. The data looks like: "tstLName","tstFname","000 N Tst DR","IDAHO sp","ID","00000000", Any ideas why I am getting this message?
View Replies !
Bulk Insert Into Sql Database Using Xml File Within Stored Proc Not Working
Ive created a table with a few fields, created a stored proc that uses and xml file to to a bulk insert into the database. When i run the stored proc and pass the parameters below it does not insert the values into the database. Can someone please help for when i try to debug the stored proc it does not display anything so i am really stuck here. I created the following table CREATE TABLE [absorbentorder] ( [dpc] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [absorbentcode] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [quantity] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]GO Here is the code for the stored proc: CREATE PROC AbsorbentOrderBulkInsertXML @items nText AS DECLARE @hDoc int exec sp_xml_preparedocument @hDoc OUTPUT, @items Insert Into absorbentorderSELECT dpc, absorbentcode, quantity FROM OPENXML (@hDoc, '/absorbentitems',1)WITH (dpc char(15), absorbentcode char(15), quantity char(15)) XMLItems EXEC sp_xml_removedocument @hDoc GO Here is the parameter for the stored proc <absorbentitems><item dpc="501"></item><item absorbentcode="CL1022"></item><item quantity="6"></item><item dpc="501"></item><item absorbentcode="CL1088"></item><item quantity="8"></item><item dpc="501"></item><item absorbentcode="CL1193"></item><item quantity="8"></item></absorbentitems> thanks! Alex Zaffalex@cleanalliance.comEnvironmental Services
View Replies !
Read A File Name From Network Folder Automatically For A BULK INSERT
Hi again all, Is there a way to read a file name automatically from a network folder? I can successfully bulk insert from this particular folder. The next step is as I add files, I wish to bulk insert the latest file added so the program must make that determination and import that specific file. I can delete the older files if necessary and save them elsewhere but it would still be nice to be able to read the file name. I then wish to store the name of this file, whatever it is, into a field called "SourceFileName" in my table that I am bulk inserting into. Does anyone have an example in dynamic SQL? Thanks. ddave
View Replies !
BULK INSERT, Setting Static Data Using The Format File
Hello dbforums, I are using a BULK INSERT to insert the data from a ascii file to a sql table. The table has a ProductInstanceId column that exists in the tables but does not exist in the ascii DICast data. I am setting the ProductInstanceId to a Guid that will be used for Metrics. I would like to create the Guid in C++ and then set it somehow during the BULK INSERT DICastRaw1hr and DICastRaw6hr. I am calling the BULK INSERT from C++/ADO. I do not see how you can set a static data in the BULK INSERT for a column that exists in the table but does not the source data ... seems there should be a way to do this with the format file? The other way to do this is with a TRIGGER. I have the TRIGGER below. Prior to the calling the BULK INSERT using ADO I will use ADO to ALTER the TRIGGER with the new Guid. When the BULK INSERT runs the ProductInstanceId will be populated with the new Guid. ALTER TRIGGER DICastRaw1hrInsertGuid ON Alphanumericdata.dbo.DICastRaw1hr FOR INSERT AS UPDATE dbo.DICastRaw1hr SET ProductInstanceId = '4f9a44eb-092b-445b-a224-cc7cdd207092' WHERE modelrundatetime = (select max(modelrundatetime) from Alphanumericdata.dbo.DICastraw1hr(NOLOCK)) More Questions: - The Trigger is slow. The Bulk Insert without the Trigger runs in about 10 sec ... with the Trigger in about 40 sec. I tried to use the sql code below in the TRigger but it was only doing the UPDATE on the last row. The TRIGGER must run after the BULK INSERT is complete. Now I am using the select (bad). Any comments ... ALTER TRIGGER DICastRaw1hrInsertDate ON Alphanumericdata.dbo.DICastRaw1hr FOR INSERT AS DECLARE @ID as integer SELECT @ID = i.recordid from inserted i UPDATE dbo.DICastRaw1hr SET ProductInstanceId = '4f9a44eb-092b-445b-a224-cc7cdd207092' WHERE recordid = @ID - I understand that I could set the Guid in the Default Value part of the table definition using the NEWID() function. I need the Guid to be the same for all the rows that are inserted during the BULK INSERT (all have the same modelrundatetime) ... how would I do this? Thanks, Chris
View Replies !
Bulk Insert Fails. Column Is Too Long In The Data File
Hi, for testing purposes I'm inserting a flat file into a sql-server table using BULK INSERT unsig the following code: BULK INSERT rsk_staging FROM 'c: empulk sk.txt' WITH ( FIELDTERMINATOR = '', ROWTERMINATOR = ' ', CODEPAGE = 'RAW', DATAFILETYPE = 'char', BATCHSIZE = 100000, ROWS_PER_BATCH = 1925604, TABLOCK ) I have two versions of "rsk.txt" one with 1.9mill rows and one with the first 2000 rows only. The files have one column only with 115 characters that I'll split in to several columns later using SUBSTRING. The one with 2000 rows fires in to the database with no problems whatsoever using this exact code, the other one throws the following error: Server: Msg 4866, Level 17, State 66, Line 1 Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly. How can I resolve this problem? EDIT: I tried several different row- and fieldterminators but this exact one works for the small data-file so I assume it should also work for the large one...the large one is however copyed directly using binary ftp from a unix-filesystem and the small one is manually copied into a new txt-file using UltraEdit.
View Replies !
Problems Creating Error File When Using Bulk Insert Or BCP From Xp_cmdshell.
BCP thru xp_cmdshell from stored procedure: EXEC sp_configure 'show advanced options', 1; RECONFIGURE EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE EXEC xp_cmdshell 'bcp database.dbo.table in c:scheduled.csv -S SERVERSQLEXPRESS -T -t, -r -c -e "error.txt"'; This is returning the following error code. I even tried placing the command in a seperate command file and calling that with no success. If I run this from the command line the error file generation does work. ================================================================= SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native Client]Unable to open BCP error-file ================================================================= Error message when using BULK INSERT as follows: BULK INSERT database.dbo.table from 'c:unscheduled.csv' with (FIELDTERMINATOR = ',', ERRORFILE = 'c:error.txt'); Returns the following error message: ================================================================= Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9 Cannot bulk load because the file "c:error.txt" could not be opened. Operating system error code 80(The file exists.). Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9 Cannot bulk load because the file "c:error.txt.Error.Txt" could not be opened. Operating system error code 80(The file exists.). ================================================================= The Bulk Insert actually creates a empty error.txt file (0kb) and never preforms the insert, I can not find any examples of anyone using the -ERRORFILE switch on BULK INSERT. Prolly some default security setting to allow file creation/modification I am missing. Anyone help me out? Thanks. EDIT: SQL SERVER EXPRESS 2005 - WINXP PRO SP2
View Replies !
Problem With BULK INSERT ASCII File Into Nvarchar Column
Hi, I have a problem with BULK INSERT. I created the following table: Code Snippetcreate table Test (id char(4), name nvarchar(16), last char(1)) I am trying to bulk insert data from ASCII (not unicode) file with only two rows: 0011First name 0018Second name Since it is a fixed length file, I am using the following format file: Code Snippet 8.0 3 1 SQLCHAR 0 4 "" 1 ID HEBREW_CI_AS 2 SQLCHAR 0 16 "" 2 NAME HEBREW_CI_AS 3 SQLCHAR 0 0 " " 3 Last HEBREW_CI_AS With bcp utility everything works just fine! Code Snippet bcp Demo.dbo.test in c: est -T -f c: est.fmt But when I use BULK INSERT in the following form: Code Snippet BULK INSERT Test FROM 'c:Test' WITH ( FORMATFILE='c:Test.fmt', CODEPAGE='OEM' ); I am getting error Server: Msg 4863, Level 16, State 1, Line 1 Bulk insert data conversion error (truncation) for row 1, column 2 (name). Now, one interesting thing: if I change the name field from nvarchar to varchar, it is working with BULK INSERT as well. Can anybody explain what is going on here? I am using MS SQL 2000 and MSDE Thanks in advance, Eugene.
View Replies !
|
TRACKER
