3

I have a stored procedure which does bulk insert on a SQL server 2005 database. When I call this stored procedure from some SQL (passing in the name of a local format file and data file) it works fine. Every time.

However, when this same stored procedure gets called from C# .NET 3.5 code using SqlCommand.ExecuteNonQuery it works intermittently.

When it fails a SqlException is generated stating:

Cannot bulk load. Invalid column number in the format file "c:\bulkinsert\MyFile.fmt"

I don't think this error message is correct.

Has anyone experienced similar problems with calling bulk insert from code?

Thanks.

Improve this question

3 Answers 3

Reset to default
1

How are you doing the bulk insert? Usually, the problem (in this scenario) is whether "c:\" is the server's "c:\", or the client's "c:\".

However. from C# code, the simplest approach is to use SqlBulkCopy. This class provides direct access to bulk-insert functionality from managed code, including mappings (although I never bother with them).

If the file is something like csv / tsv / similar, then CsvReader is highly recommended. This provides the IDataReader interface that WriteToServer uses most efficiently.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

I think the problem was todo with the format file. I am no longer using the format file and it seems to work 100% of the time now. I specify field and row terminators in the SQL instead.

Declare @Sql Nvarchar(2000); 

SET @Sql = 
'Bulk Insert TestOutputPretest
From  ''c:\rawdata\bulkinsert\Segment1839204.dat''
 WITH 
      (
         FIELDTERMINATOR ='','',
         ROWTERMINATOR = ''\n''
      )'

Exec sp_ExecuteSql @Sql;
Improve this answer

Comments

0

use

Exec sp_ExecuteSql @Sql; (100% working)

exec(@sql) is not very powerful as it has some limitations

Improve this answer

1 Comment

Please don't add signatures to your post. They're considered noise and will usually be edited out.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.