Problem generating error file using BULK INSERT or BCP thru xp_cmdshell.

BCP thru xp_cmdshell from stored procedure:

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 1;

RECONFIGURE

EXEC xp_cmdshell 'bcp database.dbo.table in c:\scheduled.csv -S SERVER\SQLEXPRESS -T -t, -r\n -c -e "error.txt"';

This is returning the following error code. I even tried placing the command in a seperate command file and calling that with no success. If I run this from the command line the error file generation does work.

=================================================================

SQLState = HY000, NativeError = 0

Error = [Microsoft][SQL Native Client]Unable to open BCP error-file

=================================================================

Error message when using BULK INSERT as follows:

BULK INSERT database.dbo.table from 'c:\unscheduled.csv' with

(FIELDTERMINATOR = ',', ERRORFILE = 'c:\error.txt');

Returns the following error message:

=================================================================

Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9

Cannot bulk load because the file "c:\error.txt" could not be opened. Operating system error code 80(The file exists.).

Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9

Cannot bulk load because the file "c:\error.txt.Error.Txt" could not be opened. Operating system error code 80(The file exists.).

=================================================================

The Bulk Insert actually creates a empty error.txt file (0kb) and never preforms the insert, I can not find any examples of anyone using the -ERRORFILE switch on BULK INSERT. Prolly some default security setting to allow file creation/modification I am missing. Anyone help me out? Thanks.

EDIT: SQL SERVER EXPRESS 2005 - WINXP PRO SP2

bcp or bulk insert will not overwrite or append to the error file. If you get an error the first time, and want to rerun your command, you need to either delete these files, or specify a new location for the error file.

|||

Unfourtunetly I am deleting the file, I'm still doing so by hand in testing.

On bcp thru xp_cmdshell it does not generate an errorfile at all (this actually works just fine from the command line just not thru a stored procedure), with bulk insert it generates a error file which is completely blank (even tho I know there is 6 rows that cannot be imported) of size 0kb, so totally empty. Also it does not actually execute the insert.

Most likely it is a security setting. I can not find it however. Another possiblity is maybe I disabled a required service.

Anyways, thanks for the reply.

Running SQL Express 2005 on WINXP PRO SP2.

|||

Does the service account which SQL Server is running under have write permissions to the disk?

According to the bulk insert error message, it does (your message says the file exists), but you say in the next post you're deleting it?

|||

Ah your the bomb.

SQLExpress service was running under network authority not local system account. I switched it to local system and now the bulk import with errorfile switch is working from sqlcmd. Write permissions or something, anyways I know where to look now, thanks!

Problem converting C application from using DBLIB bcp to using ODBC bcp.

I have an application that was converted from using DBLIB bcp to using ODBC bcp. All other field/data types bind are updated correctly, but dates put in the date

1753-01-01 00:00:00.000 when it should be NULL. The variable is a char [24] and the first item is set to \0 ( dbLoanBankruptcy.MotionforReliefReqDate[0] = NullChar; ).

Is there any example or way to put the date in as null when the variable is null and an actual date as needed?

bcp_bind(LoanBankruptcyDBPPtr, (BYTE *)&dbLoanBankruptcy.MotionforReliefReqDate, 0, 23, NULL, 0, SQLCHARACTER, 13)

Thank you,

Joel

Following site maybe helpful:

http://msdn2.microsoft.com/en-us/library/aa198011(sql.80).aspx

|||The link above only discusses dates enough to get an actual date into a table, but does not discuss how to get a NULL date stored. Is there a way to save <NULL>?

Would it be wrong to use the bcp_bind statement you needed for each row/record and re-bind each time? As in...

if(dbLoanBankruptcy.MotionforReliefFiledDate[0]==NullChar) {
if (bcp_bind(LoanBankruptcyDBPPtr, (BYTE *)&dbLoanBankruptcy.MotionforReliefFiledDate,0, SQL_NULL_DATA, NULL, 0, SQLVARCHAR, 14) == FAIL) LogBindError("LoanBankruptcy", "MotionforReliefFiledDate",LoanBankruptcyDBPPtr);
}else{
if (bcp_bind(LoanBankruptcyDBPPtr, (BYTE *)&dbLoanBankruptcy.MotionforReliefFiledDate,0, 23, NULL, 0, SQLVARCHAR, 14) == FAIL) LogBindError("LoanBankruptcy", "MotionforReliefFiledDate",LoanBankruptcyDBPPtr);
}

|||

You don't need to rebind each time

http://msdn2.microsoft.com/en-gb/library/aa177853(SQL.80).aspx

struct

{

int iIndicator;

char szDatetime[30];

} urBCPData;

if (bcp_bind(om_hDbc2, (LPCBYTE) &urBCPData, 4, SQL_VARLEN_DATA , NULL, 0,SQLVARCHAR, 1) == FAIL)

{

// Raise error and return.

return;

}

// Insert NULL

urBCPData.iIndicator = SQL_NULL_DATA;

if (bcp_sendrow(om_hDbc2) == FAIL)

{

// Raise error and return.

return;

}

// Insert 1753-01-01 00:00:00.000

urBCPData.iIndicator = 23;

strcpy(urBCPData.szDatetime, "1753-01-01 00:00:00.000");

if (bcp_sendrow(om_hDbc2) == FAIL)

{

// Raise error and return.

ShowSQLError(om_hDbc2, om_hStmt2);

return;

}

|||Tested and works... thanks.

The normal bind statement but the data portion is now (byte *)&data.datetime_struct

Code Snippet

if(dbLoanBankruptcy.MotionforReliefReqDate[0]==NullChar) {
dbLoanBankruptcy.bounddate.iIndicator = SQL_NULL_DATA;
}else{
dbLoanBankruptcy.bounddate.iIndicator = 24;
strcpy(dbLoanBankruptcy.bounddate.iValue, dbLoanBankruptcy.MotionforReliefReqDate);
}