SQL Server Bulk Insert error when calling from .NET SqlCommand

Question

I have a stored procedure that performs bulk insertion into a SQL Server 2005 database. When I call this stored procedure from some SQL (passing the name of the local format file and data file), it works fine. Everytime.

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

When a failure is created SqlException, indicating:

Impossible in bulk. Invalid column number in file format "c: \ bulkinsert \ MyFile.fmt"

I do not think this error message is correct.

Has anyone encountered similar issues with calling bulk insert from code?

Thanks.

Answer 1

? ( ) , "c: \" "c: \" "c: \" .

. #, - SqlBulkCopy. , ( ).

csv/tsv/similar, CsvReader. IDataReader, WriteToServer .

Answer 2

, . , , 100% . SQL.

Declare @Sql Nvarchar(2000); 

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

Exec sp_ExecuteSql @Sql;

Answer 3

using

Exec sp_ExecuteSql @Sql; (100% work)

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