DB Engine :: Error Trying To Execute BULK INSERT In Stored Procedure
May 16, 2015
At my customer's site they get this error trying to run a stored procedure I wrote that does BULK INSERT.
-2147217900
[Microsoft ODBC SQL Server Driver][SQL Server] You do not have permission to use the bulk load statement.
upImportFromICPMSRaw 'GSADC1CompanyInstrumentOutputFilesICPMSNew185367.csv', tblFromICPMSRaw
The customer has SQL Server 2008 R2 Express installed
The connection string to the database works on everything else and it is the sa account with password
On my own development system with SQL Server 2008 R2 Standard, it works perfectly OK.
View 5 Replies
ADVERTISEMENT
Jan 26, 2006
Firstly, I am new to sql so please excuse my ignorance!
I have a stored procedure that tests the import of about 100 text files. This is to screen for file errors prior to processing. The results for each file are held in a table. The procedure script is as follows:
<snip>
insert into st_fileimport (dealernumber, handheld_file)
values ('01', 'Fail')
BULK INSERT dbo.filetesthandheld
FROM 'c:inetpubwwwrootS_Datadealers 1foo.csv'
WITH
(
FIELDTERMINATOR = ',' ,
ROWTERMINATOR = '' ,
ROWS_PER_BATCH = 500
)
update st_fileimport
set handheld_file = 'Pass' where dealernumber = '01'
</snip>
This is then repeated for each file. This works fine, but as soon as an error is encountered the script terminates which means that you need to re-run this procedure iteratively until all files pass.
Is there a way of structuring the stored procedure so it will fail on one import and then move onto the next? Or, a better methodology all together?
Any help or advise would be greatly appreciated.
Karen
View 1 Replies
View Related
Sep 28, 2007
Hi all!!
I have a stored procedure that dynamically bulk loads several tables from several text files. If I encounter an error bulk loading a table in the stored procedure, all I get is the last error code produced, but if I run the actual bulk load commands through SQL Management Studio, it gives much more usable errors, which can include the column that failed to load. We have tables that exceed 150 columns (don't ask), and having this information cuts troubleshooting load errors from hours down to minutes. Onto my question..., is there any way to capture all of the errors produced by the bulk load from within a stored procedure (see examples below)?
Running this...
BULK INSERT Customers
FROM 'c: estcustomers.txt'
WITH (TabLock, MaxErrors = 0, ErrorFile = 'c: estcustomers.txt.err')
Produces this (notice column name at the end of the first error)...
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 1 (CustId).
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)".
Running this (similar to code in my stored procedure)...
BEGIN TRY
BULK INSERT Customers
FROM 'c: estcustomers.txt'
WITH (TabLock, MaxErrors = 0, ErrorFile = 'c: estcustomers.txt.err')
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
Produces something similar to this (which is useless)...
...Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
View 3 Replies
View Related
Jan 22, 2008
Hi,
I am strugling to execute a insert stored procedure on a button click. The stored procedure is taking values from a temp table and inserting them into a identical table. The procedure is expecting 1 value from a Query string, the stored procedure works as expected when hard coded.
Im completely new to this and have no idea where to begin, i have been looking through the forums for several hours and am still none the wiser.
please can someone point me in the right direction
View 13 Replies
View Related
Apr 8, 2008
I receive the following error message when I try to use the Bulk Insert Task to load BCP data into a table:
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 4. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (overflow) for row 1, column 1 (rowno).".
Task failed: Bulk Insert Task
In SSMS I am able to issue the following command and the data loads into a TableName table with no error messages:
BULK INSERT TableName
FROM 'C:DataDbTableName.bcp'
WITH (DATAFILETYPE='widenative');
What configuration is required for the Bulk Insert Task in SSIS to make the data load? BTW - the TableName.bcp file is bulk copy file as bcp widenative data type. The properties of the Bulk Insert Task are the following:
DataFileType: DTSBulkInsert_DataFileType_WideNative
RowTerminator: {CR}{LF}
Any help getting the bcp file to load would be appreciated. Let me know if you require any other information, thanks for all your help.
Paul
View 1 Replies
View Related
Jun 29, 2015
I'm trying to use Bulk insert for the first time and getting the following error. I think it might have something to do with my Format File and from the error msg there's a conversion error for the first column. In my database the Field is nvarchar(6) so my best guess is to use SQLNChar for the first column. I've checked the end of each line is CR LF therefore the is correct for line 7 right?
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (ASXCode).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
BULK
INSERTtbl_ASX_Data_temp
FROM
'M:DataASXImportTest.txt'
WITH
(FORMATFILE='M:DataASXSQLFormatImport.Fmt')
[code]...
View 5 Replies
View Related
Jul 31, 2015
I have a Bulk insert that doesn't load but doesn't error,
SET @SQL= 'BULK INSERT dbo.LexisNexis_import_BANKRUPTCY FROM ''' + @ImportFile + ''' WITH (FIRSTROW = 2, FORMATFILE = ''' + @FormatFilePath + ''' )'
EXEC(@SQL)
All columns in the csv are double quoted so I stip them out in a format file.There is data in the source file. Why this Isn't working?
View 4 Replies
View Related
Nov 2, 2015
We try to run a stored procedure in management studio - and we see the following error -Â
"
Executed as user: "Some User". Unspecified error occurred on SQL Server. Connection may have been terminated by the server.Â
[SQLSTATE HY000] (Error 0) Â The log for database 'Some-database' is not available.Â
Check the event log for related error messages.Â
Resolve any errors and restart the database.Â
[SQLSTATE HY000] (Error 9001) Â During undoing of a logged operation in database 'Some-Database', an error occurred at log record ID (2343114:16096:197).Â
Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.Â
[SQLSTATE HY000] (Error 3314) Â During undoing of a logged operation in database 'Some-Database', an error occurred at log record ID (2342777:81708:1).Â
Typically, the specific failure is logged previously as an error in the Windows Event Log service.
Restore the database or file from a backup, or repair the database. [SQLSTATE HY000] (Error 3314). Â The step failed.Â
View 2 Replies
View Related
Nov 18, 2015
I have a fundamental problem with how CDC works for bulk updates.When CDC enabled table is updated for single row - My CDC system tables its recording it as update (3 & 4)  which is perfect and what it should be. No Complains!But when I do a bulk update in the same CDC enabled tables for the same columns - My CDC system tables its recording as delete and then insert (1 & 2). This is not correct and this is what my problem is.  We used triggers before CDC we did not face this problem with triggers every thing was fine with triggers other than performance.The way how the CDC  is handling the bulk update is  a big problem for me because based on the output of CDC system tables we are doing some migration work to legacy system.
It will be impossible  for me to go and change my migration logic scripts because we have 100's or procedures in it.Is it a know problem with CDC? Is there any solution in CDC when a bulk update happens on a table the CDC system tables record it as updates. I don't think CDC 'net changes' in this situation because the net change would show as single inserted row.If this can't be done with CDC then I have to completely abandon CDC and go back to triggers..
View 5 Replies
View Related
Mar 14, 2006
Error Message:
Msg 7202, Level 11, State 2, Procedure LoadConvertsDB, Line 24
Could not find server 'CONVERTSDB' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
T-SQL:
EXEC master.dbo.sp_addlinkedserver @server = N'CONVERTSDB', @srvproduct=N'Access', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'F:Converts.mdb';
Environement:
SQL 2005 Std, Win2000 Pro SP4, same computer. F is the network drive. SQL Log with the Windows NT domain account. F is the network drive.
Symptoms:
1. I can execute the T-SQL in the Query window in the SQL2005 Management Studio without any issue. After that, CONVERTSDB is in the Linked Servers lists.
2. If I put the T-SQL into a stored procedure, it does not work, and generate the above error. (EXEC dbo.LoadConvertsDB)
3. In the same Query window, If the above T-SQL is executed first and then execute the stored procedure, it will succeed. In the stored procedure, only the below T-SQL is before the sp_addlinkedserver.
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @server=N'CONVERTSDB', @droplogins='droplogins';
View 4 Replies
View Related
May 9, 2008
Hello,
Consider the following:
create procedure jason_test
as
bulk insert SCORPIO_STAGE_BULK_DATAPDCC from 'd:BulkTestonmech_stat_apd_clark_credit.dat' with (formatfile = 'd:BulkTestDATAPDCC.fmt')
go
alter procedure jason_test_exec
with execute as 'bulk_insert_test_jcb'
as
bulk insert SCORPIO_STAGE_BULK_DATAPDCC from 'd:BulkTestonmech_stat_apd_clark_credit.dat' with (formatfile = 'd:BulkTestDATAPDCC.fmt')
go
Then, log into SQL Server via management stuido as the SQL user "bulk_insert_test_jcb" this user has server-level bulk admin rights and execute rights on both of these stored procs:
exec jason_test
This works
exec jason_test_exec
gives:
Msg 4834, Level 16, State 1, Procedure jason_test_exec, Line 4
You do not have permission to use the bulk load statement.
Can you help me with this? Why is the user prevented from running this bulk insert inside the stored proc with "execute as" ? The profiler trace from both of these stored procs have identical results for the SP: StmtStarting event.
Thanks!
Jason
View 4 Replies
View Related
Jan 17, 2008
Im having some issues with bulk insert.
This is the table:
CREATE TABLE [dbo].[tmp_GA_status](
[GA_recno] [int] NOT NULL,
[GA_desc] [varchar](40) NULL
)
This is the file (unicode):
1|"test1"
2|"test2"
3|"test3"
4|"test4"
5|"test5"
6|"test6"
7|"test7"
8|"test8"
and this is the sql:
bulk insert tmp_GA_status from 'C: empTextDumpGA_status.dta'
with (CODEPAGE='RAW', FIELDTERMINATOR='|', ROWTERMINATOR='
', DATAFILETYPE='widechar')
so yeah, pretty simple. But whatever I do I get this;
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (GA_desc).
So what am I doing wrong ?
View 13 Replies
View Related
Aug 29, 2007
We would like to use the bulk insert function to import large CSV files into a SSE database however we have serious concerns regarding giving all our users these high privleges. Is there some way around this can we give them the privleges temporarily do the insert and take it away again or some other solution.
View 5 Replies
View Related
Jun 21, 2006
I am trying to save user data to a sql table but keep getting the following error;
Unable to cast object of type 'System.Boolean' to type 'System.Data.SqlClient.SqlParameter'.
I am using VWD Express Edition with .NET 2.0 and a SQL 2000 database. The code that generates the error is as follows;
Public Function chkUser(ByVal strUser As String) As Stringdim MyConnection As SqlConnectionDim MyCommand As SqlCommandDim ReturnString As StringDim params As SqlParameterDim SelectCmd As String = "wm_CheckUser"MyConnection = New SqlConnection(myConnectionString)MyCommand = New SqlCommand(SelectCmd, MyConnection)MyCommand.CommandType = CommandType.StoredProcedure
Tryparams = MyCommand.Parameters.Add("@parUser", SqlDbType.VarChar, 100).Value = strUserMyCommand.Connection.Open()MyCommand.ExecuteNonQuery()ReturnString = "Record Exists!"Catch Exp As SqlExceptionReturnString = ReturnError(Exp.Number, "Users", Exp.Message)End TryMyCommand.Connection.Close()Return ReturnString
End Function
Basically, I'm traying to check to see if a user id already exists in the database before saving the data the user entered. If the email address entered by the user is already in the database I want a message to be shown to the user. If the email address does not exist then the data entered by the user is saved and the form goes to step two (2) of the user registration process.
Any help with this would be greately appreciated. I can't seem to see what is wrong here. Please someone help.
Thanks,
Jaime
View 7 Replies
View Related
Jul 21, 2004
Hi, guys
I try to add some error check and transaction and rollback function on my insert stored procedure but I have an error "Error converting data type varchar to smalldatatime" if i don't use /*error check*/ code, everything went well and insert a row into contract table.
could you correct my code, if you know what is the problem?
thanks
My contract table DDL:
************************************************** ***
create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);
My insert stored procedure is:
************************************************** *****
create proc sp_insert_new_contract
( @contractDate[smalldatetime],
@tuition [money],
@studentId[char](4),
@contactId[int])
as
if not exists (select studentid
from student
where studentid = @studentId)
begin
print 'studentid is not a valid id'
return -1
end
if not exists (select contactId
from contact
where contactId = @contactId)
begin
print 'contactid is not a valid id'
return -1
end
begin transaction
insert into contract
([contractDate],
[tuition],
[studentId],
[contactId])
values
(@contractDate,
@tuition,
@studentId,
@contactId)
/*Error Check */
if @@error !=0 or @@rowcount !=1
begin
rollback transaction
print ‘Insert is failed’
return -1
end
print ’New contract has been added’
commit transaction
return 0
go
View 1 Replies
View Related
Apr 18, 2008
Hello,
I'm just learning SSIS and I've hit my first bump. I am doing a bulk import from a tab delimited text file to an empty sql table that has a Idendity column defined. How do I tell the bulk insert task to skip that column when inserting from the text file. If I remove the identity column it imports the data fine, but I want to create the indentity column in the table too.
Thanks.
View 8 Replies
View Related
May 17, 2008
Hello,
Please consider the following:
CREATE procedure [dbo].[jason_test]
with execute as 'bulk_insert_test_jcb'
as
exec('bulk insert SCORPIO_STAGE_BULK_DATAPDCC from ''\shodbs29CDRDataonmech_stat_apd_clark_credit.dat'' with (formatfile = ''\dixdbs01ScorpioBulkDATAPDCC.fmt'')')
This is a stored proc with execute as a SQL user. It runs one bulk insert. The user bulk_insert_test_jcb does have BulkAdmin rights and if the user is logged in directly to the server, this works fine. If a SQL user is logged in and runs it (a user other than bulk_insert_test_jcb), this also works
However, if I run this as a windows user logged into the server
alter database stage_scorpio_bulk_jcb set trustworthy off
exec jason_test
--Msg 4834, Level 16, State 4, Procedure jason_test, Line 4
--You do not have permission to use the bulk load statement.
I expect this because the server-level permissions (bulk) are stripped off unless the database is trustworthy, so...
alter database stage_scorpio_bulk_jcb set trustworthy on
exec jason_test
--Msg 4861, Level 16, State 1, Procedure jason_test, Line 4
--Cannot bulk load because the file "\shodbs29CDRDataonmech_stat_apd_clark_credit.dat" could not be opened. Operating system error code 5(Access is denied.).
Why does this happen? I thought that, since I'm executing as a SQL user, SQL Server would authenticate over to the server with the datafiles as the service account, but I see the following in the log at SHODBS29
--User Logoff:
-- User Name: ANONYMOUS LOGON
-- Domain: NT AUTHORITY
-- Logon ID: (0x0,0x4C99BD2F)
-- Logon Type: 3
--
--
--For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Any ideas? It seems as if it is still trying to used windows authentication even though the stored proc is supposed to execute as a SQL user.
Someone in another forum said that ownership chaining not being allowed for bulk operations was the problem, but I don't think so, since if I put an "execute as user='bulk_insert_test_jcb'" into the exec string, it still fails with the same issue.
Thanks!
Jason
View 6 Replies
View Related
Oct 8, 1999
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 1 Replies
View Related
Jan 9, 2007
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 1 Replies
View Related
Sep 6, 2007
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 11 Replies
View Related
Feb 28, 2008
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 4 Replies
View Related
Jun 5, 2006
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 5 Replies
View Related
Apr 30, 2007
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 4 Replies
View Related
Jun 26, 2006
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 6 Replies
View Related
Mar 25, 2004
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 1 Replies
View Related
Sep 13, 2007
Hi all,
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
Please advise..
Thank You
View 3 Replies
View Related
Nov 4, 2014
I passed .net datatable from a .net app to a store procedure. From this store procedure, how to code to bulk insert (or another way) to SQL table?
View 7 Replies
View Related
Apr 13, 2000
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 1 Replies
View Related
Oct 23, 2000
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 1 Replies
View Related
May 26, 2004
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 8 Replies
View Related
Oct 30, 2014
I use code below to upload a csv file to SQL but got an error said that
Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "C:Test.csv" does not exist.
BULK
INSERT Test
FROM 'C:Test.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
View 2 Replies
View Related
Feb 19, 2008
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 1 Replies
View Related
Dec 2, 2005
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 1 Replies
View Related
