Sql-server – How to troubleshoot “BCP copy in failed”

What is up with FROM part JOIN model ON 1=1? This the same as FROM part, model, which is a cartesian join and will result in a very large number of rows. Is that join supposed to be like that?

You will likely help us help you if you provide details about the tables involved. Please "script" the definition of the tables, along with any indexes defined on those tables.

This sounds like a classic case of parameter sniffing resulting in good plan/bad plan choices for various scenarios in your data.

You may be able to get more reliable performance by making SQL Server cache different plans for different scenarios by using sp_executesql, as in the following example:

CREATE PROCEDURE [dbo].[create_grid_materials2] 
(
    @partlistid bigint
    , @pid bigint
    , @masterid bigint
)
AS
BEGIN
    begin
        DECLARE @cmd NVARCHAR(MAX);

        SET @cmd = '   
        INSERT INTO material (partid, personid, modelID)
        SELECT 
            partid = part.id
            , personid = @pid
            , modelid = model.id  
        FROM part
            INNER JOIN model ON 1=1
        WHERE (
            model.masterid = ' + CONVERT(NVARCHAR(50), @masterid) + ' 
                AND model.modelSetID IS NULL
                AND part.partlistid = ' + CONVERT(NVARCHAR(50), @partlistid) + '
                AND (
                    part.partType = 100 
                    or part.partType=120 
                    or part.partType = 130
                )
            )
            AND NOT EXISTS (
                SELECT 1 
                FROM material AS a1 
                WHERE a1.partid = part.id 
                    AND a1.personid=@pid 
                    AND a1.modelid=model.id
                )';
        DECLARE @Params VARCHAR(200);
        SET @Params = '@pid INT';
        EXEC sys.sp_executesql @cmd
            , @Params
            , @pid = @pid;
    end
End

The above code will cause a new plan to be generated for each combination of @partlistid, and @masterid.

The presumption here is some combinations of those two variables lead to a very small number of rows, whereas some combinations lead to a very large number of rows.

Forcing a plan for each combination allows SQL Server to generate more efficient plans for each. I've explicitly not included @pid since you probably want to try it with a fairly small number of combinations first; adding a third variable to the mix will make for an exponentially larger number of possible plans.

The problem with using UNION to include the column names is that column names are strings but the fields in the unioned query are not necessarily strings (as you discovered with that error posted in the Question). And you cannot convert names into other types such as INT, DECIMAL, MONEY, DATETIME, etc.

I did not have to provide any cast/convert when I used the initial syntax.

That is because BCP.exe was converting each non-string field of the table (or result set) into a string so that it could be placed into the output file. But when you UNION two or more queries together, you need to ensure that each position within each SELECT matches that same position in all the other SELECTs so that a result set can be built.

Hence you are left with a few options:

1. Use UNION (similar to what you attempted), but then use CONVERT on the columns in the table that are not strings. For example (assuming that abc1, abc3, and abc4 are VARCHAR / NVARCHAR while acb2 is DATETIME, and abc5 is INT):

   UNION ALL SELECT [abc1], CONVERT(NVARCHAR(50), [abc2], 121), [abc3],
   [abc4], CONVERT(NVARCHAR(200), [abc5]) FROM test
   

   The downside here is that you cannot use the simple syntax of just specifying the table name.

2. Create a text file of the header row as a separate step, then execute BCP, then concatenate the two files together. I show two examples of doing that in the following DBA.StackExchange answer: Insert custom header row in BCP output

3. Use .NET (SQLCLR or Console App; or PowerShell might even work) to execute the query and write the results to a text file. When using SqlCommand.ExecuteDataReaderto run the query, the returnedSqlDataReader` object will have the result set fields (names, datatypes, etc). You can use GetSchemaTable to get the result set field info.

   If this seems like a good option but you either can't code this or don't have the time and/or desire to, I created a SQLCLR Stored Procedure that does exactly this, called DB_BulkExport, and is included in the Full version (this is not available in the Free version) of SQL#. Sometime later this year there will be a stand-alone Console App version of this export, with additional features :-).