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.
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?
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)".
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
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
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')
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.Â
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..
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.
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';
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.
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.
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
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
/*Error Check */ if @@error !=0 or @@rowcount !=1 begin rollback transaction print ‘Insert is failed’ return -1 end print ’New contract has been added’
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.
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.
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.
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)".
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' )
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?
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.
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?
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?
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?
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.
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
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
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.
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
